实验环境说明:
源端主机:192.168.1.11 数据库实例名:PROD1 数据库名称: PROD1 (归档模式打开 )
目标端主机:192.168.1.22 数据库实例名:TEST 数据库名称:TEST
-
源端数据库做备份:所有的数据文件,控制文件,参数文件
-
SQL> archive log list;
-
Database log mode Archive Mode
-
Automatic archival Enabled
-
Archive destination /home/oracle/arch2
-
Oldest online log sequence 4
-
Next log sequence to archive 6
-
Current log sequence 6
-
#归档已开启
-
RMAN> backup as backupset database format '/home/oracle/full_%U.dbf';
-
-
Starting backup at 07-APR-17
-
using target database control file instead of recovery catalog
-
allocated channel: ORA_DISK_1
-
channel ORA_DISK_1: SID=47 device type=DISK
-
channel ORA_DISK_1: starting full datafile backup set
-
channel ORA_DISK_1: specifying datafile(s) in backup set
-
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
-
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
-
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
-
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
-
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
-
channel ORA_DISK_1: starting piece 1 at 07-APR-17
-
channel ORA_DISK_1: finished piece 1 at 07-APR-17
-
piece handle=/home/oracle/full_07s13lfo_1_1.dbf tag=TAG20170407T153624 comment=NONE
-
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
-
channel ORA_DISK_1: starting full datafile backup set
-
channel ORA_DISK_1: specifying datafile(s) in backup set
-
including current control file in backup set
-
including current SPFILE in backup set
-
channel ORA_DISK_1: starting piece 1 at 07-APR-17
-
channel ORA_DISK_1: finished piece 1 at 07-APR-17
-
piece handle=/home/oracle/full_08s13lg7_1_1.dbf tag=TAG20170407T153624 comment=NONE
-
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
-
Finished backup at 07-APR-17
-
#RMAN全库备份完毕
-
#/home/oracle/full_07s13lfo_1_1.dbf 是数据文件备份;/home/oracle/full_08s13lg7_1_1.dbf为参数文件和控制文件的备份
-
创建目录(与源端目录相同)并将源端数据库的RMAN备份文件拷贝到目标端
-
[oracle@host02 ~]$ mkdir /home/oracle/abc
-
[oracle@host01 ~]$ scp /home/oracle/full_07s13lfo_1_1.dbf /home/oracle/full_08s13lg7_1_1.dbf 192.168.1.22:/home/oracle/
编辑目标端环境变量 SID:TEST
-
export PATH
-
export ORACLE_BASE=/u01/app/oracle
-
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
-
export ORACLE_SID=TEST
-
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
-
配置目标库到nomount阶
-
获取源库dbid
-
[oracle@host01 ~]$ rman target /
-
-
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 7 15:34:45 2017
-
-
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-
-
connected to target database: PROD1 (DBID=2157889328)
-
设置目标库dbid
-
[oracle@host02 ~]$ rman target /
-
-
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 7 16:00:54 2017
-
-
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-
-
connected to target database (not started)
-
-
RMAN> set dbid 2157889328
-
-
executing command: SET DBID
-
启动目标库至nomount
-
RMAN> startup nomount;
-
-
startup failed: ORA-01078: failure in processing system parameters
-
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initTEST.ora'
-
-
starting Oracle instance without parameter file for retrieval of spfile
-
Oracle instance started
-
-
Total System Global Area 1068937216 bytes
-
-
Fixed Size 2260088 bytes
-
Variable Size 281019272 bytes
-
Database Buffers 780140544 bytes
-
Redo Buffers 5517312 bytes
-
在目标库利用RMAN备份集恢复参数文件(initPROD1.ora)
-
RMAN> restore spfile to pfile '?/dbs/initPROD1.ora' from '/home/oracle/full_08s13lg7_1_1.dbf';
-
-
Starting restore at 07-APR-17
-
using channel ORA_DISK_1
-
-
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/abc/full_08s13lg7_1_1.dbf
-
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
-
Finished restore at 07-APR-17
-
编辑参数文件initPROD1.ora 将PROD1替换为TEST,注意db_name仍为PROD1
-
[oracle@host02 dbs]$ vi initPROD1.ora
-
TEST.__db_cache_size=352321536
-
TEST.__java_pool_size=4194304
-
TEST.__large_pool_size=8388608
-
TEST.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
-
TEST.__pga_aggregate_target=335544320
-
TEST.__sga_target=499122176
-
TEST.__shared_io_pool_size=0
-
TEST.__shared_pool_size=125829120
-
TEST.__streams_pool_size=0
-
*.audit_file_dest='/u01/app/oracle/admin/TEST/adump'
-
*.audit_trail='db'
-
*.compatible='11.2.0.4.0'
-
*.control_files='/u01/app/oracle/oradata/TEST/control01.ctl','/u01/app/oracle/oradata/TEST/control02.ctl'
-
*.db_block_size=8192
-
*.db_domain=''
-
*.db_name='PROD1'
-
*.diagnostic_dest='/u01/app/oracle'
-
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
-
*.log_archive_dest_1='location=/home/oracle/arch1'
-
*.log_archive_dest_2='location=/home/oracle/arch2'
-
*.memory_target=833617920
-
*.open_cursors=300
-
*.processes=150
-
*.remote_login_passwordfile='EXCLUSIVE'
-
*.undo_tablespace='UNDOTBS1'
-
更改参数文件名字为initTEST.ora 并且通过pfile创建spfile
-
[oracle@host02 ~]$ cd $ORACLE_HOME/dbs
-
[oracle@host02 dbs]$ ls
-
hc_TEST.dat init.ora initPROD1.ora
-
[oracle@host02 dbs]$ mv initPROD1.ora initTEST.ora
-
[oracle@host02 dbs]$ sqlplus / as sysdba
-
-
SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 16:29:35 2017
-
-
Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
-
Connected to:
-
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
-
SQL> create spfile from pfile;
-
-
File created.
-
-
SQL> !ls spfileTEST.ora
-
spfileTEST.ora
-
创建相应文件目录 并通过RMAN恢复控制文件
-
[oracle@host02 ~]$ mkdir -p /u01/app/oracle/admin/TEST/adump
-
-
RMAN> restore controlfile from '/home/oracle/full_08s13lg7_1_1.dbf';
-
-
Starting restore at 07-APR-17
-
using target database control file instead of recovery catalog
-
allocated channel: ORA_DISK_1
-
channel ORA_DISK_1: SID=19 device type=DISK
-
-
channel ORA_DISK_1: restoring control file
-
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
-
output file name=/u01/app/oracle/oradata/TEST/control01.ctl
-
output file name=/u01/app/oracle/oradata/TEST/control02.ctl
-
Finished restore at 07-APR-17
-
将数据库启动到mount
-
RMAN> alter database mount;
-
-
database mounted
-
released channel: ORA_DISK_1
-
配置目标库到mount阶段
-
首先用set newname更改所有数据文件的位置为目标库的位置
-
RMAN> run{
-
2> set newname for datafile 1 to '/u01/app/oracle/oradata/TEST/system01.dbf';
-
3> set newname for datafile 2 to '/u01/app/oracle/oradata/TEST/sysaux01.dbf';
-
4> set newname for datafile 3 to '/u01/app/oracle/oradata/TEST/undotbs01.dbf';
-
5> set newname for datafile 4 to '/u01/app/oracle/oradata/TEST/users01.dbf';
-
6> set newname for datafile 5 to '/u01/app/oracle/oradata/TEST/example01.dbf';
-
7> RESTORE DATABASE ;
-
8> SWITCH DATAFILE ALL;
-
9> RECOVER DATABASE;
-
10> }
-
-
executing command: SET NEWNAME
-
-
executing command: SET NEWNAME
-
-
executing command: SET NEWNAME
-
-
executing command: SET NEWNAME
-
-
executing command: SET NEWNAME
-
-
Starting restore at 07-APR-17
-
using channel ORA_DISK_1
-
-
channel ORA_DISK_1: starting datafile backup set restore
-
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
-
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/TEST/system01.dbf
-
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/TEST/sysaux01.dbf
-
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/TEST/undotbs01.dbf
-
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/TEST/users01.dbf
-
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/TEST/example01.dbf
-
channel ORA_DISK_1: reading from backup piece /home/oracle/full_07s13lfo_1_1.dbf
-
channel ORA_DISK_1: piece handle=/home/oracle/full_07s13lfo_1_1.dbf tag=TAG20170407T153624
-
channel ORA_DISK_1: restored backup piece 1
-
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
-
Finished restore at 07-APR-17
-
-
datafile 1 switched to datafile copy
-
input datafile copy RECID=7 STAMP=940697601 file name=/u01/app/oracle/oradata/TEST/system01.dbf
-
datafile 2 switched to datafile copy
-
input datafile copy RECID=8 STAMP=940697601 file name=/u01/app/oracle/oradata/TEST/sysaux01.dbf
-
datafile 3 switched to datafile copy
-
input datafile copy RECID=9 STAMP=940697601 file name=/u01/app/oracle/oradata/TEST/undotbs01.dbf
-
datafile 4 switched to datafile copy
-
input datafile copy RECID=10 STAMP=940697601 file name=/u01/app/oracle/oradata/TEST/users01.dbf
-
datafile 5 switched to datafile copy
-
input datafile copy RECID=11 STAMP=940697601 file name=/u01/app/oracle/oradata/TEST/example01.dbf
-
-
Starting recover at 07-APR-17
-
using channel ORA_DISK_1
-
-
starting media recovery
-
-
unable to find archived log
-
archived log thread=1 sequence=6
-
RMAN-00571: ===========================================================
-
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
-
RMAN-00571: ===========================================================
-
RMAN-03002: failure of recover command at 04/07/2017 16:53:21
-
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 1010272
-
提示报错,按SCN号恢复数据库
-
RMAN> run{
-
2> set until SCN 1010272;
-
3> restore database;
-
4> switch datafile all;
-
5> recover database;
-
6> }
-
-
executing command: SET until clause
-
-
Starting restore at 07-APR-17
-
using channel ORA_DISK_1
-
-
skipping datafile 1; already restored to file /u01/app/oracle/oradata/TEST/system01.dbf
-
skipping datafile 2; already restored to file /u01/app/oracle/oradata/TEST/sysaux01.dbf
-
skipping datafile 3; already restored to file /u01/app/oracle/oradata/TEST/undotbs01.dbf
-
skipping datafile 4; already restored to file /u01/app/oracle/oradata/TEST/users01.dbf
-
skipping datafile 5; already restored to file /u01/app/oracle/oradata/TEST/example01.dbf
-
restore not done; all files read only, offline, or already restored
-
Finished restore at 07-APR-17
-
-
-
Starting recover at 07-APR-17
-
using channel ORA_DISK_1
-
-
starting media recovery
-
media recovery complete, elapsed time: 00:00:00
-
-
Finished recover at 07-APR-17
-
以resetlogs方式打开数据库
-
RMAN> alter database open resetlogs;
-
-
RMAN-00571: ===========================================================
-
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
-
RMAN-00571: ===========================================================
-
RMAN-03002: failure of alter db command at 04/07/2017 17:24:09
-
ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/PROD1/redo01.log'
-
ORA-27040: file create error, unable to create file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 1
-
提示报错(这是因为控制文件记录的还是源端日志信息) 改为正确路径
-
RMAN> run{
-
2> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo01.log'' TO ''/u01/app/oracle/oradata/TEST/redo01.log'' ";
-
3> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo02.log'' TO ''/u01/app/oracle/oradata/TEST/redo02.log'' ";
-
4> SQL "ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo03.log'' TO ''/u01/app/oracle/oradata/TEST/redo03.log'' ";
-
5> }
-
-
sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo01.log'' TO ''/u01/app/oracle/oradata/TEST/redo01.log''
-
-
sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo02.log'' TO ''/u01/app/oracle/oradata/TEST/redo02.log''
-
-
sql statement: ALTER DATABASE RENAME FILE ''/u01/app/oracle/oradata/PROD1/redo03.log'' TO ''/u01/app/oracle/oradata/TEST/redo03.log''
-
再次resetlogs开库
-
RMAN> alter database open resetlogs;
-
-
RMAN-00571: ===========================================================
-
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
-
RMAN-00571: ===========================================================
-
RMAN-03002: failure of alter db command at 04/07/2017 17:29:46
-
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
-
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/TEST/redo03.log'
-
根据提示 将日志组clear ,开库
-
RMAN> sql "alter database clear logfile group 3";
-
-
sql statement: alter database clear logfile group 3
-
-
RMAN> alter database open resetlogs;
-
-
database opened
-
配置目标库到open阶段
rename临时表空间
-
SQL> select file_name from dba_temp_files;
-
select file_name from dba_temp_files
-
*
-
ERROR at line 1:
-
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
-
ORA-01110: data file 201: '/u01/app/oracle/oradata/PROD1/temp01.dbf'
-
-
-
SQL> shutdown immediate
-
Database closed.
-
Database dismounted.
-
ORACLE instance shut down.
-
SQL> startup mount;
-
ORACLE instance started.
-
-
Total System Global Area 830930944 bytes
-
Fixed Size 2257800 bytes
-
Variable Size 536874104 bytes
-
Database Buffers 289406976 bytes
-
Redo Buffers 2392064 bytes
-
Database mounted.
-
SQL> alter database rename file '/u01/app/oracle/oradata/PROD1/temp01.dbf' to '/u01/app/oracle/oradata/TEST/temp01.dbf';
-
-
Database altered.
-
-
SQL> alter database open;
-
-
Database altered.
-
-
SQL> select file_name from dba_temp_files;
-
-
FILE_NAME
-
--------------------------------------------------------------------------------
-
/u01/app/oracle/oradata/TEST/temp01.dbf
-
-
SQL> !ls
-
adump control02.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
-
control01.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
更改数据库名字及DBID
-
数据库启动mount
-
SQL> shutdown immediate
-
Database closed.
-
Database dismounted.
-
ORACLE instance shut down.
-
SQL> startup mount;
-
ORACLE instance started.
-
-
Total System Global Area 830930944 bytes
-
Fixed Size 2257800 bytes
-
Variable Size 536874104 bytes
-
Database Buffers 289406976 bytes
-
Redo Buffers 2392064 bytes
-
Database mounted.
-
nid target=sys/oracle dbname=newname
-
[oracle@host02 abc]$ nid target=sys/oracle dbname=TEST
-
-
DBNEWID: Release 11.2.0.4.0 - Production on Fri Apr 7 18:20:33 2017
-
-
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-
-
Connected to database PROD1 (DBID=2157889328)
-
-
Connected to server version 11.2.0
-
-
Control Files in database:
-
/u01/app/oracle/oradata/TEST/control01.ctl
-
/u01/app/oracle/oradata/TEST/control02.ctl
-
-
Change database ID and database name PROD1 to TEST? (Y/[N]) => Y
-
-
Proceeding with operation
-
Changing database ID from 2157889328 to 2238816753
-
Changing database name from PROD1 to TEST
-
Control File /u01/app/oracle/oradata/TEST/control01.ctl - modified
-
Control File /u01/app/oracle/oradata/TEST/control02.ctl - modified
-
Datafile /u01/app/oracle/oradata/TEST/system01.db - dbid changed, wrote new name
-
Datafile /u01/app/oracle/oradata/TEST/sysaux01.db - dbid changed, wrote new name
-
Datafile /u01/app/oracle/oradata/TEST/undotbs01.db - dbid changed, wrote new name
-
Datafile /u01/app/oracle/oradata/TEST/users01.db - dbid changed, wrote new name
-
Datafile /u01/app/oracle/oradata/TEST/example01.db - dbid changed, wrote new name
-
Datafile /u01/app/oracle/oradata/TEST/temp01.db - dbid changed, wrote new name
-
Control File /u01/app/oracle/oradata/TEST/control01.ctl - dbid changed, wrote new name
-
Control File /u01/app/oracle/oradata/TEST/control02.ctl - dbid changed, wrote new name
-
Instance shut down
-
-
Database name changed to TEST.
-
Modify parameter file and generate a new password file before restarting.
-
Database ID for database TEST changed to 2238816753.
-
All previous backups and archived redo logs for this database are unusable.
-
Database has been shutdown, open database with RESETLOGS option.
-
Succesfully changed database name and ID.
-
DBNEWID - Completed succesfully.
-
通过spfile生成pfile,修改pfile的dbname为新的名字
-
SQL> create pfile from spfile;
-
-
File created.
-
-
[oracle@host02 abc]$ cd $ORACLE_HOME/dbs
-
[oracle@host02 dbs]$ ls
-
hc_TEST.dat init.ora initTEST.ora lkPROD1 spfileTEST.ora
-
[oracle@host02 dbs]$ vi initTEST.ora
-
TEST.__db_cache_size=289406976
-
TEST.__java_pool_size=4194304
-
TEST.__large_pool_size=71303168
-
TEST.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
-
TEST.__pga_aggregate_target=335544320
-
TEST.__sga_target=499122176
-
TEST.__shared_io_pool_size=0
-
TEST.__shared_pool_size=125829120
-
TEST.__streams_pool_size=0
-
*.audit_file_dest='/u01/app/oracle/admin/TEST/adump'
-
*.audit_trail='db'
-
*.compatible='11.2.0.4.0'
-
*.control_files='/u01/app/oracle/oradata/TEST/control01.ctl','/u01/app/oracle/oradata/TEST/control02.ctl'#Restore Controlfile
-
*.db_block_size=8192
-
*.db_domain=''
-
*.db_name='TEST'
-
*.diagnostic_dest='/u01/app/oracle'
-
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
-
*.log_archive_dest_1='location=/home/oracle/arch1'
-
*.log_archive_dest_2='location=/home/oracle/arch2'
-
*.memory_target=833617920
-
*.open_cursors=300
-
*.processes=150
-
*.remote_login_passwordfile='EXCLUSIVE'
-
*.undo_tablespace='UNDOTBS1'
-
通过pfile生成spfile
-
SQL> create spfile from pfile;
-
-
File created.
-
启动,resetlogs
-
SQL> alter database open resetlogs;
-
-
Database altered.
-
验证
-
[oracle@host02 dbs]$ rman target /
-
-
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 7 18:31:05 2017
-
-
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-
-
connected to target database: TEST (DBID=2238816753)
-
[oracle@host01 ~]$ rman target /
-
-
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 7 18:31:01 2017
-
-
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-
-
connected to target database: PROD1 (DBID=2157889328)
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/31405405/viewspace-2136824/
,如需转载,请注明出处,否则将追究法律责任。