【Oracle Database】Oracle DataGuard（single-single）

Oracle Data Guard确保了企业数据的高可用性、数据保护和灾难恢复。Data Guard提供了一套全面的服务，用于创建、维护、管理和监视一个或多个备用数据库，以使生产数据库能够在灾难和数据损坏中生存。Data Guard将这些备用数据库维护为生产数据库的事务一致副本。然后，如果由于计划内或计划外停机而导致生产数据库不可用，则Data Guard可以将任何备用数据库切换到生产角色，从而将与停机相关的停机时间降至最低。数据保护可以与传统的备份、恢复和群集技术一起使用，以提供高级别的数据保护和数据可用性。

[oracle@wallet01 ~]$ sqlplus / as sysdbaSQL> alter database force logging;Database altered.SQL> col force_logging for a15SQL> select force_logging from v$database;FORCE_LOGGING---------------YESSQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     35Next log sequence to archive   37Current log sequence           37SQL> alter system set db_unique_name='walletmdb' scope=spfile;SQL> alter system set log_archive_config='dg_config=(walletmdb,walletsdb)' scope=spfile;SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=walletmdb' scope=spfile;SQL> alter system set log_archive_dest_2='service=walletsdb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=walletsdb' scope=spfile;SQL> alter system set fal_server='walletsdb' scope=spfile;SQL> alter system set standby_file_management='auto' scope=spfile;SQL> alter system set log_archive_dest_state_2='defer' scope=spfile;SQL> alter system set service_names=walletdb,walletmdb scope=spfile;[oracle@wallet01 ~]$ cd $ORACLE_HOME/dbs[oracle@wallet01 dbs]$ sqlplus / as sysdbaSQL> create pfile from spfile;  File created.[oracle@wallet01 dbs]$ scp initwalletdb.ora 192.168.1.202:/u01/app/oracle/product/11.2.0/db_1/dbs[oracle@wallet01 dbs]$ scp orapwwalletdb 192.168.1.202:/u01/app/oracle/product/11.2.0/db_1/dbs[oracle@wallet01 ~]$ mkdir backup[oracle@wallet02 ~]$ mkdir backup[oracle@wallet01 ~]$ rman target /RMAN> backup device type disk format '/home/oracle/backup/%U' database plus archivelog;RMAN> backup device type disk format '/home/oracle/backup/%U' current controlfile for standby;[oracle@wallet01 ~]$ scp /home/oracle/backup/*  192.168.1.202:/home/oracle/backup[oracle@wallet01 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.orawalletmdb =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = walletdb)    )  )walletsdb =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = walletdb)    )  )[oracle@wallet02 ~]$ vi $ORACLE_HOME/network/admin/listener.oraSID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = walletdb)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)      (SID_NAME = walletdb)    )  )LISTENER =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = wallet02)(PORT = 1521))  )ADR_BASE_LISTENER = /u01/app/oracle[oracle@wallet02 ~]$ lsnrctl start[oracle@wallet02 ~]$ lsnrctl status  [oracle@wallet02 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.orawalletmdb =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = walletdb)    )  )walletsdb =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = walletdb)    )  )  [oracle@wallet01 ~]$ tnsping walletsdbUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = walletdb)))OK (40 msec)[oracle@wallet02 ~]$ tnsping walletmdbUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = walletdb)))OK (30 msec)  [oracle@wallet02 ~]$ cd $ORACLE_HOME/dbs[oracle@wallet02 dbs]$ vi initambdb.ora*.audit_file_dest='/u01/app/oracle/admin/walletdb/adump'*.audit_trail='db'*.compatible='11.2.0.4.0'*.control_files='/u01/app/oracle/oradata/ambdb/control01.ctl','/u01/app/oracle/fast_recovery_area/ambdb/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='walletdb'*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'*.db_recovery_file_dest_size=107374182400*.db_unique_name='walletsdb'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=walletdbXDB)'*.fal_server='walletmdb'*.log_archive_config='dg_config=(walletmdb,walletsdb)'*.log_archive_dest_1='location=db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=walletsdb'*.log_archive_dest_2='service=walletmdb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=walletmdb'*.memory_max_target=1073741824*.memory_target=1073741824*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.service_names='walletdb','walletsdb'*.standby_file_management='auto'*.undo_tablespace='UNDOTBS1'[oracle@wallet02 dbs]$ sqlplus / as sysdbaSQL> create spfile from pfile;File created.[oracle@wallet02 ~]$ mkdir -p /u01/app/oracle/admin/walletdb/adump[oracle@wallet02 ~]$ mkdir -p /u01/app/oracle/oradata/walletdb[oracle@wallet02 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/walletdb[oracle@wallet02 ~]$ sqlplus / as sysdbaSQL> startup nomount;ORACLE instance started.Total System Global Area 1068937216 bytesFixed Size                  2260088 bytesVariable Size             432014216 bytesDatabase Buffers          629145600 bytesRedo Buffers                5517312 bytes[oracle@wallet02 ~]$ rman target sys/oracle@walletmdb auxiliary sys/oracle@walletsdb nocatalogRecovery Manager: Release 11.2.0.4.0 - Production on Sat Mar 31 14:25:26 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: walletdb (DBID=1072562510)using target database control file instead of recovery catalogconnected to auxiliary database: walletdb (not mounted)RMAN> duplicate target database for standby dorecover nofilenamecheck;[oracle@wallet02 ~]$ sqlplus / as sysdbaSQL> alter database add standby logfile '/u01/app/oracle/oradata/walletdb/standby01.log' size 50m;SQL> alter database add standby logfile '/u01/app/oracle/oradata/walletdb/standby02.log' size 50m;SQL> alter database add standby logfile '/u01/app/oracle/oradata/walletdb/standby03.log' size 50m;SQL> alter database add standby logfile '/u01/app/oracle/oradata/walletdb/standby04.log' size 50m;SQL> alter database recover managed standby database disconnect from session using current logfile;[oracle@wallet01 ~]$ sqlplus / as sysdbaSQL> alter system set log_archive_dest_state_2=enable scope=both;SQL> alter system switch logfile;[oracle@wallet02 ~]$ sqlplus / as sysdbaSQL> alter database recover managed standby database cancel;SQL> alter database open;SQL> alter database recover managed standby database disconnect from session using current logfile;[oracle@wallet01 ~]$ sqlplus / as sysdbasql> set line 200sql> col database_mode for a30sql> col protection_mode for a30sql> col recovery_mode for a30sql> select dest_id , database_mode , recovery_mode , protection_mode from v$archive_dest_status where dest_id=2;   dest_id database_mode                  recovery_mode                  protection_mode---------- ------------------------------ ------------------------------ ------------------------------         2 open_read-only                 managed real time apply        maximum performance         sql> col dest_name for a20sql> col destination for a30sql> col error for a50 sql> select dest_id,dest_name,status, destination, error from v$archive_dest where dest_id<=2;   dest_id dest_name            status                      destination                    error---------- -------------------- --------------------------- ------------------------------ --------------------------------------------------         1 log_archive_dest_1   valid                       db_recovery_file_dest         2 log_archive_dest_2   valid                       walletsdbsql> col type for a20sql> select dest_name,destination,status,type,archived_seq#,applied_seq# from v$archive_dest_status where dest_id<=2; dest_name            destination                    status                      type                 archived_seq# applied_seq#-------------------- ------------------------------ --------------------------- -------------------- ------------- ------------log_archive_dest_1   db_recovery_file_dest          valid                       local                           50            0log_archive_dest_2   walletsdb                      valid                       physical                        50           49sql> select thread# , sequence# , status from v$log;   thread#  sequence# status---------- ---------- ------------------------------------------------         1         49 inactive         1         50 inactive         1         51 current         [oracle@wallet02 ~]$ sqlplus / as sysdbasql> set line 200sql> col archived for a10sql> select thread# , sequence# , archived , status from v$standby_log;   thread#  sequence# archived   status---------- ---------- ---------- ------------------------------         1         51 yes        active         1          0 no         unassigned         0          0 yes        unassigned         0          0 yes        unassigned         sql> select process , status , thread# , sequence# , block# , blocks  from v$managed_standby where process != 'ARCH';process                     status                                  thread#  sequence#     block#     blocks--------------------------- ------------------------------------ ---------- ---------- ---------- ----------rfs                         idle                                          0          0          0          0rfs                         idle                                          0          0          0          0rfs                         idle                                          1         51       4381          1mrp0                        applying_log                                  1         51       4381     102400

Data Guard 保护模式Data Guard 保护模式SQL> alter system set log_archive_dest_2='SERVICE=walletsdb LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=walletsdb' scope=spfile;SQL> shutdown immediate;SQL> startup mount;SQL> alter database set standby database to maximize availability;SQL> alter database open;SQL> select DEST_ID , DATABASE_MODE , RECOVERY_MODE , PROTECTION_MODE from v$archive_dest_status where DEST_ID=2;   DEST_ID DATABASE_MODE   RECOVERY_MODE           PROTECTION_MODE---------- --------------- ----------------------- --------------------         2 OPEN_READ-ONLY  MANAGED REAL TIME APPLY MAXIMUM AVAILABILITY         SQL> alter system set log_archive_dest_2='SERVICE=walletsdb LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=walletsdb' scope=spfile;SQL> shutdown immediate;SQL> startup mount;SQL> alter database set standby database to maximize protection;SQL> alter database open;QL> select DEST_ID , DATABASE_MODE , RECOVERY_MODE , PROTECTION_MODE from v$archive_dest_status where DEST_ID=2;   DEST_ID DATABASE_MODE   RECOVERY_MODE           PROTECTION_MODE---------- --------------- ----------------------- --------------------         2 OPEN_READ-ONLY  MANAGED REAL TIME APPLY MAXIMUM PROTECTION         SQL> alter system set log_archive_dest_2='SERVICE=walletsdb LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=walletsdb' scope=spfile;SQL> shutdown immediate;SQL> startup mount;SQL> alter database set standby database to maximize performance;SQL> alter database open;SQL> select DEST_ID , DATABASE_MODE , RECOVERY_MODE , PROTECTION_MODE from v$archive_dest_status where DEST_ID=2;   DEST_ID DATABASE_MODE   RECOVERY_MODE           PROTECTION_MODE---------- --------------- ----------------------- --------------------         2 OPEN_READ-ONLY  MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE

Data Guard Switchover原主库（转换为备库）SQL> select switchover_status from v$database;SWITCHOVER_STATUS--------------------TO STANDBYSQL> alter database commit to switchover to physical standby with session shutdown;Database altered.SQL> shutdown immediate;SQL> startup mount;SQL> alter database open read only;Database altered.SQL> alter database recover managed standby database using current logfile disconnect from session;Database altered.SQL> select database_role from v$database;DATABASE_ROLE----------------PHYSICAL STANDBY原备库（转换为主库）SQL> select switchover_status from v$database;SWITCHOVER_STATUS--------------------TO PRIMARYSQL> alter database commit to switchover to primary;Database altered.SQL> alter database open;     Database altered.SQL> select database_role from v$database;DATABASE_ROLE----------------PRIMARYSQL> select DEST_ID , DATABASE_MODE , RECOVERY_MODE , PROTECTION_MODE from v$archive_dest_status where DEST_ID=2;   DEST_ID DATABASE_MODE   RECOVERY_MODE           PROTECTION_MODE---------- --------------- ----------------------- --------------------         2 OPEN_READ-ONLY  MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE

Data Guard Failover开启主库的闪回模式SQL> select name, open_mode, database_role, flashback_on from v$database; NAME      OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON--------- -------------------- ---------------- ------------------DB01      READ WRITE           PRIMARY          NOSQL> shutdown immediate;SQL> startup mount SQL> alter database flashback on;SQL> alter database open; SQL> select flashback_on from v$database; FLASHBACK_ON------------------YESSQL> show parameter flashback NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_flashback_retention_target        integer     1440模拟主库故障SQL> shutdown abort; 原备库(转换为主库)SQL> alter database recover managed standby database cancel; SQL> alter database recover managed standby database finish; SQL> select open_mode, database_role from v$database; OPEN_MODE            DATABASE_ROLE-------------------- ----------------READ ONLY            PHYSICAL STANDBYSQL> alter database commit to switchover to primary with session shutdown; Database altered.SQL> alter database open;Database altered.SQL>  select open_mode, database_role from v$database; OPEN_MODE            DATABASE_ROLE-------------------- ----------------READ WRITE           PRIMARYSQL> select STANDBY_BECAME_PRIMARY_SCN from v$database;STANDBY_BECAME_PRIMARY_SCN--------------------------                   1194433                   原主库（转换为备库）SQL> startup mount; SQL> flashback database to scn 1194433;Flashback complete.SQL> alter database convert to physical standby;Database altered.SQL> shutdown immediate;SQL> startup mount;SQL> select open_mode, database_role from v$database; OPEN_MODE            DATABASE_ROLE-------------------- ----------------MOUNTED              PHYSICAL STANDBYSQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.SQL> alter database recover managed standby database cancel;SQL> alter database open; SQL> alter database recover managed standby database using current logfile disconnect from session; SQL> select open_mode, database_role from v$database; OPEN_MODE            DATABASE_ROLE-------------------- ----------------READ ONLY WITH APPLY PHYSICAL STANDBY