Data Guard Physical Standby Setup Using the Data Guard Broke
Assumptions
Primary Server SetupLoggingCheck that the primary database is in archivelog mode. SELECT log_mode FROM v$database; LOG_MODE ------------ NOARCHIVELOG SQL> If it is noarchivelog mode,switch is to archivelog mode. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; Enabled forced logging by issuing the following command. ALTER DATABASE FORCE LOGGING; -- Make sure at leat one logfile is present. ALTER SYSTEM SWITCH LOGFILE; Create standby redo logs on the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case,the following standby redo logs must be created on both servers. ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/cdb1/standby_redo01.log‘) SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/cdb1/standby_redo02.log‘) SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/cdb1/standby_redo03.log‘) SIZE 50M; ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/cdb1/standby_redo04.log‘) SIZE 50M; If you want to user flashback database,enable it on the primary now,so it will be enabled on the standby also. It‘s very useful as you will see below. ALTER DATABASE FLASHBACK ON; Initialization ParametersCheck the setting for the SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string cdb1 SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string cdb1 SQL> The Set suitable remote archive log destinations. In this case I‘m using the fast recovery area for the local location,but you could specify an location explicitly if you prefer. Notice the Make sure the ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; Service SetupEntries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers. You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup. Notice the use of the cdb1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg1)(PORT = 1521)) ) (CONNECT_DATA = (SID = cdb1) ) ) cdb1_stby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2)(PORT = 1521)) ) (CONNECT_DATA = (SID = cdb1) ) ) The "$ORACLE_HOME/network/admin/listener.ora" file on the primary server contains the following configuration. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg1)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = cdb1_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1) (SID_NAME = cdb1) ) ) ADR_BASE_LISTENER = /u01/app/oracle The "$ORACLE_HOME/network/admin/listener.ora" file on the standby server contains the following configuration. Since the broker will need to connect to the database when it‘s down,we can‘t rely on auto-registration with the listener,hence the explicit entry for the database. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = cdb1_stby_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1) (SID_NAME = cdb1) ) ) ADR_BASE_LISTENER = /u01/app/oracle Once the listener.ora changes are in place,restart the listener on both servers. lsnrctl stop lsnrctl start Standby Server SetupPrepare for DuplicateCreate a parameter file for the standby database called "/tmp/initcdb1_stby.ora" with the following contents. *.db_name=‘cdb1‘ Create the necessary directories on the standby server. mkdir -p /u01/app/oracle/oradata/cdb1/pdbseed mkdir -p /u01/app/oracle/oradata/cdb1/pdb1 mkdir -p /u01/app/oracle/fast_recovery_area/cdb1 mkdir -p /u01/app/oracle/admin/cdb1/adump Create a password file,with the SYS password matching that of the primary database. $ orapwd file=/u01/app/oracle/product/12.1.0.2/db_1/dbs/orapwcdb1 password=Password1 entries=10 Create Standby Using DUPLICATEStart the auxiliary instance on the standby server by starting it using the temporary "init.ora" file. $ export ORACLE_SID=cdb1 $ sqlplus / as sysdba SQL> STARTUP NOMOUNT PFILE=‘/tmp/initcdb1_stby.ora‘; Connect to RMAN,specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS authentication. $ rman TARGET sys/[email?protected] AUXILIARY sys/[email?protected]_stby Now issue the following DUPLICATE command. DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name=‘CDB1_STBY‘ COMMENT ‘Is standby‘ NOFILENAMECHECK; If you need to convert file locations,or alter any initialisation parameters,you can do this during the DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name=‘DB11G_STBY‘ COMMENT ‘Is standby‘ SET db_file_name_convert=‘/original/directory/path1/‘,‘/new/directory/path1/‘,‘/original/directory/path2/‘,‘/new/directory/path2/‘ SET log_file_name_convert=‘/original/directory/path1/‘,‘/new/directory/path2/‘ SET job_queue_processes=‘0‘ NOFILENAMECHECK; A brief explanation of the individual clauses is shown below.
Once the command is complete,we can start using the broker. Enable BrokerAt this point we have a primary database and a standby database,so now we need to start using the Data Guard Broker to manage them. Connect to both databases (primary and standby) and issue the following command. ALTER SYSTEM SET dg_broker_start=true; On the primary server,issue the following command to register the primary server with the broker. $ dgmgrl sys/[email?protected] DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000,2013,Oracle. All rights reserved. Welcome to DGMGRL,type "help" for information. Connected as SYSDBA. DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS cdb1 CONNECT IDENTIFIER IS cdb1; Configuration "my_dg_config" created with primary database "cdb1" DGMGRL> Now add the standby database. DGMGRL> ADD DATABASE cdb1_stby AS CONNECT IDENTIFIER IS cdb1_stby MAINTAINED AS PHYSICAL; Database "cdb1_stby" added DGMGRL> Now we enable the new configuration. DGMGRL> ENABLE CONFIGURATION; Enabled. DGMGRL> The following commands show how to check the configuration and status of the databases from the broker. DGMGRL> SHOW CONFIGURATION; Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 26 seconds ago) DGMGRL> SHOW DATABASE cdb1; Database - cdb1 Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): cdb1 Database Status: SUCCESS DGMGRL> SHOW DATABASE cdb1_stby; Database - cdb1_stby Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Average Apply Rate: 11.00 KByte/s Real Time Query: OFF Instance(s): cdb1 Database Status: SUCCESS DGMGRL> Database SwitchoverA database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following commands. Connect to the primary database (cdb1) and switchover to the standby database (cdb1_stby). $ dgmgrl sys/[email?protected] DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000,type "help" for information. Connected as SYSDBA. DGMGRL> SWITCHOVER TO cdb1_stby; Performing switchover NOW,please wait... Operation requires a connection to instance "cdb1" on database "cdb1_stby" Connecting to instance "cdb1"... Connected as SYSDBA. New primary database "cdb1_stby" is opening... Operation requires start up of instance "cdb1" on database "cdb1" Starting instance "cdb1"... ORACLE instance started. Database mounted. Switchover succeeded,new primary is "cdb1_stby" DGMGRL> Let‘s switch back to the original primary. Connect to the new primary (cdb1_stby) and switchover to the new standby database (cdb1). $ dgmgrl sys/[email?protected]_stby DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000,type "help" for information. Connected as SYSDBA. DGMGRL> SWITCHOVER TO cdb1; Performing switchover NOW,please wait... Operation requires a connection to instance "cdb1" on database "cdb1" Connecting to instance "cdb1"... Connected as SYSDBA. New primary database "cdb1" is opening... Operation requires start up of instance "cdb1" on database "cdb1_stby" Starting instance "cdb1"... ORACLE instance started. Database mounted. Switchover succeeded,new primary is "cdb1" DGMGRL> Database FailoverIf the primary database is not available the standby database can be activated as a primary database using the following statements. Connect to the standby database (cdb1_stby) and failover. $ dgmgrl sys/[email?protected]_stby DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000,type "help" for information. Connected as SYSDBA. DGMGRL> FAILOVER TO cdb1_stby; Performing failover NOW,please wait... Failover succeeded,new primary is "cdb1_stby" DGMGRL> Since the standby database is now the primary database it should be backed up immediately. The original primary database can now be configured as a standby. If flashback database was enabled on the primary database,then this can be done relatively easily with the following command. DGMGRL> REINSTATE DATABASE cdb1; Reinstating database "cdb1",please wait... Operation requires shut down of instance "cdb1" on database "cdb1" Shutting down instance "cdb1"... ORACLE instance shut down. Operation requires start up of instance "cdb1" on database "cdb1" Starting instance "cdb1"... ORACLE instance started. Database mounted. Continuing to reinstate database "cdb1" ... Reinstatement of database "cdb1" succeeded DGMGRL> If flashback database is not enabled,you would have to manually recreate cdb1 as a standby. The basic process is the reverse of what you did previously. # 1) Cleanup the old instance. sqlplus / as sysdba <<EOF SHUTDOWN IMMEDIATE; EXIT; EOF rm -Rf /u01/app/oracle/oradata/cdb1/* rm -Rf /u01/app/oracle/fast_recovery_area/cdb1 rm -Rf /u01/app/oracle/fast_recovery_area/CDB1_STBY rm -Rf /u01/app/oracle/admin/cdb1 mkdir -p /u01/app/oracle/fast_recovery_area/cdb1 mkdir -p /u01/app/oracle/admin/cdb1/adump mkdir -p /u01/app/oracle/oradata/cdb1/pdbseed mkdir -p /u01/app/oracle/oradata/cdb1/pdb1 rm $ORACLE_HOME/dbs/spfilecdb1.ora export ORACLE_SID=cdb1 sqlplus / as sysdba <<EOF STARTUP NOMOUNT PFILE=‘/tmp/initcdb1_stby.ora‘; EXIT; EOF # 2) Connect to RMAN. $ rman TARGET sys/[email?protected]_stby AUXILIARY sys/[email?protected] # 3) Duplicate the database. DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name=‘cdb1‘ COMMENT ‘Is standby‘ NOFILENAMECHECK; # 4) Connect to DGMDRL on the current primary. $ dgmgrl sys/[email?protected]_stby # 5) Enable the new standby. DGMGRL> ENABLE DATABASE cdb1; Flashback DatabaseIt was already mentioned in the previous section,but it is worth drawing your attention to Flashback Database once more. Although a switchover/switchback is safe for both the primary and standby database,a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled,the original primary must be scrapped and recreated as a standby database. An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover,the primary can be flashed back to the time before the failover and quickly converted to a standby database,as shown above. Read-Only Standby and Active Data GuardOnce a standby database is configured,it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server,thereby freeing up resources on the primary server. When open in read-only mode,archive log shipping continues,but managed recovery is stopped,so the standby database becomes increasingly out of date until managed recovery is resumed. To switch the standby database into read-only mode,do the following. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE OPEN READ ONLY; To resume managed recovery,do the following. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; In 11g,Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode,but still apply redo information. This means a standby can be available for querying,yet still be up to date. There are licensing implications for this feature,but the following commands show how active data guard can be enabled. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE OPEN READ ONLY; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Since managed recovery continues with active data guard,there is no need to switch back to managed recovery from read-only mode in this case. Snapshot StandbyIntroduced in 11g,snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode,all changes made whilst in read-write mode are lost. This is achieved using flashback database,but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature,thought it works just the same if it is. Connect to the primary (cdb1) database and convert the standby database (cdb1_stby) to a snapshot standby. $ dgmgrl sys/[email?protected] DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000,type "help" for information. Connected as SYSDBA. DGMGRL> CONVERT DATABASE cdb1_stby TO SNAPSHOT STANDBY; Converting database "cdb1_stby" to a Snapshot Standby database,please wait... Database "cdb1_stby" converted successfully DGMGRL> When you are finished with the snapshot standby,convert it back to a standby database. $ dgmgrl sys/[email?protected] DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000,type "help" for information. Connected as SYSDBA. DGMGRL> CONVERT DATABASE cdb1_stby TO PHYSICAL STANDBY; Converting database "cdb1_stby" to a Physical Standby database,please wait... Operation requires shut down of instance "cdb1" on database "cdb1_stby" Shutting down instance "cdb1"... Database closed. Database dismounted. ORACLE instance shut down. Operation requires start up of instance "cdb1" on database "cdb1_stby" Starting instance "cdb1"... ORACLE instance started. Database mounted. Continuing to convert database "cdb1_stby" ... Database "cdb1_stby" converted successfully DGMGRL> The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled. DGMGRL> SHOW CONFIGURATION; Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 38 seconds ago) DGMGRL> (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
- swift – 元素是可选的生成器如何知道它们何时到达终点?
- c – QML ComboBox项目DropDownMenu样式
- 如何解决ajax跨域问题(转) --使用两种 jsony方法
- ORACLE中的db_name,service_name,instance_name,oracle_sid
- React Native:错误:资源android:style / TextAppearance
- ruby-on-rails – 如何继续运行Capistrano 2
- Swift之旅:2.完成计算器,初探MVC设计模式
- oracle数据库的一个表中,怎么设置字段的默认值
- c# – 如何引发MouseClick事件?
- c# – 使用DataTable在DataGrid中使用WPF单个单元格颜色