11g dg 备库搭建多种方式 直接拷贝文件方式 备份恢复 duplicate（备注：密码文件非常重要！！！ orapwORACLE_SID格式，和ORACLE_SID有关切记） dg备库搭建方式有多种，一般采取3种方式：（本次使用1台虚机，运行2个实例，搭建dg环境）一、第一种单库的情况下，在配置完主备参数文件后，启动主备监听，将主库数据文件拷贝拷贝到备库，将主库上创建备库控制文件并放到相应目录文件，最后启动备库到mount状态，与主库同步差异日志，启动备库， 应用日志完成搭建；1、配置监听、tnsnames文件：主备库：[oracle@db1 admin]$ cat listener.oraLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521)) ) )SID_LIST_LISTENER= (SID_LIST = (SID_DESC =
(GLOBAL_DBNAME=prod)
(SID_NAME=prod)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
) (SID_DESC = (GLOBAL_DBNAME=proddg) (SID_NAME=prod) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) ) )ADR_BASE_LISTENER = /u01/app/oracle [oracle@db1 admin]$ cat tnsnames.orabak16 # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) ) PRODDG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = proddg) ) ) tnsping测试prod proddg通讯是否正常！ 2、修改主备参数文件（主备参数文件对应参数文件相反就可以了） 主库： [oracle@db1 ~]$ cat 1111.ora prod.__db_cache_size=654311424 prod.__java_pool_size=16777216 prod.__large_pool_size=33554432 prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment prod.__pga_aggregate_target=637534208 prod.__sga_target=956301312 prod.__shared_io_pool_size=0 prod.__shared_pool_size=234881024 prod.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/prod/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/oradata/prod/control01.ctl','/oradata/prod/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='prod' *.db_recovery_file_dest='/flash_recovery' *.db_recovery_file_dest_size=10737418240 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)' *.log_archive_dest_1='location=/archivelog' *.log_archive_format='%t_%s_%r.arc' *.memory_target=1580204032 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=prod LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,proddg)' LOG_ARCHIVE_DEST_1= 'LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' LOG_ARCHIVE_DEST_2= 'SERVICE=proddg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddg' REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=proddg DB_FILE_NAME_CONVERT='/oradata/proddg','/oradata/prod' LOG_FILE_NAME_CONVERT='/oradata/proddg','/oradata/prod' STANDBY_FILE_MANAGEMENT=AUTO 备库： [oracle@db1 ~]$ cat 2222.ora prod.__db_cache_size=654311424 prod.__java_pool_size=16777216 prod.__large_pool_size=33554432 prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment prod.__pga_aggregate_target=637534208 prod.__sga_target=956301312 prod.__shared_io_pool_size=0 prod.__shared_pool_size=234881024 prod.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/proddg/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/oradata/proddg/control01.ctl','/oradata/proddg/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='prod' *.db_recovery_file_dest='/proddg_flash_recovery' *.db_recovery_file_dest_size=10737418240 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)' *.log_archive_dest_1='location=/proddg_archivelog' *.log_archive_format='%t_%s_%r.arc' *.memory_target=1580204032 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=proddg LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,proddg)' LOG_ARCHIVE_DEST_1= 'LOCATION=/proddg_archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=proddg' LOG_ARCHIVE_DEST_2= 'SERVICE=prod ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod' REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=prod DB_FILE_NAME_CONVERT='/oradata/prod','/oradata/proddg' LOG_FILE_NAME_CONVERT='/oradata/prod','/oradata/proddg' STANDBY_FILE_MANAGEMENT=AUTO 3、启动主备监听，启动主库，创建备库控制文件，连同主库数据文件，密码文件一同拷贝到备库响应目录。 在主库上创建备库控制文件 alter database create standby controlfile as '/home/oracle/control01.ctl'; scp /home/oracle/control01.ctl oracle@db1:/home/oracle [oracle@db1 dbs]$ ll orapw*-rw-r----- 1 oracle oinstall 1536 Jan 7 22:27 orapworcl-rwxrwxr-x 1 oracle oinstall 1536 Dec 29 11:56 orapworcl1bak-rwxrwxr-x 1 oracle oinstall 1536 Jan 5 15:59 orapworcl2bak-rwxrwxr-x 1 oracle oinstall 1536 Jan 5 19:43 orapworclbak1-rw-r----- 1 oracle oinstall 1536 Jan 5 22:04 orapwprod-rw-r----- 1 oracle oinstall 1536 Jan 7 13:23 orapwproddg [oracle@db1 oradata]$ lltotal 4drwxrwxr-x 3 oracle oinstall 4096 Jan 10 22:39 orcldgdrwxr-x--- 4 oracle oinstall 39 Jan 7 21:28 ORCLDGdrwxr-x--- 2 oracle oinstall 321 Jan 7 16:57 proddrwxrwxr-x 2 oracle oinstall 321 Jan 7 16:57 proddg(复制prod数据目录为proddg) 将新建的控制文件control01.ctl 放置到proddg中，control01.ctl control02.ctl 4、备库启动到mount状态，同步主库日志 alter database mount; alter database recover managed standby database using current logfile disconnect from session; (同步主库日志) alter database recover managed standby database cancel；（取消应用日志） alter database open;(启动备库) alter database recover managed standby database using current logfile disconnect from session; (应用日志) 查看主库日志状态： SYS@prod>select error,status from v$archive_dest where dest_id=2; ERROR STATUS ----------------------------------------------------------------- --------- VALID SYS@prod>archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /archivelogOldest online log sequence 44Next log sequence to archive 46Current log sequence 46 备库同步状态： SYS@proddg>select process,sequence#,status from v$managed_standby; PROCESS SEQUENCE# STATUS --------- ---------- ------------ ARCH 44 CLOSING ARCH 0 CONNECTED ARCH 0 CONNECTED ARCH 45 CLOSING RFS 0 IDLE RFS 0 IDLE RFS 0 IDLE RFS 46 IDLE MRP0 46 APPLYING_LOG 9 rows selected. SYS@proddg>archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /proddg_archivelogOldest online log sequence 44Next log sequence to archive 0Current log sequence 46 二、第二种方式，首先rman备份主库数据拷贝到备库，其次主库创建备库控制文件，密码文件拷贝到备库，最后，备库启动到mount状态，与主库同步差异日志，启动备库，应用日志完成搭建；1、监听文件、tnsnames文件配置同上2、参数文件配置同上3、主库上创建备库控制文件，创建备份密码文件，rman全库备份alter database create standby controlfile as '/home/oracle/control01.ctl';cp orapwprod orapwproddg rman备份主库：cat backup.sh#/bin/bashexport ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1ORAOWNER=oracle #date=`date +%m%d` date=`date +"%Y_%m_%d_%H_%M"`rman target / >> /home/oracle/full_backup_$date.log << EOFrun{allocate channel c1 type disk;allocate channel c2 type disk; allocate channel c3 type disk;allocate channel c4 type disk;backup as compressed backupset tag='full_backup' format '/home/oracle/backup/full_db_%U_%T' database;sql 'alter system archive log current'; backup archivelog all format '/home/oracle/backup/archivelog_%U_%T' delete all input;backup current controlfile tag='ctl_file' format='/home/oracle/backup/ctl_file_%U_%T';backup spfile tag='spfile' format='/home/oracle/backup/spfile_%U_%T';report obsolete; delete noprompt obsolete; crosscheck backup; delete noprompt expired backup;release channel c1;release channel c2;release channel c3;release channel c4;}EOF 4、启动备库到mount状态（参数文件，备库控制文件）rman注册备份文件： RMAN> catalog start with '/home/oracle/backup'; using target database control file instead of recovery catalog searching for all files that match the pattern /home/oracle/backup List of Files Unknown to the Database ===================================== File Name: /home/oracle/backup/full_db_0h0huvgn_1_1 File Name: /home/oracle/backup/full_db_0i0huvgn_1_1 File Name: /home/oracle/backup/full_db_0j0huvgn_1_1 File Name: /home/oracle/backup/full_db_0k0huvgn_1_1 File Name: /home/oracle/backup/full_db_0l0huvgn_1_1 File Name: /home/oracle/backup/full_db_0m0huvgn_1_1 File Name: /home/oracle/backup/full_db_0n0huvhh_1_1 File Name: /home/oracle/backup/ctl_file_bi0iff4f_1_1_20220104 File Name: /home/oracle/backup/full_db_b90iff39_1_1_20220104 File Name: /home/oracle/backup/archivelog_bg0iff4e_1_1_20220104 File Name: /home/oracle/backup/archivelog_bf0iff4e_1_1_20220104 File Name: /home/oracle/backup/archivelog_be0iff4e_1_1_20220104 File Name: /home/oracle/backup/full_db_ba0iff39_1_1_20220104 File Name: /home/oracle/backup/spfile_bj0iff4h_1_1_20220104 File Name: /home/oracle/backup/full_db_bc0iff39_1_1_20220104 File Name: /home/oracle/backup/full_db_bb0iff39_1_1_20220104 File Name: /home/oracle/backup/archivelog_bh0iff4e_1_1_20220104 File Name: /home/oracle/backup.gz File Name: /home/oracle/backup1/archivelog_ch0infgh_1_1_20220107 File Name: /home/oracle/backup1/archivelog_ci0infgh_1_1_20220107 File Name: /home/oracle/backup1/archivelog_cj0infgh_1_1_20220107 File Name: /home/oracle/backup1/archivelog_ck0infgh_1_1_20220107 File Name: /home/oracle/backup1/archivelog_cl0infgj_1_1_20220107 File Name: /home/oracle/backup1/archivelog_d80iso8p_1_1_20220109 File Name: /home/oracle/backup1/archivelog_d90iso8p_1_1_20220109 File Name: /home/oracle/backup1/archivelog_da0iso8p_1_1_20220109 File Name: /home/oracle/backup1/archivelog_db0iso8p_1_1_20220109 File Name: /home/oracle/backup1/archivelog_dc0iso8r_1_1_20220109 File Name: /home/oracle/backup1/archivelog_dk0isp8h_1_1_20220109 File Name: /home/oracle/backup1/archivelog_dl0isp8h_1_1_20220109 File Name: /home/oracle/backup1/archivelog_dm0isp8h_1_1_20220109 File Name: /home/oracle/backup1/archivelog_dn0isp8h_1_1_20220109 File Name: /home/oracle/backup1/archivelog_do0isp8h_1_1_20220109 File Name: /home/oracle/backup1/ctl_file_cm0infgl_1_1_20220107 File Name: /home/oracle/backup1/ctl_file_dd0iso8t_1_1_20220109 File Name: /home/oracle/backup1/ctl_file_dp0isp8i_1_1_20220109 File Name: /home/oracle/backup1/full_db_cc0inff8_1_1_20220107 File Name: /home/oracle/backup1/full_db_cd0inff8_1_1_20220107 File Name: /home/oracle/backup1/full_db_ce0inff9_1_1_20220107 File Name: /home/oracle/backup1/full_db_cf0inff9_1_1_20220107 File Name: /home/oracle/backup1/full_db_d40iso79_1_1_20220109 File Name: /home/oracle/backup1/full_db_d50iso79_1_1_20220109 File Name: /home/oracle/backup1/full_db_d60iso79_1_1_20220109 File Name: /home/oracle/backup1/full_db_dg0isp71_1_1_20220109 File Name: /home/oracle/backup1/full_db_dh0isp71_1_1_20220109 File Name: /home/oracle/backup1/full_db_di0isp71_1_1_20220109 File Name: /home/oracle/backup1/spfile_cn0infgn_1_1_20220107 File Name: /home/oracle/backup1/spfile_de0iso90_1_1_20220109 File Name: /home/oracle/backup1/spfile_dq0isp8l_1_1_20220109 Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... no files cataloged 5、备库同步主库日志,并启动库 alter database recover managed standby database using current logfile disconnect;（同步应用日志） alter database recover managed standby database cancel； alter database open; alter database recover managed standby database using current logfile disconnect;(应用日志) 主备同步 SYS@proddg>select process,sequence#,status from v$managed_standby; PROCESS SEQUENCE# STATUS --------- ---------- ------------ ARCH 0 CONNECTED ARCH 0 CONNECTED ARCH 0 CONNECTED ARCH 46 CLOSING RFS 0 IDLE RFS 0 IDLE RFS 47 IDLE MRP0 47 APPLYING_LOG 8 rows selected. 三、第三种方式，首先配置主库，备库监听，设置好密码文件，在线使用duplicate方式搭建备库，备库应用日志，完成备库搭建。1、配置监听，tnsnames文件同上2、配置参数文件，密码文件同上3、备库启动到nomount状态，使用duplicate 命令在线复制主库搭建备库rman target sys/oracle@prod auxiliary sys/oracle@proddgduplicate target database for standby from active database nofilenamecheck;复制完成，备库启动到mount状态；4、应用同步日志，完成后，启动备库到open状态，应用备库日志； SYS@proddg>select process,sequence#,status from v$managed_standby; PROCESS SEQUENCE# STATUS --------- ---------- ------------ ARCH 0 CONNECTED ARCH 0 CONNECTED ARCH 0 CONNECTED ARCH 46 CLOSING RFS 0 IDLE RFS 0 IDLE RFS 47 IDLE MRP0 47 APPLYING_LOG 8 rows selected. 小结：从搭建的过程我们可以看到搭建过程最终要的是1、配置监听 2、编辑pfile，复制密码文件 3、分清实例、服务（dg中数据库db_name不变,其他可变） 个人认为区别和特点： 复制文件的方式建立备库，生产环境不太现实，停机时间长； 备份方式比较适合生产环境备份主库 恢复备库的方式，业务量小的时候拷贝数据； duplicate方式比较试用于库比较小的情况，库太大影响网络性能或机器性能，方便快捷。备注：建立对应的目录，方便后期维护！ yicheng16 22.01.11 -- The End -- 遗留问题：1、密码文件命名2、实例、服务、数据库3、数据备份
