Oracle DataGuard physical Standby Construction

Oracle DataGuard physical Standby builds Arch asysncOracle Dataguardprimaryhost: primaryIP: 192.168.198.136Oracle _ sid: Role: dg_pd standbyhost: standyIP: 192.168.198.128Oracle _ sid: Role: dg_st 1. configure www.2cto.com 1. set the master database to force logging mode SQL> alter database force logging; 2. modify the archive log storage path: mkidr-p/u01/archive /Chown-R oracle: oinstall/u01/archive/chmod 775/u01/archive/SQL> alter system set log_archive_dest_1 = 'location =/u01/archive/'scope = both; 3. set the master database to archive mode SQL> archive log list; SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> archive log list; 4. SQL> alter database create standby controlfile as '$ ORACLE_BASE/oradata/dgtest/standby01.ctl'; -- Description: determines whether a database is Pri. Mary or Standby is determined by the control file. [Oracle @ localhostdbs] $ orapwd file = $ ORACLE_HOME/dbs/orapwdgtest password = If oracle already exists, no creation is required. By default, the password file format in win is pwdsid. the format of ora in unix is orapwSID (case sensitive) 5. modify the initialization parameter file *. DB_UNIQUE_NAME = 'dg _ pd '*. LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (dg_pd, dg_st )'*. log_archive_dest_1 = 'location =/u01/archive VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = dg_pd '*. LOG_ARCHIVE_DEST_2 = 'service = dg_st ASYNC VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = dg_st '*. LOG_ARCHIVE_DEST_STATE_1 = ENABLE *. LOG_ARCHIVE_DEST_STAT E_2 = ENABLE *. standby_file_management = 'auto '*. FAL_SERVER = 'dg _ st '*. FAL_CLIENT = 'dg _ pd 'If the data file location of the master and slave databases is different, add the following two parameters :*. log_file_name_convert = '/u02/oradata/dgtest/', '/u03/oradata/dgtest /'*. db_file_name_convert = '/u02/oradata/dgtest/', '/u03/oradata/dgtest/' -- Note: orcl_st, orcl_pd is configured in the tnsnames file with '$ ORACLE_HOME/dbs/initdgtest. ora 'this pfile starts the database and generates a spfile. SQL> shutdown immediateDatabase closed. database dismounted. ORACLE instance shut down. SQL> startup pfile = '$ ORACLE_HOME/dbs/initdgtest. ora '; ORACLE instance started. total System Global Area 167772160 bytesFixed Size 1218316 bytesVariable Size 79694068 bytesDatabase Buffers 83886080 bytesRedo Buffers 2973696 bytesDatabase mounted. database opened. SQL> create spfile from pfile = '$ ORACLE_HOME/dbs/initdgtes T. ora '; File created. 6. modify listener. ora and tnsnames. ora file Listener. ora file: $ ORACLE_HOME/network/admin/listener. ora # listener. ora Network Configuration File:/u01/app/oracle/product/10.2.0/db1/network/admin/listener. ora # listener. ora Network Configuration File:/u01/app/oracle/product/10.2.0/db1/network/admin/listener. ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID _ DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db1) (PROGRAM = extproc ))) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1) (ADDRESS = (PROTOCOL = TCP) (HOST = primary) (PORT = 1521) Note: SID_LIST_LISTENER is configured with static registration. If this parameter is not configured and the Data Guard startup sequence is incorrect, PING [ARC1] may be reported in the master database: heartbeat failed to connect to standby 'orcl _ st '. error is 12514. The archive cannot be completed due to an error. Dynamic Registration and static registration of Oracle Listener http://www.bkjia.com/database/201006/50233.htmlTnsnames.ora File # tnsnames. ora Network Configuration File:/u01/app/oracle/product/10.2.0/db1/network/admin/tnsnames. ora # Generated by Oracle configuration tools. DG_PD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.198.136) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DG_PD ))) DG_ST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.198.128) (PORT = 1521) (CONNECT_DAT A = (SERVER = DEDICATED) (SERVICE_NAME = DG_ST) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1 ))) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) www.2cto.com II. standby end configuration 1. create a backup inventory and put the data file and background tracking directory. This directory can be the same as the master database. If it is different, it needs to be converted in the initialization file of the master database. For example :*. log_file_name_convert = '/u02/oradata/dgtest/', '/u03/oradata/orcl /'*. db_file_name_convert = '/u02/oradata/dgtest /', '/u03/oradata/orcl/' -- create the mkdir-p $ ORACLE_BASE/oradata/dgte1_kdir $ ORACLE_BASE/admin/dgte1_kdir-p $ ORACLE_BASE/admin/dgtest/adump mkdir $ ORACLE_BASE/admin/dgtest/bdump mkdir $ ORACLE_BASE/admin/dgtest/cdump mkdir $ ORACLE_BASE/admin/dgtest/dpdump mkdir $ ORACLE_BASE/admin/dgtest/pfil E mkdir $ ORACLE_BASE/admin/dgtest/udump mkdir $ ORACLE_BASE/admin/dgtest/mkdir $ ORACLE_BASE/flash_recovery_area/DGTEST/onlinelog create a directory/u01/archive/Under the root account and modify this directory has the following permissions: mkidr-p/u01/archive/chown-R oracle: oinstall/u01/archive/chmod 775/u01/archive/2. copy the password files, control files, data files, parameter files, and log files of the master database to the slave database. Note that this control file is a standby Control File Created by ourselves. Copy the copied control file in three more copies. The master and slave control files are different. In addition to the direct copy file, Rman recovery can also be used. Directly copy the database and stop it if RMAN is used. -- Data file, redo file scp *. dbf 192.168.198.128:/u01/app/oracle/oradata/dgtestscp *. log 192.168.198.128:/u01/app/oracle/oradata/dgtest -- password file, parameter file scp initdgtest. ora 192.168.198.128:/u01/app/oracle/product/10.2.0/db1/dbsp_orapwdgtest 192.168.198.128:/u01/app/oracle/product/10.2.0/db1/dbs -- listener tnsnamesscp listener. ora 192.168.198.128:/u01/app/oracle/product/10.2.0/db/network/adminscp tnsnames. ora 192.1. 68.198.128:/u01/app/oracle/product/10.2.0/db/network/admin -- standby control file scp standby01.ctl 192.168.198.128: /u01/app/oracle/oradata/dgtest/-- archivelogscp/u01/archive/* 192.168.198.128: /u01/archive/log on to the backup database 192.168.198.128. This server copies the passed standby01.ctl file to the following: cp standby01.ctl control01.ctlcp standby01.ctl control02.ctl cp standby01.ctl control03.ctl3. modify the initialization parameter file *. DB_UNIQUE_NAME = 'dg _ st '*. LOG_ARCHIVE_CONFIG = 'd G_CONFIG = (dg_pd, dg_st )'*. log_archive_dest_1 = 'location =/u01/archive VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = dg_st '*. LOG_ARCHIVE_DEST_2 = 'service = dg_pd ASYNC VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = dg_pd '*. LOG_ARCHIVE_DEST_STATE_1 = ENABLE *. LOG_ARCHIVE_DEST_STATE_2 = ENABLE *. standby_file_management = 'auto '*. FAL_SERVER = 'dg _ pd 'st *. FAL_CLIENT = 'dg _ sd' -- save as std. ora4. modify listener. ora And tnsnames. ora files. If they do not exist, copy them from the master database. So far, the Data Guard operation has been completed, and the verification will begin. Supplement: Start the slave Database SQL> startup nomount pfile = '/u01/app/oracle/product/10.2.0/db1/dbs/initdgtest. ora 'SQL> create spfile from pfile ='/u01/app/oracle/product/10.2.0/db1/dbs/initdgtest. ora '------------------------------------ environment End ready start -------------- note the Data Guard startup sequence: start sequence: First standby, then primary; close sequence: first primary, then standby; start the instance in the standby database to the mount status: SQL> startup nomount; SQL> alter databa Se mount standby database; Start redo apply: SQL> alter database recover managed standby database disconnect from session; (stop redo apply: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL ;) start listening in the slave Database: $ lsnrctl start instance in the master database: SQL> startup; start listening in the master database: $ lsnrctl start -------------------- restart uard Acitve OK ------------ NowPrimary: openStandby: mount -- Standby open in read-only mode -- stop redo application SQL> alter database recover managed Standby database cancelSQL> alter database open; SQL> select open_mode from v $ database; OPEN_MODE----------READ ONLY -- Standby returns to redo apply status SQL> shutdown immediateSQL> startup nomount SQL> alter database mount standby database; SQL> alter database recover managed standby database disconnect from session; -- verify whether the data is transmitted to Standby database -- Primary databaseSQL> select sequence #, FIRST_TIME, NEXT_TIME from v $ ARCHIVE D_LOG order by sequence #; SQL> SELECT SEQUENCE #, FIRST_TIME, NEXT_TIME FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #; SEQUENCE # FIRST_TIM NEXT_TIME 7 23-FEB-11 23-FEB-11 7 23-FEB-11 23-FEB-11 8 23-FEB-11 23-FEB-11 8 23-FEB-11 23-FEB-11 23-FEB-11 9 23-FEB-11 9 23-FEB-11 9 then changed system switch logfile. -- Standby databaseSQL> select sequence #, FIRST_TIME, NEXT_TIME from v $ ARCHIVED_LOG Order by sequence #; SEQUENCE # FIRST_TIM NEXT_TIME ------------- --------- 7 23-FEB-11 23-FEB-11 8 23-FEB-11 23-FEB-11 9 23-FEB-11 limit 10 23-FEB-11 -- verify whether Standby redo applies SQL> SELECT SEQUENCE #, applied from v $ ARCHIVED_LOG order by sequence #; SEQUENCE # APP ---------- --- 3 YES 4 YES 5 YES 6 YES 7 YES 8 YES 9 YES 10 YES8 rows selected. -- if an error occurs in the master database, verify that the archive directory is valid: SQL> SELECT STATUS, DESTINATIO N, error from v $ ARCHIVE_DEST; if an ERROR exists, check the cause. SQL> SELECT STATUS, ERROR FROM V $ ARCHIVE_DEST; STATUS ERROR --------- begin failed Note: If you execute alter database clear unarchived logfile or alter database open resetlogs in the master database, the restore uard must be rebuilt. Supplement: -- SQL> select database_role, db_unique_name, open_mode, protection_mode, protection_level, switchover_status from v $ database; -- set a new data protection mode and restart the database. When the protection mode changes the sequence: maximize protection ---> maximize availability ----> when maximize performance reduces the protection level of dataguard in the order above, the primary database is not required to be in the mount state, otherwise, the primary must be in the mount state. For example: SQL> alter database set standby database to maximize availability; alter database set standby database to maximize availability * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in anyinstanceSQL> shutdown immediateDatabase closed. database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. total System Global Area 167772160 bytesFixed Size 1218316 bytesVariable Size 79694068 bytesDatabase Buffers 83886080 bytesRedo Buffers 2973696 bytesDatabase mounted. SQL> alter database set standby database to maximize availability; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel ERROR, this is because the maximum availability needs to change the log transmission mode to lgwr synchronization mode first. Otherwise, the database cannot be opened. SQL> alter system set log_archive_dest_2 = 'Service = dg_st lgwr sync affir'; System altered. SQL> shutdown immediateDatabase closed. database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. total System Global Area 167772160 bytesFixed Size 1218316 bytesVariable Size 79694068 bytesDatabase Buffers 83886080 bytesRedo Buffers 2973696 bytesDatabase mounted. SQL> alter database set standby database to maximize avail Ability; Database altered. SQL> alter database open; Database altered. SQL> select protection_mode, protection_level from v $ database; PROTECTION_MODE PROTECTION_LEVEL -------------------------------------- MAXIMUM AVAILABILITY MAXIMUM AVAILABILITYalter database set standby database to maximize performance; tip: maximize and {PROTECTION | AVAILABILITY | PERFORMANCE} correspond to the maximum PROTECTION, maximum AVAILABILITY, and maximum PERFORMANCE respectively. In the maximum protection mode, directly close the slave database is not good, if you close the database on the slave database, there will be the following prompt: SQL> shutdown immediateORA-01154: database busy. open, close, mount, and dismount not allowed nowSQL> in maximum protection mode, the slave database cannot be closed. In this mode, the master database is disabled first, then the slave database can be closed smoothly. Note: After the protection mode of the master database is modified, the mode of the slave database will change, which is consistent with that of the master database. Www.2cto.com 3. view log archiving status the master database switches logs: SQL> Alter system switch logfile; select max (sequence #) from v $ archived_log; select max (sequence #) from v $ log_history; select group #, sequence #, archived, status from v $ log; select name, sequence #, applied from v $ archived_log; select sequence #, applied from v $ archived_log; if the data is not synchronized, 1> Check log and whether archive is lost. 2> you can perform the following operations on the slave database: -- stop redo applyalter database recover managed standby database cancel; -- start redo applyalter database recover managed standby database disconnect from session;