oracle 11g dataguard创建的简单方法

oracle 10g可以通过基于备份的rman DUPLICATE实现dataguard，通过步骤需要对数据库进行备份，并在standby侧进行数据库的恢复。
而到了11g，oracle推出了Duplicate From Active Database技术，不需要再对数据库进行rman备份恢复，一切动作都通过网络自动完成。

主库已在归档模式

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch
Oldest online log sequence     8
Next log sequence to archive   10
Current log sequence           10

 

SQL> alter database force logging;

SQL> alter database add standby logfile group 4 '/u01/app/oracle/flash_recovery_area/SALES/standbylog/standby01.log' size 50M;
SQL> alter database add standby logfile group 5 '/u01/app/oracle/flash_recovery_area/SALES/standbylog/standby02.log' size 50M;
SQL> alter database add standby logfile group 6 '/u01/app/oracle/flash_recovery_area/SALES/standbylog/standby03.log' size 50M;
SQL> alter database add standby logfile group 7 '/u01/app/oracle/flash_recovery_area/SALES/standbylog/standby04.log' size 50M;

SQL> alter system set log_archive_config='dg_config=(sales,dgsales)';
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch';
SQL> alter system set log_archive_dest_2 ='service=dgsales async valid_for=(online_logfile,primary_role) db_unique_name=dgsales';
SQL> alter system set fal_client='sales' scope=spfile;
SQL> alter system set fal_server='dgsales' scope=spfile;
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/SALES/','/u01/app/oracle/oradata/DGSALES/' scope=spfile;
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/SALES/','/u01/app/oracle/oradata/DGSALES/','/u01/app/oracle/flash_recovery_area/SALES/','/u01/app/oracle/flash_recovery_area/DGSALES/' scope=spfile;

SQL> shutdown immediate
SQL> startup
创建用于备库的参数文件
SQL> create pfile='/home/oracle/initsales.ora' from spfile;
cat /home/oracle/initsales.ora
文件内容如下：
sales.__db_cache_size=721420288
sales.__java_pool_size=16777216
sales.__large_pool_size=16777216
sales.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
sales.__pga_aggregate_target=822083584
sales.__sga_target=1207959552
sales.__shared_io_pool_size=0
sales.__shared_pool_size=436207616
sales.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/sales/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/SALES/controlfile/o1_mf_cb0g8x26_.ctl','/u01/app/oracle/flash_recovery_area/SALES/controlfile/o1_mf_cb0g8x3g_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_name='sales'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=salesXDB)'
*.log_archive_config='dg_config=(sales,dgsales)'
*.log_archive_dest_1='location=/home/oracle/arch'
*.log_archive_dest_2='service=dgsales async valid_for=(online_logfile,primary_role) db_unique_name=dgsales'
*.log_archive_format='sales_%t_%s_%r.log'
*.fal_client='sales'
*.fal_server='dgsales'
*.memory_target=2022703104
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

传输initsales.ora到备库
scp /home/oracle/initsales.ora root@192.168.59.130:/home/oracle/

mv /home/oracle/initsales.ora /home/oracle/initdgsales.ora
修改initdgsales.ora
vi /home/oracle/initdgsales.ora

dgsales.__db_cache_size=721420288
dgsales.__java_pool_size=16777216
dgsales.__large_pool_size=16777216
dgsales.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dgsales.__pga_aggregate_target=822083584
dgsales.__sga_target=1207959552
dgsales.__shared_io_pool_size=0
dgsales.__shared_pool_size=436207616
dgsales.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dgsales/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/DGSALES/controlfile/control01.ctl','/u01/app/oracle/flash_recovery_area/DGSALES/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/SALES/','/u01/app/oracle/oradata/DGSALES/'
*.db_name='dgsales'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='dgsales'
*.fal_server='sales'
*.log_archive_config='dg_config=(sales,dgsales)'
*.log_archive_dest_1='location=/home/oracle/arch'
*.log_archive_dest_2='service=sales async valid_for=(online_logfile,primary_role) db_unique_name=sales'
*.log_archive_format='dgsales_%t_%s_%r.log'
*.log_file_name_convert='/u01/app/oracle/oradata/SALES/','/u01/app/oracle/oradata/DGSALES/','/u01/app/oracle/flash_recovery_area/SALES/','/u01/app/oracle/flash_recovery_area/DGSALES/'
*.memory_target=2022703104
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

备库
创建目录
mkdir -p /u01/app/oracle/admin/dgsales/adump
mkdir -p /u01/app/oracle/oradata/DGSALES
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /home/oracle/arch

主库备库tnsnames.ora添加条目：
SALES =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.129)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sales)
    )
  )

DGSALES =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.130)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dgsales)
    )
  )

 

主库listener.ora静态注册备库监听
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
        (GLOBAL_DBNAME = dgsales)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        (SID_NAME = dgsales)
     )
   )

ADR_BASE_LISTENER = /u01/app/oracle

重启监听
lsnrctl stop
lsnrctl start

备库listener.ora静态注册主备监听
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
        (GLOBAL_DBNAME = sales)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        (SID_NAME = sales)
     )
     (SID_DESC =
        (GLOBAL_DBNAME = dgsales)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        (SID_NAME = dgsales)
     )

   )

ADR_BASE_LISTENER = /u01/app/oracle

重启监听
lsnrctl stop
lsnrctl start

传输主库sys口令文件到备库
scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwsales oracle@192.168.59.130://u01/app/oracle/product/11.2.0/db_1/dbs/
mv /u01/app/oracle/product/11.2.0/db_1/dbs/orapwsales /u01/app/oracle/product/11.2.0/db_1/dbs/orapwdgsales

启动standby 数据库到nomount
export $ORACLE_SID=dgsales
sqlplus / as sysdba
startup nomount pfile='/home/oracle/initdgsales.ora'

 

主库
rman target sys/oracle219@sales auxiliary sys/oracle219@dgsales

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database
for standby
from active database
dorecover
spfile
parameter_value_convert 'sales','dgsales'
set db_unique_name='dgsales'
set db_file_name_convert='/SALES/','/DGSALES/'
set log_file_name_convert='/SALES/','/DGSALES/'
set control_files='/u01/app/oracle/oradata/DGSALES/controlfile/dgsales01.ctl','/u01/app/oracle/oradata/DGSALES/controlfile/dgsales02.ctl'
set log_archive_max_processes='5'
set fal_client='dgsales'
set fal_server='sales'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(sales,dgsales)'
set log_archive_dest_2='service=sales async valid_for=(online_logfile,primary_role) db_unique_name=sales' ;
set log_archive_dest_state_2='enable'
set log_archive_format='dgsales_%t_%s_%r.log'
sql channel c1 "alter system archive log current";
sql channel s1 "alter database recover managed standby database using current logfile disconnect";
}