Citi New Oracle Standby / Dr creation manually

  

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ ORACLE DATABASE 11G CONFIGURE DATAGUARD PLUS BROKER AND FAST-START FAILOVER +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 

#CREATE TWO MACHINES, PRINCIPAL (prod-ora) AND STANDBY (stdb-ora)
#INSTALL SOFTWARE ORACLE DATABASE 11G ON BOTH MACHINES
#CREATE DATABASE WITH DBCA UTILITARY (IF AN ENVIRONMENT WHERE ORACLE DATABASE IS CONFIGURED AND RUNNING)
#CREATE LISTENER WITH NETCA UTILITARY (IF AN ENVIRONMENT WHERE ORACLE DATABASE IS CONFIGURED AND RUNNING)

#DATE GUARD SETUP START
##PRIMARY
#MAKE SURE THE BANK IS IN ARCHIVELOG MODE, IF YOU ARE NOT PERFORMING THE FOLLOWING STEPS

sqlplus / as sysdba
SELECT log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>

CREATE PFILE='/home/oracle/pfile_dg.ora' from spfile;
SHUTDOWN IMMEDIATE;

#CREATE DIRECTORY FOR ARCHIVES
mkdir -p /u01/app/oracle/oradata/orcl1/archive/

#EDIT FILE /home/oracle/pfile_dg.ora ADDING THE FOLLOWING LINES
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.db_file_name_convert='orcl1_stdb','orcl1'
*.log_file_name_convert='orcl1_stdb','orcl1'
*.fal_server='ORCL1_STDB'
*.log_archive_config='DG_CONFIG=(orcl1,orcl1_stdb)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/orcl1/archive/'
*.log_archive_dest_2='SERVICE=orcl1_stdb NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl1_stdb'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.db_recovery_file_dest_size=5G scope=both;
*.db_recovery_file_dest='/u01/app/oracle/oradata/orcl1/FRA';

STARTUP MOUNT PFILE '/home/oracle/pfile_dg.ora';
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;
CREATE SPFILE FROM PFILE='/home/oracle/pfile_dg.ora';
SHUTDOWN IMMEDIATE;
STARTUP;

#CONFIGURE TNSNAMES.ORA PRIMARY AND STANDBY
orcl1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.113)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl1)
    )
  )

orcl1_prod_clone =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.113)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl1)
    )
  )

orcl1_prod_dg =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.113)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl1_DGMGRL)
    )
  )

orcl1_stdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.114)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl1_stdb)
    )
  )

orcl1_stdb_clone =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.114)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl1)
    )
  )

orcl1_stdb_dg =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.114)(PORT = 1521))
    )
    (CONNECT_DATA =
       (SERVICE_NAME = orcl1_stdb_DGMGRL)
    )
  ) 
  
#CREATE STANDBY REDO LOGS ON PRIMARY
sqlplus / as sysdba
select max(GROUP#) from v$logfile;

  
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/orcl1/standby_redo01.log') THREAD 1 GROUP 4 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/orcl1/standby_redo02.log') THREAD 1 GROUP 5 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/orcl1/standby_redo03.log') THREAD 1 GROUP 6 SIZE 50M;

#CREATE BACKUP OF DATABASE (SCRIPTS DOCUMENTED IN ORACLE PHYSICAL BACKUP 11G FILE)
crontab -l
#BACKUP DATABASE ORACLE 11G

30 00 * * 6             su - oracle -c /u01/backup/scripts/bkp_level0.sh
30 00 * * 0,1,2,3,4,5   su - oracle -c /u01/backup/scripts/bkp_level2.sh
00 02,04,06,08,10,12,14,16,18,20,22 * * * su - oracle -c /u01/backup/scripts/bkp_archive.sh

#RUN BACKUP
nohup su - oracle -c /u01/backup/scripts/bkp_level0.sh & 

##STANDBY (DUPLICATE)

#CREATE DIRECTORY STRUCTURE
mkdir -p /u01/app/oracle/oradata/orcl1
mkdir -p /u01/app/oracle/oradata/orcl1/archive/
mkdir -p /u01/app/oracle/admin/orcl1/adump



#COPY PRIMARY PFILE TO STANDBY
scp oracle@prod-ora:/home/oracle/pfile_dg.ora /home/oracle



#CONFIGURE LISTENER.ORA ON PRIMARY AND STANDBY 
vi /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
      (SID_NAME = orcl1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl1_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
      (SID_NAME = orcl1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl1_stdb_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
      (SID_NAME = orcl1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
#ON PRIMARY 
#ATTENTION ONLY ENABLE ONE!!
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.113)(PORT = 1521))
#ON STANDBY
#ATTENTION ONLY ENABLE ONE!!
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.114)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

#STARTUP NOMOUNT STANDBY INSTANCE 
sqlplus / as sysdba
STARTUP NOMOUNT PFILE='/home/oracle/pfile_dg.ora';
exit

#START LISTENER
lsnrctl start



#DUPLICATE DATABASE RMAN
rman target sys/<_password_>@orcl1_prod_clone auxiliary sys/<_password_>@orcl1_stdb_clone

RMAN>
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name='orcl1_stdb' COMMENT 'Is standby' SET LOG_ARCHIVE_DEST_2='SERVICE=orcl1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl1' SET FAL_SERVER='orcl1' COMMENT 'Is primary' NOFILENAMECHECK;

RMAN> EXIT




