The SHOW command is used to display the values of current RMAN configuration settings.

RMAN> show all;

Shows all parameters.

RMAN> show archivelog backup copies;

 Shows the number of archivelog backup copies.

RMAN> show archivelog deletion policy;

Shows the archivelog deletion policy.

RMAN> show auxname;

Shows the auxiliary database information.

RMAN> show backup optimization;

Shows whether optimization is on or off.

RMAN> show  auxiliary channel;

Shows how the normal channel and auxiliary hannel are configured.

RMAN> show controlfile autobackup;

Shows whether autobackup is on or off.

RMAN> show controlfile autobackup format;

Shows the format of the autobackup control file.

RMAN> show datafile backup copies;

Shows the number of datafile backup copies being ept.

RMAN> show default device type;

Shows the default type disk or tape.

RMAN> show encryption algorithm;

Shows the encryption algorithm currently in use.

RMAN> show encryption for database;

 Shows the encryption for the database.

RMAN> show encryption for tablespace;

 Shows the encryption for the tablespace.

RMAN> show exclude;

 Shows the tablespaces excluded from the backup.

RMAN> show maxsetsize;

 Shows the maximum size for backup sets. The default value is unlimited.

RMAN> show retention policy;

 Shows the policy for datafile and control file backups and copies that RMAN marks as obsolete.

RMAN> show snapshot controlfile name;

 Shows the snapshot control filename.

Note: You can see any nondefault RMAN configured settings in the V$RMAN_CONFIGURATION database view.

ORA-19815: WARNING: db_recovery_file_dest_size of bytes is 100.00% used, and has 0 remaining bytes availableWhy the above error messages1. Flash Recovery Area Full2. How to Free space when FRA is Full3. May be your disk will have free space but still you get the above error messages due to manually deleting archive logs in FRA.
Solution:
SQL> select * from v$flash_recovery_area_usage;
SQL> select * from v$recovery_file_dest;
Step 1. Delete unwanted archive log files from disk ( rm/del)

Step 2. Connect to RMAN (rman target / nocatalog)

Step 3. rman> crosscheck archivelog all;

The above command will marks the control file that the archives have been deleted.

Step 4. rman> delete expired archivelog all;

The above command will deletes the log entries identified above.

Best option is using RMAN with nocatalog and remove the old archive logs if not required

$ rman nocatalog /

RMAN>delete archivelog all completed before ‘sysdate -3′;

The PREVIEW option of the RESTORE command allows you to identify the backups required to complete a specific restore operation. The output generated by the command is in the same format as the LIST command. In addition the PREVIEW SUMMARY command can be used to produce a summary report with the same format as the LIST SUMMARY command. The following examples show how these commands are used:

# Spool output to a log file
SPOOL LOG TO c:\oracle\rmancmd\restorepreview.lst;

# Show what files will be used to restore the SYSTEM tablespace’s datafile
RESTORE DATAFILE 2 PREVIEW;

# Show what files will be used to restore a specific tablespace
RESTORE TABLESPACE users PREVIEW;

# Show a summary for a full database restore
RESTORE DATABASE PREVIEW SUMMARY;

# Close the log file
SPOOL LOG OFF;

Migrate from 32 bit to 64 bit using RMAN

I have Checked it on 10g and confirmed it is working. You can use it from 9i to 11g.

Step 1 – Take a Backup on Source 32 Bit Server:
Create a full backup of the database including all archivelogs:
RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database plus archivelog;
backup current controlfile; }
Step 2 : Preparing target 64 bit server:
Install the OS and Oracle Software.
Step 3 : Copy RMAN backup files from Source Server:
Use simple copy command to copy the rman backup files from source server to target server.
Step 4 : Create the pfile or copy the pfile from source server:
Create the pfile or copy the pfile from source server.
Step 5 : Bring up the database
Startup nomount the new instance.
Connect to rman and set the dbid:
$ rman target / nocatalog RMAN> set dbid=;
Restore the controlfile:
RMAN> restore controlfile from /u01/backup_32bit/;
RMAN> alter database mount;
RMAN> restore database;
Identify the archivelog till you want to do the recovery and check if you have all the backups you need to do it and run:
RMAN> run {
set until sequence xxxx;
recover database;
}
RMAN> exit
Step 6 : Migrate to 64Bit:
SQL> recover database until cancel using backup controlfile;
cancel
SQL> alter database open resetlogs migrate;
SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> shutdown immediate;
SQL> startup

What the command is to recatalog all of the files in the flash recovery area back into to the RMAN catalog

CATALOG RECOVERY AREA;

1.1. Where should the catalog be created?

The recovery catalog to be used by rman should be created in a seperate database other than the target database. The reason been that the target database will be shutdown while datafiles are restored.

1.2. How do I create a catalog for rman?

First create the user rman.

CREATE USER rman IDENTIFIED BY rman
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE tools
QUOTA UNLIMITED ON tools;

GRANT connect, resource, recovery_catalog_owner TO rman;
exit

Then create the recovery catalog:-

rman catalog=rman/rman
create catalog tablespace tools;
exit

Then register the database

oracle@debian:~$ rman target=/ catalog=rman/rman@newdb

Recovery Manager: Release 10.1.0.2.0 – Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to target database: TEST (DBID=1843143191)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Note
If you try rman catalog=rman/rman and try to register the database it will not work.

Note
We have 2 Databases here 1 is newdb which is solely for catalog and the other is TEST which is our database on which we want to perform all rman operations.

1.3. How many times does oracle ask before dropping a catalog?

The default is two times one for the actual command, the other for confirmation.

1.4. How to view the current defaults for the database.

