Data guard hướng dẫn 11g
Scott Jesse has worked for Oracle since 1996 and is currently Customer Support Senior Manager for the RAC Assurance Team. He has served as the Global Team Lead and as a member of the Advanced Resolution and Escalations team within Oracle Support. Scott is the coauthor of Oracle Database 10g High Availability with RAC, Flashback, and Data Guard and Oracle9i for Windows 2000 Tips & Techniques, both from Oracle Press. Show
Bill Burton joined Oracle in 1998 and is currently a member of the Development RAC Assurance Team. He co-wrote and presented the “Upgrading to 11gR2” session at Oracle OpenWorld 2010. Bryan Vongray is a member of the RAC Assurance Team within Oracle Global Customer Support. He specialized in Oracle High Availability with specific focus on Oracle Real Application Clusters (RAC), Oracle Data Guard, Oracle Streams, backup and recovery, and Oracle Grid Control. Bryan has planned and implemented Oracle HA Solution for several high profile Oracle customers. Data Guard is the name for Oracle's standby database solution, used for disaster recovery and high availability. This article contains an updated version of the 9i physical standby setup method posted here. You should probably be using the Data Guard Broker to configure and manage your standby database, as described here. Related articles. TL;DRIf you already know about Data Guard and want to quickly set up a demo environment using VirtualBox and Vagrant you can follow the instructions in my GitHub repository here. 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; Initialization ParametersCheck the setting for the ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';5 and ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';6 parameters. In this case they are both set to "DB11G" on the primary database. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL> The ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';5 of the standby database will be the same as that of the primary, but it must have a different ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';6 value. The ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';6 values of the primary and standby database should be used in the ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;0 setting of the ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;1 parameter. For this example, the standby database will have the value "DB11G_STBY". ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)'; 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 ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;2 and the ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';6 for the remote location reference the standby location. ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; The ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;4 and ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;5 parameters must be set to appropriate values and the ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;6 must be set to exclusive. ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;7 parameters to account for your filename and path differences between the servers. ALTER SYSTEM SET FAL_SERVER=DB11G_STBY; --ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE; --ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect. 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. DB11G = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DB11G.WORLD) ) ) DB11G_STBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DB11G.WORLD) ) ) Backup Primary DatabaseIf you are planning to use an active duplicate to create the standby database, then this step is unnecessary. For a backup-based duplicate, or a manual restore, take a backup of the primary database. $ rman target=/ RMAN> BACKUP DATABASE PLUS ARCHIVELOG; Create Standby Controlfile and PFILECreate a controlfile for the standby database by issuing the following command on the primary database. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;0 Create a parameter file for the standby database. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;1 Amend the PFILE making the entries relevant for the standby database. I'm making a replica of the original server, so in my case I only had to amend the following parameters. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;2 Standby Server Setup (Manual)Copy FilesCreate the necessary directories on the standby server. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;3 Copy the files from the primary to the standby server. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;4 Notice, the backups were copied across to the standby server as part of the FRA copy. If your backups are not held within the FRA, you must make sure you copy them to the standby server and make them available from the same path as used on the primary server. Start ListenerMake sure the listener is started on the standby server. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;5 Restore BackupCreate the SPFILE form the amended PFILE. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;6 Restore the backup files. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;7 Create Redo LogsCreate online redo logs for the standby. It's a good idea to match the configuration of the primary server. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;8 In addition to the online redo logs, you should create standby redo logs on both the standby and 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. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;9 Once this is complete, we can start the apply process. Standby Server Setup (DUPLICATE)Copy FilesCreate the necessary directories on the standby server. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;3 Copy the files from the primary to the standby server. ALTER DATABASE FORCE LOGGING;1 Start ListenerWhen using active duplicate, the standby server requires static listener configuration in a "listener.ora" file. In this case I used the following configuration. ALTER DATABASE FORCE LOGGING;2 Make sure the listener is started on the standby server. SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;5 Create Standby Redo Logs on Primary ServerThe ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;8 command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server. ALTER DATABASE FORCE LOGGING;4 Create Standby Using DUPLICATEStart the auxillary instance on the standby server by starting it using the temporary "init.ora" file. ALTER DATABASE FORCE LOGGING;5 Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication. ALTER DATABASE FORCE LOGGING;6 Now issue the following DUPLICATE command. ALTER DATABASE FORCE LOGGING;7A brief explanation of the individual clauses is shown below.
Once the command is complete, we can start the apply process. Start Apply ProcessStart the apply process on standby server. ALTER DATABASE FORCE LOGGING;8 If you need to cancel the apply process, issue the following command. ALTER DATABASE FORCE LOGGING;9 If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>0 Provided you have configured standby redo logs, you can start real-time apply using the following command. ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;7 Test Log TransportOn the primary server, check the latest archived redo log and force a log switch. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>1 Check the new archived redo log has arrived at the standby server and been applied. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>2 Protection ModeThere are three protection modes for the primary database:
By default, for a newly created standby database, the primary database is in maximum performance mode. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>3 The mode can be switched using the following commands. Note the alterations in the redo transport attributes. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>4 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 statements. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>5 On the original standby database issue the following commands. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>6 Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback. FailoverIf the primary database is not available the standby database can be activated as a primary database using the following statements. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>7 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 (). If not, the whole setup process must be followed, but this time using the original primary server as the standby. Flashback DatabaseIt was already mentioned in the previous section, but it is worth drawing your attention to 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. That process is . 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. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>8 To resume managed recovery, do the following. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string DB11G SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string DB11G SQL>9 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. ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';0 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. If you are using RAC, turn off all but one of the RAC instances. Make sure the instance is in MOUNT mode. ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';1 Make sure managed recovery is disabled. ALTER DATABASE FORCE LOGGING;9 Convert the standby to a snapshot standby. The following example queries the ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;8 view to show that flashback database is not enabled prior to the conversion operation. ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';3 You can now do treat the standby like any read-write database. To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands. ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';4 The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled. |