Linux下的Oracle9i data guard配置過程

Linux下的Oracle9i data guard配置過程

[@more@]

# CONFIGURE STANDBY DATABASE
sqlplus /nolog
set lines 160
col PARAMETER for a50
col VALUE for a80
col name for a40
connect / as sysdba;
# 啟動主庫導歸檔模式
startup mount;
archive log list;
archive log start;
alter database Archivelog;
alter database force logging;
alter database open;
# Enable Archiving and Define a Local Archiving Destination

! mkdir -p /export/home/oracle/admin/ECOM/adump/ecom

# Create a Control File for the Standby Database
alter database create standby controlfile as '/export/home/oracle/9i/dbs/control01.ctl';

# Create a traditional text initialization parameter file from the server parameter file
create pfile='/export/home/oracle/9i/dbs/standby.ora' from spfile;

# select parameter
select name,TYPE,VALUE from v$parameter where name like '%archive%';
select name,TYPE,VALUE from v$parameter where name like '%fal%';

vi /export/home/oracle/9i/dbs/standby.ora

*.log_archive_start=TRUE
*.standby_archive_dest='/export/home/oracle/admin/ECOM/adump/ecom21'
*.db_file_name_convert=('/export/home/oracle/oradata/ecom21/','/export/home/oracle/oradata/ecom/')
*.LOG_FILE_NAME_CONVERT=('/export/home/oracle/oradata/ecom21/','/export/home/oracle/oradata/ecom/')
*.LOG_ARCHIVE_FORMAT=log%d_%t_%s.arc
*.log_archive_dest_1=('LOCATION=/export/home/oracle/admin/ECOM/adump/ecom21/ MANDATORY REOPEN=60')
*.log_archive_dest_state_1='ENABLE'
#*.log_archive_dest_2='SERVICE=ECOM20 LGWR ASYNC'
#*.log_archive_dest_2='SERVICE=ECOM20 LGWR SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register'
*.log_archive_dest_2='SERVICE=ECOM20'
*.log_archive_dest_state_2='ENABLE'
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.instance_name=ecom21
*.fal_server='ECOM20'
*.fal_client='ECOM21'
*.DG_BROKER_START=TRUE
*.remote_login_passwordfile='EXCLUSIVE'

# copy standby.ora and control01.ctl to standby
rsync -azv /export/home/oracle/9i/dbs/standby.ora 10.46.64.21::oracle/9i/dbs/
rsync -azv /export/home/oracle/9i/dbs/control01.ctl 10.46.64.21::oracle/9i/dbs/

mv /export/home/oracle/oradata/ECOM/control01.ctl /export/home/oracle/oradata/ECOM/control01.ctl.bak
mv /export/home/oracle/oradata/ECOM/control02.ctl /export/home/oracle/oradata/ECOM/control01.ct2.bak
mv /export/home/oracle/oradata/ECOM/control03.ctl /export/home/oracle/oradata/ECOM/control01.ct3.bak
mv /export/home/oracle/9i/dbs/control01.ctl /export/home/oracle/oradata/ECOM/control01.ctl
cp /export/home/oracle/oradata/ECOM/control01.ctl /export/home/oracle/oradata/ECOM/control02.ctl
cp /export/home/oracle/oradata/ECOM/control01.ctl /export/home/oracle/oradata/ECOM/control03.ctl
mkdir -p /export/home/oracle/admin/ECOM/adump/ecom21

# Initiate Log Apply Services on standby
sqlplus /nolog
connect /as sysdba;
create spfile from pfile='/export/home/oracle/9i/dbs/standby.ora' ;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

# 為了配置data guard broker
select protection_mode,protection_level from v$database;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

# Initiate Log Apply Services on standby
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; //主資料庫啟動之後才能正常執行

# CONFIGURE PRIMARY DATABASE
# Create a traditional text initialization parameter file from the server parameter file
sqlplus /nolog
connect /as sysdba;
create pfile='/export/home/oracle/9i/dbs/primary.ora' from spfile;
# add follow line to primary.ora
*.log_archive_start=TRUE
*.standby_archive_dest='/export/home/oracle/admin/ECOM/adump/ecom'
*.db_file_name_convert=('/export/home/oracle/oradata/ecom/','/export/home/oracle/oradata/ecom21/')
*.LOG_FILE_NAME_CONVERT=('/export/home/oracle/oradata/ecom/','/export/home/oracle/oradata/ecom21/')
*.LOG_ARCHIVE_FORMAT=log%d_%t_%s.arc
*.log_archive_dest_1=('LOCATION=/export/home/oracle/admin/ECOM/adump/ecom/ MANDATORY REOPEN=60')
*.log_archive_dest_state_1='ENABLE'
#*.log_archive_dest_2='SERVICE=ECOM21 LGWR ASYNC'
#*.log_archive_dest_2='SERVICE=ECOM21 LGWR SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register'
*.log_archive_dest_2='SERVICE=ECOM21'
*.log_archive_dest_state_2='ENABLE'
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.instance_name=ecom
*.fal_server='ECOM21'
*.fal_client='ECOM20'
*.DG_BROKER_START=TRUE
*.remote_login_passwordfile='EXCLUSIVE'

