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 45 CLOSING

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 46 CLOSING

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 46 CLOSING

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、資料備份

11g dg 備庫搭建多種方式

相關文章