Analyticsandthecloud

1. Prepare the Primary Database
   • Ensure the primary Database is Archive Log Mode Enabled.

 SQL> SELECT log_mode  FROM v$database;

           If not enabled, then enable it:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG;   

SQL> ALTER DATABASE OPEN;

SQL> ARCHIVE LOG LIST;

 2. Force Logging Mode: 

Enable force logging on the primary database to ensure all transactions generate redo, even if the user specifies the NOLOGGING clause. The NOLOGGING option reduces redo generation for certain operations, but this can cause issues for the physical standby database, as it relies on redo data for synchronization. By enabling force logging, you ensure that all changes are recorded in the redo logs, regardless of the    NOLOGGING setting, keeping the standby database consistent with the primary.

SQL> ALTER DATABASE FORCE LOGGING;

SQL> select name, force_logging from v$database;

3. Standby File Management

This ensures that whenever data files are added/dropped from the primary database, they’re automatically added/dropped on/from the standby database(s).

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT =’AUTO’;

Create Password File:

[oracle@prim ~]$ cd $ORACLE_HOME/dbs

[oracle@prim dbs]$ ls -la

total 24

drwxr-xr-x.  2 oracle oinstall  102 Jan 19 02:07 .

drwxr-xr-x. 74 oracle oinstall 4096 Jan 19 02:07 ..

-rw-rw—-.  1 oracle oinstall 1544 Jan 19 02:07 hc_testdb.dat

-rw-r–r–.  1 oracle oinstall 3079 May 15  2015 init.ora

-rw-r—–.  1 oracle oinstall   24 Jan 19 01:44 lkTESTDB

-rw-r—–.  1 oracle oinstall 3584 Jan 19 01:49 orapwtestdb

-rw-r—–.  1 oracle oinstall 3584 Jan 19 02:11 spfiletestdb.ora

Create orapassword for standby database

[oracle@prim dbs]$ orapwd file=orapwtestdb_s

Enter password for SYS: new_password123

[oracle@prim dbs]$

Copy the file to the standby server Oracle home directory using scp utility

[oracle@prim dbs]$ scp orapwtestdb_s oracle@stb:$ORACLE_HOME/dbs

The authenticity of host ‘stb (192.168.56.3)’ can’t be established.

ECDSA key fingerprint is SHA256:RMe43pWCQ7vWDEE3AzDWLG0OwcHQXrwygsZghLGr5ic.

ECDSA key fingerprint is MD5:5f:6e:1c:41:25:e5:e0:24:a1:3f:ac:e3:ae:01:05:a3.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added ‘stb,192.168.56.3’ (ECDSA) to the list of known hosts.

oracle@stb’s password:

orapwtestdb_s                                                                                          100% 6144     2.4MB/s   00:00

[oracle@prim dbs]$

Check DB Unique name parameter on primary: Make sure your primary database has DB_UNIQUE_NAME parameter set for consistency. If it’s not set properly, use ALTER SYSTEM SET command

SQL> show parameter db_unique_name;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_unique_name                       string      testdb

Enable Flashback on Primay:

1. In case of failover(Crash) , it is easy to recover/recreate the database

SQL> !mkdir -p /u01/app/oracle/fast_recovery_area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=’/u01/app/oracle/fast_recovery_area’ SCOPE=BOTH;

SQL> SHOW PARAMETER db_recovery_file_dest;

SQL> alter system set db_recovery_file_dest_size=45g;
SQL> alter database flashback on;
SQL> select flashback_on from v$database;

CONFIGURING NETWORK

TNSNAMES on both servers

TNS_NAMES

Modify the IPv4 address and SERVICE_NAME

[oracle@prim ~]$ cd $ORACLE_HOME/network/admin

[oracle@prim admin]$ vi tnsnames.ora

Paste below command and save.

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
TESTDB_S =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb_s)
)
)

CONFIGURE LISTENER ON BOTH SERVERS

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = testdb)

      (SID_NAME = testdb)

      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = testdb_DGMGRL)

      (SID_NAME = testdb)

      (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)

    )

  )

[oracle@prim admin]$ cd $ORACLE_HOME/network/admin/

[oracle@prim admin]$ vi listener.ora

paste the above and exit vi editor.

Create Dot Bash Profile on the Standby:

