Dataguard configuration


DATAGUARD:


At every primary server each archived redo-log files are uniquely identified by its log sequence number and thread number and which is transferred by LGWR or ARCH background processes from primary to standby server.

On standby site RFS (Remote File Server process) who receives the archived log and match sequence number with the sequence number of the previously received archived redo log file. If the sequence number of the current received archived redo log file is greater than the sequence number of the last received archived redo log file plus one (means missing sequence in archived log), a request is sent to the arc-hiver process of the primary database that is providing a list of missing archived redo log files and oracle generates ORA-16146 (standby destination control file enqueue unavailable).


MRP (Managed Recovery Process) is waiting for a log that has already been shipped but does not appear in v$archived_logs and there are ORA-16146 errors reported in the alert log.

The archiver of the primary database then retransmits these archived redo log files, thereby resolving the gap sequence. When there is no gap found, it applies the required (missed sequence archive log) log at standby site and all things goes well.



Congiguring Dataguard:

overview:
1.clone database to the standby side.
2. ship the changes from primary to standby side
a. we will ship the online redologs in the case of maximum protection and maximum availabilty modes.
b. we will ship archived logs in case of maximum performance mode.
3.using oracle notification services we ship the logs from primary to the standby side.



Steps to configure dataguard:

Primary: TEST
Standby: CLONE


1.place the primary database in archive log mode.
   SQL> alter database archivelog;

2.keep database in force logging mode.
  SQL> ALTER DATABASE FORCE LOGGING; 
  SQL> select force_logging from v$database;


3.enable flashback for database.
  SQL> select flashback_on from v$database;
  SQL> alter database flashback on;


4.configure password file for primary database, SYS password must be identical on every system for successful redo data transmission.
 SQL> select * from v$pwfile_users;

5. create a directory for archive log destination, by default it will be FRA. we can use the default FRA location. Here we are creating a new destination.

 mkdir -p /u01/stage/PRIMARY_LG

6.set data protection mode, default will be maximum performance mode.
  SQL> alter database set standby database to maximum performance;
  SQL>SELECT PROTECTION_MODE FROM V$DATABASE;

7. create standby redo log groups
SQL> select member from v$logfile;

 SQL> select GROUP#, THREAD#, MEMBERS, BYTES FROM V$LOG;

 SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/TEST/onlinelog/redo04.log' size 50m;

 SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/TEST/onlinelog/redo05.log' size 50m;

 SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/TEST/onlinelog/redo06.log' size 50m;

8. create pfile from spfile
SQL> create pfile from spfile;

9.Edit the pfile as follow

####Add the following paramters in Blue color, assuming all other parameters are already present####

test.__large_pool_size=33554432
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=402653184
test.__sga_target=754974720
test.__shared_io_pool_size=33554432
test.__shared_pool_size=184549376
test.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_file_record_keep_time=14
*.control_files='/u01/app/oracle/oradata/TEST/controlfile/o1_mf_doffzlxx_.ctl','/u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_doffzm2s_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/CLONE/','/u01/app/oracle/oradata/TEST/'
*.db_name='test'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=9560m
*.db_unique_name='test'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fal_client='TEST'
*.fal_server='CLONE'
*.log_archive_config='dg_config=(test,clone)'
*.log_archive_dest_1='location=/u01/stage/PRIMARY_LG valid_for=(all_logfiles,all_roles) db_unique_name=test'
*.log_archive_dest_2='SERVICE=CLONE NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=CLONE'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='/u01/app/oracle/oradata/CLONE/','/u01/app/oracle/oradata/TEST/'
*.memory_target=1100m
*.open_cursors=500
*.processes=400
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='service1,service2'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
~
~



10. configure listerner.ora and tnsnames.ora on both primary(192.168.56.101) and standby (192.168.56.103) side

primary side:

vi listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME =test)
     (ORACLE_HOME =/u01/app/oracle/product/12.1.0/dbhome_1/)
     (SID_NAME=test)
    )


vi tnsnames.ora


TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )
CLONE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = clone)
    )
  )

target side:

