Oracle Dataguard Setup

Data Guard Setup 

Oracle dataguard is the high availability and Disaster recovery concept provided by Oracle.

For configuring the dataguard, we need two servers located in different locations let’s say Singapore and Hong Kong. Dataguard configuration maintains transactionally consistent copies of the production databases in the standby database. So, for some reason due to Disaster or Planned outage if production database is not available then standby database can be switched to the production role.

Around 5 years earlier, I had configured the first dataguard setup and the below steps are drafted for my reference.

Steps for Configuring the Oracle Dataguard

1)    Primary database should be in Archive log mode.

2)    Take RMAN Backup on the primary Database.

a)    $ RMAN target /

RMAN> backup as compressed backupset device type disk format '/optware/oracle/tmp/chtigu_%U' database;

b)    RMAN> backup device type disk format '/optware/oracle/tmp/chtigu_standby.ctl' current controlfile for standby;

         (or) at DB level

 ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/optware/oracle/tmp/chtigu_standby.ctl';

3)    Take pfile from primary server and amend it to use at standby server

create pfile='/optware/oracle/tmp/initchtisu.ora' from spfile;

vi initchtisu.ora

chtisu.__db_cache_size=314572800

chtisu.__java_pool_size=4194304

chtisu.__large_pool_size=12582912

chtisu.__oracle_base='/optware/oracle'#ORACLE_BASE set from environment

chtisu.__pga_aggregate_target=364904448

chtisu.__sga_target=683671552

chtisu.__shared_io_pool_size=0

chtisu.__shared_pool_size=339738624

chtisu.__streams_pool_size=0

*.audit_file_dest='/optware/oracle/admin/chtisu/audit'

*.audit_sys_operations=true

*.audit_trail='db'

*.compatible='11.2.0.3'

*.control_file_record_keep_time=100

*.control_files='/uchtigu/uoradata/chtigu/chtisu_control01.ctl'

*.db_block_size=8192

*.db_file_multiblock_read_count=16

*.db_name='chtigu'

*.db_unique_name='chtisu'

*.fal_client='chtisu'

*.fal_server='chtigu'

*.job_queue_processes=10

*.local_listener=listener_chtisu

*.log_archive_config='dg_config=(chtigu,chtisu)'

*.log_archive_dest_1='location=/utiguarch/uoradata/chtigu_arch valid_for=(all_logfiles,all_roles) db_unique_name=chtisu'

*.log_archive_dest_2='service=chtigu LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=chtigu'

*.log_archive_dest_state_2='DEFER'

*.log_archive_format='chtigu_%s_%t_%r.arc'

*.log_archive_max_processes=10

*.memory_target=1000M

*.nls_date_format='DD/MM/RRRR'

*.open_cursors=5000

*.os_authent_prefix='EXT$'

*.processes=500

*.recyclebin='off'

*.remote_login_passwordfile='EXCLUSIVE'

*.resource_limit=true

*.sql92_security=TRUE

*.standby_file_management='AUTO'

*.tape_asynch_io=false

*.undo_retention=7200

*.undo_tablespace='undotbs'

4)    If the DB files FS differs primary to standby, make sure to add below parameters

db_file_name_convert=’/DB FS location_primary’ , ‘/DB FS location_standby’

log_file_name_convert=’/ DB log FS location_primary’ , ‘/DB log FS location_standby’

5)    Create password file, set remote_login_passwordfile to Exclusive and enable force_logging

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwchtigu password=sys123 force=y entries=5 ignorecase=Y

$ sqlplus / as sysdba

SQL> alter system set remote_login_passwordfile='EXCLUSIVE' SCOPE=SPFILE;

SQL> Alter system set force_logging=enable scope=spfile;

restart DB

SQL> select * from v$pwfile_users;

USERNAME          SYSDB   SYSOP   SYSAS

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

SYS                          TRUE    TRUE     FALSE

6)    Set parameters in primary server for DG configuration

a)    alter system set log_archive_config='dg_config=(chtigu,chtisu)' scope=both ;

b)    alter system set log_archive_dest_1='location=/upacregarch/uoradata/cnpacu_arch valid_for=(all_logfiles,all_roles) db_unique_name=chtigu' ;

c)    alter system set log_archive_dest_2='service=chtisu LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=chtisu' scope=both;

d)    alter system set fal_server='chtisu' ;

e)    alter system set fal_client=’chtigu';

f)     alter system set standby_file_management='AUTO' scope=both;

g)    alter system set log_archive_max_processes=10 scope=both;

h)   alter system set log_archive_dest_state_2='defer';

7)    Create standby logfiles at primary side