# create spfile on primary dateabase
sqlplus /nolog
connect /as sysdba;
shutdown immediate;
create spfile from pfile='/export/home/oracle/9i/dbs/primary.ora';
! mkdir -p /export/home/oracle/admin/ECOM/adump/ecom
startup
set lines 160
col PARAMETER for a50
col VALUE for a70
col name for a50
select name,TYPE,VALUE from v$parameter where name like '%archive%';
select name,TYPE,VALUE from v$parameter where name like '%fal%';

# Enable Dead Connection Detection on the Standby System on primary and standby
vi /export/home/oracle/9i/network/admin/sqlnet.ora
SQLNET.EXPIRE_TIME=2
Sqlnet.authentication_services=(NONE)

# vi /export/home/oracle/9i/network/admin/tnsnames.ora
# add #################
ECOM20 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.46.64.20)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ECOM)
)
)

ECOM21 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.46.64.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ECOM)
)
)
######################
# primary database
vi /export/home/oracle/9i/network/admin/listener.ora
######################
sqlplus /nolog
connect /as sysdba;
alter user sys identified by systemadmin;
alter user system identified by systemadmin;

# 為了配置data guard broker
select protection_mode,protection_level from v$database;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

# Start remote archiving. ON PRIMARY
col name for a70
SELECT SEQUENCE#,name, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT SEQUENCE#,name, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

# Verify that the new archived redo log was applied. On the standby database
SELECT SEQUENCE#,name,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

======switchover standby到primary
很多網站上的資料都說要在primary和standby資料庫中執行一下查詢(包括Oracle的官方網站上)
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
以後才能才能切換,但是我不知道在哪兒能設定成著個結果,我看到的結果是
SWITCHOVER_STATUS
-----------------
SESSIONS ACTIVE
之後強行切換也沒有出現錯誤不知道會不會影響資料.觀察中...

# on primary
# 執行切換前執行一下日誌切換,不然在切換的過程中我的資料庫會有報錯
SELECT SEQUENCE#,name, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT SEQUENCE#,name, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
#
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown;
SHUTDOWN IMMEDIATE
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

#ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
#*
#ERROR at line 1:
#ORA-01153: an incompatible media recovery is active
如果出現以上提示,執行
alter database recover managed standby database cancel;
後在執行
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

# on standby
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY ;
?????ALTER DATABASE OPEN; //跟網路上說的不同有可能是版本不同造成的命令不同
SHUTDOWN IMMEDIATE;
startup;

Verifying the Physical Standby Database
Step 1 Identify the existing archived redo logs.
# On the standby database
SELECT SEQUENCE#,name, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step 2 Archiving the current log.
# On the primary database
ALTER SYSTEM ARCHIVE LOG CURRENT;

Step 3 Verify that the new archived redo log was received.
# On the standby database,
SELECT SEQUENCE#,name, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step 4 Verify that the new archived redo log was applied.
# On the standby database,
SELECT SEQUENCE#,name,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

# 檢視資料庫保護模式
select protection_mode,protection_level from v$database;
# 修改資料的保護模式
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
==============================
停止 standby
SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SHUTDOWN IMMEDIATE;

==========================
# monitor on standby database
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#,name, STATUS FROM V$MANAGED_STANDBY;

# Determining the Progress of Managed Recovery Operations
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

============Oracle data guard broker
生成口令檔案
orapwd file=/export/home/oracle/9i/dbs/orapw password=systemadmin entries=10
注意:作為主庫分別配置並從起,以達到sqlplus as sysdba' 能互相登陸為止

select name,TYPE,VALUE from v$parameter where name like '%broker%';

# Stop the Data Guard broker DMON process using the following SQL statement:
ALTER SYSTEM SET DG_BROKER_START=FALSE;

# Change the configuration file names on the site:
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1=filespec1
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2=filespec2

# Restart the Data Guard broker DMON process on the site, as follows:
ALTER SYSTEM SET DG_BROKER_START=TRUE;

DGMGRL
connect sys/systemadmin //本地登陸
connect ; //透過網路登入本地
connect ; //透過網路登入遠端

CREATE CONFIGURATION FJYD AS
PRIMARY SITE IS ECOM20
RESOURCE IS ECOM20
HOSTNAME IS fmcc-www6
INSTANCE NAME IS 'ECOM'
SERVICE NAME IS 'ECOM20'
SITE IS MAINTAINED AS PHYSICAL;