vi listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME =clone)
     (ORACLE_HOME =/u01/app/oracle/product/12.1.0/dbhome_1/)
     (SID_NAME=clone)
    )
   )

vi tnsnames.ora

CLONE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = clone)
    )
  )

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )


NOTE: If you use the below duplicate command specified in step 17 for creating standby ignore step 11 because the duplicate command in step 16 will automatically restores the database without any backup.
             
         Perform step 11, only in the case if you are using  Restore database and Recover database  commands of RMAN on standby(CLONE) side to clone the database(i.e.,  By copying the backup files generated in primary side(test) to the standby side(clone) and do restore and recover)


11. Take RMAN backup on primart side including controlfile

[oracle@localhost admin]$ rman target /

RMAN> backup current controlfile for standby;

 RMAN> backup database plus archivelog;

12. copy pfile and passwordfile from primary to standby

[oracle@localhost dbs]$scp inittest.ora     oracle@192.168.56.103:'/u01/app/oracle/product/12.1.0/dbhome_1/dbs'


[oracle@localhost dbs]$ scp orapwtest       oracle@192.168.56.103:'/u01/app/oracle/product/12.1.0/dbhome_1/dbs'


13. make the following changes on stanby side pfile

####Edit the following changes in RED color####

test.__data_transfer_cache_size=0
test.__db_cache_size=436207616
test.__java_pool_size=16777216
test.__large_pool_size=33554432
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=436207616
test.__sga_target=721420288
test.__shared_io_pool_size=33554432
test.__shared_pool_size=184549376
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/clone/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_file_record_keep_time=14
*.control_files='/u01/app/oracle/oradata/CLONE/controlfile/o1_mf_doffzlxx_.ctl','/u01/app/oracle/fast_recovery_area/CLONE/controlfile/o1_mf_doffzm2s_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/TEST/','/u01/app/oracle/oradata/CLONE/'
*.db_name='test'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=9560m
*.db_unique_name='clone'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloneXDB)'
*.fal_client='CLONE'
*.fal_server='TEST'
*.log_archive_config='dg_config=(clone,test)'
*.log_archive_dest_1='location=/u01/stage/PRIMARY_LG valid_for=(all_logfiles,all_roles) db_unique_name=clone'
*.log_archive_dest_2='SERVICE=TEST NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='/u01/app/oracle/oradata/TEST/','/u01/app/oracle/oradata/CLONE/'
*.memory_target=1100m
*.open_cursors=500
*.processes=400
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
~
~
~
~

14. create the following directories on the standby side

$ mkdir -p /u01/app/oracle/admin/clone/adump'
$ mkdir -p /u01/app/oracle/oradata/CLONE
$ mkdir -p /u01/app/oracle/fast_recovery_area/CLONE/
$ mkdir -p /u01/stage/PRIMARY_LG

15. startup database to nomount stage using pfile

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initclone.ora';
SQL> create spfile from pfile;

#restart the database using newly created spfile#

SQL> shut immediate;
SQL> startup nomount;

 Note: Ignore step 11 and step 16 if are using step 17(duplicate database command)
