使用rman中的duplicate复制数据库(数据文件路径一致)

目标库和复制库环境:

OS: Linux Red Hat AS 4

DB Version: 11.2.0.4

 

1.目标库和复制库信息

Rman 中的目标库(target database)指的是被复制的库,复制库(duplicate database)是指复制后生成的新库(生成新的DBID).这里为了测试方便,复制库和目标库的目录结构设置相同.

 

目标库:

IP:192.168.56.101

SID:slnngk

db_name:slnngk

复制库:

IP:192.168.56.102

SID:slnngk

db_name:slnngk

2.在辅助库上安装数据库软件

前提是需要安装oracle软件(不创建数据库),需要创建oracle用户.

3.创建辅助库初始化化参数文件
主库生成pifle文件

SQL> connect / as sysdba

Connected.

SQL> create pfile='/home/oracle/initslnngk.ora' from spfile;

 

File created.

将pfile文件传送到辅助库的$ORACLE_HOME/dbs目录

 

[oracle@hxl01 ~]$ scp initslnngk.ora oracle@192.168.56.102:/u01/app/oracle/product/11.2.0.4/db_1/dbs/

原来主库的参数文件

[oracle@hxl02 ~]$ more initslnngk.ora

slnngk.__db_cache_size=331350016

slnngk.__java_pool_size=4194304

slnngk.__large_pool_size=8388608

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

slnngk.__pga_aggregate_target=96468992

slnngk.__sga_target=473956352

slnngk.__shared_io_pool_size=0

slnngk.__shared_pool_size=121634816

slnngk.__streams_pool_size=0

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

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/slnngk/control01.ctl','/u01/app/oracle/fast_recovery_area/slnngk/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='slnngk'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=slnngkXDB)'

*.memory_target=0

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=471859200

*.sga_target=471859200

*.undo_tablespace='UNDOTBS1'

修改后的参数文件

[oracle@hxl02 ~]$ more initslnngk.ora

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

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

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/slnngk/control01.ctl','/u01/app/oracle/fast_recovery_area/slnngk/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='slnngk'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=slnngkXDB)'

*.memory_target=0

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=471859200

*.sga_target=471859200

*.undo_tablespace='UNDOTBS1'

我这里主要是把个内存分配参数去掉了，因为生产环境主库和目的库的配置不一样，要是修改这些参数的话，只要修改sga_max_size和sga_target即可.

4.创建初始化参数文件里定义的目录

control_files参数指定的路径

audit_file_dest 参数指定的路径

db_recovery_file_dest 指定的路径

[oracle@hxl02 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area

[oracle@hxl02 ~]$ mkdir -p /u01/app/oracle/admin/slnngk/adump

[oracle@hxl02 ~]$ mkdir -p /u01/app/oracle/oradata/slnngk

[oracle@hxl02 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/slnngk

5.备份主库

run{

  allocate channel c1 device type disk;

  allocate channel c2 device type disk;

  allocate channel c3 device type disk;

  allocate channel c4 device type disk;

  backup database format '/home/oracle/rman_bak/full_%u_%T.bak';

  backup spfile format '/home/oracle/rman_bak/spfile_%u_%T.bak';

  backup current controlfile format '/home/oracle/rman_bak/ctl_%u_%T.bak';

  sql 'alter system archive log current';

  backup archivelog all delete input format '/home/oracle/rman_bak/arc_%u_%T.bak';

  release channel c1;

  release channel c2;

  release channel c3;

  release channel c4;

}

6.拷贝主库的备份集到辅助库的同样的目录

[oracle@hxl01 rman_bak]$ scp *.bak oracle@192.168.56.102:/home/oracle/rman_bak/

7.辅助端配置监听
这里需要配置静态注册的方式

[oracle@hxl02 admin]$ more listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = hxl02)(PORT = 1521))

    )

  )

 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = slnngk)

      (ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/db_1)

      (SID_NAME =slnngk)

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

8.辅助端配置tns

cd /u01/app/oracle/product/11.2.0.4/db_1/network/admin

vi tnsnames.ora

该文件内容如下:

[oracle@hxl02 admin]$ more tnsnames.ora

tns_slnngk =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = slnngk)

    )

  )

 

tns_slnngk01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = slnngk)

    )

  )

tns_slnngk 指向主数据库

tns_slnngk01指向辅助数据库

同时启动监听器
lsnrctl start