alter database add standby logfile  thread 1 group 4 '/upacreg1/uoradata/chtigu/ chtigu _standby_log01.dbf' size 104857600;

alter database add standby logfile  thread 1 group 5 '/upacreg1/uoradata/chtigu/ chtigu _standby_log02.dbf' size 104857600;

alter database add standby logfile  thread 1 group 6 '/upacreg1/uoradata/chtigu/ chtigu _standby_log03.dbf' size 104857600;

alter database add standby logfile  thread 1 group 7 '/upacreg1/uoradata/chtigu/ chtigu _standby_log04.dbf' size 104857600;

alter database add standby logfile  thread 1 group 8 '/upacreg1/uoradata/chtigu/ chtigu _standby_log05.dbf' size 104857600;

alter database add standby logfile  thread 1 group 9 '/upacreg1/uoradata/chtigu/ chtigu _standby_log06.dbf' size 104857600;

8)    Copy password file, amended pfile, RMAN DB backup files, standby controlfile and tnsnames.ora file to Standby server

cp -ip orapwchtigu /optware/oracle/tmp/orapwchtisu

scp

9)    Login with sys user on primary and standby to cross-check connectivity.

Primary -- $ sqlplus sys@chtigu as sysdba

Standby -- $ sqlplus sys@chtisu as sysdba  ( this step we can check only when DB is mount)

10) On standby server

a)    Set DG sid

b)    Create Spfile from pfile

c)    $ rman target /

RMAN> startup nomount

RMAN> restore standby controlfile from '/optware/oracle/tmp/chtisu_standby.ctl';

RMAN> alter database mount;

RMAN> configure default device type to disk;

RMAN> restore database;

11) If the restore is from RMAN, connect to the standby database and drop standby logfiles.  If the restore is from file system cold backup it is not needed.

sqlplus /as sysdba

alter database drop standby logfile group 4;

alter database drop standby logfile group 5;

alter database drop standby logfile group 6;

alter database drop standby logfile group 7;

alter database drop standby logfile group 8;

alter database drop standby logfile group 9;

alter database add standby logfile  thread 1 group 4 '/upacreg1/uoradata/chtigu/ chtigu _standby_log01.dbf' size 104857600;

alter database add standby logfile  thread 1 group 5 '/upacreg1/uoradata/chtigu/ chtigu _standby_log02.dbf' size 104857600;

alter database add standby logfile  thread 1 group 6 '/upacreg1/uoradata/chtigu/ chtigu _standby_log03.dbf' size 104857600;

alter database add standby logfile  thread 1 group 7 '/upacreg1/uoradata/chtigu/ chtigu _standby_log04.dbf' size 104857600;

alter database add standby logfile  thread 1 group 8 '/upacreg1/uoradata/chtigu/ chtigu _standby_log05.dbf' size 104857600;

alter database add standby logfile  thread 1 group 9 '/upacreg1/uoradata/chtigu/ chtigu _standby_log06.dbf' size 104857600;

12) Put the standby database in managed recovery mode

SQL> recover managed standby database using current logfile disconnect;

13) On Primary database enable archive dest state2

SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';

SQL> alter system switch logfile;

14) Check standby side whether log shipping is happening or not

SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;

15) Incase of any error crosscheck from primary side dest status

SQL> select DEST_ID,ERROR,STATUS from v$archive_dest_status where dest_id in (1,2);

16) If  standby DB has to be on RAC, then register DB through SRVCTL

$ srvctl add database -d chtisu -o /optware/oracle/11.2/db -s mount -r physical_standby

Partitioning in oracle database

What is partitioning in theory:

Oracle partitioning is a licensing option of oracle database only available with Oracle EE.

Partitioning allows a table, index, IOT to be subdivided into small pieces. Each piece of these objects is called a partition. Each partition has its own name. From DBA Perspective, a partitioned objects has several partitions and can be managed individually or collectively. However, From Application perspective partitioning of a DB object is transparent and partitioned object is similar to non-partition object and not necessary to modify the application queries when accessing to the partitioned object.

From DBA point of view, we can achieve several performance benefits with partitioning. Here I mentioned few.

•   Let’s say a company is running for over 10 years and their application is having 10 years of employee data consists of the employee joining date, salary and other details and this table is partitioned by a year and has 10 partitions in the table. The company HR is trying to access the data of the employees for a particular year then this query would access one partition instead of 10 partitions. Like this we can achieve 10 times faster data retrieval to the customer.
•  The I/O contention will be reduced. Partitioned tables will be physically stored in the datafiles of the tablespace in different drives or filesystems. Hash partitioning can be used to evenly distributes the rows among partitions, giving partitions approximately the same size.
•  Let say one partition of the table is unavailable and all remaining partitions are available and online. The application can access the table and get the results until and unless the query required the unavailable partition likewise High availability can achieved.