rman> show all;

RMAN> show all
2> ;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u02/app/oracle/product/10.1.0/db_1/dbs/snapcf_test.f’; # default

1.5. Backup the database.

RMAN> run{
configure retention policy to recovery window of 2 days;
backup database plus archivelog;
delete noprompt obsolete;
}
tarting backup at 04-JUL-05
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=256 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
……………

1.6. How to resolve the ora-19804 error

Basically this error is because of flash recovery area been full. One way to solve is to increase the space available for flashback database.

sql>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G; –It can be set to K,M or G.
rman>backup database;
……………….
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 04-JUL-05
channel ORA_DISK_1: finished piece 1 at 04-JUL-05
piece handle=/u02/app/oracle/flash_recovery_area/TEST/backupset/2005_07_04/o1_mf_ncsnf_TAG20050704T205840_1dmy15cr_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 04-JUL-05

Oracle Flashback

After taking a back up resync the database.

Restoring the whole database.

run {
shutdown immediate;
startup mount;
restore database;
recover database;
alter database open;
}

1.7. What are the various reports available with RMAN

rman>list backup; rman> list archive;

1.8. What does backup incremental level=0 database do?

Backup database level=0 is a full backup of the database. rman>>backup incremental level=0 database;

You can also use backup full database; which means the same thing as level=0;

1.9. What is the difference between DELETE INPUT and DELETE ALL command in backup?

Generally speaking LOG_ARCHIVE_DEST_n points to two disk drive locations where we archive the files, when a command is issued through rman to backup archivelogs it uses one of the location to backup the data. When we specify delete input the location which was backedup will get deleted, if we specify delete all all log_archive_dest_n will get deleted.

DELETE all applies only to archived logs. delete expired archivelog all;

Chapter 2. Recovery

Recovery involves placing the datafiles in the appropriate state for the type of recovery you are performing. If recovering all datafiles, then mount the database, if recovering a single tablespace or datafile then you can keep the database open and take the tablespace or datafile offline. Perform the required recovery and put them back online.

Put the commands in a rman script .rcv file such as myrman.rcv

run
{
# shutdown immediate; # use abort if this fails
startup mount;
#SET UNTIL TIME ‘Nov 15 2001 09:00:00′;
# SET UNTIL SCN 1000; # alternatively, you can specify SCN
SET UNTIL SEQUENCE 9923; # alternatively, you can specify log sequence number
restore database;
recover database;
alter database open;
}

Run the myrman.rcv file as :- rman target / @myrman.rcv

After successful restore & recovery immediately backup your database, because the database is in a new incarnation.

ALTER DATABASE open resetlogs; –command creates a new incarnation of the database — database with a new stream of sequence numbers starting with sequence 1.

Before running RESETLOGS it is a good practice to open the database in read only mode and examining the data contents.

2.1. Simulating media failure.

2.1.1. How to simulate media failure and recover a tablespace in the database ?
2.1.2. What is the difference between alter database recover and sql*plus recover command?

2.1.1. How to simulate media failure and recover a tablespace in the database ?

Firstly create the table in the required tablespace.

CREATE TABLE mytest ( id number(10));

Then insert into the table mytest values(100); execute the insert statement a couple of times but do not commit the results.

Take the tablespace offline, this is possible only if the database is in archivelog mode.

now commit the transaction. by issuing commit.

Now try to bring the tablespace online, at this point you will get the error that datafile 4 needs media recovery.

issue the following command to recover the tablespace, please note that the database itself can remain open.

SQL>recover tablespace users;
media recovery completed.

now bring the tablespace online.

SQL>alter tablespace users online;

2.1.2. What is the difference between alter database recover and sql*plus recover command?

ALTER DATABASE recover is useful when you as a user want to control the recovery. SQL*PLUS recover command is useful when we prefer automated recovery.

Chapter 3. Duplicate database with control file

What are the steps required to duplicate a database with control file?

Copy initSID.ora to the new initXXX.ora file. i.e.,

cp $ORACLE_HOME/dbs/inittest.ora $ORACLE_HOME/dbs/initDUP.ora

Edit the parameters that are specific to location and instance:-

db_name = dup
instance_name = dup
control_files = change the location to point to dup
background_dump_dest = change the location to point to dup/bdump
core_dump_dest = change the location to point to dup/cdump
user_dump_dest = change the location to point to dup/udump
log_archive_dest_1 = dup/archive
db_file_name_convert = (test, dup)
log_file_name_convert = (test, dup)
remote_login_passwordfile = exclusive

Actual settings :-

*.background_dump_dest=’/u02/app/oracle/admin/DUP/bdump’
*.compatible=’10.1.0.2.0′
*.control_files=’/u02/app/oracle/oradata/DUP/control01.ctl’,'/u02/app/oracle/oradata/DUP/control02.ctl’,'/u02/app/oracle/oradata/DUP/control03.ctl’
*.core_dump_dest=’/u02/app/oracle/admin/DUP/cdump’
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’DUP’
*.db_recovery_file_dest=’/u02/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=DUPXDB)’
*.java_pool_size=50331648
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1=’LOCATION=/u02/app/oracle/oradata/payroll MANDATORY’
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=250
*.shared_pool_size=99614720
*.sort_area_size=65536
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u02/app/oracle/admin/DUP/udump’
*.remote_login_passwordfile=exclusive
*.db_file_name_convert = (test, dup)
*.log_file_name_convert =(test,dup)

Make the directories for the dump destination:-

oracle@debian:/u02/app/oracle/admin/DUP$ mkdir bdump
oracle@debian:/u02/app/oracle/admin/DUP$ mkdir cdump
oracle@debian:/u02/app/oracle/admin/DUP$ mkdir udump

