11g dg 备库搭建多种方式   直接拷贝文件方式    备份恢复   duplicate

（备注：密码文件非常重要！！！ orapwORACLE_SID格式，和ORACLE_SID有关切记）

dg备库搭建方式有多种，一般采取3种方式：（本次使用1台虚机，运行2个实例，搭建dg环境）

一、第一种单库的情况下，在配置完主备参数文件后，启动主备监听，将主库数据文件拷贝拷贝到备库，将主库上创建备库控制文件并放到相应目录文件，最后启动备库到mount状态，与主库同步差异日志，启动备库， 应用日志完成搭建；

1、配置监听、tnsnames文件：

主备库：

[oracle@db1 admin]$ cat listener.ora

LISTENER =

  (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]$ ll

total 4

drwxrwxr-x 3 oracle oinstall 4096 Jan 10 22:39 orcldg

drwxr-x--- 4 oracle oinstall   39 Jan  7 21:28 ORCLDG

drwxr-x--- 2 oracle oinstall  321 Jan  7 16:57 prod

drwxrwxr-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 Mode

Automatic archival             Enabled

Archive destination            /archivelog

Oldest online log sequence     44

Next log sequence to archive   46

Current 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 Mode

Automatic archival             Enabled

Archive destination            /proddg_archivelog

Oldest online log sequence     44

Next log sequence to archive   0

Current 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/bash

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

ORAOWNER=oracle 

#date=`date +%m%d` 

date=`date +"%Y_%m_%d_%H_%M"`

rman target / >> /home/oracle/full_backup_$date.log  << EOF

run{

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@proddg

duplicate 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、数据备份