Physical Standby # Active dataguard 11g

we know that, all our standby databases are in mount stage where MRP process are applied manually on standby databases . In 11g, Oracle Introduced very cool feature called Active dataguard, where shipped online redo applied on standby database in Open read only state. Active dataguard gives  applications flexibility to access and query standby database. Anyway customer need extra licensing to configure active dataguard. We can implement Active Data Guard for High Scalability, Hight Availability and Real Time Data Changes. Block corruptions are automatic in Active dataguard  and where application retries for data on the standby database if they encounter with ORA-01578. The corrupted blocks  are repaired on standby automatically. 

Procedure to convert physical standby to Active Dataguard 

Stop the MRP 

SQL> alter database recover managed standby database cancel;

OPEN THE STANDBY DATABASE IN READ ONLY

SQL> alter database open read only;

SQL> select open_mode, db_name,database_role from v$database;

APPLY THE MRP PROCESS;

SQL> alter database recover managed standby database using current logfile disconnect;

SQL> select process,status,sequence# from v$managed_standby;

Monitoring Active Dataguard using following Query’s

SQL> SELECT name, value, datum_time, time_computed  FROM V$DATAGUARD_STATS WHERE name like 'apply lag';

SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM  WHERE NAME = 'apply lag' AND COUNT > 0;

we can set SLA( Service Level Agreement) for query’s accessing ACTIVE DATAGUARD 

In 11g we have new session setting called “standby_max_data_delay” 

if we set “ standby_max_data_delay=none “ queries will will be executed regardless of the apply lag on database by default 

if the parameter is set to NON-ZERO value,  query’s will be executed only after the apply_lag or equal to non - zero value 

If the Query is waiting beyond the “standby_max_data_delay” applications have to access primary results in error “ORA-03172” 

In order to set SLA #  create trigger as follows # 

CREATE OR REPLACE TRIGGER hr_logon_set_SLA_trigger 

AFTER LOGON ON hr.schema 

BEGIN 

IF (SYS_CONTEXT('USERENV','DATABASE_ROLE')

IN ('PHYSICAL STANDBY')) 

THEN

execute immediate ‘ALTER SESSION SET STANDBY_MAX_DATA_DELAY=2;’ 

END IF; 

END; 

/

Following trigger will not allow query’s to occur on primary until redo log applied on standby

CREATE OR REPLACE TRIGGER hr_logon_sync_trigger 

AFTER LOGON ON hr.schema

BEGIN

IF (SYS_CONTEXT('USERENV','DATABASE_ROLE') 

IN ('PHYSICAL STANDBY')) 

THEN

execute immediate ‘ALTER SESSION SYNC WITH PRIMARY;’ 

END IF; 

END;

If the MRP is down, where application encounters with ORA-03173. To avoid errors oracle recommends you to configure physical standby configuration in maximum availability mode 

Reference # 

http://www.oracle.com/technetwork/database/features/availability/311400-134359.pdf

—Nikhil Tatineni—

—Dataguard — 

dbms_workload_repository, modify_snapshot_settings : AWR report

How do we modify the snapshot time and retention to store snapshots # 
when ever snapshot is created, MMON will load the in the sysaux tablespace, using following query you can see what are the minimum and max snapshots available on database 

sql> select min(snap_id),MAX(snap_id) from WRH$_ACTIVE_SESSION_HISTORY;

MIN(SNAP_ID) MAX(SNAP_ID)

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

  27                         54

we can verify snapshot interval and retention time on sysaux  using  Data dictionary view "dba_hist_wr_control"
sql> select dbid from v$database;
sql>set linesize 100
sql>column SNAP_INTERVAL format a30
sql>column RETENTION format a30
sql>select * from dba_hist_wr_control;
 DBID          SNAP_INTERVAL          RETENTION       TOPNSQL CON_ID
---------- ------------------------------ ------------------------------ ---------- ----------
1854508752 +00000 00:30:00.0   +00030 00:00:00.0  DEFAULT      0

