Friday, March 8, 2019

Oracle Dataguard Configuration

Here in this post we'll see about Oracle Dataguard Configuration.

I've configured Oracle Dataguard Configuration using two methods. One is Oracle Standard Method. It is the approach recommended by oracle and the second one is configuring using a different method. The second one is much easier to configure, but it is not recommended and also manual intervention is needed to sync the log files between Primary and Standby after Role transitions (i.e) Switch-Over/Switch-Back

Now we'll see about the Oracle recommended approach to configure Dataguard setup. If you want to configure using the second method Click Here

Naming Conventions:

Primary DB Name              : asdb
Primary DB Unique Name  : asdb

Standby DB Name              : asdb
Standby DB Unique Name  : stdasdb

Below is the High-Level steps for Dataguard Configuration

  1. Add entries in hosts file
  2. Configure Primary Database for Disaster Recovery
  3. Backup Primary Database for Standby Database creation
  4. Install Oracle Home for Standby Database
  5. Edit and change the pfile on the Standby side
  6. Create Standby Database using Primary backup
  7. Edit listener, tnsnames and sqlnet files of both the databases
  8. Check tnsping and sql connectivity from both the ends
  9. Start MRP process
  10. Important Queries

Add entries in hosts file

Add entries in hosts file of both Primary and Standby servers in such a way that both servers should have host entries of Primary and Standby servers.


Configure Primary Database for Disaster Recovery


Configuration of the Primary Database includes the following tasks to be done.



Enabling Archive log




Open a command prompt to your operating system.

Log in to SQL*Plus as SYSDBA:

sqlplus '/as sysdba'



Configure how much space you want to dedicate to your Fast Recovery Area:

alter system set db_recovery_file_dest_size = 50G;
This example dedicates 50GB. You should see this:
System altered.

Choose the destination:
alter system set db_recovery_file_dest=’/orahome/app/oracle/product/FRA’;
You should see this:
System altered.

Now Oracle automatically creates your archive logs under the Fast Recovery Area. The archive process creates a folder for your database and the subfolder for the date the archives were created. It organizes them very nicely.
If you want to see how much of your Fast Recovery Area is used, log in to SQL*Plus and query the view V$FLASH_RECOVERY_AREA_USAGE. It’s sort of a misnomer. Oracle still hasn’t changed the name of this view from FLASH to FAST even though they made the change in the documentation two versions ago.
After determining where you want to keep the archive logs, you can turn on archiving. These steps walk you through the process:

Open a command prompt to your operating system.
Log in to SQL*Plus as SYSDBA:
sqlplus '/as sysdba'

Shut down the database:
shutdown immediate

You see this:
Database closed.
Database dismounted.
ORACLE instance shut down.

Start the database in mount mode:
startup mount

You see something like this:
ORACLE instance started.
Total System Global Area 1336176640 bytes
Fixed Size         2287480 bytes
Variable Size      1258293384 bytes
Database Buffers      67108864 bytes
Redo Buffers        8486912 bytes
Database mounted.

Issue the command to enable archive mode:
alter database archivelog;
You should see this:
Database altered.

Open the database:

alter database open;
You should see this:
Database altered.

Now your database is in archive log mode, and archive log files should show up in your Fast Recovery Area.


Enable Forced logging

SQL> alter database force logging;


Configuring log archive destination parameters


SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=asdb';



SQL> alter system set log_archive_dest_2= 'SERVICE=asdbdr NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdasdb'



Configuring log archive dest state parameter

SQL> alter system set log_archive_dest_state_2='enable';

Creating password file


In password file creation below points needs to be taken care and they are mandatory.

                                I.            Password file should be created from Primary

                             II.            Password files’s password should match the password of the the SYS user

                           III.            Password file should be copied from Primary to Standby and not should not be created separately on Standby server

orapwd file=orapwasdb password=Manager_123 force=y


Configuring fal_server and fal_client parameter


SQL> alter system set fal_server=' asdbdr';

SQL> alter system set fal_client='asdb';



Configuring log archive config parameter




SQL> alter system set log_archive_config='DG_CONFIG=(asdb,stdasdb)';



Configuring remote login parameter




SQL> alter system set remote_login_passwordfile='EXCLUSIVE';



Configuring case sensitive login parameter




SQL> alter system set sec_case_sensitive_logon=TRUE;



Configuring standby file management parameter




SQL> alter system set standby_file_management='AUTO';



Configuring local listener parameter




SQL> alter system set local_listener='LISTENER_ASDB';





Backup Primary Database for Standby Database creation




Use the below syntax to create a backup for Standby database from Primary



Syntax

rman target /

RMAN> backup database include current controlfile for standby plus archivelog backup location ‘<backup location>’;



Install Oracle Home for Standby Database






































Edit and change the pfile on the Standby side

First of all copy the pfile from Primary to Standby Database, make a backup copy and then follow the below steps to incorporate the required changes on the Standby Database.

Configuring log archive destination parameters


SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdasdb';



SQL> alter system set log_archive_dest_2='SERVICE=asdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=asdb';



Configuring log archive dest state parameter

SQL> alter system set log_archive_dest_state_2='enable';


Configuring fal_server and fal_client parameter

