Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In order to make Failover from the broker, we need to set the Listener and TNS settings correctly.
Listener and TNS files of a SINGLE NODE Data Guard Environment whose broker configuration is as follows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | DGMGRL> show configuration Configuration - DG_Solution Protection Mode: MaxAvailability Members: primary - Primary database prmyFS - Far sync instance physical - (*) Physical standby database logical - Logical standby database Members Not Receiving Redo: physclFS - Far sync instance Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 13 seconds ago) |
LISTENER.ORA
[Primary]1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | [root@primary ~]# su - oracle [oracle@primary ~]$ cd $ORACLE_HOME/network/admin [oracle@primary admin]$ pwd /u01/app/oracle/product/12.1.0/db_1/network/admin [oracle@primary admin]$ ls listener.ora samples shrept.lst sqlnet.ora tnsnames.ora [oracle@primary admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = primary) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = primary) ) (SID_DESC = (GLOBAL_DBNAME = primary_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = primary) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle |
[PrimaryFS]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [root@prmyFS ~]# su - oracle [oracle@prmyFS ~]$ cd $ORACLE_HOME/network/admin [oracle@prmyFS admin]$ pwd /u01/app/oracle/product/12.1.0/db_1/network/admin [oracle@prmyFS admin]$ ls listener.ora samples shrept.lst tnsnames.ora [oracle@prmyFS admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = prmyFS) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = prmyFS) ) (SID_DESC = (GLOBAL_DBNAME = prmyFS_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = prmyFS) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prmyFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) ADR_BASE_LISTENER = /u01/app/oracle |
[Physical]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [root@physical ~]# su - oracle [oracle@physical ~]$ cd $ORACLE_HOME/network/admin [oracle@physical admin]$ pwd /u01/app/oracle/product/12.1.0/db_1/network/admin [oracle@physical admin]$ ls listener.ora samples shrept.lst tnsnames.ora [oracle@physical admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = physical) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = physical) ) (SID_DESC = (GLOBAL_DBNAME = physical_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = physical) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = physical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) ADR_BASE_LISTENER = /u01/app/oracle |
[Logical]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [root@logical ~]# su - oracle [oracle@logical ~]$ cd $ORACLE_HOME/network/admin [oracle@logical admin]$ pwd /u01/app/oracle/product/12.1.0/db_1/network/admin [oracle@logical admin]$ ls listener.ora samples shrept.lst tnsnames.ora [oracle@logical admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = logical) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = logical) ) (SID_DESC = (GLOBAL_DBNAME = logical_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = logical) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = logical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) ADR_BASE_LISTENER = /u01/app/oracle |
[PhysicalFS]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [root@physclFS ~]# su - oracle [oracle@physclFS ~]$ cd $ORACLE_HOME/network/admin [oracle@physclFS admin]$ pwd /u01/app/oracle/product/12.1.0/db_1/network/admin [oracle@physclFS admin]$ ls listener.ora samples shrept.lst tnsnames.ora [oracle@physclFS admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = physclFS) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = physclFS) ) (SID_DESC = (GLOBAL_DBNAME = physclFS_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = physcl) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = physclFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) ADR_BASE_LISTENER = /u01/app/oracle |
TNSNAMES.ORA
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | [oracle@primary admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PRMYFS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prmyFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prmyFS) ) ) LOGICAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = logical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = logical) ) ) PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary) ) ) BROKER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = broker.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = broker) ) ) PHYSICAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = physical.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = physical) ) ) CLOUD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = cloud.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cloud) ) ) PHYSCLFS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = physclFS.tivibulab.local)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760)) ) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = physclFS) ) ) |
The TNSNAMES.ORA file is the same in all instances.
After this information, Failover tests can be started.
1. We check if there is a problem by querying the broker configuration.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | [oracle@physical ~]$ dgmgrl 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. DGMGRL> connect sysdg Password: Connected as SYSDG. DGMGRL> show configuration Configuration - DG_Solution Protection Mode: MaxAvailability Members: physical - Primary database physclFS - Far sync instance primary - (*) Physical standby database logical - Logical standby database Members Not Receiving Redo: prmyFS - Far sync instance Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 55 seconds ago) |
2. We question whether the Physical Standby Databases are ready for Failover.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | DGMGRL> validate database verbose primary Database Role: Physical standby database Primary Database: physical Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Capacity Information: Database Instances Threads physical 1 1 primary 1 1 Temporary Tablespace File Information: physical TEMP Files: 1 primary TEMP Files: 1 Flashback Database Status: physical: On primary: On Data file Online Move in Progress: physical: No primary: No Standby Apply-Related Information: Apply State: Running Apply Lag: 0 seconds (computed 0 seconds ago) Apply Delay: 0 minutes Transport-Related Information: Transport On: Yes Gap Status: No Gap Transport Lag: 0 seconds (computed 0 seconds ago) Transport Status: Success Log Files Cleared: physical Standby Redo Log Files: Cleared primary Online Redo Log Files: Cleared primary Standby Redo Log Files: Available Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (physical) (primary) 1 3 2 Insufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (primary) (physical) 1 3 2 Insufficient SRLs Current Configuration Log File Sizes: Thread # Smallest Online Redo Smallest Standby Redo Log File Size Log File Size (physical) (primary) 1 50 MBytes 50 MBytes Future Configuration Log File Sizes: Thread # Smallest Online Redo Smallest Standby Redo Log File Size Log File Size (primary) (physical) 1 50 MBytes 50 MBytes Apply-Related Property Settings: Property physical Value primary Value DelayMins 0 0 ApplyParallel AUTO AUTO Transport-Related Property Settings: Property physical Value primary Value LogXptMode ASYNC ASYNC RedoRoutes (physical:physclFS SYNC) (primary:prmyFS SYNC) Dependency <empty> <empty> DelayMins 0 0 Binding optional optional MaxFailure 0 0 MaxConnections 1 1 ReopenSecs 300 300 NetTimeout 30 30 RedoCompression DISABLE DISABLE LogShipping ON ON Automatic Diagnostic Repository Errors: Error physical primary No logging operation NO NO Control file corruptions NO NO SRL Group Unavailable NO NO System data file missing NO NO System data file corrupted NO NO System data file offline NO NO User data file missing NO NO User data file corrupted NO NO User data file offline NO NO Block Corruptions found NO NO |
3. It is checked whether the Primary Database is ready for Failover.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | DGMGRL> validate database verbose physical Database Role: Primary database Ready for Switchover: Yes Capacity Information: Database Instances Threads physical 1 1 Temporary Tablespace File Information: physical TEMP Files: 1 Flashback Database Status: physical: On Data file Online Move in Progress: physical: No Transport-Related Information: Transport On: Yes Log Files Cleared: physical Standby Redo Log Files: Cleared Automatic Diagnostic Repository Errors: Error physical No logging operation NO Control file corruptions NO System data file missing NO System data file corrupted NO System data file offline NO User data file missing NO User data file corrupted NO User data file offline NO Block Corruptions found NO |
4. The failover process is started.
1 2 3 4 5 6 | DGMGRL> failover to primary; Performing failover NOW, please wait... Error: ORA-16600: not connected to target standby database for failover Failed. Unable to failover |
The reason for getting this error is that the broker is not logged in with the correct user. Since the failover process is triggered from Physical Standby, it must be connected to the Broker with Physical Standby.
5. Connect to Physical Standby Database from the broker.
1 2 | DGMGRL> connect sysdg/Passw0rd4@primary Connected as SYSDG. |
6. The failover process is triggered.
1 2 3 | DGMGRL> failover to primary Performing failover NOW, please wait... Failover succeeded, new primary is "primary" |
7. We check if there is an error by querying the broker configuration.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | DGMGRL> show configuration Configuration - DG_Solution Protection Mode: MaxAvailability Members: primary - Primary database Warning: ORA-16817: unsynchronized fast-start failover configuration prmyFS - Far sync instance physical - (*) Physical standby database Warning: ORA-16657: reinstatement of database in progress logical - Logical standby database (disabled) ORA-16661: the standby database needs to be reinstated Members Not Receiving Redo: physclFS - Far sync instance Fast-Start Failover: ENABLED Configuration Status: WARNING (status updated 12 seconds ago) |
The reason for the Warning in the Primary Database is that the Reinstate operations in the Physical Standby and Logical Standby have not been completed yet.
The reason for the Warning in Physical Standby is that the automatically starting Reinstate has not been completed yet. This can be seen when checked from Observer. If Fast-start Failover was DISABLE, Reinstate operation of the Original Primary Database would be done manually.
The reason for the Warning in Logical Standby is the requirement of Reinstate.
8. When the reinstate operation in the Observer is completed, the Broker configuration is queried again.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | DGMGRL> show configuration Configuration - DG_Solution Protection Mode: MaxAvailability Members: primary - Primary database prmyFS - Far sync instance physical - (*) Physical standby database logical - Logical standby database (disabled) ORA-16661: the standby database needs to be reinstated Members Not Receiving Redo: physclFS - Far sync instance Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 6 seconds ago) |
9. Reinstate operation for Logical Standby is triggered manually.
[Primary-DGMGRL]1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | DGMGRL> reinstate database logical; Reinstating database "logical", please wait... Operation requires shut down of instance "logical" on database "logical" Shutting down instance "logical"... Database closed. Database dismounted. ORACLE instance shut down. Operation requires start up of instance "logical" on database "logical" Starting instance "logical"... ORACLE instance started. Database mounted. Continuing to reinstate database "logical" ... Operation requires shut down of instance "logical" on database "logical" Shutting down instance "logical"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires start up of instance "logical" on database "logical" Starting instance "logical"... ORACLE instance started. Database mounted. Continuing to reinstate database "logical" ... Reinstatement of database "logical" succeeded |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | DGMGRL> show configuration Configuration - DG_Solution Protection Mode: MaxAvailability Members: primary - Primary database prmyFS - Far sync instance physical - (*) Physical standby database logical - Logical standby database Members Not Receiving Redo: physclFS - Far sync instance Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 49 seconds ago) |
a. The statuses of the databases are queried.
1 2 3 4 5 | [Primary] SQL> select status from v$instance; STATUS ------------ OPEN |
1 2 3 4 5 | [Physical] SQL> select status from v$instance; STATUS ------------ OPEN |
1 2 3 4 5 | [Logical] SQL> select status from v$instance; STATUS ------------ OPEN |
1 2 3 4 5 | [PrimaryFS] SQL> select status from v$instance; STATUS ------------ MOUNTED |
1 2 3 4 5 | [PhysicalFS] SQL> select status from v$instance; STATUS ------------ MOUNTED |
b. Open modes, roles and protection modes of databases are queried.
1 2 3 4 5 | [Primary] SQL> select open_mode, database_role, protection_mode from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE -------------------- ---------------- -------------------- READ WRITE PRIMARY MAXIMUM AVAILABILITY |
1 2 3 4 5 | [Physical] SQL> select open_mode, database_role, protection_mode from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE -------------------- ---------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY |
1 2 3 4 5 | [Logical] SQL> select open_mode, database_role, protection_mode from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE -------------------- ---------------- -------------------- READ WRITE LOGICAL STANDBY MAXIMUM AVAILABILITY |
1 2 3 4 5 | [PrimaryFS] SQL> select open_mode, database_role, protection_mode from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE -------------------- ---------------- -------------------- MOUNTED FAR SYNC MAXIMUM AVAILABILITY |
1 2 3 4 5 | [PhysicalFS] SQL> select open_mode, database_role, protection_mode from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_MODE -------------------- ---------------- -------------------- MOUNTED FAR SYNC MAXIMUM AVAILABILITY |
c. Recovery modes of Standby Databases are queried.
1 2 3 4 5 | [Physical] SQL> select recovery_mode from v$archive_dest_status where dest_id<2; RECOVERY_MODE ----------------------- MANAGED REAL TIME APPLY |
1 2 3 4 5 | [Logical] SQL> select recovery_mode from v$archive_dest_status where dest_id<2; RECOVERY_MODE ----------------------- LOGICAL REAL TIME APPLY |
12. Tests are entered.
a. Existing archive log sequence numbers are queried.
1 2 3 4 5 | [Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 41 1 |
1 2 3 4 5 | [PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 41 1 |
1 2 3 4 5 | [Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 41 1 |
1 2 3 4 5 | [Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 41 1 |
1 2 3 4 5 | [PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 40 1 |
b. The archive sequence numbers are checked by performing the Log Switch operation.
1 2 3 4 5 6 7 8 9 | [Primary] SQL> alter system switch logfile; System altered. [Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 41 1 |
1 2 3 4 5 | [PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 41 1 |
1 2 3 4 5 | [Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 41 1 |
1 2 3 4 5 | [Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 41 1 |
1 2 3 4 5 | [PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('11/02/2017 13:58:39', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 40 1 |
The reason why the archive sequence number does not increase despite the Log Switch is the use of the wrong where condition. Since the databases eat RESETLOGS, the most recent RESETLOGS_TIME is written in the where condition.
c. The nls_data_format of the session is changed so that RESETLOGS_TIME can be seen in date and time format.
1 2 3 4 5 6 7 8 9 | [Primary] SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'; Session altered. [Primary] SQL> select resetlogs_time from v$database; RESETLOGS_TIME ------------------- 12-02-2017 11:20:43 |
d. This date is written in the where condition and the archive sequence numbers are checked again.
1 2 3 4 5 | [Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 6 1 |
1 2 3 4 5 | [PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 6 1 |
1 2 3 4 5 | [Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 6 1 |
1 2 3 4 5 | [Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 6 1 |
1 2 3 | [PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#; no rows selected |
The reason why there is no data in Physical Far SYNC is that this instance is not actively used after the specified RESETLOGS_TIME.
Logs are sent to Standby Databases via Primary Far SYNC instance.
e. It is checked whether the archive sequence number has increased by performing the Log Switch operation.
1 2 3 4 5 6 7 8 9 | [Primary] SQL> alter system switch logfile; System altered. [Primary] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 7 1 |
1 2 3 4 5 | [PrimaryFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 7 1 |
1 2 3 4 5 | [Physical] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 7 1 |
1 2 3 4 5 | [Logical] SQL> select max(sequence#),thread# from dba_logstdby_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 7 1 |
1 2 3 | [PhysicalFS] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('12/02/2017 11:20:43', 'DD-MM-YYYY HH24:MI:SS') group by thread#; no rows selected |
f. Finally, a table belonging to the TEST user is created and it is checked whether it also occurs in Standby databases.
i. We are querying the existing tables.
1 2 3 4 5 6 | [Primary] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- JOBS_YEDEK EMPLOYEES_YEDEK |
1 2 3 4 5 6 | [Physical] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- JOBS_YEDEK EMPLOYEES_YEDEK |
1 2 3 4 5 6 | [Logical] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- JOBS_YEDEK EMPLOYEES_YEDEK |
ii. We create a new table.
1 2 3 4 5 6 7 8 9 10 11 | [Primary] SQL> create table test.regions_yedek as select * from hr.regions; Table created. [Primary] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- JOBS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK |
1 2 3 4 5 6 7 | [Physical] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- JOBS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK |
1 2 3 4 5 6 7 | [Logical] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME -------------------------------------------------------------------------------- JOBS_YEDEK EMPLOYEES_YEDEK REGIONS_YEDEK |
