一：注意事項(xiàng)

1:

目錄不同

db_file_name_convert=('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/oradu/')

log_file_name_convert=('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/oradu/')

必須設(shè)置

2:

創(chuàng)建密碼文件，必須保持target DB和auxiliary DB的密碼一致。這里我直接對(duì)target DB密碼文件重命名使用

[root@oracledba dbs]# cp orapworcl orapworadu

3:

開(kāi)始復(fù)制,復(fù)制時(shí)需要注意是否使用nofilenamecheck參數(shù)，如果兩個(gè)實(shí)例相關(guān)數(shù)據(jù)目錄結(jié)構(gòu)完全相同，則需要指定，否則會(huì)報(bào)錯(cuò)。這里不需要使用。復(fù)制日志太多，有興趣的可以看看，或者

跳過(guò)直接看結(jié)果!

[oracle@oracledba ~]$ export ORACLE_SID=oradu ? ?--------異機(jī)操作可省略此步操作

[oracle@oracledba ~]$rman target sys/oracle@orcl auxiliary sys/oracle@oradu

duplicate target database to oradu from active database;

如果目錄相同:

duplicate target database to oradu from active database nofilenamecheck;

4:

alter database open read only;

5:ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

6:特別注意：在修改主庫(kù)參數(shù)時(shí)必須創(chuàng)建pfile

create pfile='/home/oracle/init.ora' from spfile;

二：ADG配置

1、確定數(shù)據(jù)庫(kù)是否為歸檔模式

On primarydb:

select log_mode from v$database;

archive log list;

2、開(kāi)啟強(qiáng)制日志模式

On primarydb:

select FORCE_LOGGING from v$database;

ALTER DATABASE FORCE LOGGING;

3、驗(yàn)證是否有密碼文件(HA備庫(kù)也驗(yàn)證)

On primarydb:

cd $ORACLE_HOME/dbs

ls orap*

4、添加standby redo

On primarydb:

確認(rèn)redo 大小和路徑

set linesize 200

col member for a55

select a.group#,a.thread#,b.type,a.bytes/1024/1024 mb ,b.member from v$log a ,v$logfile b where a.group#=b.group#;

select a.group#,a.thread#,b.type,a.bytes/1024/1024 mb ,b.member from v$standby_log a ,v$logfile b where a.group#=b.group#;

alter database add standby logfile thread 1 group 11 '/oradata/PROD/redo11.log' size 1000M;

alter database add standby logfile thread 1 group 12 '/oradata/PROD/redo12.log' size 1000M;

alter database add standby logfile thread 1 group 13 '/oradata/PROD/redo13.log' size 1000M;

alter database add standby logfile thread 1 group 14 '/oradata/PROD/redo14.log' size 1000M;

alter database add standby logfile thread 1 group 15 '/oradata/PROD/redo15.log' size 1000M;

alter database add standby logfile thread 2 group 16 '/oradata/PROD/redo16.log' size 1000M;

alter database add standby logfile thread 2 group 17 '/oradata/PROD/redo17.log' size 1000M;

alter database add standby logfile thread 2 group 18 '/oradata/PROD/redo18.log' size 1000M;

alter database add standby logfile thread 2 group 19 '/oradata/PROD/redo19.log' size 1000M;

alter database add standby logfile thread 2 group 20 '/oradata/PROD/redo20.log' size 1000M;

/oradata/PROD/

5、修改主庫(kù)參數(shù)

On primarydb:

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PRODDG)';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+FRADG/PROD/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD';

alter system set LOG_ARCHIVE_DEST_3='SERVICE=PRODDG LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODDG';

alter system set fal_server='PRODDG';

alter system set standby_file_management=AUTO scope=both;

下列2個(gè)參數(shù)需要下次重啟后生效

alter system set DB_FILE_NAME_CONVERT='/oradata/PROD/','+DATADG/PROD/data/' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT='/oradata/PROD/','+DATADG/PROD/data/' scope=spfile;

7、創(chuàng)建備庫(kù)靜態(tài)監(jiān)聽(tīng)文件

On Standbydb:

cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin

vi listener.ora

LISTENER =

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST= 10.18.0.210)(PORT=1521))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=PRODDG)

(SID_NAME= PRODDG)

(ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)

)

)

8、啟動(dòng)備庫(kù)監(jiān)聽(tīng)

On Standbydb:

lsnrctl start

9、修改主、備庫(kù)的TNSNAMES文件(主庫(kù)另外一節(jié)點(diǎn)也要復(fù)制)

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.18.0.83)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PROD)

)

)