Enable partition option as a DBA

Partition enabling in 10g

Please follow the below steps to enable the partition in the oracle database using chopt tool

·         Check whether the partitioning is enable or not with the below query

Select * from v$option;

·         If disable then shut down the database using sqlplus or srvctl command and its services

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk part_on

$ORACLE_HOME/bin/relink all

·         Bring up the database.

·         Select * from v$option;

Partition enabling in 11g

Cd $ORACLE_HOME/bin

chopt enable partitioning

Disable partitioning

cd $ORACLE_HOME/bin
chopt disable partitioning

Switchover Testing from Primary database to Physical Standby database.

Switchover from Primary database to Physical Standby database

Here today we discuss how the switchover will be performed between Primary and Physical Standby database. During a Switchover, The primary database transition to the standby role and standby database transition to the primary role. Usually This activity will come into picture when there is a planned Maintenance on the primary server like OS Patching, upgradation of the hardware 

(Hardware Changes) or DR Testing.

From my experience i did this activity as part of the DR testing. I worked in a environment where we have Datacenters in Singapore and Hongkong. 

Singapore  -- Primary Site

Hongkong  -- Standby Site ( DR Site )

I setup the production database and configured the dataguard. Before Go-Live of the project we have to make sure the switchover and failover must work well. Failover scenarios will discuss in my next topic. 

Switchover :

This procedure is non-destructive, Guarantee no loss of data and can be performed in reverse when the primary site becomes available, without having to rebuild either database. A switchover is initiated on the primary database and is completed on the target standby database.

I have just briefed in the below steps

1) Application team has to shut down the application and make sure there are no active sessions connected to the database.

2) Comment the crontab jobs & reschedule the jobs in OEM if any during the scheduled period. 

3) Once the application is brought down. Verify whether the primary database can be switched over to the standby role or not by executing the below.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
---------------------------------
TO STANDBY

If there are any still any active sessions then the above query returns SESSIONS ACTIVE. We can ignore safely as we are performing the switchover with session shutdown option. So, it will rollback the active session automatically.

In the primary database initiate the switchover

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE  DATABASE_ROLE
---------- ----------------
MOUNTED    PHYSICAL STANDBY

Now the primary database has been switched to physical standby database. Now proceed with switching the standby database to primary as below.

In the standby database, Verify whether it Can Be Switched to the Primary Role.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO PRIMARY

On Standby Database, Switch Standby Database Role to the Primary Role

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;


OPEN_MODE  DATABASE_ROLE
---------- ----------------
READ WRITE PRIMARY

Now start the listener on the earlier primary database. 

Start the MRP Process on the current standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Post Steps

1. Do a log switch on the current primary database and check whether the log is applying on the current standby database.
2. Provide the new tns details to the application team and ask them to test the application connectivity.
3. Once the planned Maintenance or DR testing is completed. Revert back using the same steps above.

Switchover in Primary RAC and Standby RAC servers

The process will be the same for both the RAC Dataguard configuration or Standalone Dataguard Configuration except the below step.

• Before performing a switchover to a RAC primary/standby shut down all but one primary/standby instance (they can be restarted after the role transition has completed).

ASMCMD Copy failing with ORA-15046 Error

ORA-15046

Copying of the file among the ASM disks will fail with the ORA-15046 error.

ASMCMD> cp +DATA/gbjfou/data/carlos.297.878629835 +REDO/gbjfou/data/carlos.297.878626689

copying +DATA/gbjfou/data/carlos.297.878629835 -> +REDO/gbjfou/data/carlos.297.878626689

ASMCMD-8016: copy source '+DATA/gbjfou/data/carlos.297.878629835' and target '+REDO/gbjfou/data/carlos.297.878626689' failed

ORA-15056: additional error message

ORA-15046: ASM file name '+REDO/gbjfou/data/carlos.297.878626689' is not in single-file creation form

ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 415

ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

Here, i am trying to copy the carlos.297.878626689 file from +DATA diskgroup to the +REDO diskgroup. As per the MOS note Doc ID 452158.1 they said it is a known behavior because the file carlos.297.878626689 creating in the target disk is not in the form where that can be used to create a single file. As here we can see the file name extension with numerals where it represents the filenumber / Incarnation.

Then, i removed the numerals and tried to copy which worked fine for me :)

ASMCMD> cp +DATA/gbjfou/data/carlos.297.878629835 +REDO/gbjfou/data/carlos
source +DATA/gbjfou/data/carlos.297.878629835
target +REDO/gbjfou/data/carlos
copying file(s)...file, +REDO/gbjfou/data/carlos, copy committed.