Steps to create active data guard in 11g and up-gradation to 12c using manual method - IV

• «
• 1
• 2
• 3
• 4

10. Post upgrade steps


i. Set cluster_database to TRUE

Alter system set cluster_database=TRUE scope=spfile;

Srvctl stop instance –d TESTDBPRD –I NEWDBPRD1

Srvctl start instance –d TESTDBPRD –I NEWDBPRD1

Srvctl start instance –d TESTDBPRD –I NEWDBPRD2



ii. Update profile of DB services with new 12c home

Srvctl upgrade database –d TESTDBPRD –o /rdbms/app/oracle/12.1.0.2/db


iii. Re verify if all components are in valid state in dba_registry


A. Issues Faced


i. ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted

Steps to create active data guard in 11g and up-gradation to 12c using manual method - III

• «
• 1
• 2
• 3
• 4
• »

8. Initiate 12c upgrade


i.   Copy current pfile to 12c $ORACLE_HOME/dbs on both nodes
ii. Open db in upgrade in new 12c home


[oracle@testlabdb005a ~]$ . ./.bash_profile_12c
[oracle@testlabdb005a ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Thu JAN 11 08:53:36 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
idle> startup upgrade
ORACLE instance started.
Total System Global Area 4.8318E+10 bytes
Fixed Size 4510496 bytes
Variable Size 6174018784 bytes
Database Buffers 4.2010E+10 bytes
Redo Buffers 129703936 bytes
Database mounted.
Database opened.


iii. Run upgrade script

cd $ORACLE_HOME/rdbms/admin

Steps to create active data guard in 11g and up-gradation to 12c using manual method - II

• «
• 1
• 2
• 3
• 4
• »

6. Activating standby


Following steps can be taken to activate the standby and opening db instance in read write mode


SQL> select name,open_mode,database_role ,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR GUARD_S
------------ ------------ -------------------- -------- -------
TESTDBPRD MOUNTED PHYSICAL STANDBY NOT ALLOWED DISABLED NONE



SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected


SQL> alter database recover managed standby database cancel;

Database altered.

How to enable sql session tracing in oracle database

Enabling session tracing in oracle database


1. Identify sql client session to be traced


For example you are planning to trace the session for module ABC , identify the SPID for session e.g. 13762780

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set line 1000
col OSUSER for a10
col SID for 999
col USERNAME for a20
col MACHINE for a20
set pagesize 100
SELECT distinct
a.sid,
a.serial#,
b.spid, sq.sql_id,
b.pid,
a.username,
a.machine,
a.status,
a.module,
a.osuser,
a.LAST_CALL_ET/60 "Inac_Min",a.logon_time,c.event, sq.sql_text
FROM
v$session a,
v$process b,
v$session_wait c,
v$sqlarea sq
WHERE
a.username IS NOT NULL
AND
a.paddr=b.addr
and a.username='GENEVA_ADMIN' and a.sql_hash_value=sq.hash_value
and (upper(a.module) like '%ABC%')
and c.sid=a.sid;


2. Enable trace

Steps to create active data guard in 11g and up-gradation to 12c using manual method - I

Steps to Create 11 ADG and up-gradation to 12c using manual method

 10 steps will take you through systematic approach to create standby database/ADG (active data guard) followed by activation and up-gradation to 12c using manual method

    1.  11g dummy instance creation for ADG

     2.  Dummy instance cleanup and preparation for rman restore to create standby

     3.  RMAN level 0 restoration initiation

     4.  ADG parameter setting

     5.  Restore archives and apply to standby

     6.  Activating standby

     7.  Pre-requisites of 12c upgrade

     8.  Initiate 12c upgrade

     9.  Time zone upgrade

    10. Post upgrade steps
     A.  Issues Faced

Above approach is best suited to reduce overall downtime involved to application and DB upgrade can be performed within 6 Hrs.

Please refer the setup as below 

testlabdb005a/testlabdb005b -: New two node RAC database hosts to be used for 11g restore/ADG/Upgrade to 12c

testoldlabdb1/testoldlabdb12 -: Old two node RAC database with 11g database TESTDBPRD which acts as a primary db node

TESTDBPRD :- Old 11g Database global name , name to be preserved in ADG

NEWDBPRD :- New 11g ADG database unique name , older name will be preserved as global dbname

 1.   11g dummy instance creation for ADG

We will create a dummy database instance to get db service /ASM/ TNS configuration readily available

[oracle@testlabdb005a ~]$ . ./.bash_profile_11g

[oracle@testlabdb005a ~]$ which sqlplus

/golden/app/oracle/11.2.0.3.0/db/bin/sqlplus

[oracle@testlabdb005a ~]$  export DISPLAY=164.16.18.55:10.0

[oracle@testlabdb005a ~]$ dbca