【Oracle Database】 DataGuard（rac-single）

[oracle@king01 ~]$ 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     30Next log sequence to archive   31Current log sequence           31SQL> alter system set db_unique_name='kingmdb' scope=spfile;SQL> alter system set log_archive_config='dg_config=(kingmdb,kingsdb)' 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=kingmdb' scope=spfile;SQL> alter system set log_archive_dest_2='service=kingsdb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=kingsdb' scope=spfile;SQL> alter system set fal_server='kingsdb' 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=kingdb,kingmdb scope=spfile;[oracle@king01 ~]$ mkdir backup[oracle@king01 ~]$ 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@king01 ~]$ scp /home/oracle/backup/*  192.168.1.203:/home/oracle/backup[oracle@king01 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.orakingmdb =  (DESCRIPTION =   (ADDRESS_LIST =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))   )    (CONNECT_DATA =      (SERVICE_NAME = kingdb)    )  )  kingsdb =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = kingdb)    )  )  [oracle@king02 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.orakingmdb =  (DESCRIPTION =   (ADDRESS_LIST =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))   )    (CONNECT_DATA =      (SERVICE_NAME = kingdb)    )  )  kingsdb =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = kingdb)    )  )[oracle@king03 ~]$ vi $ORACLE_HOME/network/admin/listener.oraSID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = kingdb)      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)      (SID_NAME = kingdb)    )  )LISTENER =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = king03)(PORT = 1521))  )ADR_BASE_LISTENER = /u01/app/oracle[oracle@king03 ~]$ lsnrctl start[oracle@king03 ~]$ lsnrctl status[oracle@king03 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.orakingmdb =  (DESCRIPTION =   (ADDRESS_LIST =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))   )    (CONNECT_DATA =      (SERVICE_NAME = kingdb)    )  )  kingsdb =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = kingdb)    )  )[oracle@king01 ~]$ tnsping kingsdbUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = kingdb)))OK (110 msec)[oracle@king03 ~]$ tnsping kingmdbUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = kingdb)))OK (0 msec)[oracle@king03 ~]$ vi .bash_profileexport ORACLE_SID=kingdbexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1export PATH=$ORACLE_HOME/bin:$PATHexport DISPLAY=192.168.1.200:0export NLS_LANG=AMERICAN_AMERICA.UTF8export NLS_DATE_FORMAT="yyyy-mm-dd hh34:mi:ss"stty erase ^H[oracle@king03 ~]$ source .bash_profile [oracle@king03 ~]$ cd $ORACLE_HOME/dbs[oracle@king03 dbs]$ vi initkingdb.ora *.audit_file_dest='/u01/app/oracle/admin/kingdb/adump'*.audit_trail='db'*.compatible='11.2.0.4.0'*.control_files='/u01/app/oracle/oradata/kingdb/control01.ctl','/u01/app/oracle/fast_recovery_area/kingdb/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='kingdb'*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'*.db_recovery_file_dest_size=107374182400*.db_file_name_convert='+DATAFILE/kingdb/datafile','/u01/app/oracle/oradata/kingdb','+DATAFILE/kingdb/tempfile','/u01/app/oracle/oradata/kingdb'*.db_unique_name='kingsdb'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=kingdbXDB)'*.fal_server='kingmdb'*.log_archive_config='dg_config=(kingmdb,kingsdb)'*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=kingsdb'*.log_archive_dest_2='service=kingmdb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=kingmdb'*.log_file_name_convert='+DATAFILE/kingdb/onlinelog','/u01/app/oracle/oradata/kingdb'*.memory_max_target=1073741824*.memory_target=1073741824*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.standby_file_management='auto'*.undo_tablespace='UNDOTBS1'[oracle@king03 dbs]$ sqlplus / as sysdbaSQL> create spfile from pfile;File created.[oracle@king03 dbs]$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwkingdb password=oracle entries=5 force=y[oracle@king03 ~]$ mkdir -p /u01/app/oracle/admin/kingdb/adump[oracle@king03 ~]$ mkdir -p /u01/app/oracle/oradata/kingdb[oracle@king03 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/kingdb[oracle@king03 ~]$ 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@king03 ~]$ rman target sys/oracle@kingmdb auxiliary sys/oracle@kingsdb nocatalogRecovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 14 13:41:26 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: KINGDB (DBID=4127342910)using target database control file instead of recovery catalogconnected to auxiliary database: KINGDB (DBID=4127342910)RMAN> duplicate target database for standby dorecover nofilenamecheck;[oracle@king03 ~]$ sqlplus / as sysdbaSQL> alter database add standby logfile thread 1 '/u01/app/oracle/oradata/kingdb/standby01.log' size 50m;SQL> alter database add standby logfile thread 1 '/u01/app/oracle/oradata/kingdb/standby02.log' size 50m;SQL> alter database add standby logfile thread 1 '/u01/app/oracle/oradata/kingdb/standby03.log' size 50m;SQL> alter database add standby logfile thread 2 '/u01/app/oracle/oradata/kingdb/standby04.log' size 50m;SQL> alter database add standby logfile thread 2 '/u01/app/oracle/oradata/kingdb/standby05.log' size 50m;SQL> alter database add standby logfile thread 2 '/u01/app/oracle/oradata/kingdb/standby06.log' size 50m;SQL> alter database recover managed standby database disconnect from session using current logfile;[oracle@king01 ~]$ sqlplus / as sysdbaSQL> alter system set log_archive_dest_state_2=enable scope=both;SQL> alter system switch logfile;[oracle@king03 ~]$ 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@king01 ~]$ 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                       USE_DB_RECOVERY_FILE_DEST         2 LOG_ARCHIVE_DEST_2   VALID                       kingsdb         SQL> 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                                  VALID                       LOCAL                           30            0LOG_ARCHIVE_DEST_2   kingsdb                        VALID                       PHYSICAL                        23           22SQL> select thread# , sequence# , status from v$log;   THREAD#  SEQUENCE# STATUS---------- ---------- ------------------------------------------------         1         31 CURRENT         1         30 INACTIVE         2         23 INACTIVE         2         24 CURRENT[oracle@king03 ~]$ sqlplus / as sysdba SQL> select thread# , sequence# , archived , status from v$standby_log;   THREAD#  SEQUENCE# ARCHIVED   STATUS---------- ---------- ---------- ------------------------------         1         31 YES        ACTIVE         1          0 NO         UNASSIGNED         1          0 YES        UNASSIGNED         2          0 NO         UNASSIGNED         2         24 YES        ACTIVE         2          0 YES        UNASSIGNED         SQL> select process , status , thread# , sequence# , block# , blocks  from v$managed_standby where process != 'ARCH';PROCESS                     STATUS                                  THREAD#  SEQUENCE#     BLOCK#     BLOCKS--------------------------- ------------------------------------ ---------- ---------- ---------- ----------MRP0                        APPLYING_LOG                                  2         24      20942     102400RFS                         IDLE                                          0          0          0          0RFS                         IDLE                                          0          0          0          0RFS                         IDLE                                          0          0          0          0RFS                         IDLE                                          1         31      49129          1RFS                         IDLE                                          0          0          0          0RFS                         IDLE                                          0          0          0          0RFS                         IDLE                                          0          0          0          0RFS                         IDLE                                          2         24      20942          1