SQL> alter system set fal_server=' asdb';
SQL> alter system set fal_client='asdbdr';


Configuring log archive config parameter

SQL> alter system set log_archive_config='DG_CONFIG=(stdasdb,asdb)';


Configuring remote login parameter

SQL> alter system set remote_login_passwordfile='EXCLUSIVE';


Configuring case sensitive login parameter

SQL> alter system set sec_case_sensitive_logon=TRUE;


Configuring standby file management parameter

SQL> alter system set standby_file_management='AUTO';


Configuring local listener parameter

SQL> alter system set local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=<server ip>)(PORT=1521)) ';



Create Standby Database using Primary backup

Create a Standby Database from the Primary backup using the below syntax
Open a command prompt to your operating system.
Log in to SQL*Plus as SYSDBA:

Shutdown the database:

sqlplus '/as sysdba'
SQL> shutdown immediate

Start the database in mount mode:

SQL> startup nomount

Create Standby Database

rman auxiliary /
RMAN> duplicate target database for standby backup location '<RMAN Backup location>' nofilenamecheck;


Edit listener, tnsnames and sqlnet files of both the databases

Below is the configuration of listener, tnsnames and sqlnet files of both databases so that connectivity between the databases will be established, which in turn will made the log shipping possible between the two servers.

Primary Files:

listener.ora
[oracle@bapas1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /orahome/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.domain>)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ASDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.domain>)(PORT = 1521))
)

SID_LIST_ASDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = asdb) >> Here you can mention your DB name with or without your domain name.
(ORACLE_HOME = /orahome/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = asdb)
)
)

tnsnames.ora
[oracle@bapas1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /orahome/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ASDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.domain>)(PORT = 1521))

ASDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.domain>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = asdb.<domain name>)
)
)

ASDBDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.domain>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdasdb.<domain name>) >> Here use your Standby DB’s unique name.
(UR = A)
)
)

sqlnet.ora

[oracle@bapas1 admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /orahome/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)




Standby Files:

listener.ora
[oracle@bapas3 admin]$ cat listener.ora
# listener.ora Network Configuration File: /orahome/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_ASDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = asdb)
(ORACLE_HOME =/orahome/app/oracle/product/12.2.0/dbhome_1 )
(SID_NAME = asdb)
)
)


ASDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.domain>)(PORT = 1521))
CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = asdb)
)
)


SID_LIST_ASDBDR =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = asdb)
(ORACLE_HOME = /orahome/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = asdb)
)
)

ASDBDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.domain>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = asdb)
)
)

tnsnames.ora
[oracle@bapas3 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /orahome/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

#LISTENER_ASDB =

# (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.domain>)(PORT = 1521))

#STDASDB =(ADDRESS = (PROTOCOL = TCP)(HOST =<hostname.domain> )(PORT = 1521))



ASDBDR =

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.domain>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdasdb.<domain name>) >> Here use your Standby DB’s unique name.
)
)

ASDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname.domain>)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = asdb.<domain name>)
)
)


sqlnet.ora

[oracle@bapas3 admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /orahome/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


Check tnsping and sql connectivity from both the ends

Primary Side:

 

Standby Side:


















Start MRP process

Use the below command to start the Media Recovery Process.

@ Standby Site:
SQL> alter database recover managed standby database disconnect;









Important Queries

Commands to check the Sync between Primary and Standby Databases
1) Select max(sequence#) from v$archived_log; - To check the latest sequence of the archived log

2) Select sequence#, applied from v$archived_log; - To check the sequence# and status of archived log at standby database

3) alter database recover managed standby database disconnect from session - Start the managed recovery operation

4) Alter database recover managed standby database cancel; - Cancel managed recovery operations

5) SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; - Find out if the standby database is performing managed recovery. If the MRP0 or MRP process exists, then the standby database is performing managed recovery

6) ALTER SYSTEM SWITCH LOGFILE; - Switch the log to send the redo data to the standby database

7) select (select max(SEQUENCE#) from v$archived_log where THREAD#='1') as BAPS1, (select max(SEQUENCE#) from v$archived_log where THREAD#='2') as BAPS2 from dual; - To check maximum sequence of archived log from both the nodes of a RAC database

8) select 'Last applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log where applied='YES') union select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log); - To check the data and time of recent logs received and applied time

9) select (select max(SEQUENCE#) from v$archived_log where THREAD#='1' and APPLIED='YES') as BAPS1_Applied, (select max(SEQUENCE#) from v$archived_log where THREAD#='1' and APPLIED='NO') as BAPS1_NOT_Applied, (select max(SEQUENCE#) from v$archived_log where THREAD#='2' and APPLIED='YES') as BAPS2_Applied, (select max(SEQUENCE#) from v$archived_log where THREAD#='2' and APPLIED='NO') as BAPS2_Not_Applied from dual; - To check the applied and not-applied log sequence numbers from both the instances of a RAC database

10) select thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1; - To check the Last sequence received on the Standby Database

11) select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1; - To check the Last sequence applied on the Standby Database


                                     ****************************நன்றி****************************

1 comment:

IAS Cache initialization failed

 Today I faced an Issue in R12.2 instance. The solution I followed to overcome the issue is very simple, but they are more than one solution...