Dataguard Configuration steps

-

 Dataguard Configuration step by steps

Primary Server Configurations:

Primary Sever Name : TestDB

<Step – 1 >

Enable Archive log:

<Step – 1 >

Primary database is in No Archive Mode, Enable it:

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     10

Next log sequence to archive   12

Current log sequence           12

<Step – 2 >

Enable force logging with the help of following SQL command:

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

<Step – 3 >

Verify initialization parameters db_name and db_unique_name on primary, In my case those are set to: testdb

SQL> show parameter db_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      testdb

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      testdb

<Step – 4 >

Issue following command to set LOG_ARCHIVE_CONFIG parameter for data guard config.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb,testdbdr)';

System altered.

Ensure your above changes as below:

SQL> show parameter LOG_ARCHIVE_CONFIG

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string      DG_CONFIG=(testdb,testdbdr)

<Step – 5 >

Issue following to set LOG_ARCHIVE_DEST_2, In my case i have used flash recovery area for remote archive log destination.

Note: Service and DB_UNIQUE_NAME reference the remote standby location.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=testdbdr NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdbdr';

System altered.

Ensure your above changes as below:

SQL> show parameter LOG_ARCHIVE_DEST_2

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2                   string      SERVICE=testdbdr NOAFFIRM ASYN

                                                 C VALID_FOR=(ONLINE_LOGFILES,P

                                                 RIMARY_ROLE) DB_UNIQUE_NAME=na

                                                 pwardr

Enable LOG_ARCHIVE_DEST_2:

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_state_2             string      ENABLE

<Step – 6 > 

Set log_archive_format parameter as below:

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

System altered.

SQL> show parameter log_archive_format

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_format                   string      %t_%s_%r.arc

Set log_archive_max_processes parameter to 30:

SQL> alter system set log_archive_max_processes=30;

System altered.

SQL> show parameter log_archive_max_processes

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_max_processes            integer     30

Set remote_login_passwordfile parameter to exclusive:

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

remote_login_passwordfile            string      EXCLUSIVE

<Step – 7 > 

Set fal_server and fal_client parameter for primary database:

SQL> alter system set fal_server=testdbdr;

System altered.

SQL> show parameter fal_server

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fal_server                           string      testdbdr

SQL> show parameter fal_server

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fal_server                           string      testdbdr

SQL> alter system set fal_client='testdb';

System altered.

SQL> show parameter fal_client

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fal_client                           string      testdb

Set standby_file_management parameter to auto:

SQL> alter system set standby_file_management=auto;

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

standby_file_management              string      AUTO

<Step – 8 > 

Configure listener file and copy it to DR @ location: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin

Listener file:

testdb =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = testdb)(PORT = 1528))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1528))

    )

  )

SID_LIST_testdb =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = testdb)

      (ORACLE_HOME = /oracle/app/oracle/product/19.0.0/dbhome_1)

      (SID_NAME = testdb)

   )

)

INBOUND_CONNECT_TIMEOUT_testdb=180

VALID_NODE_CHECKING_REGISTRATION_testdb = OFF

DYNAMIC_REGISTRATION_testdb = off

tnsnames.ora:

testdb =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =testdb)(PORT = 1528))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = testdb)

    )

  )

TESTDBDR =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =TESTDBDR)(PORT =1528))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME =testdb_dr)

    )

  )

oracledb%  tnsping TESTDBDR

TNS Ping Utility for Solaris: Version 19.0.0.0.0 - Production on 23-JUL-2019 19:54:32

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

/oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =TESTDBDR)(PORT =1648)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =testdb)))

OK (40 msec)

oracledb% tnsping testdb

TNS Ping Utility for Solaris: Version 19.0.0.0.0 - Production on 23-JUL-2019 19:54:38

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

/oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =oracledb)(PORT = 1528)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = testdb)))

OK (0 msec)

<Step – 10 > 

Backup primary database via RMAN backup utility:

oracledb% rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jul 24 01:25:49 2019

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: testdb (DBID=3509764128)

 RMAN> backup database plus archivelog;

 Note: Backup location: Flash Recovery Area.

<Step – 11> 

Create Standby control file.

SQL> alter database create standby controlfile as '/u01/bkup/stndbyctrl.ctl';

Create pfile from spfile:

SQL> create pfile='/oracle/app/oracle/product/19.0.0.0/dbs/inittestdb.ora' from spfile;

<Step – 13>

After creating parameter file as above, edit following changes in newly created pfile:

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      testdb


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

fal_server                           string      testdbDR

SQL> show parameter log_archive_dest_2


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2                   string      SERVICE=testdbdr DB_UNIQUE_NAM

                                                 E=testdbdr NOAFFIRM ASYNC VALI

                                                 D_FOR=(ONLINE_LOGFILES,PRIMARY

                                                 _ROLE)

<Step – 14>

Copy parameter file to DR @ location: /oracle/app/oracle/product/19.0.0/dbhome_1/dbs/

Copy the backup file DC to DR location including Controlfiles

Copy password file to DR @ location: /oracle/app/oracle/product/19.0.0/dbhome_1/dbs/

Copy Listener file to DR @ location: /oracle/app/oracle/product/19.0.0/dbhome_1/network/admin

Edit DR database listener file with host name. ( i.e. (HOST = dr))

And finally copy RMAN backup to DR @ flash recovery area.

Standby/DR Server Configurations:

<Step – 15>

Startup standby database in mount state:

Set following environment variable as oracle user OR edit those in /home/oracle/.bash_profile in order to set it for every time while oracle user logged in:

setenv ORACLE_SID testdb

setenv ORACLE_HOME oracle/app/oracle/product/19.0.0/dbhome_1

setenv PATH usr/sbin:/oracle/app/oracle/product/19.0.0/dbhome_1/bin:/oracle/app/oracle/product/19.0.0/dbhome_1/lib:/oracle/app/oracle/product/19.0.0/dbhome_1/OPatch/:/usr/bin:/bin:/usr/sbin:/sbin

 <Step – 16>

Create spfile from pfile:

SQL> create spfile from pfile='/oracle/app/oracle/product/19.0.0/dbhome_1/dbs/inittestdb.ora';

[oracle@DR ~]$ mkdir -p /oracle/app/oracle/admin/testdb/adump

<Step – 17>

Create following directories on DR server, in case those are not available:

/u01/app/oracle/admin/RTS/adump

/u01/app/oracle/flash_recovery_area

<Step – 18>

Start listener on standby:

[oracle@dr ~]$ lsnrctl start testdb

<Step – 19>

 SQL> sqlplus / as sysdba

  SQL> startup nomount

 ORACLE instance started.

 Total System Global Area 972898304 bytes

 Fixed Size 2219272 bytes

 Variable Size 566231800 bytes

 Database Buffers 398458880 bytes

 Redo Buffers 5988352 bytes

<Step – 19>

Restore the Standby control file

$ rman target /

RMAN> restore standby controlfile from '/backup/stdb_cntl.bkp';

RMAN> Alter database mount;

 Database mounted.

Restore and recover database @ DR with RMAN backup utility.

RMAN> list backup of database summary;

 List of Backups

 ===============

 Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

 ------- -- -- - ----------- --------------- ------- ------- ---------- ---

 6 B F A DISK 05-JUL-19 1 1 NO TAG20141005T065604

 RMAN> restore database;

 RMAN> recover database;

 <Step – 20>

Create standby redolog file to Primary and DR for the user of switch over, It should be match the configuration of the primary server.

Note: Create one additional standby redolog file on both.

SQL> sqlplus / as sysdba

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=spfile;

SQL>ALTER DATABASE ADD STANDBY LOGFILE('/oradbf1/oradata/testdbDR/onlinelog/stndby1.log') size 200M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE('/oradbf1/oradata/testdbDR/onlinelog/stndby2.log') size 200M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE('/oradbf1/oradata/testdbDR/onlinelog/stndby3.log') size 200M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE('/oradbf1/oradata/testdbDR/onlinelog/stndby4.log') size 200M; 

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile;

<Step – 21>

Start apply process @ DR.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE nodelay DISCONNECT FROM SESSION;

In case of you want to cancel apply process, issue following command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

<Step – 22>

After graceful completion of above apply process, verify archive logs on Primary as well as DR.

On Primary: 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /archive/testdb

Oldest online log sequence     41

Next log sequence to archive   44

Current log sequence           43

On Standby:

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /archive/testdb

Oldest online log sequence     41

Next log sequence to archive   44

Current log sequence           43


Comments

Post a Comment

Popular posts from this blog

RMAN-08137: WARNING: archived log not deleted

-
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process When we are taking archive log backup with delete then got an error. RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/pac/lci9t1/arch/redo_1_16470_842608348.arc thread=1 sequence=16470 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process archived log file name=/pac/lci9t1/arch/redo_1_16471_842608348.arc thread=1 sequence=16471 RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process SOLUATION: Cause: The archive logs seems that it is not yet applied in the standby database.                         Select sequence# , applied from v$archived_log; Action: Check the standby database and apply t...
Read more

steps to setup rman

-
Steps to setup RMAN This article is tested in oracle12C R1. How do we setup the RMAN in oracle? There are couple of ways, we can setup the RMAN. We can use control file to store backup catalog info or we can have separate database to store catalog info. Here I am using separate database to store backup catalog information. I am using Linux OS. Please remember, the directories and folder might change based on the operating system and environment. But the below steps are pretty much same for any environment. Here i am using ORCL as primary database and CATDB as catalog database.   Step1  Enabling Archive log. See the following link for the First step  Click Here Step2  Create the tablespace and user in catalog database to hold backup information. SQL> CONNECT sys/password@catdb AS SYSDBA Connected. SQL> CREATE TABLESPACE RMAN 2 DATAFILE '/app/oracat/oradata/catdb/rman01.dbf' SIZE 6208K REUSE 3 AUTOEXTEND ON NEXT 64K MAXSIZE 32767M ...
Read more

Enabling Archive log

-
Enabling Archive log in RMAN This article is tested in oracle12C R1. How do we setup the RMAN in oracle? There are couple of ways, we can setup the RMAN. We can use control file to store backup catalog info or we can have separate database to store catalog info. Here I am using separate database to store backup catalog information. I am using Linux OS. Please remember, the directories and folder might change based on the operating system and environment. But the below steps are pretty much same for any environment. Here i am using ORCL as primary database and CATDB as catalog database. Step1  Enable the archive log in ORCL database. Step 1.1  We need to build the pfile from spfile to add new entries. If you  already have recent pfile, the you do not need to do this step. Login as sys user and execute this to create pfile. create pfile='/u01/app/oraint/product/12.1.0/dbhome_1/dbs/pfile.ora' from spfile; Step 1.2  Once pfile is created, t...
Read more