Make a directory to hold control files, datafiles and such:-

oracle@debian:/u02/app/oracle/oradata/PRD$ cd ..
oracle@debian:/u02/app/oracle/oradata$ mkdir DUP

Ensure that the oracle sid is pointing to the right Database. Make an ora password file so that other users can connect too.

export ORACLE_SID=DUP
$orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=easypass
sqlplus / as sysdba
sql>startup nomount;

oracle@debian:/u02/app/oracle/product/10.1.0/db_1/dbs$ sqlplus / as sysdba;

SQL*Plus: Release 10.1.0.2.0 – Production on Wed Aug 24 21:05:26 2005

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 188743680 bytes
Fixed Size 778036 bytes
Variable Size 162537676 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
SQL>

Check net8 connectivity sqlplus sys/easypass@dup if that goes through successfully then exit. The idea is to check for sql*net connectivity.

if you get ORA-12154: TNS:could not resolve the connect identifier specified then more work needs to be done.
DUP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = debian)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = dup)
)

$tnsping dup
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = debian)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = dup)))
OK (0 msec)

Even with this if you are getting ORA-12528: TNS:listener: all appropriate instances are blocking new connections then we have to connect to the auxiliary (the database to be duplicated as / ) and the target database ( source ) with user/pass@test
start duplicating the database. export ORACLE_SID=DUP

rman target sys/easypass@test auxiliary /
run{
allocate auxiliary channel ch1 type disk;
duplicate target database to dup;
}

oracle@debian:/u02/app/oracle/product/10.1.0/db_1/network/admin$ rman target sys/kernel@test auxiliary /

Recovery Manager: Release 10.1.0.2.0 – Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied

The work around to this is to create a user with dba privileges and connect through that users id .

$export ORACLE_SID=test
SQL>grant sysdba to mhg;

oracle@debian:/u02/app/oracle/product/10.1.0/db_1/network/admin$ rman target mhg/mhg@test auxiliary /

Recovery Manager: Release 10.1.0.2.0 – Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to target database: TEST (DBID=1843143191)
connected to auxiliary database: DUP (not mounted)

oracle@debian:~$ rman target mhg/mhg@test auxiliary / @run.rcv

Recovery Manager: Release 10.1.0.2.0 – Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to target database: TEST (DBID=1843143191)
connected to auxiliary database: DUP (not mounted)

RMAN> run{
2> allocate auxiliary channel c1 type disk;
3> duplicate target database to dup;
4> }
5>
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=270 devtype=DISK

Starting Duplicate Db at 24-AUG-05
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/24/2005 21:13:09
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary filename /u02/app/oracle/oradata/test/users01.dbf conflicts with a file used by the target database

This error is primarily because the files of the test database are already present, this is a bad thing we have to use db_file_name_convert and long_file_name_convert to overcome these errors.

This is the final run output:-

oracle@debian:~$ rman target mhg/mhg@test auxiliary /

Recovery Manager: Release 10.1.0.2.0 – Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to target database: TEST (DBID=1843143191)
connected to auxiliary database: DUP (not mounted)

RMAN> @run.rcv

RMAN> run{
2> allocate auxiliary channel c1 type disk;
3> duplicate target database to dup;
4> }
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=270 devtype=DISK

Starting Duplicate Db at 24-AUG-05

contents of Memory Script:
{
set until scn 2150046;
set newname for datafile 1 to
“/u02/app/oracle/oradata/DUP/system2.dbf”;
set newname for datafile 2 to
“/u02/app/oracle/oradata/DUP/undotbs01.dbf”;
set newname for datafile 3 to
“/u02/app/oracle/oradata/DUP/sysaux01.dbf”;
set newname for datafile 4 to
“/u02/app/oracle/oradata/DUP/users01.dbf”;
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 24-AUG-05

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/app/oracle/oradata/DUP/system2.dbf
restoring datafile 00002 to /u02/app/oracle/oradata/DUP/undotbs01.dbf
…………..
datafile copy filename=/u02/app/oracle/oradata/DUP/sysaux01.dbf recid=2 stamp=567206656

cataloged datafile copy
datafile copy filename=/u02/app/oracle/oradata/DUP/users01.dbf recid=3 stamp=567206656

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=567206656 filename=/u02/app/oracle/oradata/DUP/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=567206656 filename=/u02/app/oracle/oradata/DUP/sysaux01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=567206656 filename=/u02/app/oracle/oradata/DUP/users01.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 24-AUG-05

RMAN> **end-of-file**

This ends a successful duplication of a database without control file.

Chapter 4. Using rman tocheck logical and physical block corruption

To generate block corruption you can use the dd unix utility
caution, it will corrupt your block(s):-
$dd if=/dev/null of=/u02/oradata/myrac/anyfile.dbf bs=8192 conv=notrunc seek=10 count=1
seek=10 write at block 10, count=1 write to only that block
now you can run dbv to verify that the blocks are actually corrupt
and then recover the datafile by using oracle’s blockrecover command.

export ORACLE_HOME=test
rman target /
run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}
To validatea datafile(s) :-
run {
allocate channel d1 type disk;
backup check logical validate datafile 1,2;
release channel d1;
}

During this command every block is written to memory and then subsequently rewriten to another portion of the memory, during
this memory to memory write every block is checked for corruption.
RMAN’s backup command with validate and check logical clause allow to quickly validate for both physical and logical corruption.

Chapter 5. Checking for datafile corruption

