duplicate 数据库 from backupset [oracle 11.2.0.3 + asm] => [oracle 11.2.0.3 + fs ]
参考 rhel64_11gr2_asm.txt 安装好另一台机器的rhel6.4,gi software,rdbms software。
vi /etc/hosts
10.1.1.35 asmnode
10.1.1.36 asmnodedup
在asmnode 节点 查看initdbasm.ora 文件,确保target database 处于归档模式,否则duplicate 会报错
[oracle@asmnode dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@asmnode dbs]$ ls -l
total 28
-rw-rw---- 1 oracle asmadmin 1544 Apr 25 21:49 hc_dbasm.dat
-rw-r----- 1 oracle oinstall 40 Apr 25 21:49 initdbasm.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle asmadmin 24 Apr 25 21:06 lkDBASM
-rw-r----- 1 oracle oinstall 1536 Apr 25 21:06 orapwdbasm
[oracle@asmnode dbs]$ cat initdbasm.ora
SPFILE='+DG_DATA/dbasm/spfiledbasm.ora'
在asmnode 节点 用oracle 用户创建 参数文件 abc.ora 文件
[oracle@asmnode dbs]$ sqlplus / as sysdba;
sql> create pfile='?/dbs/abc.ora' from memory;
run {
allocate channel ch00 device type disk;
sql 'alter system checkpoint';
backup as compressed backupset database
include current controlfile format '/u01/backup/DB_%T_%U'
plus archivelog delete all input format '/u01/backup/ARCH_%T_%U';
release channel ch00;
}
单纯归档备份, archivelog 必须紧接着 all
run {
allocate channel ch00 device type disk;
sql 'alter system checkpoint';
backup as compressed backupset archivelog all delete all input format '/u01/backup/arch_%T_%U';
release channel ch00;
}
备份过程中均有备份集的存放路径输出
查看备份集
[oracle@asmnode backup]$ cd /u01/backup/
[oracle@asmnode backup]$ ls -l
total 251104
-rw-r----- 1 oracle asmadmin 42496 May 11 16:48 ARCH_20160511_24r5b7av_1_1
-rw-r----- 1 oracle asmadmin 2560 May 11 16:49 ARCH_20160511_27r5b7cq_1_1
-rw-r----- 1 oracle asmadmin 255967232 May 11 16:49 DB_20160511_25r5b7b1_1_1
-rw-r----- 1 oracle asmadmin 1114112 May 11 16:49 DB_20160511_26r5b7co_1_1
把备份集拷贝到asmnodedup 节点的相同路径下,如果不是相同路径下,可通过ln -s 来解决
[oracle@asmnode backup]$ scp /u01/backup/ARCH* oracle@10.1.1.36:/u01/backup/
[oracle@asmnode backup]$ scp /u01/backup/DB* oracle@10.1.1.36:/u01/backup/
在 asmnodedup 节点 创建pfile参数文件
export ORACLE_SID = dbasmd
[grid@asmnodedup dbs]$ vi /u01/app/grid/product/11.2.0/grid_1/dbs/initdbasmd.ora
*.audit_file_dest='/u01/app/oracle/admin/dbasmd/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/dbasmd/current.260.911598793'#Set by RMAN
*.core_dump_dest='/u01/app/oracle/diag/rdbms/dbasmd/dbasmd/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBASMD'#Reset to original value by RMAN
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbasmXDB)'
*.log_buffer=7020544# log buffer update
*.memory_target=1536M
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=300
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.result_cache_max_size=3936K
*.sessions=472
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTBS1'
#*.db_create_file_dest='/u01/app/oracle/oradata/dbasmd/'
*.db_file_name_convert=('+DG_DATA/dbasm/','/u01/app/oracle/oradata/dbasmd/')
*.log_file_name_convert=('+DG_DATA/dbasm/','/u01/app/oracle/oradata/dbasmd/')
需要修改db_file_name_convert,log_file_name_convert
或者直接设定 db_create_file_dest
在 asmnodedup 节点 创建必要的目录
mkdir -p /u01/app/oracle/admin/dbasmd/adump
mkdir -p /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace
mkdir -p /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/cdump
mkdir -p /u01/app/oracle/oradata/dbasmd/{controlfile,datafile,tempfile,onlinelog}
需要保证target和auxiliary 库的sys用户口令相同。
从asmnode 拷贝原数据库的密码文件到 asmnodedup 节点 oracle 用户 $ORACLE_HOME/dbs 目录下,然后重命名
[oracle@asmnode dbs]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwdbasm oracle@10.1.1.36:/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@asmnodedup dbs]$ mv orapwdbasm orapwdbasmd
或者在 asmnodedup 使用 orapwd 创建一个和target database 的sys一样密码的密码文件。
在 asmnodedup 节点 grid 用户 用asmcd 查看一下
[grid@asmnodedup bin]$ ./asmcmd
ASMCMD> ls -l
State Type Rebal Name
MOUNTED EXTERN N DG_DATA/
静态监听,两个节点都要添加
在 asmnode 节点 grid 用户下添加静态监听
[grid@asmnodedup admin]$ vi /u01/app/grid/product/11.2.0/grid_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =dbasm)
)
)
在 asmnodedup 节点 grid 用户下添加静态监听
[grid@asmnodedup admin]$ vi /u01/app/grid/product/11.2.0/grid_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =dbasmd)
)
)
在 asmnode, asmnodedup 节点 重启监听,看如输出状态为 UNKNOWN 就表示静态监听已添加成功
[grid@asmnodedup admin]$ lsnrctl stop
[grid@asmnodedup admin]$ lsnrctl start
Services Summary...
Service "dbasmd" has 1 instance(s).
Instance "dbasmd", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
在 asmnode,asmnodedup 节点, oracle 用户下 添加 tnsnames.ora 文件
tns_dbasm =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = asmnode)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbasm)
)
)
tns_dbasmd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = asmnodedup)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbasmd)
)
)
在 asmnodedup 节点 oracle 用户
[oracle@asmnodedup dbs]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 6 15:52:22 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdbasmd.ora';
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 956304848 bytes
Database Buffers 637534208 bytes
Redo Buffers 7344128 bytes
在 asmnodedup 节点 需要创建spfile,否则后面会报错 ORA-32001: write to SPFILE requested but no SPFILE is in use
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdbasmd.ora';
在 asmnodedup 节点 开始复制, auxiliary 需要启动到 nomount状态, 否则duplicate时就会报错
MAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
[oracle@asmnodedup dbs]$ rman target sys/oracle@tns_dbasm auxiliary sys/oracle@tns_dbasmd
Recovery Manager: Release 11.2.0.3.0 - Production on Fri May 6 16:12:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBASM (DBID=2252470157)
connected to auxiliary database: DBASMD (not mounted)
RMAN> duplicate target database to dbasmd ;
Starting Duplicate Db at 11-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 956304848 bytes
Database Buffers 637534208 bytes
Redo Buffers 7344128 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DBASM'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DBASMD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''DBASM'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DBASMD'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 956304848 bytes
Database Buffers 637534208 bytes
Redo Buffers 7344128 bytes
Starting restore at 11-MAY-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=245 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/DB_20160511_26r5b7co_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/backup/DB_20160511_26r5b7co_1_1 tag=TAG20160511T164832
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/dbasmd/controlfile/current.260.911598793
Finished restore at 11-MAY-16
database mounted
contents of Memory Script:
{
set until scn 1586002;
set newname for datafile 1 to
"/u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185";
set newname for datafile 2 to
"/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189";
set newname for datafile 3 to
"/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191";
set newname for datafile 4 to
"/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201";
restore
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 11-MAY-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/DB_20160511_25r5b7b1_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/backup/DB_20160511_25r5b7b1_1_1 tag=TAG20160511T164832
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 11-MAY-16
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201
contents of Memory Script:
{
set until scn 1586002;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 11-MAY-16
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=113
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/ARCH_20160511_27r5b7cq_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/backup/ARCH_20160511_27r5b7cq_1_1 tag=TAG20160511T164930
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_113_910127181.dbf thread=1 sequence=113
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_113_910127181.dbf RECID=32 STAMP=911600707
media recovery complete, elapsed time: 00:00:04
Finished recover at 11-MAY-16
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 956304848 bytes
Database Buffers 637534208 bytes
Redo Buffers 7344128 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DBASMD'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DBASMD'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 956304848 bytes
Database Buffers 637534208 bytes
Redo Buffers 7344128 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBASMD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/dbasmd/onlinelog/group_1.257.910127185' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/dbasmd/onlinelog/group_2.258.910127185' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/oradata/dbasmd/onlinelog/group_3.259.910127185' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/dbasmd/tempfile/temp.263.910127193";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189",
"/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191",
"/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/dbasmd/tempfile/temp.263.910127193 in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189 RECID=1 STAMP=911600724
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191 RECID=2 STAMP=911600724
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201 RECID=3 STAMP=911600724
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=911600724 file name=/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=911600724 file name=/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=911600724 file name=/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 11-MAY-16
至此,复制完成,用sqlplus 登陆
[oracle@asmnodedup bin]$ sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 9 16:55:37 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select sysdate from dual;
SYSDATE
------------
11-MAY-16
至此 duplicate 数据库完成
/*********************************************************************************/
vi /etc/hosts
10.1.1.35 asmnode
10.1.1.36 asmnodedup
在asmnode 节点 查看initdbasm.ora 文件,确保target database 处于归档模式,否则duplicate 会报错
[oracle@asmnode dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@asmnode dbs]$ ls -l
total 28
-rw-rw---- 1 oracle asmadmin 1544 Apr 25 21:49 hc_dbasm.dat
-rw-r----- 1 oracle oinstall 40 Apr 25 21:49 initdbasm.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle asmadmin 24 Apr 25 21:06 lkDBASM
-rw-r----- 1 oracle oinstall 1536 Apr 25 21:06 orapwdbasm
[oracle@asmnode dbs]$ cat initdbasm.ora
SPFILE='+DG_DATA/dbasm/spfiledbasm.ora'
在asmnode 节点 用oracle 用户创建 参数文件 abc.ora 文件
[oracle@asmnode dbs]$ sqlplus / as sysdba;
sql> create pfile='?/dbs/abc.ora' from memory;
在asmnode 节点 用oracle 用户创建备份集
run {
allocate channel ch00 device type disk;
sql 'alter system checkpoint';
backup as compressed backupset database
include current controlfile format '/u01/backup/DB_%T_%U'
plus archivelog delete all input format '/u01/backup/ARCH_%T_%U';
release channel ch00;
}
单纯归档备份, archivelog 必须紧接着 all
run {
allocate channel ch00 device type disk;
sql 'alter system checkpoint';
backup as compressed backupset archivelog all delete all input format '/u01/backup/arch_%T_%U';
release channel ch00;
}
备份过程中均有备份集的存放路径输出
查看备份集
[oracle@asmnode backup]$ cd /u01/backup/
[oracle@asmnode backup]$ ls -l
total 251104
-rw-r----- 1 oracle asmadmin 42496 May 11 16:48 ARCH_20160511_24r5b7av_1_1
-rw-r----- 1 oracle asmadmin 2560 May 11 16:49 ARCH_20160511_27r5b7cq_1_1
-rw-r----- 1 oracle asmadmin 255967232 May 11 16:49 DB_20160511_25r5b7b1_1_1
-rw-r----- 1 oracle asmadmin 1114112 May 11 16:49 DB_20160511_26r5b7co_1_1
把备份集拷贝到asmnodedup 节点的相同路径下,如果不是相同路径下,可通过ln -s 来解决
[oracle@asmnode backup]$ scp /u01/backup/ARCH* oracle@10.1.1.36:/u01/backup/
[oracle@asmnode backup]$ scp /u01/backup/DB* oracle@10.1.1.36:/u01/backup/
在 asmnodedup 节点 创建pfile参数文件
export ORACLE_SID = dbasmd
[grid@asmnodedup dbs]$ vi /u01/app/grid/product/11.2.0/grid_1/dbs/initdbasmd.ora
*.audit_file_dest='/u01/app/oracle/admin/dbasmd/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/dbasmd/current.260.911598793'#Set by RMAN
*.core_dump_dest='/u01/app/oracle/diag/rdbms/dbasmd/dbasmd/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBASMD'#Reset to original value by RMAN
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbasmXDB)'
*.log_buffer=7020544# log buffer update
*.memory_target=1536M
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=300
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.result_cache_max_size=3936K
*.sessions=472
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTBS1'
#*.db_create_file_dest='/u01/app/oracle/oradata/dbasmd/'
*.db_file_name_convert=('+DG_DATA/dbasm/','/u01/app/oracle/oradata/dbasmd/')
*.log_file_name_convert=('+DG_DATA/dbasm/','/u01/app/oracle/oradata/dbasmd/')
需要修改db_file_name_convert,log_file_name_convert
或者直接设定 db_create_file_dest
在 asmnodedup 节点 创建必要的目录
mkdir -p /u01/app/oracle/admin/dbasmd/adump
mkdir -p /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/trace
mkdir -p /u01/app/oracle/diag/rdbms/dbasmd/dbasmd/cdump
mkdir -p /u01/app/oracle/oradata/dbasmd/{controlfile,datafile,tempfile,onlinelog}
需要保证target和auxiliary 库的sys用户口令相同。
从asmnode 拷贝原数据库的密码文件到 asmnodedup 节点 oracle 用户 $ORACLE_HOME/dbs 目录下,然后重命名
[oracle@asmnode dbs]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwdbasm oracle@10.1.1.36:/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@asmnodedup dbs]$ mv orapwdbasm orapwdbasmd
或者在 asmnodedup 使用 orapwd 创建一个和target database 的sys一样密码的密码文件。
在 asmnodedup 节点 grid 用户 用asmcd 查看一下
[grid@asmnodedup bin]$ ./asmcmd
ASMCMD> ls -l
State Type Rebal Name
MOUNTED EXTERN N DG_DATA/
静态监听,两个节点都要添加
在 asmnode 节点 grid 用户下添加静态监听
[grid@asmnodedup admin]$ vi /u01/app/grid/product/11.2.0/grid_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =dbasm)
)
)
在 asmnodedup 节点 grid 用户下添加静态监听
[grid@asmnodedup admin]$ vi /u01/app/grid/product/11.2.0/grid_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =dbasmd)
)
)
在 asmnode, asmnodedup 节点 重启监听,看如输出状态为 UNKNOWN 就表示静态监听已添加成功
[grid@asmnodedup admin]$ lsnrctl stop
[grid@asmnodedup admin]$ lsnrctl start
Services Summary...
Service "dbasmd" has 1 instance(s).
Instance "dbasmd", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
在 asmnode,asmnodedup 节点, oracle 用户下 添加 tnsnames.ora 文件
tns_dbasm =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = asmnode)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbasm)
)
)
tns_dbasmd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = asmnodedup)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbasmd)
)
)
在 asmnodedup 节点 oracle 用户
[oracle@asmnodedup dbs]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 6 15:52:22 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdbasmd.ora';
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 956304848 bytes
Database Buffers 637534208 bytes
Redo Buffers 7344128 bytes
在 asmnodedup 节点 需要创建spfile,否则后面会报错 ORA-32001: write to SPFILE requested but no SPFILE is in use
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdbasmd.ora';
在 asmnodedup 节点 开始复制, auxiliary 需要启动到 nomount状态, 否则duplicate时就会报错
MAN-04006: error from auxiliary database: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
[oracle@asmnodedup dbs]$ rman target sys/oracle@tns_dbasm auxiliary sys/oracle@tns_dbasmd
Recovery Manager: Release 11.2.0.3.0 - Production on Fri May 6 16:12:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBASM (DBID=2252470157)
connected to auxiliary database: DBASMD (not mounted)
RMAN> duplicate target database to dbasmd ;
Starting Duplicate Db at 11-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 956304848 bytes
Database Buffers 637534208 bytes
Redo Buffers 7344128 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DBASM'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DBASMD'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''DBASM'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DBASMD'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 956304848 bytes
Database Buffers 637534208 bytes
Redo Buffers 7344128 bytes
Starting restore at 11-MAY-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=245 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/DB_20160511_26r5b7co_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/backup/DB_20160511_26r5b7co_1_1 tag=TAG20160511T164832
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/dbasmd/controlfile/current.260.911598793
Finished restore at 11-MAY-16
database mounted
contents of Memory Script:
{
set until scn 1586002;
set newname for datafile 1 to
"/u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185";
set newname for datafile 2 to
"/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189";
set newname for datafile 3 to
"/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191";
set newname for datafile 4 to
"/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201";
restore
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 11-MAY-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/DB_20160511_25r5b7b1_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/backup/DB_20160511_25r5b7b1_1_1 tag=TAG20160511T164832
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 11-MAY-16
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=911600705 file name=/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201
contents of Memory Script:
{
set until scn 1586002;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 11-MAY-16
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=113
channel ORA_AUX_DISK_1: reading from backup piece /u01/backup/ARCH_20160511_27r5b7cq_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/backup/ARCH_20160511_27r5b7cq_1_1 tag=TAG20160511T164930
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_113_910127181.dbf thread=1 sequence=113
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_113_910127181.dbf RECID=32 STAMP=911600707
media recovery complete, elapsed time: 00:00:04
Finished recover at 11-MAY-16
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 956304848 bytes
Database Buffers 637534208 bytes
Redo Buffers 7344128 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DBASMD'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DBASMD'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 956304848 bytes
Database Buffers 637534208 bytes
Redo Buffers 7344128 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBASMD" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/dbasmd/onlinelog/group_1.257.910127185' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/dbasmd/onlinelog/group_2.258.910127185' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/oradata/dbasmd/onlinelog/group_3.259.910127185' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/dbasmd/datafile/system.260.910127185'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/dbasmd/tempfile/temp.263.910127193";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189",
"/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191",
"/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/dbasmd/tempfile/temp.263.910127193 in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189 RECID=1 STAMP=911600724
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191 RECID=2 STAMP=911600724
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201 RECID=3 STAMP=911600724
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=911600724 file name=/u01/app/oracle/oradata/dbasmd/datafile/sysaux.261.910127189
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=911600724 file name=/u01/app/oracle/oradata/dbasmd/datafile/undotbs1.262.910127191
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=911600724 file name=/u01/app/oracle/oradata/dbasmd/datafile/users.264.910127201
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 11-MAY-16
至此,复制完成,用sqlplus 登陆
[oracle@asmnodedup bin]$ sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 9 16:55:37 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select sysdate from dual;
SYSDATE
------------
11-MAY-16
至此 duplicate 数据库完成
/*********************************************************************************/