Oracle 19c-Step by Step Configure Snapshot Standby Database

Description:-

  • Oracle Database 11g introduced the Snapshot Standby feature in Data Guard
  • A snapshot standby database is a fully updateable standby database created by converting a physical standby database into a snapshot standby database.
  • A snapshot standby database receives and archives, but does not apply,
    redo data from a primary database.
  • The redo data received from the primary database is applied once a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

Dataguard Configuration:-

Step:1 Check both primary and standby role.

Primary:-

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS       INSTANCE_NAME     DATABASE_ROLE     OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN            class             PRIMARY         READ WRITE

Standby:-

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS         INSTANCE_NAME    DATABASE_ROLE        OPEN_MODE
------------ ---------------- ----------------   --------------------
MOUNTED        class            PHYSICAL STANDBY     MOUNTED

Step 2:- Check the archived sequence in standby,

select thread#,max(sequence#) from v$archived_log group by thread#;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
---------- --------------
1              37

Step 3:- Check the flashback status and db_recovery_file_set location,

select flashback_on from v$database;

show parameter db_recovery_file_dest

SQL> show parameter db_recovery_file_dest

NAME                          TYPE            VALUE
-------------------------- ----------- ------------------------------
db_recovery_file_dest       string      /u01/app/oracle/recovery_dest/
db_recovery_file_dest_size  big integer            2G


SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Step:4 In standby side,stop the mrp process.

 alter database recover managed standby database cancel;

SQL> recover managed standby database cancel;
Media recovery complete.

Step:5 Bounce the database and keep in mount stage,

shut immediate

startup mount

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 583008256 bytes
Database Buffers 348127232 bytes
Redo Buffers 3678208 bytes
Database mounted.

Step:6 Convert to snapshot standby database

alter database convert to snapshot standby

SQL> alter database convert to snapshot standby;

Database altered.

Step:7 Open the standby database in READ/WRITE mode

alter database open

SQL> alter database open;

Database altered.

Step:8 Check the DATABASE_ROLE and OPEN_MODE,

select status,instance_name,database_role,open_mode from v$database,v$Instance

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS        INSTANCE_NAME     DATABASE_ROLE       OPEN_MODE
------------ ----------------  ----------------  --------------------
OPEN            class          SNAPSHOT STANDBY    READ WRITE

Step:9 Testing on the snapshot standby database,

create user test identified by test;
grant connect,resource to test;
conn test/test
create table t1(sno number, name varchar2(20));
insert into t1 values (1,’example’);
commit;

SQL> alter user test quota unlimited on USERS;

User altered.

SQL> conn test/test;
Connected.

SQL> create table t1(sno number, name varchar2(20));

Table created.

SQL> insert into t1 values (1,'example');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

SNO           NAME
---------- --------------------
1             example

SQL>

Step:10 Both side verify the archived sequence

Primary side
select thread#,max(sequence#) from v$archived_log group by thread#;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
----------  --------------
1              47

Standby side

select thread#,max(sequence#) from v$archived_log group by thread#;
select process,status,sequence# from v$managed_standby;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD#     MAX(SEQUENCE#)
---------- --------------
1               47

Step 11: In Standby Side,bounce and mount the database

shut immediate
startup mount

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 759169024 bytes
Database Buffers 171966464 bytes
Redo Buffers 3678208 bytes
Database mounted.

Step:12 Convert the snapshot standby database to physical standby database,

alter database convert to physical standby;

SQL> alter database convert to physical standby;

Database altered.

Step:13 Bounce the database,

shut immediate
startup

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 943716968 bytes
Fixed Size 8903272 bytes
Variable Size 759169024 bytes
Database Buffers 171966464 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.

Step:14 Check the standby database_role and mode.

select status,instance_name,database_role,open_mode from v$database,v$Instance;

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS        INSTANCE_NAME    DATABASE_ROLE      OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN          class           PHYSICAL STANDBY    READ ONLY

Step:15 Enable the MRP process
alter database recover managed standby database disconnect from session;

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Step:16 After convert to physical standby database,check the table status

Note:  After convert back to Physical standby database  once again restart the MRP process.

SQL> select * from test.t1;
select * from test.t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Oracle GoldenGate vs Oracle Data Guard

The configuration of Oracle GoldenGate / Data Guard has the purpose of synchronizing data between two or more systems .

The basic features of the two products may look similar but takes GoldenGate uses replication while dataguard not.

Oracle Data Guard and GoldenGate involving at least two systems where transactional data from one database is required to be moved to another database. The purpose of the replication of data can be disaster recovery, migration of data or preparation of a secondary system.