A corrupted block requires dropping an object. The message identifies the block in error by file number and block number. The cure has always been to run a query such as: SELECT owner, segment_name, segment_type FROM dba_extents WHERE file_id = AND BETWEEN block_id AND block_id + blocks – 1; where and were the numbers from the error message. This query indicates which object contains the corrupted block. Then, depending on the object type, recovery is either straightforward (for indexes and temporary segments), messy (for tables), or very messy (for active rollback segments and parts of the data dictionary). In Oracle 9i Enterprise Edition, however, a new Recovery Manager (RMAN) command, BLOCKRECOVER, can repair the block in place without dropping and recreating the object involved. After logging into RMAN and connecting to the target database, type: BLOCKRECOVER DATAFILE filenumber BLOCK blocknumber; A new view, V$DATABASE_BLOCK_CORRUPTION, gets updated during RMAN backups, and a block must be listed as corrupt for a BLOCKRECOVER to be performed. To recover all blocks that have been marked corrupt, the following RMAN sequence can be used: BACKUP VALIDATE DATABASE; BLOCKRECOVER CORRUPTION LIST; This approach is efficient if only a few blocks need recovery. For large-scale corruption, it’s more efficient to restore a prior image of the datafile and recover the entire datafile, as before. As with any new feature, test it carefully before using it on a production database.

run {
allocate channel ch1 type ;
blockrecover datafile block ;
}

1. What are the steps to start the database from a text control file?
1.1. What are the steps required to start a database from text based control file?
1.2. Give a complete scenario of backup, delete and restore.
1.3. How do I backup archive log?
1.4. How do I do a incremental backup after a base backup?
1.5. What is ORA-002004 error?
1.6. What Information is Required for RMAN TAR?
1.7. How To turn Debug Feature on in rman?
1. What are the steps to start the database from a text control file?
1.1. What are the steps required to start a database from text based control file?
1.2. Give a complete scenario of backup, delete and restore.
1.3. How do I backup archive log?
1.4. How do I do a incremental backup after a base backup?
1.5. What is ORA-002004 error?
1.6. What Information is Required for RMAN TAR?
1.7. How To turn Debug Feature on in rman?

1.1. What are the steps required to start a database from text based control file?

ALTER DATABASE BACKUP CONTROLFILE TO ‘/oracle/backup/cf.bak’ REUSE; –or to a file name on the OS. With this command you will get a text based version of your control file. REUSE clause specifies Oracle to overwrite the control files. If we ignore this option Oracle will not overwrite the control file if it is already present in the directory specified by the initSID.ora file.

Start the database in nomount mode. If you have 3 control file entries in pfile / spfile you will get 3 new control files.

Now run the control file script to create your control files.

recover database using backup controlfile until cancel

1.2. Give a complete scenario of backup, delete and restore.

Given that you want to take a base level backup, simulate complete failure by removing controlfile, datafile, redo log, archive log, these are the steps to be followed.

First take a base level backup of the database.

backup incremental level=0 database;

Simulate media failure by removing the control file and data file. sqlplus / as sysdba; shutdown immediate; exit; rm control* system*

When we don’t have a control file the problem becomes quite complex the reason been that the rman backup information is stored in the control file. So when we don’t have the control file we won’t have the information about backups. First step should be towards restoring the control file. Fortunately we can do a listing in our flash recovery area and guess which backup piece has the information about our control file. In my box following is the listing on the flash recovery area:-

/u02/app/oracle/flash_recovery_area/TEST/backupset/2005_07_30/
o1_mf_ncnn0_TAG20050730T130722_1gqn4jy2_.bkp
o1_mf_nnnd0_TAG20050730T130722_1gqmzdjz_.bkp
now I am assuming xxcnn0xxx has the control file information in it.

We have to use a nifty pl/sql program to recover our control file, once it is done successfully then we can go on our merry way using rman to recover the rest of the database.

DECLARE
v_devtype VARCHAR2(100);
v_done BOOLEAN;
v_maxPieces NUMBER;
TYPE t_pieceName IS TABLE OF varchar2(255) INDEX BY binary_integer;
v_pieceName t_pieceName;
BEGIN
— Define the backup pieces… (names from the RMAN Log file)
v_pieceName(1) :=
‘/u02/app/oracle/flash_recovery_area/TEST/backupset/2005_07_30/o1_mf_ncnn0_TAG20050730T130722_1gqn4jy2_.bkp’;
v_maxPieces := 1;
— Allocate a channel… (Use type=>null for DISK, type=>’sbt_tape’ for TAPE)
v_devtype := DBMS_BACKUP_RESTORE.deviceAllocate(type=>NULL, ident=>’d1′);
— Restore the first Control File…
DBMS_BACKUP_RESTORE.restoreSetDataFile;
— CFNAME mist be the exact path and filename of a controlfile taht was backed-up
DBMS_BACKUP_RESTORE.restoreControlFileTo(cfname=>’/u02/app/oracle/oradata/test/control01.ctl’);
dbms_output.put_line(‘Start restoring ‘||v_maxPieces||’ pieces.’);
FOR i IN 1..v_maxPieces LOOP
dbms_output.put_line(‘Restoring from piece ‘||v_pieceName(i));
DBMS_BACKUP_RESTORE.restoreBackupPiece(handle=>v_pieceName(i), done=>v_done, params=>null);
exit when v_done;
END LOOP;
— Deallocate the channel…
DBMS_BACKUP_RESTORE.deviceDeAllocate(‘d1′) ;
EXCEPTION
WHEN OTHERS THEN
DBMS_BACKUP_RESTORE.deviceDeAllocate;
RAISE;
END;
/

