Adding datafile on standby database Manually.

Adding datafile on standby database Manually.

firstly we add datafile on primary server.

SQL> alter tablespace swati_ARCHIVE add datafile '/crd04/oracle/joshi/data/swati_ARCHIVE10.dbf'size 1G;

Tablespace altered.

2-But file _mangement is not auto  in standby,so we need add in manually.
3connect to rman

[oracle@nano ~]$ rman target/

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Jan 4 02:17:24 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: joshi (DBID=382866142, not open)

4. RMAN> report schema;

using target database control file instead of recovery catalog
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema

48   1024     PART102              ***     /crd04/oracle/joshi_DG/data/part_v102_2.dbf
49   1024     PART102              ***     /crd03/oracle/joshi_DG/data/part_v102_2.dbf
50   0        swati_ARCHIVE       ***     /oracle/product/10.2.0/db_1/dbs/UNNAMED00050
51   4096     swati_ARCHIVE       ***     /crd03/oracle/joshi_DG/data/swati_ARCHIVE008.dbf
52   4096     OUTLN                ***     /crd04/oracle/joshi_DG/data/OUTLN001.dbf
53   2048     UNDOTBS1             ***     /crd02/oracle/joshi_DG/data/undotbs07.dbf


5.Then you can rename the datafile to what you like with the following SQL:

SQL> alter database create datafile
'/oracle/testdev/product/10.2.0.1/dbs/UNNAMED00012'
as
'/oradata/testdev/dbs/ucsc_dev01.dbf';

6.At last restart mrp process.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Precaution for adding datafile in standby environment.

Precaution for adding datafile in standby environment.

1. we have sufficient space on mount point.

2.New datafile name is unique.

3.If database is associated with standby database make sure standby file management is auto.

4And we have proper setting of db file name convert.

5.we have sufficient space on standby also.

6. After adding datafile on primary server we need to make sure it has been added on standby database also.

7.we can check this detail via report schema and v$datafile.

How to restore a NOARCHIVELOG database using a backup controlfile

How to restore a NOARCHIVELOG database using a backup controlfile

Firstly we change archive destination in noarchive mode.

shutdown the database;

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

2. startup mount

SQL> startup mount

ORACLE instance started.

SQL> alter database noarchivelog;

Database altered.

3. connect to rman ;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

using target database control file instead of recovery catalog

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

4- shutdown the database

RMAN> shutdown immediate;

database closed

database dismounted

Oracle instance shut down

5- Startup Mount

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area    2147483648 bytes

Fixed Size                     2085320 bytes

Variable Size                318770744 bytes

Database Buffers            1811939328 bytes

Redo Buffers                  14688256 bytes

6- Take the full Backup of the database

run

{

allocate channel d1 device type disk format '/backup/test/test_1/%U.bkp';

allocate channel d2 device type disk format '/backup/test/test_1/%U.bkp';

allocate channel d3 device type disk format '/backup/test/test_1/%U.bkp';

allocate channel d4 device type disk format '/backup/test/test_1/%U.bkp';

backup database include current controlfile ;

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

7-And then removed all my datafiles, redolog and current controlfile [oracle@localhost orcl]$

 ls 

APEX_1930613455248703.dbf  example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf 

users01.dbf 

control01.ctl              redo01.log     redo03.log  system01.dbf 

undotbs01.dbf 

[oracle@localhost orcl]$ rm *

8-connect to rman/

startup nomount

RMAN> startup nomount

connected to target database (not started)

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     2085320 bytes

Variable Size                318770744 bytes

Database Buffers            1811939328 bytes

Redo Buffers                  14688256 bytes

9-Restore controlfile

RMAN> restore controlfile from autobackup;

Starting restore at 03-JAN-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=101 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20150103

channel ORA_DISK_1: autobackup found: c-3233538786-20150103-00

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/crd01/standby/control01.ctl

output filename=/crd01/standby/control02.ctl

output filename=/crd01/standby/control03.ctl

output filename=/crd01/standby/control04.ctl

output filename=/crd01/standby/control05.ctl

output filename=/crd01/standby/control06.ctl

Finished restore at 03-JAN-15

10-RMAN> alter database mount;

using target database control file instead of recovery catalog

database mounted

11-connect to the db sqlplus / as sysdba 

and check control file status

SQL> select controlfile_type from V$DTABASE; 

CONTROL 

------- 

BACKUP

12- From the rman restore the database .

RMAN> restore database;

Starting restore at 03-JAN-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=98 devtype=DISK

skipping datafile 9; already restored to file /crd01/oracle/test/users_o1.dbf

skipping datafile 10; already restored to file /crd01/oracle/test/joshi_o1.dbf

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /crd01/standby/data/system01.dbf

restoring datafile 00003 to /crd01/standby/data/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /backup/test/test_1/11protks_1_1.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/backup/test/test_1/11protks_1_1.bkp tag=TAG20150103T040547

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to /crd01/standby/data/undo01.dbf

restoring datafile 00007 to /crd01/standby/data/system04.dbf

restoring datafile 00008 to /crd01/standby/data/tmp.dbf

channel ORA_DISK_1: reading from backup piece /backup/test/test_1/12protks_1_1.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/backup/test/test_1/12protks_1_1.bkp tag=TAG20150103T040547

channel ORA_DISK_1: restore complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to /crd01/standby/data/users.dbf

restoring datafile 00006 to /crd01/standby/data/users02.dbf

channel ORA_DISK_1: reading from backup piece /backup/test/test_1/10protks_1_1.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/backup/test/test_1/10protks_1_1.bkp tag=TAG20150103T040547

channel ORA_DISK_1: restore complete, elapsed time: 00:01:15

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00005 to /crd01/standby/data/users01.dbf

channel ORA_DISK_1: reading from backup piece /backup/test/test_1/0vprotks_1_1.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/backup/test/test_1/0vprotks_1_1.bkp tag=TAG20150103T040547

channel ORA_DISK_1: restore complete, elapsed time: 00:01:05

Finished restore at 03-JAN-15

13-Finally open your database with the resetlogs option :

RMAN> alter database open resetlogs;

database opened