Oracle Recovery Data

Recovering a database using Rman

Because of the need to build a test environment, the last weekend of a full-time recovery, first back up the test library on the existing important tables:

Exp Banping/[email protected] file=f:\20090905.dmp tables= (msgtaskdef,rptcol,rptdata)

Then copy the full backup slice file and the control file:

RCP P5b1:/orabak/ctl_file/c-2400249746-20090828-02/orabak
rcp p5b1:/orabak/arch/*/orabak

File owner to Oracle:

[Email Protected]/orabak#chown ORACLE.DBA *

Then start the database into the Nomount state, enter the Rman environment, restore control files:

$ Rman Target/
rman> restore Controlfile from '/orabak/c-2400249746-20090828-02 ';

Boot to Mount State:

rman> SQL ' ALTER DATABASE mount ';

Rename the location of the data file according to the environment of the Test library:

Run {
Allocate channel C1 device type disk;
Allocate channel C2 device type disk;
Set newname for datafile '/dev/rdb_system ' to '/u02/oradata/erpdev2/rdb_system ';
Set newname for datafile '/dev/rdb_undotbs1 ' to '/U02/ORADATA/ERPDEV2/RDB_UNDOTBS1 ';
Set newname for datafile '/dev/rdb_sysaux ' to '/u02/oradata/erpdev2/rdb_sysaux ';
Set newname for datafile '/DEV/RDB_UNDOTBS2 ' to '/U02/ORADATA/ERPDEV2/RDB_UNDOTBS2 ';
Set newname for datafile '/dev/rdb_users ' to '/u02/oradata/erpdev2/rdb_users ';
Set newname for datafile '/dev/rdb_erp ' to '/u02/oradata/erpdev2/rdb_erp ';
Set newname for datafile '/dev/rdb_erp_index ' to '/u02/oradata/erpdev2/rdb_erp_index ';
Set newname for datafile '/dev/rdb_erp_xm ' to '/U02/ORADATA/ERPDEV2/RDB_ERP_XM ';
Set newname for datafile '/dev/rdb_erp_ht ' to '/u02/oradata/erpdev2/rdb_erp_ht ';
Set newname for datafile '/dev/rdb_erp_wl ' to '/u02/oradata/erpdev2/rdb_erp_wl ';
Set newname for datafile '/DEV/RDB_ERP_CW ' to '/U02/ORADATA/ERPDEV2/RDB_ERP_CW ';
Set newname for datafile '/dev/rdb_erp_zj ' to '/u02/oradata/erpdev2/rdb_erp_zj ';
Set newname for datafile '/dev/rdb_erp_sp ' to '/u02/oradata/erpdev2/rdb_erp_sp ';
Set newname for datafile '/dev/rdb_cndwl ' to '/u02/oradata/erpdev2/rdb_cndwl ';
Set newname for datafile '/dev/rdb_xdwl ' to '/u02/oradata/erpdev2/rdb_xdwl ';
Set newname for datafile '/dev/rdb_sysaux2 ' to '/u02/oradata/erpdev2/rdb_sysaux2 ';
Set newname for datafile '/dev/rdb_erp2 ' to '/u02/oradata/erpdev2/rdb_erp2 ';
Set newname for datafile '/dev/rdb_erp_ht2 ' to '/u02/oradata/erpdev2/rdb_erp_ht2 ';
Restore database;
Switch datafile all;
}

Then perform the recover operation:

rman> Recover database;

At this time the database error:

Oracle Error:
Ora-01547:warning:recover succeeded but OPEN Resetlogs would get error below
Ora-01152:file 1 was wasn't restored from a sufficiently old backup
Ora-01110:data file 1: '/u02/oradata/erpdev2/rdb_system '

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE, STACK follows ===============
RMAN-00571: ===========================================================
Rman-03002:failure of recover command at 09/04/2009 14:31:39
Rman-06053:unable to perform media recovery because of missing log
Rman-06025:no Backup of Log thread 2 seq 106090 lowscn 10440022578 found to restore
Rman-06025:no Backup of Log thread 2 seq 106089 lowscn 10440021828 found to restore
Rman-06025:no Backup of Log thread 2 seq 106088 lowscn 10440020848 found to restore
Rman-06025:no Backup of Log thread 2 seq 106087 lowscn 10440018779 found to restore
Rman-06025:no Backup of Log thread 2 seq 106086 lowscn 10440007985 found to restore
Rman-06025:no Backup of Log thread 2 seq 106085 lowscn 10439981571 found to restore
Rman-06025:no Backup of Log thread 2 seq 106084 lowscn 10439980750 found to restore
Rman-06025:no Backup of Log thread 1 seq 99356 lowscn 10440022588 found to restore
Rman-06025:no Backup of Log thread 1 seq 99355 lowscn 10440021839 found to restore
Rman-06025:no Backup of Log thread 1 seq 99354 lowscn 10440020856 found to restore
Rman-06025:no Backup of Log thread 1 seq 99353 lowscn 10440018824 found to restore
Rman-06025:no Backup of Log thread 1 seq 99352 lowscn 10440008036 found to restore
Rman-06025:no Backup of Log thread 1 seq 99351 lowscn 10439981670 found to restore
Rman-06025:no Backup of Log thread 1 seq 99350 lowscn 10439980746 found to restore

It seems that some of the backup pieces are incomplete, so put the full backup piece back into the/orabak/arch directory, first registered to the control file:

rman> catalog start with '/orabak/arch ';

Searching for all files that match the Pattern/orabak/arch

List of Files Unknown to the Database
=====================================
File Name:/orabak/arch/cnderpdb_arch_20090829_696171285_15256_1
File Name:/orabak/arch/cnderpdb_arch_20090829_696171311_15258_1
File Name:/orabak/arch/cnderpdb_arch_20090829_696171617_15257_1
File Name:/orabak/arch/cnderpdb_arch_20090829_696171922_15259_1
File Name:/orabak/arch/cnderpdb_arch_20090829_696172209_15260_1
File Name:/orabak/arch/cnderpdb_arch_20090829_696172385_15261_1

Really want to catalog the above files (enter YES or NO)? Y
Cataloging files ...
Cataloging done

List of cataloged Files
=======================
File Name:/orabak/arch/cnderpdb_arch_20090829_696171285_15256_1
File Name:/orabak/arch/cnderpdb_arch_20090829_696171311_15258_1
File Name:/orabak/arch/cnderpdb_arch_20090829_696171617_15257_1
File Name:/orabak/arch/cnderpdb_arch_20090829_696171922_15259_1
File Name:/orabak/arch/cnderpdb_arch_20090829_696172209_15260_1
File Name:/orabak/arch/cnderpdb_arch_20090829_696172385_15261_1

To re-recover the operation:

rman> Recover database;

Starting recover at 07-sep-09
Using channel Ora_disk_1

Starting Media recovery

Archive log thread 1 sequence 99417 is already on disk as FILE/U02/ORA_ARCH/1_99417_640266118.DBF
Unable to find archive log
Archive Log thread=2 sequence=106154
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE, STACK follows ===============
RMAN-00571: ===========================================================
Rman-03002:failure of recover command at 09/07/2009 08:59:16
Rman-06054:media Recovery requesting unknown Log:thread 2 seq 106154 lowscn 10444622603

Continue to find the necessary backup tablets, after a few attempts to find that will always look like this:

Unable to find archive log
Archive Log thread=1 sequence=99470
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE, STACK follows ===============
RMAN-00571: ===========================================================
Rman-03002:failure of recover command at 09/07/2009 09:29:02
Rman-06054:media recovery requesting unknown Log:thread 1 seq 99470 lowscn 10444784948

For testing purposes, specify the SCN recovery:

Rman> Run {
2> set until SCN 10444783788;
3> Recover database;
4>}

Executing command:set until clause

Starting recover at 07-sep-09
Using channel Ora_disk_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE, STACK follows ===============
RMAN-00571: ===========================================================
Rman-03002:failure of recover command at 09/07/2009 09:38:44
Rman-06556:datafile 1 must is restored from backup older than SCN 10444783788

The SCN that was found through list backup of Archivelog all turned out to be wrong, continue to take back the backup slice and perform the recovery by saying the missing 10,444,784,948th SCN:

Rman> Run {
2> set until SCN 10444784948;
3> Recover database;
4>}

Executing command:set until clause

Starting recover at 07-sep-09
Using channel Ora_disk_1

Starting Media recovery

Archive log thread 2 sequence 106205 is already on disk as FILE/U02/ORA_ARCH/2_106205_640266118.DBF
Channel ora_disk_1:starting archive log restore to default destination
Channel ora_disk_1:restoring Archive Log
Archive Log thread=1 sequence=99470
Channel ora_disk_1:reading from backup Piece/orabak/arch/cnderpdb_arch_20090830_696257683_15267_1
Channel ora_disk_1:restored backup Piece 1
Piece Handle=/orabak/arch/cnderpdb_arch_20090830_696257683_15267_1 tag=tag20090830t130014
Channel Ora_disk_1:restore complete, elapsed time:00:00:03
Archive Log filename=/u02/ora_arch/1_99470_640266118.dbf thread=1 sequence=99470
Archive Log filename=/u02/ora_arch/2_106205_640266118.dbf thread=2 sequence=106205
Media recovery complete, elapsed time:00:00:03
Finished recover at 07-sep-09

Error when attempting to open:

rman> ALTER DATABASE open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE, STACK follows ===============
RMAN-00571: ===========================================================
Rman-03002:failure of Alter DB command at 09/07/2009 09:57:10
Ora-01589:must use Resetlogs or noresetlogs option for database open

rman> ALTER DATABASE open resetlogs;

Rman-06900:warning:unable to generate V$rman_status or V$rman_output row
rman-06901:warning:disabling update of the V$rman_status and V$rman_output rows
ORACLE error from Target database:
Ora-19921:maximum number of rows exceeded

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE, STACK follows ===============
RMAN-00571: ===========================================================
Rman-03002:failure of Alter DB command at 09/07/2009 09:57:20
Ora-00344:unable to re-create online log '/dev/rdb_redo1_11 '
Ora-27040:file create error, unable to create file
IBM AIX RISC system/6000 Error:13:permission denied

Follow the backup file to create a series of redo files in the test library:

sql> ALTER DATABASE rename file '/dev/rdb_redo1_11 ' to '/u02/oradata/erpdev2/rdb_redo1_1 ';

Database altered.

To create a temporary tablespace file, you can delete unnecessary:

Sql> select name from V$tempfile;

NAME
--------------------------------------------------------------------------------
/dev/rdb_erp_ Temp
/DEV/RDB_ERP_TEMP2
/dev/rdb_erp_temp3

sql> ALTER DATABASE rename file '/dev/rdb_erp_temp ' to '/ U02/oradata/erpdev2/rdb_erp_temp ';

Database altered.

Sql> select name from V$tempfile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/ Erpdev2/rdb_erp_temp
/DEV/RDB_ERP_TEMP2
/dev/rdb_erp_temp3

sql> alter DATABASE Tempfile '/dev/rdb_ ERP_TEMP2 ' drop including datafiles;

Database altered.

Sql> ALTER DATABASE tempfile '/dev/rdb_erp_temp3 ' drop including datafiles;

Database altered.

Sql> select name from V$tempfile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/ Erpdev2/rdb_erp_temp

Because it is not fully restored, to resetlogs open:

sql> ALTER DATABASE open;
ALTER DATABASE Open
*
ERROR at line 1:
Ora-01589:must use Resetlogs or noresetlogs option for database open
sql> ALTER DATABASE open resetlogs;

Database altered.

To view the archive mode:

Sql> Archive Log List
Database Log Mode Archive mode
Automatic Archival Enabled
Archive destination/u02/ora_arch/
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1

Close archive:

sql> shutdown Immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> startup Mount;
ORACLE instance started.

Total System Global area 603979776 bytes
Fixed Size 2074704 bytes
Variable Size 318769072 bytes
Database buffers 276824064 bytes
Redo buffers 6311936 bytes
Database mounted.
sql> ALTER DATABASE Noarchivelog;

Database altered.

sql> ALTER DATABASE open;

Database altered.

Oracle Recovery Data