Duplicate 复制数据库 搭建Dataguard

1 操作系统环境

　　此处隐藏具体信息

System	IP-address	db_name	db_version	Comment
				Target DB
				Auxiliary DB

2 复制数据库前的准备工作

 

2.1 standby 端安装数据库软件

2.2 primary 与standby 端生成密码文件

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=5

 

2.3 修改参数文件

　　如果standby端，不需要后期的日志应用，那么无需配置dataguard相关参数。只配置db_name参数即可启动到nomount状态。

db_name=
compatible='11.2.0.4.0'
db_create_file_dest=
#control_files=
#db_block_size=

 

备注： 

If OMF were not used , we must configure the Parameter control_files manually.

If the target database's db_block_size is not the default 8192, we must configure the auxiliary database as same as the target.

If undo_tablespace is set , the value in auxiliary must the same with the target.

如果作为standby 使用，还需要添加dataguard相关参数：

*.db_name='&db_name' 
*.db_unique_name='&db_unique_name'
*.fal_client='primary_tns' 
*.fal_server='standby_tns' 
*.log_archive_config='DG_CONFIG=(primary_tns,standby_tns)' 
*.log_archive_dest_1='LOCATION=/data/oracle/product/11.2.0/dbhome_1/dbs/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=&db_unique_name' 
*.log_archive_dest_2='SERVICE=upgdbs LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=&db_unique_name' 
*.log_archive_dest_state_1='ENABLE' 
*.log_archive_dest_state_2='ENABLE' 
*.log_archive_format='%t_%s_%r.arc' 
*.log_file_name_convert='/data/oracle/oradata','/data/oracle/oradata'
*.db_file_name_convert='/data/oracle/oradata','/data/oracle/oradata'
*.standby_file_management='auto'

 

Note 

其中log_file_name_convert 参数在standby端必须要设置，即使primary 与standby 的文件存储路径完全一致。 否则在duplicate命令处理redo log时会提示:

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 7 thread 0: '/data/oracle/oradata/redo701.log'

2.4 网络配置

　　在这一步中所有需要配置的文件都在 $ORACLE_HOME/network/admin/中.

• Listener

　　由于standby 端只有软件，实例无法启动到mount状态，此时PMON进程无法完成自动注册。远程客户端只有通过静态监听，才能连接该实例。
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

　　静态监听:

sid_list_listener = 
(sid_list =
  (sid_desc =
   (global_dbname= )
   (sid_name= )
   (oracle_home=/opt/app/oracle/product/11.2.0/dbhome_1)
  )
 )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /opt/app/oracle

 

• TNS

target_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = )
    )
  )

auxiliary_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = )
    )
  )

 

Notes 

Be sure to fill the blank with right value, such as service_name, host

2.5 创建standby redo log

假如目标库是作为standby 使用的，那必须在主库创建好standby redolog. 否则在duplicate过程中会提示： 添加standby redo log 示例:

alter database add logfile group No. ('member1','member2') size 2G;

3 Auxiliary 库启动到nomount

sqlplus sys/oracle@auxiliary_db as sysdba
startup nomount;
exit

4 主库启动到open状态

sqlplus sys/oracle@target_db as sysdba
startup
select dbid,open_mode from v$database;

5 复制新库

　　在执行复制之前，需要考虑以下两点问题：

1. auxiliary 库是否使用的是spfile.如果不是的话，先创建一个spfile>
2. 如果primary 与standby 两端数据文件路径，日志文件路径不同，可以通过下面SQL 为RMAN生成"set newname for " 语句

select 'set newname for datafile '||file_id||' to ''&&path'||tablespace_name||'_'||rank() over (partition by tablespace_name order by file_id)||'.dbf'';'
from dba_data_files 
union all
select 'set newname for tempfile '||file_id||' to ''&&path'||tablespace_name||'_'||rank() over (partition by tablespace_name order by file_id)||'.dbf'';'
from dba_temp_files

 

示例1： 
可用于复制数据库（非dataguard)

su - oracle
rman target sys/oracle@target_db sys/oracle@auxiliary_db
run
{
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
set newname for database to '/data/oradata/%b';
duplicate target database to &ORACLE_SID from active database;
logfile 
group 1 ('/data/oradata/redo1.log') size 2G,
group 2 ('/data/oradata/redo2.log') size 2G,
group 3 ('/data/oradata/redo3.log') size 2G;
release channel c1;
release channel c2;
release channel c3;
}

 

示例2

可用于搭建standby(,适用于dataguard)

run
{
allocate channel cl1 type disk;
allocate channel cl2 type disk;
allocate channel cl3 type disk;
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
duplicate target database for standby from active database nofilenamecheck dorecover;
release channel c1;
release channel c2;
release channel c3;
release channel cl1;
release channel cl2;
release channel cl3;
}

 

note 

1. 手动分配复制通道时(allocate channel) 必须要加上allocate auxiliary channel，否则会提示:

   RMAN-05503: at least one auxiliary channel must be allocated to execute this command
   

2. 如果数据库没有做过全备，则duplicate命令必须带关键词" from active database",否则会提示:

   RMAN-06024: no backup or copy of the control file found to restore
   

3. 如果duplicate命令中使用关键词" from active database",则必须为主库分配通道。否则会提示:

   RMAN-06034: at least 1 channel must be allocated to execute this command
   

4. 相关路径必须存在，比如控制文件路径不存在，在恢复时会提示:

   ORA-17628: Oracle error 19505 returned by remote Oracle server