ORA-03113: end-of-file on communication channel (Error: ora-03113 at time of database open)

[oracle@techtest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 27 11:11:47 2016

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

Connected to an idle instance.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 3.2002E+10 bytes

Fixed Size                  2269032 bytes

Variable Size            1.4026E+10 bytes

Database Buffers         1.7918E+10 bytes

Redo Buffers               55541760 bytes

Database mounted.

SQL>

SQL>

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 3065

Session ID: 66 Serial number: 3

Solution:

Step-1: First look at [alert.log] file the find the actual error

[oracle@techtest alert]$ tail -f log.xml

<msg time='2016-09-27T11:12:09.555+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'

 pid='3065'>

 <txt>ARCH: Error 19809 Creating archive log file to &apos;/fra/TECHDB/archivelog/2016_09_27/o1_mf_1_16404_%u_.arc&apos;

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.555+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3069'>

 <txt>ARC1: Error 19809 Creating archive log file to &apos;/fra/TECHDB/archivelog/2016_09_27/o1_mf_1_16405_%u_.arc&apos;

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.614+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3069'>

 <txt>ARCH: Archival stopped, error occurred. Will continue retrying

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.614+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3069'>

 <txt>ORACLE Instance techdb - Archival Error

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.620+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'

 pid='3065'>

 <txt>Errors in file /u01/app/oracle/diag/rdbms/techdb/techdb/trace/techdb_ora_3065.trc:

ORA-16038: log 3 sequence# 16404 cannot be archived

ora-19809:limit exceeded for recovery files

ORA-00312: online log 3 thread 1: &apos;/techtestdb/techdb/redo03.log&apos;

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.620+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.x.12' module=''

 pid='3069'>

 <txt>ORA-16038: log 2 sequence# 16405 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 2 thread 1: &apos;/techtestdb/techdb/redo02.log&apos;

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.688+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'

 pid='3065'>

 <txt>USER (ospid: 3065): terminating the instance due to error 16038

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.710+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3031'>

 <txt>System state dump requested by (instance=1, osid=3065), summary=[abnormal instance termination].

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.729+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3031'>

 <txt>System State dumped to trace file /u01/app/oracle/diag/rdbms/techdb/techdb/trace/techdb_diag_3031_20160927111209.trc

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.870+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module=''

 pid='3031'>

 <txt>Dumping diagnostic data in directory=[cdmp_20160927111209], requested by (instance=1, osid=3065), summary=[abnormal instance termination].

 </txt>

</msg>

<msg time='2016-09-27T11:12:09.951+06:00' org_id='oracle' comp_id='rdbms'

 client_id='' type='UNKNOWN' level='16'

 host_id='techtest.nblbd.com' host_addr='xxx.xx.xx.12' module='sqlplus@techtest.nblbd.com (TNS V1-V3)'

 pid='3065'>

 <txt>Instance terminated by USER, pid = 3065

 </txt>

</msg>

 Step-2:  After look the alert log file, We have found two point on it and that are-

1. Error to create archive log

2. Limit exceeded for recovery files.

>>Step-3:  First try to delete the archive log to overcome error

[oracle@techtest ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Sep 27 11:15:23 2016

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

connected to target database (not started)

RMAN> delete noprompt archivelog all;

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of delete command at 09/27/2016 11:15:29

RMAN-06403: could not obtain a fully authorized session

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

It is not possible to delete archive log as database is not open and limit exceeded for the recovery file.

>>Step-4:  Increase the size of DB_RECOVERY_FILE_DEST_SIZE

4.1: current value

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE;

NAME                                 TYPE        VALUE

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

db_recovery_file_dest_size           big integer 50000M

4.2: Increase size

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =55g;

System altered.

>>Step-5:  Database Open

[oracle@techtest ~]$

[oracle@techtest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 27 11:18:30 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL>

SQL> startup

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL>

SQL> alter database open;

Database altered.

SQL> exit

INITRANS and MAXTRANS parameter in Oracle 11gR2

A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. Every transaction which modifies a block must acquire an entry in the Interested Transaction List (ITL) in block. Space for this list is defined by INITRANS. The space required for Each and every ITL entry is operating system dependent; however, ITL entry in most operating systems requires approximately 23 bytes.

The database block size plays an important role in allocating the number of inital ITLs for the blocks.

The rule is “the total size allocated for initial ITLs SHOULD be LESS THAN 50% of the database block size”

ie :  sizeof(INITIAL ITLs) <  ( 50 % of the DATABASE BLOCK SIZE )

INITRANS

The default value is 1 for tables and 2 for clusters and indexes.

MAXTRANS

The default value is an operating system-specific function of block size, not exceeding 255.

Example:

Let's say you have a block that has 50 records and 5 people simultaneously trying to update 5 records in the block. Let's say INITRANS parameter value is 1 for this block, therefore, at a time only one update will proceed and others update will wait. If INITRANS parameter value increase to 5 then at a time 5 record will update concurrently. Those will boost up the performance.

Initrans Parameter Modify:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 11 11:44:45 2016

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

  

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL>

SQL> select INI_TRANS,owner from dba_tables where table_name='F_VERSION_LINK_DEFAULTS';

 INI_TRANS OWNER

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

         1 ***

         1 ***

         1 CREDITTEST

SQL> conn credittest/credittest

Connected.

SQL>

SQL>

SQL> select count(1) from F_VERSION_LINK_DEFAULTS;

  COUNT(1)

----------

     28296

SQL>

SQL>

SQL> alter table F_VERSION_LINK_DEFAULTS initrans 100;

Table altered.

SQL>

SQL>

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

$

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 11 11:45:42 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL>

SQL> select INI_TRANS,owner from dba_tables where table_name='F_VERSION_LINK_DEFAULTS';

 INI_TRANS OWNER

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

         1 ***

         1 ***

       100 CREDITTEST

SQL>

SQL>

Auditing Enable in Oracle 11gR2 RAC database

This document show steps of enable auditing in Oracle 11gR2 RAC database:
 

Auditing is the monitoring and recording of selected user database actions. Auditing allow you to monitor database activities, for example logon activity, database changes etc. In standard auditing, you use initialization parameters and the AUDIT and NOAUDIT SQL statements to audit SQL statements, privileges, and schema objects, and network and multi-tier activities.

Standard auditing is enabled by setting the AUDIT_TRAIL parameter and is configured with the AUDIT/NOAUDIT commands.  It can write its records to either the SYS.AUD$ table or to OS files depending on the value of AUDIT_TRAIL.  AUDIT_TRAIL can have the following values.

AUDIT_TRAIL={NONE or FALSE| OS| DB or TRUE| DB_EXTENDED| XML |XML_EXTENDED}

The following list provides a description of each value:

NONE or FALSE -> Auditing is disabled.

DB or TRUE -> Auditing is enabled, with all audit records stored in the database audit trial (AUD$).    DB_EXTENDED –> Same as DB, but the SQL_BIND and SQL_TEXT columns are also populated.

XML-> Auditing is enabled, with all audit records stored as XML format OS files.

XML_EXTENDED –> Same as XML, but the SQL_BIND and SQL_TEXT columns are also populated.

OS -> Auditing is enabled, with all audit records directed to the operating system's file specified by AUDIT_FILE_DEST.

>>Syntax of audit command:

audit {statement_option|privilege_option} [by user] [by {session|access}] [whenever {successful|not successful}]

Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.

>>Auditing Enable in RAC DB:

Node-1

oracle@dcpdb1 [/home/oracle]$

oracle@dcpdb1 [/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 5 18:05:45 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL> alter system set audit_trail=DB, EXTENDED scope=spfile sid='*';

System altered.

SQL>

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 8.2309E+10 bytes

Fixed Size                  2214472 bytes

Variable Size            3.7581E+10 bytes

Database Buffers         4.4560E+10 bytes

Redo Buffers              165543936 bytes

Database mounted.

Database opened.

SQL>

SQL>

SQL>

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

SQL>

SQL>

SQL> select inst_name from v$active_instances;

INST_NAME

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

dcpdb1:dcpdb1

dcpdb2:dcpdb2

SQL>

SQL>

SQL>

SQL>

SQL> show parameter audit;

NAME                                 TYPE        VALUE

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

audit_file_dest                      string      /u02/app/oracle/admin/dcpdb/adump

audit_sys_operations                 boolean     FALSE

audit_syslog_level                   string

audit_trail                          string      DB, EXTENDED

SQL>

SQL>

SQL>

SQL>

SQL> audit all by bishwanath by access;

Audit succeeded.

SQL> audit select table,update table,insert table,delete table by bishwanath by access;

Audit succeeded.

SQL> audit all privileges by bishwanath by access;

Audit succeeded.

SQL> audit create user,alter user,drop user by bishwanath by access;

Audit succeeded.

SQL> audit execute procedure by bishwanath by access;

Audit succeeded.

SQL>

Node-2:

oracle@dcpdb2 [/home/oracle]$

oracle@dcpdb2 [/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 5 18:06:23 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 8.2309E+10 bytes

Fixed Size                  2214472 bytes

Variable Size            3.7044E+10 bytes

Database Buffers         4.5097E+10 bytes

Redo Buffers              165543936 bytes

Database mounted.

Database opened.

SQL>

SQL>

SQL>

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

SQL>

SQL>

SQL>

SQL>

SQL>

SQL>

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

SQL>

SQL>

SQL>

SQL>

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

SQL>

SQL>

SQL>

SQL>

SQL>

SQL>

SQL> show parameter audit;

NAME                                 TYPE        VALUE

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

audit_file_dest                      string      /u02/app/oracle/admin/dcpdb/ad

                                                 ump

audit_sys_operations                 boolean     FALSE

audit_syslog_level                   string

audit_trail                          string      DB, EXTENDED

SQL>

SQL>

SQL>

SQL>

SQL>

>>"bishwanath" DB USER Login and activities perform.

bishwanath@dcpdb1 [/home/bishwanath]$ sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 5 18:17:15 2016

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

Enter user-name: bishwanath

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL> select count(1) from nblt24.fbnk_stmt_entry;

  COUNT(1)

----------

  58247001

SQL>

SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME

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

dcpdb

drpdb

SQL>

>>"bishwanath" DB USER Activities monitor from SYS user.

SQL>

SQL> select username,to_char(timestamp,'dd/mm/rrrr hh24:mi:ss')dt,owner,obj_name,action_name,sql_text from dba_audit_trail where username='BISHWANATH' and trunc(timestamp)=trunc(sysdate) and action <105  order by timestamp desc;

USERNAME   DT                  OWNER           OBJ_NAME        ACTION_NAME     SQL_TEXT

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

BISHWANATH 05/09/2016 18:25:04 SYS             V_$DATAGUARD_CO SELECT          select * from v$dataguard_config

                                               NFIG

BISHWANATH 05/09/2016 18:25:04 SYS             V$DATAGUARD_CON SELECT          select * from v$dataguard_config

                                               FIG

BISHWANATH 05/09/2016 18:25:04 SYS             X$KRSTDGC       SELECT          select * from v$dataguard_config

BISHWANATH 05/09/2016 18:17:59 NBLT24          FBNK_STMT_ENTRY SELECT          select count(1) from nblt24.fbnk_stmt_entry

BISHWANATH 05/09/2016 18:17:31 SYSTEM          PRODUCT_PRIVS   SELECT          SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS W

                                                                               HERE   (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)

                                                                               ) AND   ((USER LIKE USERID) OR (USERID = 'PUB

                                                                               LIC')) AND   (UPPER(ATTRIBUTE) = 'ROLES')

BISHWANATH 05/09/2016 18:17:31                                 LOGON

BISHWANATH 05/09/2016 18:17:31 SYSTEM          SQLPLUS_PRODUCT SELECT          SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS W

                                               _PROFILE                        HERE   (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)

                                                                               ) AND   ((USER LIKE USERID) OR (USERID = 'PUB

                                                                               LIC')) AND   (UPPER(ATTRIBUTE) = 'ROLES')

BISHWANATH 05/09/2016 18:17:31 SYSTEM          PRODUCT_PRIVS   SELECT          SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VAL

                                                                               UE,DATE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE

                                                                                (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND

                                                                               (USER LIKE USERID)

BISHWANATH 05/09/2016 18:17:31 SYSTEM          SQLPLUS_PRODUCT SELECT          SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VAL

                                               _PROFILE                        UE,DATE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE

                                                                                (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND

                                                                               (USER LIKE USERID)

BISHWANATH 05/09/2016 18:17:31 SYS             DUAL            SELECT          SELECT USER FROM DUAL

BISHWANATH 05/09/2016 18:17:31 SYS             DUAL            SELECT          SELECT DECODE('A','A','1','2') FROM DUAL

11 rows selected.

SQL>

>> Disable Auditing

The NOAUDIT statement turns off the various audit options of Oracle. Use it to reset statement, privilege and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY USER option to specify a list of users to limit the scope of the statement and privilege audit options.

SQL> NOAUDIT;

SQL> NOAUDIT session;

SQL> NOAUDIT session BY bishwanath, scott;

SQL> NOAUDIT DELETE ON fbnk_account;

SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;

SQL> NOAUDIT ALL;

SQL> NOAUDIT ALL PRIVILEGES;

SQL> NOAUDIT ALL ON DEFAULT;