Pl/SQL completed successfully. I had 3 control files, the above command will restore only one control file so I will do a operating system copy to restore the rest of the control files. cp control01.ctl control02.ctl cp control01.ctl control03.ctl

After control file is restored launch rman and list all the backup information.,

rman target /
rman>sql ‘ alter database mount’;
rman>list backup;
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
21 Incr 0 2G DISK 00:02:39 30-JUL-05
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20050730T130722
Piece Name: /u02/app/oracle/flash_recovery_area/TEST/backupset/2005_07_30/o1_mf_nnnd0_TAG20050730T130722_1gqmzdjz_.bkp List of Datafiles in backup set 21
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 0 Incr 1723296 30-JUL-05 /u02/app/oracle/oradata/test/system2.dbf
2 0 Incr 1723296 30-JUL-05 /u02/app/oracle/oradata/test/undotbs01.dbf
3 0 Incr 1723296 30-JUL-05 /u02/app/oracle/oradata/test/sysaux01.dbf
4 0 Incr 1723296 30-JUL-05 /u02/app/oracle/oradata/test/users01.dbf

The above command indicates that the backup set key is 21 and tag is blah and time is blah.

Connect to rman target / restore database; recover database; exit;

sqlplus / as sysdba; startup; Now the database should have been recovered to your current SCN at which time we encountered a complete media failure.

1.3. How do I backup archive log?

In order to backup archivelog we have to do the following:-

run {
backup
(archivelog all delete input);
}

If you want to delete archive logs ignoring those that were inaccesible after backup you have to use (archivelog all skip inaccessible delete input);

1.4. How do I do a incremental backup after a base backup?

RMAN> backup incremental level=1 database plus archivelog delete all input;

This will take a incremental backup of the database and make a copy of archivelog and delete all input.

1.5. What is ORA-002004 error?

A disk I/O failure was detected on reading the controlfile.

Basically check whether the control file is available, permissions
are right on the control file,
spfile/init.ora right to the right location, if all checks were
done still you are getting the error, then from the multiplexed
control file overlay on the corrupted one, let us say you have
three control files control01.ctl, control02.ctl and control03.ctl
and now you are getting errors on control03.ctl then just cp control01.ctl
over to control03.ctl and you should be all set.
In order to issue
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
database should be mounted and in our case it is not mounted then the only
other option available is to restore control file from backup or cp the
multiplexed control file over to the bad one.

1.6. What Information is Required for RMAN TAR?

Hardware Configuration
* The name of the node that hosts the database
* The make and model of the production machine
* The version and patch of the operating system
* The disk capacity of the host
* The number of disks and disk controllers
* The disk capacity and free space
* The media management vendor (if you use a third-party media manager)
* The type and number of media management devices
Software Configuration
* The name of the database instance (SID)
* The database identifier (DBID)
* The version and patch release of the Oracle database server
* The version and patch release of the networking software
* The method (RMAN or user-managed) and frequency of database backups
* The method of restore and recovery (RMAN or user-managed)
* The datafile mount points

You should keep this information both in electronic and hardcopy form. For example, if you save this information in a text file on the network or in an email message, then if the entire system goes down, you may not have this data available.

1.7. How To turn Debug Feature on in rman?

run {
allocate channel c1 type disk;
debug on;
}
rman>list backup of database;

You will see a output similar to
DBGMISC: ENTERED krmkdftr [18:35:11.291]

DBGSQL: EXEC SQL AT TARGET begin dbms_rcvman . translateDataFile (
:fno ) ; end ; [18:35:11.291]
DBGSQL: sqlcode=0 [18:35:11.300]
DBGSQL: :b1 = 1
DBGMISC: ENTERED krmkgdf [18:35:11.301]
DBGMISC: ENTERED krmkgbh [18:35:11.315]
DBGMISC: EXITED krmkgbh with status Not required – no flags
[18:35:11.315] elapsed time [00:00:00:00.000]
DBGMISC: EXITED krmkgdf [18:35:11.315] elapsed time [00:00:00:00.014]
DBGMISC: EXITED krmkdftr [18:35:11.315] elapsed time [00:00:00:00.024]
DBGMISC: EXITED krmknmtr with status DF [18:35:11.315] elapsed time
[00:00:00:00.024]
DBGMISC: EXITED krmknmtr with status DFILE [18:35:11.315] elapsed time
[00:00:00:00.024]
DBGMISC: EXITED krmknmtr with status backup [18:35:11.315] elapsed time
[00:00:00:00.024]
DBGMISC: krmknmtr: the parse tree after name translation is:
[18:35:11.315]
DBGMISC: EXITED krmknmtr with status list [18:35:11.316] elapsed time
[00:00:00:00.078]
DBGMISC: krmkdps: this_reset_scn=1573357 [18:35:11.316]
DBGMISC: krmkdps: this_reset_time=19-AUG-06 [18:35:11.316]
DBGMISC: krmkdps: untilSCN= [18:35:11.317]

You can always turn debug off by issuing

rman>debug off;

You must use the reset database command in RMAN to create a new database incarnation record in the recovery catalog.

% rman connect target user/pwd catalog user/pwd
RMAN> reset database;

Immediately back up the database. This will give the database a new incarnation.

Explanation:
If you issue the ALTER DATABASE OPEN RESETLOGS but do not reset the database, then RMAN will not access the recovery catalog because it cannot distinguish between a RESETLOGS command and an accidental restore of an old control file. By resetting the database, you inform RMAN that the database has been opened with the RESETLOGS option