using above query, we can confirm, snap_interval is 30 minutes, indicated database generates snapshot for every 15 minutes and retention is 30 days i.e for every 30 days old snapshots are purged automatically 

we can modify snapshot Interval using package "dbms_workload_repository"

-- Changing snapshot interval to 15 minutes:
-- Note - retention: 43,200 = 30 days # in minutes 
sql> exec dbms_workload_repository.modify_snapshot_settings(retention=>43200, interval=>15, dbid=>1854508752);

Validate changes using following Query 
sql> select dbid from v$database;
sql>set linesize 100
sql>column SNAP_INTERVAL format a30
sql>column RETENTION format a30
sql>select * from dba_hist_wr_control;

create snapshot manually using dbms_world_Repository package as follows # 
sql> exec dbms_workload_repository.create_snapshot;

Dataguard : converting physical standby to snapshot standby

snapshot standby which is 11g new feature along with active dataguard. regularly all standby databases are in mount stage and where DBA start MRP process in mount stage. standby database cannot be updated or opened. If you try to update and open standby database stage, standby database will get corrupted. Snapshot feature gives us flexibility to convert physical standby to snapshot standby database and can update snapshot standby database. 

when you want to convert physical standby to snapshot standby, we have to stop MRP and need to convert physical standby to snapshot standby in mount stage. when you convert physical standby to snapshot standby, Internally restore point is created on physical standby database. when we start updating the snapshot standby database flash logs will be created and  oracle uses these flash logs in FRA  to rollback the database until the restore point when you convert back from snapshot standby to physical standby database. Make sure you have enough space in FRA before you convert the physical standby to snapshot database. 

To test new application functionality on real time data, snapshot database feature is best option. 

procedure to convert physical standby to snap standby database  #
1#Make sure Flashback is enabled on standby
2#stop the MRP if it is up and running 
3#Make sure we have enough space to store flash logs in FRA 
4#Convert the physical standby to snapshot standby
5#check database role and validate snapshot standby & open snapshot database 

1#Make sure Flashback is enabled on standby
sql> select db_name,db_unique_name,open_mode,flashback_on from v$database;

2#stop the MRP if it is up and running 
sql> alter database recover managed standby database cancel;

3#Make sure we have enough space to store flash logs in FRA 
check parameter db_recovery_file_dest_size, if the space is available on the mount, increase db_recovery_file_dest_size to max value. Investigate how much space left in FRA

4#Convert the physical standby to snapshot standby
sql>select db_name,db_unique_name,open_mode,database_role from v$database;
sql>alter database convert to snapshot standby;

Here restore point is created and now standby database create flash logs 
We can query and check for restore point from v$restore_point
sql> select * from v$restore_point;

5#check database role and validate snapshot standby & open snapshot database 
sql>  select db_name,db_unique_name,open_mode,database_role from v$database;

sql> alter database open;

After regression testing, snapshot database can be converted into physical standby.
The timetaken to convert to physical standby depends up on type of operations performed when physical standby database is converted to snapshot standby. when we convert to snapshot to physical standby, oracle has to rollback the snapshot database to restore point recreated and convert backs to physical standby.procedure as follows 

1 # shut down the snapshot database
2 # Bring to mount stage 
3 # convert back to snapshot standby
4 # start the MRP process 

SQL> Shut immediate;
SQL> startup mount;
SQL> alter database convert to physical standby;

SQL> alter database recover managed standby disconnect from session;

--Nikhil Tatineni--
--Dataguard #

Knowledge on BI-DIRECTIONAL Replication GoldenGate : CDR Parameters

Knowledge of Bi-directional Replication and How to resolve conflicts @
Loop Detection # when we configured Bi-directional replication, Both instance's are active, where both instances are updated by application. Here its hard to find out goldengate or application performed the transactions. if the goldengate start replicating the transaction. Goldengate replicat's apply transaction where goldengate extract collect the transaction again. In this scenario, it is never ending loop
we will avoid loop by using following parameter in extract parameter file
tranlogoptions excludeuser <ggsuser>
tracetable ggsuser.trctab