PRODDG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.18.0.210)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = PRODDG)

)

)

11、tnsping測(cè)試，確保都能通

主庫(kù)：(主庫(kù)另外一節(jié)點(diǎn)也要測(cè)試)

On Primarydb:

tnsping PROD

tnsping PRODDG

備庫(kù)：

On Standbydb:

tnsping PROD

tnsping PRODDG

12、創(chuàng)建備庫(kù)密碼文件

從主庫(kù)中scp 密碼文件備庫(kù),sys密碼不能有下劃線

On Primarydb:

cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs

scp orapwPROD1 ? 10.18.0.210:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwPRODDG

13、為備庫(kù)創(chuàng)建初始化參數(shù)文件

On Primarydb:

create pfile= '/tmp/p.ora' from spfile;

scp /tmp/p.ora ?10.18.0.210:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

修改參數(shù)文件

On Standbydb:

cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

*.db_unique_name='PRODDG'

*.fal_server='PROD'

*.log_archive_dest_1='location=/arch/PROD ?VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDG'

*.log_archive_dest_3='service=PROD lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=PROD'

*.db_create_file_dest='/oradata'

*.db_file_name_convert='+DATADG/PROD/','/oradata/PROD/'

*.log_file_name_convert='+DATADG/PROD/','/oradata/PROD/'

*.control_files='/oradata/PROD/controlfile/ctrol01.ctl','/oradata/PROD/controlfile/ctrol02.ctl'

#*.cluster_database=true

#testdb2.thread=2

#testdb1.thread=1

#testdb1.instance_number=1

#testdb2.instance_number=2

#*.remote_listener='testcluster:1521'

*.undo_tablespace='UNDOTBS1'

#testdb2.undo_tablespace='UNDOTBS2'

檢查文件目錄權(quán)限

/oradata

/arch

chown -R oracle:oinstall /oradata

chown -R oracle:oinstall /arch

14、創(chuàng)建備庫(kù)相應(yīng)目錄

On Standbydb:

su - oracle

mkdir -p /u01/app/oracle/admin/PROD/adump

mkdir -p /oradata/PROD/datafile

mkdir -p /oradata/PROD/tempfile

mkdir -p /oradata/PROD/onlinelog

mkdir -p /oradata/PROD/controlfile

mkdir -p /arch/PROD

15、為備庫(kù)創(chuàng)建spfile

On Standbydb:

export ORACLE_SID=PRODDG

SQL> startup nomount pfile='/tmp/p.ora'

SQL> create spfile from pfile='/tmp/p.ora';

16、關(guān)閉重啟

shutdown abort

startup nomount

SQL> show parameter name

SQL> show parameter log

17、連接至主數(shù)據(jù)庫(kù)作為其目標(biāo)數(shù)據(jù)庫(kù)，以運(yùn)行創(chuàng)建備用 ON STANDBY

On Primarydb:

sqlplus sys/oracle@PROD as sysdba

sqlplus sys/oracle@PRODDG as sysdba

On Standbydb:

sqlplus sys@PROD as sysdba

<<<

sqlplus sys@PRODDG as sysdba

<<<

18、配置dataguard備庫(kù)

On Primarydb:

export ORACLE_SID=PROD1

sqlplus / as sysdba

show parameter name

exit

export ORACLE_SID=PROD1

rman target ?sys/oracle auxiliary sys/oracle@PRODDG nocatalog <

run {

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database;

}

exit

EOF

19、只讀模式打開(kāi)數(shù)據(jù)庫(kù)

備庫(kù)：

alter database open;

20、應(yīng)用實(shí)時(shí)日志

備庫(kù)：

alter database recover managed standby database using current logfile disconnect from session;

alter database recover managed standby database disconnect from session;

21 ,check

#on standby

select * from v$archive_gap;

select process,client_process,sequence#,status from v$managed_standby;

select database_role,protection_mode,protection_level,open_mode from v$database;

set linsize 200

col value for a20

col name for a30

col time for a30

col time_computed for a20

select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIME from V$DATAGUARD_STATS WHERE NAME LIKE '%lag';

shutdown immediate;

startup

recover managed standby database using current logfile disconnect from session;

#on primary

create table adg_tst (id int);

insert into adg_tst values(1);

commit;

alter system switch logfile;

alter system archive log current;

#on standby

select * from adg_tst;

閱讀(3095) | 評(píng)論(0) | 轉(zhuǎn)發(fā)(0) |

總結(jié)

以上是生活随笔為你收集整理的查询oracle数据库adg的模式,Oracle11g ADG配置的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。