9.创建密码文件
手工创建密码文件
orapwd file=$ORACLE_HOME/dbs/orapwslnngk password=pwdslnngk entries=10 force=y
这里的密码是pwdslnngk,连接的时候需要指定该密码,否则密码不对会报如下错误

oracle@hxl02 admin]$ rman target sys/oracle@tns_slnngk auxiliary sys/123456@tns_slnngk01

 

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

[oracle@hxl02 admin]$

10.环境变量设置ORACLE_SID

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

 

# User specific environment and startup programs

 

export EDITOR=vi

export ORACLE_SID=slnngk

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin:/bin

umask 022

11.启动数据到nomount状态

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  471830528 bytes

Fixed Size                  2254344 bytes

Variable Size             197134840 bytes

Database Buffers          268435456 bytes

Redo Buffers                4005888 bytes

这里启动没有特别指定pfile,数据库会自动找到$ORACLE_HOME/dbs/initslnngk.ora的初始化参数启动,若找到不到该参数文件会报如下错误:

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/initslnngk.ora'
SQL>

12.创建spfile然后以spfile启动数据库

[oracle@hxl02 dbs]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 26 05:40:59 2018

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

SQL> connect / as sysdba

Connected.

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initslnngk.ora';

 

File created.

以pfile启动

SQL> shutdown immediate

ORA-01507: database not mounted

 

 

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  471830528 bytes

Fixed Size                  2254344 bytes

Variable Size             197134840 bytes

Database Buffers          268435456 bytes

Redo Buffers                4005888 bytes

SQL>

13.创建duplicate database

执行复制命令,必须以sys连接源数据库

rman target sys/oracle@tns_slnngk

RMAN> connect auxiliary /

RMAN> duplicate target database to slnngk nofilenamecheck;

或是这样

rman target sys/oracle@tns_slnngk auxiliary sys/pwdslnngk@tns_slnngk01

RMAN> duplicate target database to slnngk nofilenamecheck;

tns_slnngk01指向辅助数据库

这里不指定redo日志文件的话,默认会在fast_recovery_area下创建，如下:

SQL> Column group# format 99;

SQL> Column Member format a80;

SQL> Select group#,Member From v$logfile Order By group#;

 

GROUP# MEMBER

------ --------------------------------------------------------------------------------

     1 /u01/app/oracle/fast_recovery_area/SLNNGK/onlinelog/o1_mf_1_fm436jxb_.log

     2 /u01/app/oracle/fast_recovery_area/SLNNGK/onlinelog/o1_mf_2_fm436kb0_.log

     3 /u01/app/oracle/fast_recovery_area/SLNNGK/onlinelog/o1_mf_3_fm436koz_.log

所以上面的duplicate命令可以修改如下:

RMAN>run{

duplicate target database to slnngk nofilenamecheck

logfile group 1 ('/u01/app/oracle/oradata/slnngk/redo01_01.log','/u01/app/oracle/oradata/slnngk/redo01_02.log') size 50M,

group 2 ('/u01/app/oracle/oradata/slnngk/redo02_01.log','/u01/app/oracle/oradata/slnngk/redo02_02.log') size 50M,

group 3 ('/u01/app/oracle/oradata/slnngk/redo03_01.log','/u01/app/oracle/oradata/slnngk/redo03_02.log') size 50M;

}

14.复制完成后检查数据库是否开启

[oracle@hxl02 admin]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 26 05:54:39 2018

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

SQL> connect / as sysdba

Connected.

SQL> select status from v$instance;    

 

STATUS

------------

OPEN

 

SQL> select open_mode from v$database;

 

OPEN_MODE

--------------------

READ WRITE

15.添加临时表空间文件
根据需要添加临时表空间文件

常见问题:
1.恢复时找不到归档日志的情况

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/27/2018 22:42:27
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 22 and starting SCN of 1291654 found to restore

可以采用如下方法:
报错后需要重新startup nomount后再重新执行如下命令

duplicate target database to slnngk nofilenamecheck until scn 1291654;
duplicate target database to slnngk nofilenamecheck until sequence 52;

2.更改控制文件路径
若有多个控制文件的情况下，更改control_files参数的时候，需要在每个控制文件加上单引号，如下：
alter system set control_files='/u01/app/oracle/oradata/slnngk/control01.ctl','/u01/app/oracle/oradata/slnngk/control02.ctl' scope=spfile;