Read Access On Oracle Database Trace Files .-- _trace_files_public hidden parameter

 

The " _trace_files_public" parameter is a hidden parameter whose default value is false and once it's set to true that will allow all users accessing the server where the oracle database is hosted will be able to read the trace files. 

This is not recommended as per CIS security standards however in some environment , application team do come up  with requirements . 

Since this is a hidden parameter and your query its current setting following X$ tables need to be queried:

select A.ksppinm, B.ksppstvl

from sys.x$ksppi a,sys.x$ksppcv b

where A.indx=B.indx

and A.ksppinm like '\_%trace_files_public' escape '\';

To change the value of this parameter, it will need a database bounce after running:

alter system set "_trace_files_public" = TRUE scope=spfile;

Other Way is to handle from server level using umask 

umask 0002 this will give u 664 permission

umask 0000 this will give u 666 permission

Oracle database Enable / Disable Restricted session

 

‘alter system enable restricted session‘ does not “kick out” user automatically. It just blocks new user who does not have the ‘restrict session’ privilege to login,

For  Non Rac 

1) 

 ( To  Enable ) 

STARTUP RESTRICT   

 ( To  Disable ) 

Shu immediate 

Startup 

Or 

2)   

 ( To  Enable ) 

 startup mount;

 ALTER SYSTEM ENABLE RESTRICTED SESSION;

Alter database Open ;

 ( To  Disable ) 

Shu immediate 

Startup 

Or  

3)  

 ( To  Enable ) 

alter system enable restricted session;

 ( To  Disable ) 

alter system disable restricted session;

For   Rac 

alter system enable restricted session command enables restricted mode only on the node on which it runs.

srvctl start database -d orclcdb -o restrict  /  srvctl start database -d orclcdb -startoption restrict

srvctl start instance -d orclcdb -i ORCLCDB  -o restrict     ( for instance ) 

For   Container Database 

SQL> alter session set container = pdb1;

 

Session altered.

 

SQL> alter system enable restricted session;

 

System altered.

 

SQL> select logins from v$instance;

 

LOGINS

----------

RESTRICTED

 

SQL> alter system disable restricted session;

alter system disable restricted session

*

ERROR at line 1:

ORA-65144: ALTER SYSTEM DISABLE RESTRICTED SESSION is not permitted

You can get out of the predicament, by force opening the pluggable database as shown below, but probably best to look at the latest 12c PSU, which contains a fix (unverified)

 

SQL> conn / as sysdba

Connected.

 

SQL> alter pluggable database pdb1 open force;

 

Pluggable database altered.

 

SQL> alter session set container = pdb1;

 

Session altered.

 

SQL> select logins from v$instance;

 

LOGINS

----------

ALLOWED

QUIESCE RESTRICTED  and UNQUIESCE

ALTER SYSTEM QUIESCE RESTRICTED;

Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active. For example, If a user issues a SQL query in an attempt to force an inactive session to become active, the query will appear to be hung. When the database is later unquiesced, the session is resumed, and the blocked action is processed.

The following statement restores the database to normal operation:

ALTER SYSTEM UNQUIESCE;

All non-DBA activity is allowed to proceed. In an Oracle Real Application Clusters environment, this statement is not required to be issued from the same session, or even the same instance, as that which quiesced the database. If the session issuing the ALTER SYSTEM UNQUIESCE statement terminates abnormally, then the Oracle Database server ensures that the unquiesce operation completes.

The ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance. The column values has one of these values:

NORMAL: Normal unquiesced state.

QUIESCING: Being quiesced, but some non-DBA sessions are still active.

QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

Views to  check  :

 

set linesize 200

col HOST_NAME for a30

 col STATUS for a20

 select instance_name, host_name, archiver, thread#, decode(STATUS,'STARTED','STARTUP***NOMOUNT',status) status,LOGINS, STARTUP_TIME from gv$instance ;

set head off verify off echo off pages 150 linesize 120 feedback off

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

select distinct

'DATABASE_NAME..............................................: '|| NAME             ,

'INSTANCE NAME..............................................: '|| INSTANCE_NAME    ,

'LOG_MODE...................................................: '|| LOG_MODE         ,

'OPEN MODE..................................................: '|| OPEN_MODE        ,

'INSTANCE_NAME..............................................: '|| INSTANCE_NAME    ,

'HOSTNAME...................................................: '|| HOST_NAME        ,

'STATUS.....................................................: '|| STATUS           ,

'LOGINS.....................................................: '|| LOGINS           ,

'STARTUP-TIME...............................................: '|| STARTUP_TIME     ,

'DATAGUARD BROKER...........................................: '|| DATAGUARD_BROKER ,

'GUARD STATUS...............................................: '|| GUARD_STATUS     ,

'FLASHBACK ON...............................................: '|| FLASHBACK_ON     ,

'PROTECTION MODE............................................: '|| PROTECTION_MODE  ,

'CONTROLFILE TYPE...........................................: '|| CONTROLFILE_TYPE

from gv$instance i, v$database d

-- where 1=1  