This will exclude transactions or operations performed by goldengate user :) 
if you still want to investigate the looping we can trace extract and replicat process# 
 

Conflict Detection # When we configured bi-directional replication, detecting if any update occurred on both the source and target instances, before changes are applied by goldengate 
we have to use the parameter "getbeforecols" to log before changes for update and delete operations to trail, which helps us to compare and detect conflict while goldengate is processing transaction on database # 
parameter used in extract as follows # 
table schema.tabname, getbeforecols ( on update all, on delete all)  

conflict can be occurred in different scenario, during an update, insert or delete. How to resolve is little bit tricky #  lets talk about resolution for a while #

Conflict resolution #  To handle collisions we will compare the data using timestamp column or range of values  #  # At least timestamp column should be added to the tables replicated by goldengate  in bi-directional replication which help's to compare before and after values i.e logged on the trail. When conflict is detected using the data on the trail # we have to automate the process to resolve the conflict

Conflict Resolution  and management Options #  
Insert -> Insertrowexists -> Overwrite 
                                        -> Ignore
                                        -> Discard
                                        -> USEMIN,USEMAX

Update -> Updaterowmissing  -> Overwrite 
                                                 -> Ignore
                                                 -> Discard
                  Updaterowmissing -> Overwrite 
                                                 -> Ignore
                                                 -> Discard
                                                 -> USEMIN,USEMAX
                                                 -> USEDELTA 

 Delete -> Deleterowmissing -> Ignore
                                           -> Discard
                 Deleterowexists -> Overwrite
                                            -> Ignore
                                            -> Discard

For Example Conflict can be handled as follows MAP mots.emp, target delta.emp,
COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
RESOLVECONFLICT (INSERTROWEXISTS, (DEFAULT, USEMAX(CHANGE_TS)));
RESOLVECONFLICT (UPDATEROWEXISTS, (DEFAULT, USEMIN(CHANGE_TS)));
RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, [ DISCARD | IGNORE ]));


----Nikhil Tatineni---


 

move datafile online 12c

In previous Oracle versions to move datafile to another location # procedure as follows
# Make datafile offline
# Rename datafile to another location 
# copy datafile from old location to new location @
# Recover datafile 
# Make datafile online 

In 12c # we can move datafile online # 

[oracle@red ~]$ mkdir -p /u01/app/mldb69

[oracle@red ~]$ exit

SQL> alter database move datafile '/u02/database/mldb69/MLDB69/datafile/fix0001.dbf' to '/u01/app/mldb69/fix0001.dbf';

Database altered.

SQL> COLUMN file_name FORMAT A70

SELECT file_id, file_name FROM dba_data_files ORDER BY file_id;SQL> 

   FILE_ID FILE_NAME

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

1 /u02/database/mldb69/MLDB69/datafile/o1_mf_system_bmjl16tx_.dbf

2 /u02/database/mldb69/MLDB69/datafile/o1_mf_sysaux_cnkdbgdz_.dbf

3 /u02/database/mldb69/MLDB69/datafile/o1_mf_sysaux_bmjkxj7o_.dbf

4 /u02/database/mldb69/MLDB69/datafile/o1_mf_undotbs1_bmjl4npx_.dbf

5 /u02/database/mldb69/MLDB69/datafile/o1_mf_stage_cnjrt641_.dbf

6 /u02/database/mldb69/MLDB69/datafile/o1_mf_users_bmjl4k6w_.dbf

7 /u02/database/mldb69/MLDB69/datafile/o1_mf_system_cnkdc79d_.dbf

8 /u02/database/mldb69/MLDB69/datafile/ggs_stage01.dbf

9 /u02/database/mldb69/MLDB69/datafile/harry01.dbf

10 /u02/database/mldb69/MLDB69/datafile/admin01.dbf

12 /u01/app/mldb69/fix0001.dbf

---Nikhil Tatineni---
---12c Database #

Dataguard # Oldest online log sequence 0

scenario # 

configured DR database for Oracle database version 12.1.0.2 

Archive logs are shipping to standby and logs are getting applied on standby database 

when I issue archive log list; Result as follows 

SQL> archive log list;

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       /u02/archived/

Oldest online log sequence       0

Next log sequence to archive    0

Current log sequence                0

After I did alot of Investigation, I found information from metalink (Doc ID 2041137.1). This is a BUG. Do not use "archive log list" to compare primary and standby is in sync and use following Query's to compare both databases 

Primary: 

SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

PhyStdby:

SQL> 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;

PhyStdby:

SQL>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;

--Nikhil Tatineni--

--Oracle 12c-- 

Changing Database Name using NID utility 12c

step1 #  Mount the database

step 2 # Change database name using NID utility

step 3 # Edit the dbname in pfile # 

step 4 # Mount the database using pfile 

step 5 # And Open the database with RESETLOGS # 
Step 6 # I recommend to change Instance Name and Update Network after changing the dbname and opening the database with reset logs 

step1 #  Mount the database

SQL> startup mount

ORACLE instance started.

Total System Global Area 1107296256 bytes

Fixed Size     2923488 bytes

Variable Size   805307424 bytes

Database Buffers   67108864 bytes

Redo Buffers   13852672 bytes

In-Memory Area   218103808 bytes

Database mounted.

SQL> !

[oracle@red ~]$ 

[oracle@red ~]$ ps -ef | grep pmon

oracle    4933     1  0 23:29 ?        00:00:00 ora_pmon_p1d2685

oracle    5064  5040  0 23:29 pts/3    00:00:00 grep pmon

[oracle@red ~]$ . oraenv

ORACLE_SID = [p1d2685] ? 

The Oracle base remains unchanged with value /u01/app/oracle

step 2 # Change database name using NID utility

[oracle@red ~]$ nid target=sys/oracle dbname=db12c

DBNEWID: Release 12.1.0.2.0 - Production on Fri Jul 1 23:30:19 2016

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

Connected to database P1D2685 (DBID=3669686930)

Connected to server version 12.1.0

Control Files in database:

    /u01/app/oracle/oradata/P1D2685/controlfile/o1_mf_bbngryqv_.ctl

    /u01/app/oracle/fast_recovery_area/P1D2685/controlfile/o1_mf_bbngrzv0_.ctl

Change database ID and database name P1D2685 to DB12C? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 3669686930 to 1370138315

Changing database name from P1D2685 to DB12C

    Control File /u01/app/oracle/oradata/P1D2685/controlfile/o1_mf_bbngryqv_.ctl - modified

    Control File /u01/app/oracle/fast_recovery_area/P1D2685/controlfile/o1_mf_bbngrzv0_.ctl - modified

    Datafile /u01/app/oracle/oradata/P1D2685/datafile/o1_mf_system_bbngn72x_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/P1D2685/datafile/o1_mf_sysaux_bbngjlcv_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/P1D2685/datafile/o1_mf_undotbs1_bbngqnc4_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/P1D2685/datafile/o1_mf_system_bbngtt8q_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/P1D2685/datafile/o1_mf_users_bbngqjt9_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/P1D2685/datafile/o1_mf_sysaux_bbngtt8k_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/P1D2685/0BE3A840C7CC324FE0530100007FDF9B/datafile/o1_mf_system_bbnhczpz_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/P1D2685/0BE3A840C7CC324FE0530100007FDF9B/datafile/o1_mf_sysaux_bbnhczqm_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/P1D2685/0BE3A840C7CC324FE0530100007FDF9B/datafile/o1_mf_users_bbnhhfhk_.db - dbid changed, wrote new name

    Datafile /u01/in_mem.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/DB1/datafile/ggadmin01.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/P1D2685/datafile/o1_mf_temp_bbngtgv8_.tm - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/P1D2685/datafile/pdbseed_temp012015-01-05_02-22-32-AM.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/P1D2685/0BE3A840C7CC324FE0530100007FDF9B/datafile/o1_mf_temp_bbnhczqp_.db - dbid changed, wrote new name

    Control File /u01/app/oracle/oradata/P1D2685/controlfile/o1_mf_bbngryqv_.ctl - dbid changed, wrote new name

    Control File /u01/app/oracle/fast_recovery_area/P1D2685/controlfile/o1_mf_bbngrzv0_.ctl - dbid changed, wrote new name

    Instance shut down

Database name changed to DB12C.

Modify parameter file and generate a new password file before restarting.

Database ID for database DB12C changed to 1370138315.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

step 3 # Edit the dbname in pfile # 

[oracle@red dbs]$ vi initp1d2685.ora

[oracle@red dbs]$ 

[oracle@red dbs]$ cat initp1d2685.ora 

p1d2685.__data_transfer_cache_size=0

p1d2685.__db_cache_size=134217728

p1d2685.__java_pool_size=16777216

p1d2685.__large_pool_size=33554432

p1d2685.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

p1d2685.__pga_aggregate_target=385875968

p1d2685.__sga_target=721420288

p1d2685.__shared_io_pool_size=16777216

p1d2685.__shared_pool_size=285212672

p1d2685.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/p1d2685/adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/u01/app/oracle/oradata/P1D2685/controlfile/o1_mf_bbngryqv_.ctl','/u01/app/oracle/fast_recovery_area/P1D2685/controlfile/o1_mf_bbngrzv0_.ctl'

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_domain=''

*.db_name='db12c'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4560m

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=db12cXDB)'

*.enable_pluggable_database=true

*.inmemory_size=209715200

*.memory_target=1048m

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

step 4 # Mount the database using pfile 

[oracle@red dbs]$ . oraenv

ORACLE_SID = [p1d2685] ?      

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@red dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 1 23:35:22 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile='$ORACLE_HOME/dbs/initp1d2685.ora';

ORACLE instance started.

Total System Global Area 1107296256 bytes

Fixed Size     2923488 bytes

Variable Size   788530208 bytes

Database Buffers   83886080 bytes

Redo Buffers   13852672 bytes

In-Memory Area   218103808 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

step 5 # And Open the database with RESETLOGS # 

SQL> alter database open RESETLOGS;

Database altered.

SQL> show parameter db_name;

NAME     TYPE VALUE

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

db_name      string db12c

Step 6 # Shutdown the Instance. Changing instance_name by renaming the parameter file # 
[oracle@red dbs]$ cp initp1d2685.ora initdb12c.ora

[oracle@red ~]$ . oraenv

ORACLE_SID = [p1d2685] ? db12c

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1/

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@red ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 2 06:13:01 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 1107296256 bytes

Fixed Size     2923488 bytes

Variable Size   822084640 bytes

Database Buffers   50331648 bytes

Redo Buffers   13852672 bytes

In-Memory Area   218103808 bytes

Database mounted.

Database opened.

SQL> show parameter db_name;

NAME     TYPE VALUE

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

db_name      string db12c

SQL> 

SQL> create spfile from pfile;

File created.

# Shutdown and startup the database using spfile # 

Update /etc/oratab file after changing the Instance Name as follows # 

#

db12c:/u01/app/oracle/product/12.1.0/dbhome_1:N

mldb69:/u01/app/oracle/product/12.1.0/dbhome_1:N

SQL> !

Update Listener and Network configuration Files # 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.30)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = mldb69)

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

      (SID_NAME = mldb69)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = db12c)

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

      (SID_NAME = db12c)

    )

)

P1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.30)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = db12c)

    )

  )

[oracle@red admin]$ lsnrctl reload listener 

[oracle@red admin]$ tnsping p1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 02-JUL-2016 06:19:02

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

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.30)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = db12c)))

OK (0 msec)

--Nikhil Tatineni--

--Nid Utility #