1. Introduction:
This article discusses disaster recovery – i.e. a situation in which your database server has been destroyed and has taken all your database files (control files, logs and data files) with it. Obviously, recovery from a disaster of this nature is dependent on what you have in terms of backups and hardware resources. We assume you have the following available after the disaster:
A server with the same disk layout as the original.
The last full hot backup on tape.
With the above items at hand, it is possible to recover all data up to the last full backup. One can do better if subsequent archive logs (after the last backup) are available. In our case these aren’t available, since our only archive destination was on the destroyed server (see Part I ). Oracle provides methods to achieve better data protection. We will discuss some of these towards the end of the article.

Now on with the task at hand. The high-level steps involved in disaster recovery are:
Build replacement server.
Restore backup from tape.
Install database software.
Create Oracle service.
Restore and recover database.
It sounds quite straightforward and it is reasonably so. However, there are some details that are often missed in books and documentation. The devil, as always, is in these details. Here we hope to provide you with enough detail to plan and practice disaster recovery in your test environment.

2. Build the server

You need a server to host the database, so the first step is to acquire or build the new machine. This is not strictly a DBA task, so we won’t delve into details here. The main point to keep in mind is that the replacement server should, as far as possible, be identical to the old one. In particular, pay attention to the following areas:
Disk layout and capacity: Ideally the server should have the same number of disks as the original. This avoids messy renaming of files during recovery. Obviously, the new disks should also have enough space to hold all software and data that was on the original server.
Operating system, service pack and patches: The operating system environment should be the same as the original, right up to service pack and patch level.
Memory: The new server must have enough memory to cater to Oracle and operating system / other software requirements. Oracle memory structures (Shared pool, db buffer caches etc) will be sized identically to the original database instance. Use of the backup server parameter file will ensure this.
Although you probably won’t build and configure the machine yourself. It is important to work with your systems people so that the above items are built to the recovery server specs.

3. Restore backup from tape

The next step is to get your backup from tape on to disk. In our example from Part I, the directory to be restored is e:\backup. The details of this depend on the backup product used, so we can’t go into it any further. This task would normally be performed your local sysadmin.

4. Install Oracle Software

Now we get to the meat of the database recovery process. The next step is to install Oracle software on the machine. The following points should be kept in mind when installing the software:
Install the same version of Oracle as was on the destroyed server. The version number should match right down to the patch level, so this may be a multi-step process involving installation followed by the application of one or more patchsets and patches.
Do not create a new database at this stage.
Create a listener using the Network Configuration Assistant. Ensure that it has the same name and listening ports as the original listener. Relevant listener configuration information can be found in the backed up listener.ora file.
4. Create directory structure for database files

After software installation is completed, create all directories required for datafiles, (online and archived) logs, control files and backups. All directory paths should match those on the original server. This, though not mandatory, saves additional steps associated with renaming files during recovery.

Don’t worry if you do not know where the database files should be located. You can obtain the required information from the backup spfile and control file at a later stage. Continue reading – we’ll come back to this later.

5. Create Oracle service

As described in section 2 of Part II, an Oracle service must be exist before a database is created. The service is created using the oradim utility, which must be run from the command line. The following commands show how to create and modify a service (comments in italics, typed commands in bold):

–create a new service with manual startup

C:\>oradim -new -sid ORCL -startmode m

–modify service to startup automatically

C:\>oradim -edit -sid ORCL -startmode a

Unfortunately oradim does not give any feedback, but you can check that the service exists via the Services administrative panel. The service has been configured to start automatically when the computer is powered up. Note that oradim offers options to delete, startup and shutdown a service. See the documentation for details.

6. Restore and recover database

Now it is time to get down to the nuts and bolts of database recovery. There are several steps, so we’ll list them in order:
Copy password and tnsnames file from backup: The backed up password file and tnsnames.ora files should be copied from the backup directory (e:\backup, in our example) to the proper locations. Default location for password and tnsnames files are ORACLE_HOME\database ORACLE_HOME\network\admin respectively.

Set ORACLE_SID environment variable: ORACLE_SID should be set to the proper SID name (ORCL in our case). This can be set either in the registry (registry key: HKLM\Software\Oracle\HOME\ORACLE_SID) or from the system applet in the control panel.

Invoke RMAN and set the DBID: We invoke rman and connect to the target database as usual. No login credentials are required since we connect from an OS account belonging to ORA_DBA. Note that RMAN accepts a connection to the database although the database is yet to be recovered. RMAN doesn’t as yet “know” which database we intend to connect to. We therefore need to identify the (to be restored) database to RMAN. This is done through the database identifier (DBID). The DBID can be figured out from the name of the controlfile backup. Example: if you use the controlfile backup format suggested in Part I, your controlfile backup name will be something like “CTL_SP_BAK_C-1507972899-20050228-00″. In this case the DBID is 1507972899. Here’s a transcript illustrating the process of setting the DBID:

C:\>rman

Recovery Manager: Release 9.2.0.4.0 – Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN> set dbid 1507972899

executing command: SET DBID

RMAN>

Restore spfile from backup: To restore the spfile, you first need to startup the database in the nomount state. This starts up the database using a dummy parameter file. After that you can restore the spfile from the backup (which has been restored from tape in Section 3). Finally you restart the database in nomount state. The restart is required in in order to start the instance using the restored parameter file. Here is an example RMAN transcript for the foregoing procedure. Note the difference in SGA size and components between the two startups:

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘C:\ORACLE\ORA92\DATABASE\INITORCL.ORA’

trying to start the Oracle instance without parameter files …
Oracle instance started

Total System Global Area 97590928 bytes

Fixed Size 454288 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes

RMAN> restore spfile from ‘e:\backup\CTL_SP_BAK_C-1507972899-20050228-00′;

