Script for Compiling INVALID objects

SET ECHO        OFF
SET FEEDBACK    OFF
SET HEADING     OFF
SET LINESIZE    180
SET PAGESIZE    0
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

spool compile.sql

SELECT  'alter ' ||
       decode(object_type, 'PACKAGE BODY', 'package', object_type) ||
       ' ' ||
       object_name||
       ' compile' ||
       decode(object_type, 'PACKAGE BODY', ' body;', ';')
FROM   dba_objects
WHERE  status = 'INVALID'
/

spool off

SET ECHO        off
SET FEEDBACK    off
SET HEADING     off
SET LINESIZE    180
SET PAGESIZE    0
SET TERMOUT     on
SET TIMING      off
SET TRIMOUT     on
SET TRIMSPOOL   on
SET VERIFY      off

@compile

SET FEEDBACK    6
SET HEADING     ON

ORA-01031: insufficient privileges

cause for this particular case was
Somebody modified the sqlnet.ora file by putting an entry SQLNET.AUTHENTICATION_SERVICES = (NONE)

This entry is meant to enable one or more authentication services. If authentication has been installed,
then it is recommended that this parameter be set to either none or to one of the authentication methods.
none - for no authentication methods, including Microsoft Windows native operating system authentication. When SQLNET.AUTHENTICATION_SERVICES is set to none,a valid user name and password can be used to access the database.
all  - for all authentication methods.
nts  - for Microsoft Windows native operating system authentication.
Authentication Methods Available with Oracle Advanced Security:
kerberos5  - for Kerberos authentication.
radius - for RADIUS authentication.
tcps  - for SSL authentication.

After making the change  "SLAVE COMMUNICATION ERROR WITH ASM ; TERMINATING PROCESS " observed in the alert log and the database instances went down.,ASM status was intermediate.
Restarting the cluster brought everything up except the ASM instance which complained "Insufficient Privilege to start"
While trying to start the ASM manually "ORA-01031: insufficient privileges message" was thrown.

Finally removing "SQLNET.AUTHENTICATION_SERVICES=(NONE)" entry from the sqlnet.ora file saved the day!

SQLPLUS HANGING

      sqlplus connection might hang due to a number of reasons for instance when maximum number of processes specified by the parameter file reached, when the new sessions need to allocate some memory from shared pool while no memory is available to do so, etc
      If you cannot connect, you have two choices, either wait while the performance is degraded impacting the end users or immediately bounce the instance.

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 16:56:40 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-00020: maximum number of processes (100) exceeded
Enter user-name: ^C
$

To bounce the Instance:

1. We can directly kill the process using unix id

First list the process id for that particular instance like this
$ps -fe|grep pmon
oracle 2031994     1    0  Oct 22 -  4:18 ora_pmon_TESTDB

Kill that particular instance by its unix process id like this
$kill -9 2031994
 And then export the instance and startit up once again
$export ORACLE_SID=TESTDB
$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 16:59:43 2014

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

Conected to an idle instance.
SQL>startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 310379864 bytes
Database Buffers 306184192 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SQL>

2.Alternatively you can use the –prelim option 
-prelim option stands for "preliminary connection".

       When we log on normally including SYSDBA connections, the following 3 steps are completed
1. A new Oracle process is started (either by the listener or by local sqlplus if using the local BEQ connection)
2. The new process attaches to SGA shared memory segments (so it could access all the needed SGA structures)
3. The new process allocates process and session state objects and initializes new session structures in SGA

        But when we use the prelim option only the first two steps are completed. Since allocating memory is not a possible operation on shared memory structures that are protected by some sort of locks usually latches or Oracle KGX mutexes

$sqlplus -prelim / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 17:13:24 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> shutdown abort
ORACLE instance shut down.
SQL>startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 310379864 bytes
Database Buffers 306184192 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SQL>
 Bouncing the instance is only possible while it is affordable, What about for 24/7 databases,still there is a work around provided by oracle to run the ORADEBUG commands while you are connected to a running database using the prilim option
$sqlplus -prelim "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 14 17:33:09 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL> oradebug hanganalyze 3

Statement processed.

The command seems successful,However when looking into the resulting tracefile, we see this:

Processing Oradebug command 'HANGANALYZE 3'
====================================================================
HANG ANALYSIS:

ERROR: Can not perform hang analysis dump without a process
       state object and a session state object.
  ( process=(nil), sess=(nil) )

=====================================================================
We still can do this
SQL> oradebug setmypid
Statement processed.

SQL> oradebug hanganalyze 12
Hang Analysis in /u01/app/oracle/diag/rdbms/testdb/TESTDB_ora_1234.trc

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

LSNRCTL>lsnrctl status

Service "orcl" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...

SQL> conn scott/tiger@orcl

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

$rman target sys/password@PRODDB auxiliary sys/password@TESTDB

RMAN-04006: error from auxiliary database:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

SOLUTIONS

The best place to start with is to check the tnsnames.ora and listener.ora files and make sure that all is well.

1. By passing oracle Net authentication  using operating system authentication for auxiliary database.

$export ORACLE_SID=TESTDB
rman target sys/password@PRODDB auxiliary /
instead of 
rman target sys/password@PRODDB auxiliary sys/password@TESTDB

2. But if still Oracle Net authentication is required put UR=A entry in the tnsnames.ora file to take advantage of using restricted session privilege across the network.

3.  To statically configure service information for the listener put the following entry in the listener.ora file  
     (SID_DESC=
        (GLOBAL_DBNAME=TESTDB)
        (SID_NAME=TESTDB)
        (ORACLE_HOME=/oracle/base/dbhome01/)
      )