[oracle@stb ~]$ vi .bash_profile_12c

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs
export ORACLE_SID=testdb_s
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1
PATH=$PATH:$HOME/.local/bin:$ORACLE_HOME/bin

export PATH

Source your bash profile:

[oracle@stb ~]$ . .bash_profile_12c

Check the Environmental Variables

[oracle@stb ~]$ env|grep ORA
ORACLE_SID=testdb_s
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1

CONFIGURE TNSNAMES and LISTENER 

[oracle@stb ~]$ cd $ORACLE_HOME/network/admin
[oracle@stb admin]$ vi listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = testdb_s)
(SID_NAME = testdb_s)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
)
(SID_DESC =
(GLOBAL_DBNAME = testdb_s_DGMGRL)
(SID_NAME = testdb_s)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)

DUPLICATE TARGET DATABASE FOR STANDBY 
FROM ACTIVE DATABASE NOFILENAMECHECK;

Verify Standby Configuration.

On Standby: 
Run the following queries;
Currently, the standby database requires recovery

SQL> shut immediate;
SQL> startup mount;
SQL> alter database recover managed standby database disconnect;

This command will start/enable MRP on the standby

Check the Status of DG on both Servers:

SQL> set lines 999;
select * from v$dataguard_status order by timestamp;

SQL> select dest_id, status, destination, error from v$archive_dest where dest_id<=2;

Run the following query on standby:

SELECT DEST_ID, STATUS, DESTINATION, ERROR FROM V$ARCHIVE_DEST WHERE TARGET = ‘STANDBY’;

On Primary:
================

This query will tell you if logs are being applied.

SQL> select sequence#, first_time, next_time, applied, archived from v$archived_log where name = ‘testdb_s’ order by first_time;

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC
———- ——— ——— ——— —
5 26-JAN-25 26-JAN-25 YES YES
6 26-JAN-25 26-JAN-25 YES YES
7 26-JAN-25 26-JAN-25 YES YES
8 26-JAN-25 26-JAN-25 YES YES
9 26-JAN-25 26-JAN-25 YES YES
10 26-JAN-25 26-JAN-25 YES YES
11 26-JAN-25 26-JAN-25 YES YES

ON STANDBY
===========

SQL> select process, status, sequence# from v$managed_standby;

You can see that MRP is waiting for log# 12

PROCESS STATUS SEQUENCE#
——— ———— ———-
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 12
MRP0 WAIT_FOR_LOG 12

ON PRIMARY:
===========

Let us switch logs a couple times and see how fast that’s applied on standby. 

SQL> ALTER SYSTEM SWITCH LOGFILE;

On the stndby, you can now see that MRP is waiting on log# 20

PROCESS STATUS SEQUENCE#
——— ———— ———-
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CLOSING 15
ARCH CLOSING 17
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 20
RFS IDLE 20

This confirms that our Data Guard Setup is working perfectly well. This concludes verification. 

Let’s check the exact location of the archive log files and see if they’re being applied or not.

On Standby
========

select sequence#, applied, first_time, next_time, name filename from v$archived_log order by sequence#;

ENABLE FLASHBACK ON STANDBY

To enable Flashback Database on a standby database, follow the steps below. Flashback Database is useful in Oracle Data Guard environments as it allows you to rewind a database to a previous point in time without needing to restore from backups, which is especially helpful during failovers or testing scenarios.

1. Check Flashback Status

Run this query to verify if Flashback Database is already enabled:

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

2. DISABLE MRP ON STANDBY;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3. Configure a Flash Recovery Area (FRA)

Flashback Database requires a Flash Recovery Area (FRA) to store flashback logs.

First verify that FRA exists:

SHOW PARAMETER DB_RECOVERY_FILE_DEST; SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE;

If not, then configure using below scripts.

Set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=‘/path/to/recovery_area’ SCOPE=BOTH; ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=50G SCOPE=BOTH;

4. Enable Flashback Database

Enable Flashback Database on the standby:

SQL> ALTER DATABASE FLASHBACK ON;

5. Verify Flashback is Enabled

After enabling Flashback, verify its status:

ON PRIMARY, CONFIGURE ARCHIVE DELETION POLICY

On Primary:
===========
rman target / 
configure archivelog deletion policy to applied on all standby;