Starting restore at 01/MAR/05

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: autobackup found: e:\backup\CTL_SP_BAK_C-1507972899-20050228-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 01/MAR/05

RMAN> startup force nomount

Oracle instance started

Total System Global Area 1520937712 bytes

Fixed Size 457456 bytes
Variable Size 763363328 bytes
Database Buffers 754974720 bytes
Redo Buffers 2142208 bytes

RMAN>

The instance is now started up with the correct initialisation parameters.

We are now in a position to determine the locations of control file and archive destination, as this information sits in the spfile. This is done via SQL Plus as follows:

C:\>sqlplus /nolog

….output not shown

SQL>connect / as sysdba
Connected.
SQL> show parameter control_file

….output not shown

SQL> show parameter log_archive_dest
….output not shown

The directories listed in the CONTROL_FILES and LOG_ARCHIVE_DEST_N parameters should be created at this stage if they haven’t been created earlier.

Restore control file from backup: The instance now “knows” where the control files should be restored, as this is listed in the CONTROL_FILES initialisation parameter. Therefore, the next step is to restore these files from backup. Once the control files are restored, the instance should be restarted in mount mode. A restart is required because the instance must read the initialisation parameter file in order to determine the control file locations. At the end of this step RMAN also has its proper configuration parameters, as these are stored in the control file.

Here is a RMAN session transcript showing the steps detailed here:

RMAN> restore controlfile from ‘e:\backup\CTL_SP_BAK_C-1507972899-20050228-00′;

Starting restore at 01/MAR/05

allocated channel: ORA_DISK_1
hannel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL01.CTL
output filename=E:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL02.CTL
output filename=C:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL03.CTL
Finished restore at 01/MAR/05

RMAN> shutdown

Oracle instance shut down

RMAN> exit

Recovery Manager complete.

C:\>rman target /

Recovery Manager: Release 9.2.0.4.0 – Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 1520937712 bytes

Fixed Size 457456 bytes
Variable Size 763363328 bytes
Database Buffers 754974720 bytes
Redo Buffers 2142208 bytes

RMAN> show all;

using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘e:\backup\ctl_sp_bak_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT ‘e:\backup\%U.bak’ MAXPIECESIZE 4G;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT ‘e:\backup\%U.bak’ MAXPIECESIZE 4G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘C:\ORACLE\ORA92\DATABASE\SNCFORCL.ORA’; # default

RMAN>

At this stage we can determine the locations of data files and redo logs if we don’t know where they should go. This is done from SQL Plus as follows:

C:\>sqlplus /nolog

…output not shown

SQL> connect / as sysdba
Connected.
SQL> select name from v$datafile;

…output not shown

SQL> select member from v$logfile;

…output not shown

SQL>

The directories shown in the output should be created manually if this hasn’t been done earlier.

Restore all datafiles: This is easy. Simply issue a “restore database” command from RMAN, and it will do all the rest for you:

RMAN> restore database;

Starting restore at 01/MAR/05

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=8 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS02.DBF
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
restoring datafile 00005 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
restoring datafile 00006 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS02.DBF
channel ORA_DISK_2: restored backup piece 1
piece handle=E:\BACKUP\80G6E1TT_1_1.BAK tag=TAG20041130T222501 params=NULL
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\81G6E1TU_1_1.BAK tag=TAG20041130T222501 params=NULL
channel ORA_DISK_2: restored backup piece 2
piece handle=E:\BACKUP\80G6E1TT_2_1.BAK tag=TAG20041130T222501 params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=E:\BACKUP\81G6E1TU_2_1.BAK tag=TAG20041130T222501 params=NULL
channel ORA_DISK_1: restored backup piece 3
piece handle=E:\BACKUP\81G6E1TU_3_1.BAK tag=TAG20041130T222501 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_2: restored backup piece 3
piece handle=E:\BACKUP\80G6E1TT_3_1.BAK tag=TAG20041130T222501 params=NULL
channel ORA_DISK_2: restore complete
Finished restore at 01/MAR/05

RMAN>

Recover database: The final step is to recover the database. Obviously recovery is dependent on the available archived (and online) redo logs. Since we have lost our database server and have no remote archive destination, we can recover only up to the time of the backup. Further, since this is an incomplete recovery, we will have to open the database with resetlogs. Here’s a sample RMAN session illustrating this:

RMAN> recover database;

Starting recover at 01/MAR/05
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

unable to find archive log archive log thread=1 sequence=1388

RMAN-00571: ==============================
RMAN-00569: =ERROR MESSAGE STACK FOLLOWS =
RMAN-00571: ===============================
RMAN-03002: failure of recover command at 04/01/2005 14:14:43
RMAN-06054: media recovery requesting unknown log: thread 1 scn 32230460

RMAN> alter database open resetlogs;

database opened

RMAN>

Note that RMAN automatically applies all available archive logs. It first applies the backed up log and then searches for subsequent logs in the archive destination. This opens the door for further recovery if the necessary logs are available. In our case, however, we have no more redo so we open the database with resetlogs. The error message above simply indicates that RMAN has searched, unsuccessfully, for subsequent logs.
That’s it. The database has been recovered, from scratch, to the last available backup. Now having done this, it is worth spending some time in discussing how one can do better – i.e. recover up to a point beyond the backup. We do this in the next section.

7. Options for better recovery