RMAN-05531: a mounted database cannot be duplicated while the datafiles are fuzzy

This error is observed while we are trying active database duplication from standby database while MRP process is still running and applying the changes.

RMAN-00571:====================================================
RMAN-00569:==========ERROR MESSAGE STACK FOLLOWS============
RMAN-00571:====================================================
RMAN-03002: failure of Duplicate Db command at 11/10/2014 17:03:07
RMAN-05501: aborting duplication of target database
RMAN-05531: a mounted database cannot be duplicated while datafiles are fuzzy

Recovery Manager complete

Bug 11715084Active duplicate should work when connected to Standby as source DB 
[ID 11715084.8]

While MRP is running, the datafiles in the standby database get continuously updated and RMAN active database duplication doesn't currently support the standby to be cloned while the standby database is in mounted mode and the datafiles are being continuously updated.This problem is fixed in 11.2.0.4 and on wards releases.

WORKAROUND 

follow these steps
1.Open the standby database(the target) in read only mode.
2.Stop MRP process
3.Proceed with the active database duplication

SWITCH OVER TO DISASTER RECOVERY DATABASE

Oracle switchover may be needed as an IT procedure to check the validity of the DR,For maintenance purpose or any other reasons. The following procedure might help to switchover to the Disaster Recovery Site in any case. Since switchover is planned unlike failover, we need to do the following precaution actions first.

PRECAUTIONS !

• Make sure that the full database backup is taken for safety just in case anything goes wrong during the switch over process and validate the backup taken.
• Make sure the backup encompasses all important files besides the data files and the archive logs like backup of control file, parameter files, etc.

INFORMATION 

primary database name PROD

primary database instance name PROD1

standby database name PROD

standby database instance name PRODSBY

production machine name RAC1

standby machine name RACSBY
Steps that has to be followed in the process of Switching over to Standby database


 1. Verify that both the primary and standby parameter files support role transition.

$ export ORACLE_SID=PROD1

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 21 07:46:45 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
THE INSTANCE NAME IS  PROD1
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
---------       --------------------         ----------------
PROD        READ WRITE           PRIMARY
$ export ORACLE_SID=PRODSBY
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon May 21 07:46:45 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
THE INSTANCE NAME IS  PRODSBY
SQL> select name,open_mode,database_role from v$database;
NAME      OPEN_MODE            DATABASE_ROLE
---------       --------------------         ----------------
PROD        READ WRITE           PHYSICAL STANDBY
2. Verify that there is network connectivity between the primary and standby
locations.
$ ping RAC1 from standby machine OS and should reply, alternatively ip can be used.
$ ping RACSBY from production machine OS again should reply

3. Each location in the Data Guard configuration should have connectivity through Oracle Net to the primary database and to all associated standby databases. This can be checked by
tnsping  PROD ..........from the standby
nslookup RAC1 ........from the standby
4. Convert the primary database to the new standby
 alter database commit to switchover to physical standby;
5. Shutdown the former primary and mount as a standby database:
shutdown immediate
startup nomount
alter database mount standby database;
6. Defer the remote archive destination on the old primary
alter system set log_archive_dest_state_2=defer;
7. Verify that the physical standby can be converted to the new primary
select switchover_status from v$database;
8. Convert the physical standby to the new primary
alter database commit to switchover to primary;
9. Shutdown and startup the new primary
Connect as ‘oracle’ user and’ PRODSBY’ ORACLE_SID
shutdown immediate
startup
10. Enable remote archiving on the new primary to the new standby
alter system set log_archive_dest_state_2=enable;
11. Start managed recover on the new standby database
recover managed standby database disconnect;
12. Check the gap between the new standby and new primary

TOP SEGMENTS IN ORACLE DATABASE BY SIZE

The following script lists out the top segments in your database by size grouping by segment type. 



COLUMN segment_type          FORMAT A20                HEADING 'Segment_Type'
COLUMN owner                        FORMAT A15                HEADING 'Owner'
COLUMN segment_name        FORMAT A30                HEADING 'Segment_Name'
COLUMN partition_name         FORMAT A30                HEADING 'Partition_Name'
COLUMN tablespace_name     FORMAT A20                HEADING 'Tablespace_Name'
COLUMN bytes               FORMAT 9,999,999,999,999  HEADING 'Size (in bytes)'
COLUMN extents             FORMAT 999,999,999        HEADING 'Extents'

BREAK ON segment_type SKIP 1

COMPUTE sum OF bytes ON segment_type

SELECT
    a.segment_type      segment_type
  , a.owner             owner
  , a.segment_name      segment_name
  , a.partition_name    partition_name
  , a.tablespace_name   tablespace_name
  , a.bytes             bytes
  , a.extents           extents
FROM
    (select
         b.segment_type
       , b.owner
       , b.segment_name
       , b.partition_name
       , b.tablespace_name
       , b.bytes
       , b.extents
     from
         dba_segments b
     order by
         b.bytes desc
    ) a
WHERE
    rownum < 101
ORDER BY
    segment_type, bytes desc, owner, segment_name
/

Segment_Type         Owner    Segment_Name     Partition_Name      Tablespace_Name     Bytes          Extents
-----------------               --------    ---------------------    ----------------------     -------------------------     --------           ----------
INDEX                         xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
LOB PARTITION      xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
LOBSEGMENT         xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
TABLE               xxx          xxxxxxxx            xxxxxxxx               xxxxxxxx                 xxxx               xxxxx
TABLE PARTITION xxx         xxxxxxxx           xxxxxxxx               xxxxxxxx                 xxxx               xxxxx