Migrating an Oracle database using Rman's level0 backup file-incomplete recovery

Migrating an Oracle database using Rman's level0 backup file-incomplete recovery-20160811

Migrating an Oracle database from one machine A to another machine B (same as Linux platform), set to a different path, different instance name

SOURCE side:

Oracle_base=/u01/app/oracle

Oracle_home=/u01/app/oracle/product/12.1.0.2/db_1

Oracle_sid=scpdb

Data File location:/u01/app/oracle/oradata/scpdb/datafile/

Target side:

Oracle_base=/u01/app/oracle

Oracle_home=/u01/app/oracle/product/12.1.0.2/db_1

Oralce_sid=scpdb01

Data File location:/u01/app/oracle/oradata/scpdb01/datafile/

Upload the backup to the target side/u01/rmanscpdb/

Backup Set Information:

[Email protected]/]$ ll/u01/rmanscpdb/*

-rw-r--r--. 1 Oracle Oinstall 10256384 18:18/U01/RMANSCPDB/CTL_AUTO_C-2612507248-20160712-00.BK

-rw-r--r--. 1 Oracle Oinstall 1089299968 18:18/U01/RMANSCPDB/O12C_FULL_LEVEL0_SCPDB201607122ERAHKA5_1_1.BK

-rw-r--r--. 1 Oracle Oinstall 2024849408 18:19/U01/RMANSCPDB/O12C_FULL_LEVEL0_SCPDB201607122FRAHKAN_1_1.BK

-rw-r--r--. 1 Oracle Oinstall 12288 18:19/U01/RMANSCPDB/O12C_FULL_LEVEL0_SCPDB201607122GRAHKBG_1_1.BK

The following are all used by Oracle user operations on the target side

CD $ORACLE _base

mkdir Flash_recovery_area

Mkdir-p ADMIN/SCPDB01

CD ADMIN/SCPDB01

mkdir {A,b,c,d,u}dump

Start recovery, at which point the restored instance is named Scpdb

Export oracle_sid=scpdb

Rman Target/

===================================================

= = in Rman start database to nomount, = = =

= = Restore SPFile, open database using Nomount = = =

= = edit one of the simplest pfile, only db_name: = = =

==vi Initscpdb.ora = = =

==cat Initscpdb.ora = = =

==db_name=scpdb = = =

==                                                ===

= = also can not create pfile, direct boot to nomount = = =

===================================================

Rman> Startup Nomount

rman> restore SPFile from '/U01/RMANSCPDB/CTL_AUTO_C-2612507248-20160712-00.BK ';

rman> shutdown Immediate

Edit the parameter file to change the associated path in the parameter to the new path

Note: oracle_sid=scpdb

Sqlplus/as SYSDBA

Create Pfile from SPFile;

Edit the generated $oracle_home/dbs/initscpdb.ora file,

Modify the corresponding parameters as needed, such as increasing the SGA size, etc.

Next, modify the associated path in the Pfile file to point to the new location, where all the scpdb directories in the parameter file should be modified to SCPDB01

*.audit_file_dest= '/u01/app/oracle/admin/scpdb01/adump '

*.control_files= '/u01/app/oracle/oradata/scpdb01/controlfile/o1_mf_clozc9vs_.ctl ', '/u01/app/oracle/fast_ Recovery_area/scpdb01/controlfile/o1_mf_clozcb1k_.ctl '

*.log_archive_dest_1= ' Location=/u01/archscp01 '

Note that the db_name parameter is unchanged, for the original db_name,

Use Nid to modify after recovery is complete

Booting to the Nomount state using the Pfile file

Some parameters of the target side

*.__data_transfer_cache_size=0

*.__db_cache_size=1808m

*.__java_pool_size=16m

*.__large_pool_size=32m

*.__oracle_base= '/u01/app/oracle ' # Oracle_base set from environment

*.__pga_aggregate_target=800m

*.__sga_target=2384m

*.__shared_io_pool_size=80m

*.__shared_pool_size=432m

*.__streams_pool_size=0

*.pga_aggregate_target=790m

*.sga_target=2384m

Some parameters of the source side

Scpdb.__data_transfer_cache_size=0

scpdb.__db_cache_size=15904800768

scpdb.__java_pool_size=469762048

scpdb.__large_pool_size=1140850688

Scpdb.__oracle_base= '/u01/app/oracle ' #ORACLE_BASE set from environment

scpdb.__pga_aggregate_target=6777995264

scpdb.__sga_target=20333985792

scpdb.__shared_io_pool_size=536870912

scpdb.__shared_pool_size=2214592512

Scpdb.__streams_pool_size=0

---restore control files

Export oracle_sid=scpdb

Rman Target/

Rman> Startup Nomount

rman> restore Controlfile from '/U01/BACKUP/CTL_AUTO_C-2612507248-20160809-00.BK ';

---switch to mount State, note that db_name must keep the original db_name, otherwise prompt when switching to mount

---ora-01103:database name ' scpdb ' in control file was not ' SCPDB01 '

Rman> ALTER DATABASE Mount;

Rman> exit

---Open the second window and modify the log file location

Export oracle_sid=scpdb

Sqlplus/as SYSDBA

Sql> SELECT ' ALTER DATABASE rename file ' | | member| | "to" | | Replace (member, ' scpdb ', ' SCPDB01 ') | | '; ' from V$logfile;

Alter DATABASE rename file '/u01/app/oracle/fast_recovery_area/scpdb/onlinelog/redo08a.log ' to '/u01/app/oracle/fast _recovery_area/scpdb01/onlinelog/redo08a.log ';

Alter DATABASE rename file '/u01/app/oracle/oradata/scpdb/onlinelog/redo08b.log ' to '/u01/app/oracle/oradata/scpdb01 /onlinelog/redo08b.log ';

Alter DATABASE rename file '/u01/app/oracle/fast_recovery_area/scpdb/onlinelog/redo09a.log ' to '/u01/app/oracle/fast _recovery_area/scpdb01/onlinelog/redo09a.log ';

Alter DATABASE rename file '/u01/app/oracle/oradata/scpdb/onlinelog/redo09b.log ' to '/u01/app/oracle/oradata/scpdb01 /onlinelog/redo09b.log ';

Alter DATABASE rename file '/u01/app/oracle/fast_recovery_area/scpdb/onlinelog/redo05a.log ' to '/u01/app/oracle/fast _recovery_area/scpdb01/onlinelog/redo05a.log ';

Alter DATABASE rename file '/u01/app/oracle/oradata/scpdb/onlinelog/redo05b.log ' to '/u01/app/oracle/oradata/scpdb01 /onlinelog/redo05b.log ';

Alter DATABASE rename file '/u01/app/oracle/fast_recovery_area/scpdb/onlinelog/redo06a.log ' to '/u01/app/oracle/fast _recovery_area/scpdb01/onlinelog/redo06a.log ';

Alter DATABASE rename file '/u01/app/oracle/oradata/scpdb/onlinelog/redo06b.log ' to '/u01/app/oracle/oradata/scpdb01 /onlinelog/redo06b.log ';

Alter DATABASE rename file '/u01/app/oracle/fast_recovery_area/scpdb/onlinelog/redo07a.log ' to '/u01/app/oracle/fast _recovery_area/scpdb01/onlinelog/redo07a.log ';

Alter DATABASE rename file '/u01/app/oracle/oradata/scpdb/onlinelog/redo07b.log ' to '/u01/app/oracle/oradata/scpdb01 /onlinelog/redo07b.log ';

---Execute the above statement

sql> ALTER DATABASE rename file '/u01/app/oracle/oradata/scpdb/datafile/o1_mf_temp_cto6lh07_.tmp ' to '/u01/app/ Oracle/oradata/scpdb01/datafile/o1_mf_temp_cto6lh07_.tmp ';

.......

---Back to the first window

Export oracle_sid=scpdb

Rman Target/

---Use catalog start with to specify the backup file location

rman> catalog start with '/u01/backup/';

---Restore and restore the database, because different locations are used, we need to use the SET newname clause, as in the following example

run{

Allocate channel CH1 device type disk;

Set newname for datafile 1 to '/u01/app/oracle/oradata/scpdb01/datafile/system.dbf ';

Set newname for DataFile 3 to '/u01/app/oracle/oradata/scpdb01/datafile/sysaux.dbf ';

Set newname for datafile 4 to '/u01/app/oracle/oradata/scpdb01/datafile/undotbs1.dbf ';

Set newname for datafile 6 to '/u01/app/oracle/oradata/scpdb01/datafile/users.dbf ';

Set newname for datafile 2 to '/u01/app/oracle/oradata/scpdb01/datafile/tbs_notifydb.dbf ';

Set newname for datafile 5 to '/u01/app/oracle/oradata/scpdb01/datafile/tbs_gmscp.dbf ';

Set newname for datafile 7 to '/u01/app/oracle/oradata/scpdb01/datafile/tbsp_index01.dbf ';

Restore database;

Release channel CH1;

Switch datafile all;

}

=====================================================================================

----can also use

Set newname for database to '/u01/app/oracle/oradata/scpdb01/datafile/%b ';

run{

Allocate channel CH1 device type disk;

Set newname for database to '/u01/app/oracle/oradata/scpdb01/datafile/%b ';

Restore database;

Release channel CH1;

Switch datafile all;

}

=====================================================================================

rman> Recover database;

You receive the following error message during the---recover

Archived Log thread=1 sequence=614

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE, STACK follows ===============

RMAN-00571: ===========================================================

Rman-03002:failure of recover command at 08/11/2016 14:21:27

Rman-06054:media recovery requesting unknown archived log for thread 1 with sequence 614 and starting SCN of 8223302

rman> recover database until sequence 614;

rman> ALTER DATABASE open resetlogs;

---modify temporary file location

Export oracle_sid=scpdb

Sqlplus/as SYSDBA

sql> shutdown immediate;

Sql> Startup Mount

Sql> SELECT ' ALTER DATABASE rename file ' | | name| | "to" | | Replace (name, ' scpdb ', ' SCPDB01 ') | | '; ' from V$tempfile;

Alter DATABASE rename file '/u01/app/oracle/oradata/scpdb/datafile/gmscptemp01.dbf ' to '/u01/app/oracle/oradata/ SCPDB01/DATAFILE/GMSCPTEMP01.DBF ';

Alter DATABASE rename file '/u01/app/oracle/oradata/scpdb/datafile/notifydbtemp01.dbf ' to '/u01/app/oracle/oradata/ SCPDB01/DATAFILE/NOTIFYDBTEMP01.DBF ';

Alter DATABASE rename file '/u01/app/oracle/oradata/scpdb/datafile/o1_mf_temp_ctr6mjvh_.tmp ' to '/u01/app/oracle/ Oradata/scpdb01/datafile/o1_mf_temp_ctr6mjvh_.tmp ';

sql> ALTER DATABASE open;

---Modify the instance name, and the database name

Export oracle_sid=scpdb

Sqlplus/as SYSDBA

sql> shutdown immediate;

Sql> Startup Mount

sql>!nid target=sys/oracle dbname=scpdb01 logfile=/tmp/change_name2scpdb01.log

Sql> create SPFile from Pfile;

sql> startup Mount;

Start the database, this time will report the database name inconsistent error, do not care about him (ora-01103:database name ' scpdb ' control file is not ' SCPDB01 ')

Sql> alter system set Db_name= ' SCPDB01 ' scope=spfile;

Sql> create Pfile from SPFile;

sql> shutdown immediate;

[Email protected] ~]$ export ORACLE_SID=SCPDB01

[Email protected] ~]$ Sqlplus/as SYSDBA

sql> startup pfile= '/u01/app/oracle/product/12.1.0.2/db_1/dbs/initscpdb.ora ';

Sql> create SPFile from pfile= '/u01/app/oracle/product/12.1.0.2/db_1/dbs/initscpdb.ora ';

sql> shutdown immediate;

sql> startup Mount;

Sql> create Pfile from SPFile;

sql> ALTER DATABASE open resetlogs;

Sql> alter system register;

sql> alter USER system identified by Oracle;

Related references:

Http://blog.sina.com.cn/s/blog_67be3b4501017zz9.html

http://blog.csdn.net/leshami/article/details/8076841

Http://www.2cto.com/database/201408/327614.html

Configuring Rman and backing up

Rman> Show All;

RMAN configuration parameters for database with Db_unique_name SCPDB01 is:

CONFIGURE RETENTION POLICY to RECOVERY windows of 7 days;

CONFIGURE BACKUP optimization OFF; # Default

CONFIGURE DEFAULT DEVICE TYPE to DISK;

CONFIGURE Controlfile autobackup on;

CONFIGURE controlfile autobackup FORMAT for DEVICE TYPE DISK to '/U01/BACKUP/LEVEL0/CTL_AUTO_%F.BK ';

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 CHANNEL DEVICE TYPE DISK FORMAT '/U01/BACKUP/LEVEL0/O12C_FULL_LEVEL0_%D%T%U.BK ';

CONFIGURE maxsetsize to UNLIMITED; # Default

CONFIGURE encryption for DATABASE OFF; # Default

CONFIGURE encryption algorithm ' AES128 '; # Default

CONFIGURE COMPRESSION algorithm ' BASIC ' as of RELEASE ' DEFAULT ' OPTIMIZE for LOAD TRUE; # Default

CONFIGURE RMAN OUTPUT to KEEP for 7 days; # Default

CONFIGURE ARCHIVELOG deletion POLICY to NONE; # Default

CONFIGURE SNAPSHOT controlfile NAME to '/u01/app/oracle/product/12.1.0.2/db_1/dbs/snapcf_scpdb01.f '; # Default

Rman> BACKUP database plus archivelog delete input;

Migrating Oracle Databases using Rman's level0 backup files-not fully recovered