#START APPLY PROCESS
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

#IF YOU NEED TO CANCEL THE SYNCHRONY PROCESS RUN COMMAND
##ON STANDBY
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
##ON PRIMARY
sqlplus / as sysdba
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

#IF YOU NEED TO RESUME THE ARCHIVES SYNCHRONY PROCESS
##ON PRIMARY
sqlplus / as sysdba
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
##ON STANDBY
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;




#TEST LOG TRANSPORT
#ON PRIMARY
sqlplus / as sysdba
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;

9 rows selected.

ALTER SYSTEM SWITCH LOGFILE;
 System altered.
 
#ON STANDNBY
sqlplus / as sysdba
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;





#PROTECTION MODE
    Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
    Maximum Performance:  Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
    Maximum Protection:   Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.
By default, for a newly created standby database, the primary database is in maximum performance mode.

ON PRIMARY OR STANDBY RUN
sqlplus / a sysdba
SELECT protection_mode FROM v$database;

  
-- Maximum Availability.
sqlplus / as sysdba
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl1_stdb AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL1_STDB';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- Maximum Performance.
sqlplus / as sysdba
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl1_stdb NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL1_STDB';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Protection.
sqlplus / as sysdba
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl1_stdb AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL1_STDB';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;  





#DATABASE SWITCHOVER
#PRIMARY SWITCHOVER FOR STANDBY
#CHECK GAP BETWEEN PRODUCTION AND STANDBY
##PRIMARY
sqlplus / as sysdba
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

##STANDBY
sqlplus / as sysdba
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

 
##PRIMARY
sqlplus / as sysdba
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

##STANDBY
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE;
STARTUP;




#STANDBY SWITCHBACK FOR PRIMARY 
#CHECK GAP BETWEEN PRODUCTION AND STANDBY
##PRIMARY
sqlplus / as sysdba
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

##STANDBY
sqlplus / as sysdba
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

##PRIMARY
sqlplus / as sysdba
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

##STANDBY
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE;
STARTUP;




#FAILOVER
If the primary database is not available the standby database can be activated as a primary database using the following statements.

##ON STANDBY
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;





#READ-ONLY STANDBY DATABASE AND ACTIVE DATA GUARD

Once a standby database is configured, it can be opened in read-only mode to allow query access. 
When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.

##ON STANDBY
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

To resume managed recovery, do the following.
##ON STANDBY
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

In 11g, Oracle introduced the Active Data Guard feature.This allows the standby database to be open in read-only mode, but still apply redo information.
##ON STANDBY
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
select open_mode, database_role from v$database;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl1/temp01.dbf' SIZE 1G AUTOEXTEND ON NEXT 50M MAXSIZE 31G;




#SNAPSHOT STANDBY DATABASE
Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. 
When switched back into standby mode, all changes made whilst in read-write mode are lost. 
This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.

##ON STANDBY
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SELECT flashback_on FROM v$database;
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;
SELECT flashback_on FROM v$database;
SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
 



#CONVERT IT BACK TO THE PHYSICAL STANDBY
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;
 




#ENABLE BROKER DATA GUARD

##ON PRIMARY
sqlplus / as sysdba
ALTER SYSTEM SET dg_broker_start=true;
##ON STANDBY 
sqlplus / as sysdba
ALTER SYSTEM SET dg_broker_start=true;

##ON PRIMARY
DGMGRL> CREATE CONFIGURATION DGORCL1 AS PRIMARY DATABASE IS orcl1 CONNECT IDENTIFIER IS orcl1;
 
DGMGRL> ADD DATABASE orcl1_stdb AS CONNECT IDENTIFIER IS orcl1_stdb MAINTAINED AS PHYSICAL;
DGMGRL> ENABLE CONFIGURATION;
 
DGMGRL> SHOW CONFIGURATION;
 
DGMGRL> SHOW DATABASE orcl1;
 
DGMGRL> SHOW DATABASE orcl1_stdb;
 

DGMGRL> SWITCHOVER TO orcl1_stdb;
DGMGRL> SWITCHOVER TO orcl1;
 



#DISABLE/RESET CONFIGURATION DATA GUARD BROKER

#ON PRIMARY AND STANDBY
sqlplus / as sysdba
ALTER SYSTEM SET DG_BROKER_START=FALSE;
show parameter dg_broker_config_file;

/dbs/dr2orcl1.dat
#ON PRIMARY
!rm -vf /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr1orcl1.dat
!rm -vf /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr2orcl1.dat
#ON STANDBY
!rm -vf /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr1orcl1_stdb.dat
!rm -vf /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr2orcl1_stdb.dat




#TROUBLESHOUTS DATA GUARD

show database verbose orcl1;
show database verbose orcl1_stdb;
sqlplus sys/<_password_>@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.113)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl1_stdb_DGMGRL)(INSTANCE_NAME=orcl1)(SERVER=DEDICATED)))' as sysdba
sqlplus sys/<_password_>@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.114)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl1_stdb_DGMGRL)(INSTANCE_NAME=orcl1)(SERVER=DEDICATED)))' as sysdba
sqlplus sys/<_password_>@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.113)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl1_DGMGRL)(INSTANCE_NAME=orcl1)(SERVER=DEDICATED)))' as sysdba
sqlplus sys/<_password_>@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.114)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl1_DGMGRL)(INSTANCE_NAME=orcl1)(SERVER=DEDICATED)))' as sysdba

 

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more