The above recovery leaves one with a sense of dissatisfaction: one could have done much better had the necessary logs been available. Clearly, one would have to copy the logs to a remote machine in order to guarantee access in a disaster situation. A couple of ways to do this include:
Copy archive logs to a remote destination using OS scripts: This is achieved simply by a script scheduled to run every hour or so. The script copies, to a remote network computer, all the archive logs generated since the script last ran. This achieves better recoverability than before. However, it does not achieve up to the minute recovery. Further, one has to ensure that the a log switch is performed before the logs are copied, so as to ensure that redo associated with recent transactions (within the last hour) is copied to the remote destination.The log switch can be performed using the “alter system archive log current” command.
Configure the Oracle ARC process to copy logs to the remote destination: This is done by defining a secondary archive destination via one of the LOG_ARCHIVE_DEST_N initialisation parameters. This method is not recommended because it is somewhat fragile – see this discussion on Tom Kyte’s site, for example. Be sure to use a mapped network drive as the archive destination if you choose to go down this path. Oracle 9i will not recognise archive log destinations specified using UNC (Universal Naming Convention).

Finally, any article on disaster recovery should mention Oracle Data Guard – which is Oracle Corporation’s recommended disaster recovery solution for mission critical systems. This is essentially a standby database that is kept synchronised with the primary through the continuous application of redo. There are different levels of synchronisation depending on required availability, performance and (most important!) the acceptable data loss. There are two types of standby databases depending on how redo is applied: 1) Physical standby – which is an identical, block for block, copy of the primary, and 2) Logical standby – which is kept synchronised by applying SQL mined from redo logs. Interested readers are referred to the Oracle documentation on Data Guard for further details, as it is a vast subject, appropriate for a book rather than an article this size. A good starting point is Oracle Data Guard Concepts and Administration guide.

Case 1: A multiplexed copy of the control file is available.

On startup Oracle must read the control file in order to find out where the datafiles and online logs are located. Oracle expects to find control files at locations specified in the CONTROL_FILE initialisation parameter. The instance will fail to mount the database if any one of the control files are missing or corrupt. A brief error message will be displayed, with further details recorded in the alert log. The exact error message will vary depending on what has gone wrong. Here’s an example:

SQL> startup

ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

SQL>

On checking the alert log, as suggested, we find the following:

ORA-00202: controlfile: ‘e:\oracle_dup_dest\controlfile\ORCL\control02.ctl’
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 5447783)

The above corruption was introduced by manually editing the control file when the database was closed.

The solution is simple, provided you have at least one uncorrupted control file – replace the corrupted control file with a copy using operating system commands. Remember to rename the copied file. The database should now start up without any problems.

Case 2: All control files lost

What if you lose all your control files? In that case you have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all intervening logs are available. Here’s an annotated transcript of a recovery session (as usual, lines in bold are commands to be typed, lines in italics are explanatory comments, other lines are RMAN feedback):

– Connect to RMAN
C:\>rman target /

Recovery Manager: Release 9.2.0.4.0 – Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORCL (not mounted)

– set DBID – get this from the name of the controlfile autobackup.
– For example, if autobackup name is
– CTL_SP_BAK_C-1507972899-20050124-00 the the DBID is
– 1507972899. This step will not be required if the instance is
– started up from RMAN

RMAN> set dbid 1507972899

executing command: SET DBID

–restore controlfile from autobackup. The backup is not at the default
–location so the path must be specified

RMAN> restore controlfile from ‘e:\backup\CTL_SP_BAK_C-1507972899-20050124-00′;

Starting restore at 26/JAN/05

using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL01.CTL
output filename=E:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL02.CTL
output filename=C:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL03.CTL
Finished restore at 26/JAN/05

– Now that control files have been restored, the instance can mount the
– database.

RMAN> mount database;

database mounted

– All datafiles must be restored, since the controlfile is older than the current
– datafiles. Datafile restore must be followed by recovery up to the current log.

RMAN> restore database;

Starting restore at 26/JAN/05

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\0DGB0I79_1_1.BAK tag=TAG20050124T115832 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\0CGB0I78_1_1.BAK tag=TAG20050124T115832 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05

–Database must be recovered because all datafiles have been restored from
– backup

RMAN> recover database;

Starting recover at 26/JAN/05
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 2 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_2.ARC
archive log thread 1 sequence 4 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO02A.LOG
archive log thread 1 sequence 5 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO01A.LOG
archive log thread 1 sequence 6 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_2.ARC thread=1 sequence=2
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_3.ARC thread=1 sequence=3
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO02A.LOG thread=1 sequence=4
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO01A.LOG thread=1 sequence=5
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG thread=1 sequence=6
media recovery complete
Finished recover at 26/JAN/05

– Recovery completed. The database must be opened with RESETLOGS
– because a backup control file was used. Can also use
– “alter database open resetlogs” instead.

RMAN> open resetlogs database;

database opened

Several points are worth emphasising.
Recovery using a backup controlfile should be done only if a current control file is unavailable.
All datafiles must be restored from backup. This means the database will need to be recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.
As with any database recovery involving RESETLOGS, take a fresh backup immediately.
Technically the above is an example of complete recovery – since all committed transactions were recovered. However, some references consider this to be incomplete recovery because the database log sequence had to be reset.
After recovery using a backup controlfile, all temporary files associated with locally-managed tablespaces are no longer available.
You can check that this is so by querying the view V$TEMPFILE – no rows will be returned. Therefore tempfiles must be added (or recreated) before the database is made available for general use. In the case at hand, the tempfile already exists so we merely add it to the temporary tablespace. This can be done using SQLPlus or any tool of your choice:

SQL> alter tablespace temp add tempfile
‘D:\oracle_data\datafiles\ORCL\TEMP01.DBF’;

Tablespace altered.

SQL>

Check that the file is available by querying v$TEMPFILE.