16.  If you use step 11(control file and full db backup plus archivelogs to clone the database, Restore and Recover the database.


SCP the backup from source database to standby database:

On Primary server:

[oracle@localhost dbs]$ cd /u01/app/oracle/fast_recovery_area/TEST/backupset/28_12_2017/

 {oracle@localhost dbs]$scp *   oracle@192.168.56.103:'/u01/app/oracle/fast_reecovery_area/CLONE/backupset/28_12_2017/'

On standby database:

[oracle@localhost dbs]$ rman target /

 RMAN> restore standby controlfile from '/u01/app/oracle/fast_recovery_area/CLONE/backupset/28_12_2017/o1_cnnn_control01.bkp';

Mount the database:

RMAN>sql 'alter database mount';

 RMAN> restore database;
RMAN> recover database;
NOTE: If you use the below duplicate command (Ignore Step 11) for creating standby, because the duplicate command in step 17 will automatically restores  the database without any backup.

17. duplicate the database by using rman backup

$rman auxiliary sys/oracle@CLONE target sys/oracle@TEST

 RMAN> duplicate target database for standby from active database nofilenamecheck;

18. start redo apply on standby
   #to start redo apply#
SQL> alter database recover managed standby database disconnect from session;
   #to stop redo apply#
SQL> alter database recover managed standby database cancel;


18. TO verify the whelther logs are properly shiped and being applied on to the database
#primary server#
SQL> select sequence#, first_time, next_time from v$archived_log;
#standby server#
SQL> select sequence#, first_time, next_time from v$archived_log;
#primary server#
SQL>alter system switch logfile;
#standby server#
SQL>select sequence#, applied from v$archived_log order by sequence#;



Datagaurd Manual Switchover:
### primary Server ###
SQL> alter database commit to switchover to standby with session shutdown;
SQL> shutdown immediate;
SQL> startup nomount;
SQL>alter database mount standby database;
SQL>alter system set log_archive_dest_state_2=defer;
SQL>recover managed standby database disconnect;

### standby server###
SQL> alter database commit to switchover to primary with session shutdown;
SQL>alter system set log_archive_dest_state_2=ENABLE;
SQL>shut immediate;
SQL>startup;





###commands to stop and start MRP###
https://oracleinsightspk.blogspot.com/2017/10/dataguard-starting-and-stoping-mrp.html




Comments

Post a Comment

Popular posts from this blog

Oracle Database Client(12.1.0.2.0) Installation for Microsoft Windows(x64)

Image
Step1) Download Oracle Client 12C from below link http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html Oracle Database Client (12.1.0.2.0) for Microsoft Windows (x64) winx64_12102_client.zip  (64-bit) (925,039,944 bytes) Oracle Database Client (12.1.0.2.0) for Microsoft Windows (32-bit) winnt_12102_client32.zip  (32-bit) (902,922,170 bytes)  chose the one based on your laptop Step2) Extract the downloaded file Oracle 12c 64 bit client has now been extracted. Double click ‘setup.exe’. This may test your patience, let it work! (Sometimes a minute or less can seem like an eternity). Don’t touch anything! Administrator: Enables applications to connect to an Oracle database on the local system or on a remote system. It provides tools that let you administer
Read more

configuring goldengate

configuring goldengate Pre req:  Install Goldengate software on both the servers golden gate home: /u02/app/goldengate Update bash_profile on both Source and Target side [oracle@localhost ~]$ vi .bash_profile if [ -f ~/.bashrc ]; then         . ~/.bashrc fi # User specific environment and startup programs export ORACLE_SID=test export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 export GRID_HOME=/u01/app/oracle/product/12.1.0/grid export LD_LIBRARY_PATH=$ORACLE_HOME/lib export PATH=$ORACLE_HOME/bin:$PATH: export PATH=$GRID_HOME/bin:$PATH: export PATH=$ORACLE_HOME/OPatch:$PATH: PATH=$PATH:$HOME/bin export PATH export GGS_HOME=/u02/app/goldengate alias ggn='cd ${GGS_HOME}' alias ggsci='cd ${GGS_HOME}/ggsci' export LD_LIBRARY_PATH=$GGS_HOME:$ORACLE_HOME/lib:/lib:/usr/lib ".bash_profile" 25L, 684C                                     24,1          75% Source db_name: TEST target db_name: CLONE 1. check for
Read more

sample extract,pump and replicate(goldengate)

NAMING CONVENTION should be followed in any environment we are Implementing GoldenGate# E starts with Extract  P starts with Pump R starts with Replicat  Here we are taking extract: EWORK pump: PWORK replicat:RWORK ###we are replicating schema pavan(including all tables)### goldengate home:'/u02/app/goldengate' source side:  test GGSCI (localhost.localdomain) 2> start mgr Manager started. GGSCI (localhost.localdomain) 1> dblogin userid ogg_user@test password oracle; Successfully logged into database. GGSCI (localhost.localdomain as ogg_user@test) 2> add schematrandata pavan allcols 2017-10-09 14:22:20  INFO    OGG-01788  SCHEMATRANDATA has been added on schema pavan. 2017-10-09 14:22:20  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema pavan. 2017-10-09 14:22:21  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema pavan. Configuring Extract: GGSCI (local
Read more