Data Guard is best for disaster recovery and data protection problems, GoldenGate is a more flexible heterogeneous replication mechanism and is also able to transform the data while it is being replicated.

  • Data Guard is an Oracle specific technology while GoldenGate support heterogeneous database systems including all the major RDBMS as DB2,Sybase, MySQL .
  • Data Guard supports active-passive replication. One of the database is the primary database and the other one is in an inactive Data Guard mode.
  • GoldenGate supports an active-active replication mode and allows both systems to work simultaneously while maintaining the data integrity.
  • GoldenGate allows transformation of the data, with conflict management while it is being replicated between both database systems.
  • GoldenGate allows replication across platform. Data can be extracted from a Unix platform and replicated to an Oracle database running on platform Windows.
  • GoldenGate has many case of utilization. The use of flat files for data transportation and the support of heterogeneous systems makes the technology so very interesting

 

GoldenGate is the best solution:
  • Replicate one or many tables to a read-write database.
  • Replicate and transform tables.
  • Bi-directional replication.
  • Zero or near-zero downtime upgrade.
  • Heterogeneous replication

GoldenGate replication.

• Zero Downtime Upgrade/Migration: You may want to setup an exact replica of your database for upgrade or migration, keep the data in sync till the last moment.

High Availability: Your applications cannot afford to have downtime so you want to have two databases up and running at the same time to support those applications.

Query or Reporting: You want to offload the reporting or long query operations from your production database.

Real Time BI: For your data warehouse requirements.

Data Synchronization: You want to Synchronize data across different departments of the company (different databases).

Furthermore the GoldenGate software supports extraction from the legacy systems and does not have any limitation on the distance either. Additionally, the trail files generated by GoldenGate capture can also be compressed and encrypted.

Oracle Data Guard

Used for

  • High availability
  • Data protection
  • Disaster recovery
  • Primary database workload reduction

Database roles

In a data guard environment, one database is a primary database, the others are standby databases. These roles are mutually exclusive.
The role of a database can be queried in the column database_role in v$database.

Primary database

The primary db is the »real« production database.
The primary db must run in archive log mode and with forced logging.

Standby databases

Standby databases are (transactionally consistent) copies of the primary database.
Up to 30 standby databases are supported.

Physical and logical standby databases

A standby DB is either physical or logical standby database. (A standby database can also become a snapshot standby database).
A physical database is an exact byte-wise copy of the primary database.
A logical database has the same data as the primary database, but is not necessarily stored in the same db blocks.
The physical database is kept synchronized with redo apply, the logical database with SQL apply.

Cascading standby database

A physical standby database might be configured to receive redo from a primary database and relay it to a another standby database.
In this case, the relaying database is  called a cascading standby database. The receiving standby databases is also known as a terminal destination.
With such a configuration, the load on the primary database can be reduced.

SQL*Plus

Some SQL*Plus statements come with the standby keyword which indicates that the statement should be applied on the standby databases.

Role of redo

Data Guard basically works as follows:

  • Archived redo log files from the production DB are copied to the standby DB.
  • They are then registered with the standby DB.
  • Finally, using media recovery, the redo is used to roll forward the standby database.

Preventing physical errors

A standby database validates the redo it receives from the primary DB.
Thus, physical errors (such as data corruption) on the primary DB are not propagated to standby databases.

Changing roles of databases

The role (primary vs standby) of a database can be changed by either a

  • switch over or
  • a fail-over
A switch over is a planned role transition. In a switch over, the primary database becomes a standby database while (one of) the standby databases becomes the primary database.
A switch over is performed in two steps:
1) the primary DB becomes a standby DB
2) a standby DB becomes a primary DB.
A fail over occurs when the primary database becomes unavailable. In this case, one of the standby databases becomes the new primary database.

RAC

The primary db and the standby databases can be run as single instance or real application clusters(RAC).

Active Data Guard

Active data guard offers the same features as data guard plus that it standby databases are opened read only while the copying of data takes place
Thus, it allows to query the standby databases without impacting the performance of the production database (for example to create resource-intensive reports).
The standbase database is called physical standby database.
The buzzword is real time query.
Active data guard must be licenced seperatly.

Data protection modes

Data Guard runs in one of three data protection modes. This allows to balance data availability against system performance requirements.

These protection modes are:

  • Maximum performance (default)
  • Maximum protection
  • Maximum availability

Automatic gap detection

If connectivity is lost between the primary and one or more standby databases
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba