Tracing a session with a client identifier

Below is the Methodology to trace a session with Client Identifiers, I have created a special trigger also to generate client_identifier for a user with DBMS_SESSION package.

SQL> create table sys.trigger_test  (cid varchar2(1000), sdate date, loggin varchar2(1000));

Table created.

CREATE OR REPLACE TRIGGER trig_logon_scott_v1
  AFTER LOGON
  ON SCOTT.SCHEMA
BEGIN
DBMS_SESSION.SET_IDENTIFIER('hr');
END;
/

Trigger created.

CREATE OR REPLACE TRIGGER set_trace_logon_test1
AFTER LOGON ON SCOTT.SCHEMA
WHEN (lower(sys_context('USERENV', 'CLIENT_IDENTIFIER')) like '%hr%')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12 : 10053 trace name context forever, level 1''';
EXECUTE IMMEDIATE 'alter session set "_fix_control"="6765823:off"';
insert into sys.trigger_test values (sys_context('USERENV', 'CLIENT_IDENTIFIER'),sysdate,'LOGON');
commit;
END set_trace_logon_test1;
/

Trigger created.

[oracle@node4 ~]$
[oracle@node4 ~]$ sqlplus scott

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 11 12:00:55 2015

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


Checking and confirming at the trace location

[oracle@node4 trace]$  ls -ltr |tail -2
-rw-r----- 1 oracle oinstall 131747 Mar 11 12:00 prd_ora_6289_From_Trigger.trm
-rw-r----- 1 oracle oinstall 477686 Mar 11 12:00 prd_ora_6289_From_Trigger.trc

OTN Conference (OTN Yathra) in 2015 held at India

The great event OTN Yathra which happens annually and is going on right now in India, I also got a chance as a Speaker to present on few topics which were Understanding Histograms (Including what's new in 12C) and SQL Plan Management (Including what's new in 12C), So i had 2 sessions for 2 hours, First i presented on 13th Feb @ Gurgaon where the first event of this conference was held as a start of this conference, the second event where i presented was on 17th Feb @ Chandigarh. Below are few of the pictures when i was presenting the sessions. I would like to thank All India Oracle Users Group (AIOUG) and OTN for giving me the opportunity to speak at this great big event.

Predefined Roles for Oracle 12C

When we run standard scripts that are part of database creation, Oracle 12C automatically defines few roles which are listed below

Predefined Roles for Oracle 12C

ADM_PARALLEL_EXECUTE_TASK
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
AUDIT_ADMIN
AUDIT_VIEWER
AUTHENTICATEDUSER
CAPTURE_ADMIN
CDB_DBA, CONNECT
CSW_USR_ROLE
CTXAPP
CWM_USER
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
DBA
DBFS_ROLE
DELETE_CATALOG_ROLE
EJBCLIENT
EM_EXPRESS_ALL
EM_EXRESS_BASIC
EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
GLOBAL_AQ_USER_ROLE
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_ROLE
HS_ADMIN_SELECT_ROLE
IMP_FULL_DATABASE
JAVADEBUGPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVAUSERPRIV
JAVA_ADMIN
JAVA_DEPLOY
JMXSERVER
LBAC_DBA
LOGSTDBY_ADMINISTRATOR
OEM_ADVISOR
OEM_MONITOR
OLAP_DBA
OLAP_USER
OLAP_XS_ADMIN
OPTIMIZER_PROCESSING_RATE
ORDADMIN
PDB_DBA
PROVISIONER
RECOVERY_CATALOG_OWNER
RESOURCE
SCHEDULER_ADMIN
SELECT_CATALOG_ROLE
SPATIAL_CSW_ADMIN
SPATIAL_WFS_ADMIN
WFS_USR_ROLE
WM_ADMIN_ROLE
XDBADMIN
XDB_SET_INVOKER
XDB_WEBSERVICES
XDB_WEBSERVICES_OVER_HTTP
XDB_WEBSERVICES_WITH_PUBLIC
XS_CACHE_ADMIN
XS_NSATTR_ADMIN
XS_RESOURCE
XS_SESSION_ADMIN

SYS user table USER$ in Oracle Database

SYS.USER$ Internal table in Oracle Database

# PTIME provides the date the password was last changed.
# LCOUNT provides the number of failed logins.
# CTIME provides the date the user was created.
# LTIME provides the date the user was last locked.

In the case of LTIME column the value will not be set to null if the user will be unlocked.

Below is an example of how to query user$ table :-

select ctime,ltime,ptime from user$ where name = 'SCOTT';

In Oracle database 12C the SELECT ANY DICTIONARY Privilege does not allows users to access some SYS Data Dictionary Tables like for example the sys schema tables USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS, DEFAULT_PWD$, ENC$, LINK$, and USER$. So thats a new enhancement for 12C security, there are lots of them and i will be publishing them too soon on this blog on Label Oracle Security.

Tracing in Oracle Data Guard

Sometime to do additional analysis and troubleshooting in oracle databases or even to provide additional information to Oracle Support we need to enable tracing, and when it comes to oracle data guard we have several tracing levels as well, like other oracle events.

Gathering trace data for database workload there are lot of ways which is also shown in my another article,  however today we will see special type of tracing which is for log archive and log apply services. To gather and generate trace data for archiving you can enable tracing on the Primary and Standby Databases. Archive Tracing can be enabled via the Initialization Parameter ‘log_archive_trace’.

There are several Levels available. To enable different levels of tracing for oracle data guard archiving set the ‘log_archive_trace’ to the Sum of the desired Levels. Remember that setting this parameter back to ‘0’ disables the Tracing. Relevant and common Levels for Log Transport Services are:


For the Primary Database:

1:            Tracks archiving of log files
2:            Tracks archive status by archive log file destination
64:         Tracks ARCn process state activity
128:       Tracks FAL server process activity
512:       Tracks LGWR redo shipping network activity
2048:     Tracks RFS/ARCn ping heartbeat

For the Standby Database:

1:            Tracks archiving of log files
2:            Tracks archive status by archive log file destination
64:          Tracks ARCn process state activity
256:       Tracks RFS Logical Client
1024:     Tracks RFS physical client
2048:     Tracks RFS/ARCn ping heartbeat

There are some more Levels which are concerning local Archiving and Log Apply Services which can be set as well if required. See below

4096 Tracks real-time apply activity

8192 Tracks Redo Apply activity (media recovery or physical standby)

16384 Tracks archive I/O buffers

32768 Tracks LogMiner dictionary archiving

You can combine tracing levels by setting the value of the LOG_ARCHIVE_TRACE parameter to the sum of the individual levels. For example, setting the parameter to 6 generates level 2 and level 4 trace output.

Note: The level numbers do not appear in the actual trace output; they are shown here for clarification only.

You can read oracle documentation for further information from following link of doc

Reference :- http://docs.oracle.com/cd/E11882_01/server.112/e41134/toc.htm

Switchover not happening in Oracle Data guard

Usually when we are trying to switchover, problems come when sessions are active, that is why session shutdown option is there in oracle, so in case some sessions are

active, make sure you wait for them to end or kill them, also do the following workaround in order to make sure the a clean switchover happens.


So like i said When sessions are active we should not try to switchover, even if we do an attempt to switch over fails with the following error message:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY * 
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
Action: Query the V$SESSION view to determine which processes are causing the error. For example:

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION WHERE TYPE = 'USER' AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);

SID        PROCESS   PROGRAM
---------  --------  ------------------------------------------------
        7      3537  oracle@nhclone2 (CJQ0)
       10
       14
       16
       19
       21

6 rows selected.

In the above output and example, the JOB_QUEUE_PROCESSES parameter corresponds to the CJQ0 process entry.JOB_QUEUE_PROCESSES parameter is used for lot of things in

oracle like for example refresh support for materialized views or may be advanced queus using job queues for message propagation. Oracle says the job queue process is

a user process, it is counted as a SQL session that prevents switchover from taking place. The entries with no process or program information are threads started by

the job queue controller.

JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per

instance (J000, ... J999).


Verify the JOB_QUEUE_PROCESSES parameter is set using the following SQL statement:

SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES;
NAME                           TYPE      VALUE
------------------------------ -------   --------------------
job_queue_processes            integer   5

Then, set the parameter to 0. For example:

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;


Some more sessions can also be active and below processes are very critical to stop in order for a clean switchover to take place.

## Change the AQ_TM_PROCESSES dynamic parameter to the value 0. The change will take effect immediately without having to restart the instance.

## Issue the emctl stop agent command from the operating system prompt.

Mastering 12C Data Guard BROKER

In this article we will see how to master 12C Oracle Data Guard Broker, we will see how to setup a broker, troubleshoot the broker, problems faced with 12C Broker, Switchover and failover with 12C Broker and also Fast Start failover with 12C Broker.

In this article we have a data guard setup with primary as PRD and standby as DRS.

[oracle@node4 ~]$ dgmgrl

DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect /   

Connected as SYSDG.

DGMGRL> create configuration myconfig1 as primary database is 'prd'  connect identifier is prd ;

Error: 

ORA-16525: the Data Guard broker is not yet available

## So in this case we will enable the initialization parameter dg_broker_start

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

PRD       READ WRITE

SQL> alter system set dg_broker_start=true;

System altered.

## Lets go to standby database

[oracle@node4 ~]$ sqlplus sys@drs as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 8 15:09:22 2014

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

Enter password: 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>  alter system set dg_broker_start=true;

System altered.

[oracle@node4 ~]$ dgmgrl

DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys@prd

Password:

Connected as SYSDBA.

DGMGRL>  create configuration myconfig1 as primary database is 'prd'  connect identifier is prd ;

Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

Failed.

## In this case we should disable the following initialization parameter

SQL>   ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=" ";

System altered.

DGMGRL>  create configuration myconfig1 as primary database is 'prd'  connect identifier is prd ;

Configuration "myconfig1" created with primary database "prd"

DGMGRL> add database drs as  connect identifier is drs ;

Database "drs" added

DGMGRL> 

DGMGRL> 

DGMGRL> enable configuration ;

Enabled.

DGMGRL> show configuration ;

Configuration - myconfig1

  Protection Mode: MaxPerformance

  Databases:

  prd - Primary database

    Warning: ORA-16789: standby redo logs not configured

    drs - Physical standby database 

      Error: ORA-16525: the Data Guard broker is not yet available

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

## So we will configure standby redo logs now for primary

SQL> alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m;

Enter value for a: 4

Enter value for a: red04.log

old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m

new   1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/prd/red04.log') size 50m

Database altered.

SQL> /

Enter value for a: 5

Enter value for a: red05.log

old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m

new   1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/prd/red05.log') size 50m

Database altered.

SQL> /

Enter value for a: 6

Enter value for a: red06.log

old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m

new   1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/prd/red06.log') size 50m

Database altered.

SQL> /

Enter value for a: 7

Enter value for a: red07.log

old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m

new   1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/prd/red07.log') size 50m

Database altered.

DGMGRL> show database drs

Database - drs

  Role:              PHYSICAL STANDBY

  Intended State:    APPLY-ON

  Transport Lag:     46 seconds (computed 17 seconds ago)

  Apply Lag:         2 minutes 36 seconds (computed 17 seconds ago)

  Apply Rate:        (unknown)

  Real Time Query:   OFF

  Instance(s):

    drs

  Database Warning(s):

    ORA-16789: standby redo logs not configured

Database Status:

WARNING

## Standby logs also not configured for standby database

SQL> alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m;

Enter value for a: 4

Enter value for a: red04.log

old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m

new   1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/drs/red04.log') size 50m

Database altered.

SQL> /

Enter value for a: 5

Enter value for a: red05.log

old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m

new   1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/drs/red05.log') size 50m

Database altered.

SQL> /

Enter value for a: 6

Enter value for a: red06.log

old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m

new   1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/drs/red06.log') size 50m

Database altered.

SQL> /

Enter value for a: 7

Enter value for a: red07.log

old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/drs/&a') size 50m

new   1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/drs/red07.log') size 50m

Database altered.

DGMGRL>  show database drs

Database - drs

  Role:              PHYSICAL STANDBY

  Intended State:    APPLY-ON

  Transport Lag:     4 minutes 45 seconds (computed 6 seconds ago)

  Apply Lag:         4 minutes 45 seconds (computed 6 seconds ago)

  Apply Rate:        (unknown)

  Real Time Query:   OFF

  Instance(s):

    drs

  Database Error(s):

    ORA-16766: Redo Apply is stopped

Database Status:

ERROR

## Redo apply is stopped now because we had to add standby log and for that we have to disable MRP Process

SQL> alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m;

Enter value for a: 4

Enter value for a: red04.log

old   1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/prd/&a') size 50m

new   1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/prd/red04.log') size 50m

alter database add standby logfile group 4 ('/u01/app/oracle/oradata/prd/red04.log') size 50m

*

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

SQL> recover managed standby database cancel ;

Media recovery complete.

## Now i was able to add standby redo log files

DGMGRL> show database drs

Database - drs

  Role:              PHYSICAL STANDBY

  Intended State:    APPLY-ON

  Transport Lag:     6 minutes 47 seconds (computed 28 seconds ago)

  Apply Lag:         6 minutes 47 seconds (computed 28 seconds ago)

  Apply Rate:        (unknown)

  Real Time Query:   OFF

  Instance(s):

    drs

Database Status:

SUCCESS

DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance

  Databases:

  prd - Primary database

    drs - Physical standby database 

      Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold

Fast-Start Failover: DISABLED

Configuration Status:

WARNING

DGMGRL> disable database drs ;

Disabled.

DGMGRL> enable database drs ;

Enabled.

DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance

  Databases:

  prd - Primary database

    drs - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

## Lets do a switchover and a failover

DGMGRL> edit database prd set property FastStartFailoverTarget = 'drs' ;

Property "faststartfailovertarget" updated

DGMGRL> edit database drs set property FastStartFailoverTarget = 'prd' ;

Property "faststartfailovertarget" updated

DGMGRL> enable fast_start failover ;

Enabled.

DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance

  Databases:

  prd - Primary database

    Warning: ORA-16819: fast-start failover observer not started

    drs - (*) Physical standby database 

      Warning: ORA-16819: fast-start failover observer not started

Fast-Start Failover: ENABLED

Configuration Status:

WARNING

DGMGRL> start observer

Observer started

## While doing switchover and failover i kept on investigating for a long time why invalid username password error is coming even though the password and password file is same for primary and standby, i later realized that the password was not complex because it was simple 3 letter password. and that was the reason i was getting this error. Let us see now for switchover and failover example.

DGMGRL> switchover to prd ;

Performing switchover NOW, please wait...

Operation requires a connection to instance "prd" on database "prd"

Connecting to instance "prd"...

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

        connect to instance "prd" of database "prd"

DGMGRL> switchover to prd ;

not logged on

DGMGRL> connect sys@drs

Password:

ORA-01017: invalid username/password; logon denied

## Now i made sure my password is complex

DGMGRL> connect sys@drs

Password:

Connected as SYSDBA.

DGMGRL> switchover to prd ;

Performing switchover NOW, please wait...

Operation requires a connection to instance "prd" on database "prd"

Connecting to instance "prd"...

Connected as SYSDBA.

New primary database "prd" is opening...

Operation requires startup of instance "drs" on database "drs"

Starting instance "drs"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "prd"

DGMGRL> show configuration

Configuration - myconfig1

  Protection Mode: MaxPerformance

  Databases:

  prd - Primary database

    drs - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>  switchover to drs ;

Performing switchover NOW, please wait...

Operation requires a connection to instance "drs" on database "drs"

Connecting to instance "drs"...

Connected as SYSDBA.

New primary database "drs" is opening...

Operation requires startup of instance "prd" on database "prd"

Starting instance "prd"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "drs"

## It is really important to connect to the target

DGMGRL> connect sys@drs

Password:

Connected as SYSDBA.

DGMGRL> failover to prd ;

Performing failover NOW, please wait...

Error: ORA-16600: not connected to target standby database for failover

Failed.

Unable to failover

DGMGRL> connect sys@prd 

Password:

Connected as SYSDBA.

DGMGRL> failover to prd ;

Performing failover NOW, please wait...

Failover succeeded, new primary is "prd"

## Reinstating the standby database DRS

DGMGRL> reinstate database drs ;

Reinstating database "drs", please wait...

Operation requires shutdown of instance "drs" on database "drs"

Shutting down instance "drs"...

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "drs" on database "drs"

Starting instance "drs"...

ORACLE instance started.

Database mounted.

Continuing to reinstate database "drs" ...

Reinstatement of database "drs" succeeded

____________

DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:          30 seconds

  Target:             (none)

  Observer:           node4.oracle.com

  Lag Limit:          30 seconds

  Shutdown Primary:   TRUE

  Auto-reinstate:     TRUE

  Observer Reconnect: (none)

  Observer Override:  FALSE

Configurable Failover Conditions

  Health Conditions:

    Corrupted Controlfile          YES

    Corrupted Dictionary           YES

    Inaccessible Logfile            NO

    Stuck Archiver                  NO

    Datafile Offline               YES

  Oracle Error Conditions:

    (none)

DGMGRL> enable fast_start failover condition "1031" ;

Succeeded.

DGMGRL> enable fast_start failover condition "1578" ;

Succeeded.

DGMGRL>  show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:          30 seconds

  Target:             (none)

  Observer:           node4.oracle.com

  Lag Limit:          30 seconds

  Shutdown Primary:   TRUE

  Auto-reinstate:     TRUE

  Observer Reconnect: (none)

  Observer Override:  FALSE

Configurable Failover Conditions

  Health Conditions:

    Corrupted Controlfile          YES

    Corrupted Dictionary           YES

    Inaccessible Logfile            NO

    Stuck Archiver                  NO

    Datafile Offline               YES

  Oracle Error Conditions:

    ORA-01031: insufficient privileges

    ORA-01578: ORACLE data block corrupted (file # %s, block # %s)

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = "15" ;

Property "faststartfailoverthreshold" updated

DGMGRL> enable fast_start failover ;

Enabled.

SQL> conn scott 

Enter password: 

Connected.

SQL> alter system set log_checkpoints_to_alert=true;

alter system set log_checkpoints_to_alert=true

*

ERROR at line 1:

ORA-01031: insufficient privileges

### Broker Log is given below

7:48:32.60  Monday, September 08, 2014

Initiating Fast-Start Failover to database "drs"...

Performing failover NOW, please wait...

Failover succeeded, new primary is "drs"

17:48:36.72  Monday, September 08, 2014