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