CREATE SITE ECOM21
RESOURCE IS ECOM21
HOSTNAME IS fmcc-www7
INSTANCE NAME IS 'ECOM'
SERVICE NAME IS 'ECOM21'
SITE IS MAINTAINED AS PHYSICAL;

ALTER RESOURCE ECOM20 ON SITE ECOM20 SET PROPERTY LogArchiveFormat='log%d_%t_%s.arc';
ALTER RESOURCE ECOM20 ON SITE ECOM20 SET PROPERTY StandbyArchiveDest = '/export/home/oracle/admin/ECOM/adump/ecom/';
ALTER RESOURCE ECOM20 ON SITE ECOM20 SET PROPERTY LogXptMode='ARCH';

ALTER RESOURCE ECOM21 ON SITE ECOM21 SET PROPERTY LogArchiveFormat='log%d_%t_%s.arc';
ALTER RESOURCE ECOM21 ON SITE ECOM21 SET PROPERTY StandbyArchiveDest = '/export/home/oracle/admin/ECOM/adump/ecom21/';
ALTER RESOURCE ECOM21 ON SITE ECOM21 SET PROPERTY LogXptMode='ARCH';

ALTER SYSTEM set log_archive_dest_2='SERVICE=ECOM21 ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register' scope=both;
ALTER SYSTEM set log_archive_dest_state_2='ENABLE' scope=both;
ALTER SYSTEM set log_archive_dest_2='SERVICE=ECOM20 ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register' scope=both;
ALTER SYSTEM set log_archive_dest_state_2='ENABLE' scope=both;
# ALTER CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

# 啟用配置方案
ENABLE CONFIGURATION;
SHOW CONFIGURATION VERBOSE;
disable configuration;
remove configuration;

show log latest on site ECOM20;
show log latest on site ECOM21;

SHOW RESOURCE verbose ECOM20 SendQEntries;
SHOW RESOURCE verbose ECOM21 SbyLogQueue;
SHOW RESOURCE verbose ECOM21 ApplyParallel;
show RESOURCE verbose ECOM20 InconsistentLogXptProps ;

show site verbose ECOM20;
show site verbose ECOM21;

# swithcover
switchover to ecom20;
# failover
failover to ecom21 GRACEFUL;

==============================

-----------------------oracle 9i data guard test command
set lines 160
col PARAMETER for a50
col VALUE for a80
col name for a40
col tablespace_name for a15
col file_name for a60
select name,TYPE,VALUE from v$parameter where name like '%archive%';
select name,TYPE,VALUE from v$parameter where name like '%password%' ;
select name,TYPE,VALUE from v$parameter where name like '%broker%';
select protection_mode,protection_level from v$database;

ALTER SYSTEM set DG_BROKER_START=false scope=both;
ALTER SYSTEM set DG_BROKER_START=true scope=both;
ALTER SYSTEM set remote_login_passwordfile='SHARED' scope=both;
ALTER SYSTEM set remote_login_passwordfile='EXCLUSIVE' scope=both;

# primary
ALTER SYSTEM SET fal_server=ECOM21 scope=both;
ALTER SYSTEM set fal_client=ECOM20 scope=both;
ALTER SYSTEM set log_archive_dest_2='SERVICE=ECOM21 LGWR ASYNC' scope=both;

create pfile='/export/home/oracle/9i/dbs/primary.ora' from spfile;
create spfile='/export/home/oracle/9i/dbs/spfile_primary.ora' from pfile='/export/home/oracle/9i/dbs/primary.ora';
shutdown immediate;
! rm -rf /export/home/oracle/9i/dbs/spfile_bak.ora
! mv /export/home/oracle/9i/dbs/spfileECOM.ora /export/home/oracle/9i/dbs/spfile_bak.ora
! mv /export/home/oracle/9i/dbs/spfile_primary.ora /export/home/oracle/9i/dbs/spfileECOM.ora
startup;

# standby
ALTER SYSTEM SET fal_server=ecom20 scope=both;
ALTER SYSTEM set fal_client=ecom21 scope=both;
ALTER SYSTEM set log_archive_dest_2='SERVICE=ECOM20 LGWR ASYNC' scope=both;

create pfile='/export/home/oracle/9i/dbs/standby.ora' from spfile;
create spfile='/export/home/oracle/9i/dbs/spfile_standby.ora' from pfile='/export/home/oracle/9i/dbs/standby.ora';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate;
! rm -rf /export/home/oracle/9i/dbs/spfile_bak.ora
! mv /export/home/oracle/9i/dbs/spfileECOM.ora /export/home/oracle/9i/dbs/spfile_bak.ora
! mv /export/home/oracle/9i/dbs/spfile_standby.ora /export/home/oracle/9i/dbs/spfileECOM.ora
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

select protection_mode,protection_level from v$database;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

! tail -f /export/home/oracle/admin/ECOM/bdump/dr*