【Oracle Database】 DataGuard（rac-single）

[oracle@king01 ~]$ sqlplus / as sysdba
SQL> alter database force logging;
Database altered.

SQL> col force_logging for a15
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------
YES

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     30
Next log sequence to archive   31
Current log sequence           31

SQL> 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.ora
kingmdb =
  (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.ora
kingmdb =
  (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.ora
SID_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.ora
kingmdb =
  (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 kingsdb
Used TNSNAMES adapter to resolve the alias
Attempting 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 kingmdb
Used TNSNAMES adapter to resolve the alias
Attempting 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_profile
export ORACLE_SID=kingdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export DISPLAY=192.168.1.200:0
export NLS_LANG=AMERICAN_AMERICA.UTF8
export 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 sysdba
SQL> 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 sysdba
SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             432014216 bytes
Database Buffers          629145600 bytes
Redo Buffers                5517312 bytes

[oracle@king03 ~]$ rman target sys/oracle@kingmdb auxiliary sys/oracle@kingsdb nocatalog
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 14 13:41:26 2018
Copyright (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 catalog
connected to auxiliary database: KINGDB (DBID=4127342910)

RMAN> duplicate target database for standby dorecover nofilenamecheck;

[oracle@king03 ~]$ sqlplus / as sysdba
SQL> 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 sysdba
SQL> alter system set log_archive_dest_state_2=enable scope=both;
SQL> alter system switch logfile;

[oracle@king03 ~]$ sqlplus / as sysdba
SQL> 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 sysdba
SQL> set line 200
SQL> col database_mode for a30
SQL> col protection_mode for a30
SQL> col recovery_mode for a30
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
         
SQL> col dest_name for a20
SQL> col destination for a30
SQL> 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 a20
SQL> 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            0
LOG_ARCHIVE_DEST_2   kingsdb                        VALID                       PHYSICAL                        23           22

SQL> 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     102400
RFS                         IDLE                                          0          0          0          0
RFS                         IDLE                                          0          0          0          0
RFS                         IDLE                                          0          0          0          0
RFS                         IDLE                                          1         31      49129          1
RFS                         IDLE                                          0          0          0          0
RFS                         IDLE                                          0          0          0          0
RFS                         IDLE                                          0          0          0          0
RFS                         IDLE                                          2         24      20942          1