-- and i.inst_id=d.inst_id

order by 2;

set head on verify on echo on feedback on

Oracle Database Dataguard Switchover

 

############## Switchover Preparation   ##############

1)  Take  full backup of  Database 

2) Take backup  of  Controlfile 

3)  srvctl config database -d primary -a 

##############    Switchover Without Datagurad Broker   ##############

1.1 Start Oem  Blackout 

emctl start blackout  blackout_name  -nodelevel 

emctl  status blackout 

1.2  Perform some  log switch 

Alter system archive log current  ; 

Alter system archive log current  ; 

Alter system archive log current  ; 

2.1 On Primary Database, Verify That it Can Be Switched to the Standby Role

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO STANDBY

2.2 Initiate the Switchover

On Standby  Database,

SQL> alter database recover managed standby database cancel;

SQL> alter database recover managed standby database finish;

On Primary Database, Initiate the Switchover

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

SQL> SHUTDOWN ABORT;

SQL> STARTUP MOUNT;

SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

2.3 On Standby Database, Verify That it Can Be Switched to the Primary Role

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO PRIMARY

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

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

SQL> ALTER DATABASE OPEN;

SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

Below  is  one of good features we never used but good to be included 

ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;   ( pre   switchover ) 
ALTER DATABASE SWITCHOVER TO BOSTON FORCE;

##############  Switchover  With Datagurad Broker   ##############

2.1   Connect to Primary 

DMGRL >   show configuration

DMGRL >   show database  verbose primary  

DGMGRL> validate database verbose primary  

DMGRL >   show database   primary  inconsistantproperties 

DMGRL >    show database  verbose  standby 

DGMGRL> validate database verbose standby 

DMGRL >    show database   standby inconsistantproperties 

DMGRL >    switchover to standby 

##############  Switchover Post task ##############

We need to update right role of database in Crs 

1)  srvctl config database -d primary -a    

Verify Service is started on New primary 

2)  srvctl status  service -d primary

##############  Tracing for switchover  ##############

We had one of  case where switchover was not complete  and oracle suggested to  enable below tracing .

 

SQL> alter system set log_archive_trace=8191;  -- enabling trace
SQL> alter system set log_archive_trace=0;      -- disabling trace

##############   ORA-12514 during switchover using Data Guard Broker  ##############

SID_LIST_lsnrDBNMAE =
        (SID_LIST =
         (SID_DESC =  (GLOBAL_DBNAME = DBNMAE) (ORACLE_HOME = /path) (SID_NAME = DBNMAE))
         (SID_DESC =  (GLOBAL_DBNAME = DBNMAE_DGMGRL) (ORACLE_HOME = /pth) (SID_NAME = DBNMAE))
        )

Switch over fails – 2 standby :   ORA-16816:

Problem Description

Should you find yourself in a situation where a Data Guard Broker switchover to Standby has failed and left your environment with 2 Physical Standby Databases, follow this simple procedure to switch the failed switchover Standby Database back to Primary.

You may also see the following error from a DGMGRL "show configuration" command:

ORA-16816: incorrect database role

Solution

 

1.       Logon (as sysdba) to the instance that was your Primary database instance before the switchover.

2.       Confirm the database role.

SQL> select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

 

3.       Shutdown the instance.

SQL> shutdown immediate;

4.       Mount the database.

SQL> startup mount;

5. Cancel the MRP process. You will receive “ORA-16136: Managed Standby Recovery not active” if it is           not running, but you can ignore.

SQL> alter database recover managed standby database cancel;

6.       Terminate the current switchover to Standby that never completed fully.  

SQL> alter database recover managed standby database finish;

7.       Now switchover to Primary.

SQL> alter database commit to switchover to primary with session shutdown;

8.       Open the database.

SQL> alter database open;

9.       Confirm the database role.

SQL> select database_role from v$database;

 

DATABASE_ROLE

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

PRIMARY

Additional Steps

When attempting to open the Primary Database you may suffer the following error:

 

SQL> alter database open

*

ERROR at line 1:

ORA-16649: possible failover to another database prevents this database being opened

 

In this case, before you can open the database, you must disable Data Guard Broker as follows:

SQL> alter system set dg_broker_start=false scope=both sid=’*’;

System altered.

 

SQL> alter database open;

 

Database altered.

Now re-install Data Guard Broker.

##############  Known Issues  ##############

Primary Instance Fails To Mount W/ Ora-19970 After Switchover Interrupted (document ID 2256447.1)

DGMGRL>switchover to <standby> Fails with ORA-12514 (Doc ID 1582927.1)

If  we  have  broker ,  use dgmgrl  fir switchover or else we will end  up with 

ORA-16649 

##############  References   ##############

https://docs.oracle.com/en/database/oracle/oracle-database/19/spmss/switchover-to-a-physical-db.html#GUID-AAD70601-D248-4309-B8DD-F461EE31A5FF

https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/examples-using-data-guard-broker-DGMGRL-utility.html

SRDC - Collect Data Guard Diagnostic Information (Doc ID 2219763.1)