ORA-01105: mount is incompatible with mounts by other instances ORA-19808: recovery destination parameter mismatch

Please check both db_recovery_file_dest and  db_recovery_file_dest_size parameters . In my case db_recovery_file_dest_size is different . See the below details 


1)Instance 1

SQL> show parameter recovery

NAME                                        TYPE       VALUE
-------------- ---------------------- ----------- ------------------------------
db_recovery_file_dest                      string      +ARCH01_R5
db_recovery_file_dest_size                 big integer  50G

2)Instance 2

SQL> show parameter recovery

NAME                                        TYPE       VALUE
-------------- ---------------------- ----------- ------------------------------
db_recovery_file_dest                      string      +ARCH01_R5
db_recovery_file_dest_size                 big integer  40G

I have Started  the Instance 2 and changed the file_dest_size to 50G

Terminal> srvctl start instance -d DBname -i Instance_name

Terminal> sqlplus /nolog

 SQL>conn / as sysdba

SQL> alter system set db_recovery_file_dest_size =50G scope=both;

Then again i tried to start first instance and it is successfully started.

Terminal>srvctl start instance -d DBname -i Instance_name

Thanks,

Jyothish Balakrishnan

Schema Details

Sharing a simple query occupying some useful information about Oracle schema.

select a.username,a.created,a.profile,a.default_tablespace,a.account_status,
round(sum(b.bytes/1024/1024/1024),2) as "SIZE",c.ptime as "Last_password_change" 
from dba_users a,
dba_segments b,
sys.user$ c
 where a.username=b.owner
and a.username='JYOTHISH' and c.name=a.username and c.name=b.owner group by username,a.created,a.profile,a.default_tablespace,a.account_status,c.ptime;


Thanks,
Jyothish 

ORA-00214: control file '+DATA02_R5/fdmn1i/controlfile/current.265.799827461' version 6909395 inconsistent with file '+ARCH02_R5/fdmn1i/controlfile/current.11120.826208033' version 6909384

We already know about the reason but my overconfidence spoil around 1 hr for this error in RAC environment. Simple explanation is needed here '+DATA02_R5/fdmn1i/controlfile/current.265.799827461'
scn is 6909395 and other controlfile '+ARCH02_R5/fdmn1i/controlfile/current.11120.826208033' not able to write the changes after scn 6909384. So we need to need the recreate second
controfile as well. Please follow the below steps.

1)Add the Controlfile Entry Using spfile.


C:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 19 16:43:00 2013

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size                  2230600 bytes
Variable Size            1996490424 bytes
Database Buffers         1124073472 bytes
Redo Buffers               17231872 bytes
SQL> alter system set control_files ='+DATA02_R5/fdmn1i/controlfile/current.265.799827461' ,'+ARCH02_R5' scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup nomount

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     14
control_files                        string      +DATA02_R5/fdmn1i/controlfile/
                                                 current.265.799827461, +ARCH02
                                                 _R5
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

2)Restore controlfile using RMAN
rman target /


Recovery Manager: Release 11.2.0.2.0 - Production on Sat Oct 19 08:47:29 2013

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

connected to target database: FDMN1I (not mounted)

RMAN> restore controlfile from '+DATA02_R5/fdmn1i/controlfile/current.265.799827461';

Starting restore at 19-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1511 instance=FDMN1I1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA02_R5/fdmn1i/controlfile/current.265.799827461
output file name=+ARCH02_R5/fdmn1i/controlfile/current.10645.829212487
Finished restore at 19-OCT-13

RMAN> exit


Recovery Manager complete.
/ora001/g09104064 FDMN1I1]>sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Sat Oct 19 08:48:17 2013

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

SQL> conn / as sysdba
Connected.
SQL> alter system set control_files ='+DATA02_R5/fdmn1i/controlfile/current.265.799827461' ,'+ARCH02_R5/fdmn1i/controlfile/current.10645.829212487' scope=spfile;

System altered.

Please Dont use sqlplus here if you are already configured with Cluster Service

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Terminal>srvctl stop database -d FDMN1I -o immediate;
Terminal>srvctl  start database -d FDMN1I
Terminal>srvctl status database -d FDMN1I
Instance FDMN1I1 is running on node svr00085
Instance FDMN1I2 is running on node svr00086


Thanks,
Jyothish

ORA-1555 Snapshot Too old while a running job or query .

If we are getting this error frequently we need to think about the size of  undo tablespace and undo retention  time. Below query helped me to take optimum size of
undo tablespace and undo retention time.

                                                                        ACTUAL UNDO SIZE
OPTIMAL UNDO RETENTION =  ____________________________________
                                                       DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC


SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';



                     
OPTIMAL UNDO SIZE      = UNDO RETENTION*DB_BLOCK_SIZE*UNDO_BLOCK_PER_SEC

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *

       g.undo_block_per_sec) / (1024*1024) 

      "NEEDED UNDO SIZE [MByte]"

  FROM (

       SELECT SUM(a.bytes) undo_size

         FROM v$datafile a,

              v$tablespace b,

              dba_tablespaces c

        WHERE c.contents = 'UNDO'

          AND c.status = 'ONLINE'

          AND b.name = c.tablespace_name

          AND a.ts# = b.ts#

       ) d,

      v$parameter e,

       v$parameter f,

       (

       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

         undo_block_per_sec

         FROM v$undostat

       ) g

 WHERE e.name = 'undo_retention'

  AND f.name = 'db_block_size';

 
 
 
  Note:This above estimation undo size and retention are depends upon the calculations from v$undostat during the previous run. Please impliment this estimation carefully
  as per your requirment and resource avaialability.


Thanks,
Jyothish

Execution Time of an SQL using SQL_ID with respect to SNAP_ID

Here we can calculate Elapsed of time of an sql statement using sql id. Below query will figure out the Elapsed time in seconds.



col begin_time for a25
col end_time for a11
col inst for 99999
col snapid for 999999
set lines 200
set pages 20000
select snap_id snapid,
(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time
,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b where b.snap_id=a.snap_id and

a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time
,INSTANCE_NUMBER inst , PLAN_HASH_VALUE,
EXECUTIONS_DELTA Executions,
ROWS_PROCESSED_DELTA rows1,
round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,
round( ELAPSED_TIME_DELTA /1000000,0) elapsed
from wrh$_sqlstat a where sql_id in('&SQL_ID')
order by snap_id, INSTANCE_NUMBER;

Thanks,

Jyothish Balakrishnan

Archive Generation Details

Archive Generation per Hour

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
select trunc(COMPLETION_TIME,'HH') Hour,round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH') order by 1 ;

Archive Generation per Day

select trunc(COMPLETION_TIME,'DD') Day, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD') order by 1;

How to find out ASM file locations for a Database

column full_alias_path format a70
column file_type format a15


select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
       system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
              a.reference_index rindex , a.system_created, a.alias_directory,
              c.type file_type
       from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
       where a.group_number = b.group_number
             and a.group_number = c.group_number(+)
             and a.file_number = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     )
start with (mod(pindex, power(2, 24))) = 0
            and rindex in
                ( select a.reference_index
                  from v$asm_alias a, v$asm_diskgroup b
                  where a.group_number = b.group_number
                        and (mod(a.parent_index, power(2, 24))) = 0
                        and a.name = '&DATABASENAME'
                )
connect by prior rindex = pindex;


You can use this query with any Database running on the same server for a Database.

Drop Database Link in another schema .

We can drop Database link in another schema by a simple procedure without knowing password of the user

SQL> conn / as sysdba
SQL> create or replace procedure schema.procedurename as
begin
execute immediate  ‘drop database link  db_link_name’;
end procedurename;


SQL>exec Procedurename;

Oracle Multimedia (ORDIM) status is "LOADING" in DBA_REGISTRY

Oracle Version - 11g

Cause : Oracle Multimedia Registry Components are not properly configured .

sqlplus / as sysdab

SQL> COL COMP_NAME FOR A20
SQL> COL COMP_ID FOR A20
SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

COMP_ID              COMP_NAME            STATUS
-------------------- -------------------- --------
ORDIM                Oracle Multimedia    LOADING



Action

1) Reload Registry component


SQL>conn / as sysdba

SQL> execute sys.dbms_registry.loaded('ORDIM');

2) Validate Registry Status .


SQL> execute sys.dbms_registry.valid('ORDIM');


Now we can see the status is "VALID"


SQL> select comp_id,comp_name,status from dba_registry where comp_name='Oracle Multimedia';

COMP_ID              COMP_NAME            STATUS
-------------------- -------------------- --------
ORDIM                Oracle Multimedia    VALID

Drop user failed with ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist

Cause

Table system.aq$_internet_agent_privs is missing in "SYSTEM" Schema.



Action:

1. Check if system.aq$_internet_agent_privs exist.

       SQL> conn / as sysdba

       SQL> desc system.aq$_internet_agent_privs

2. Make sure that the default tablespace of SYSTEM user is properly set to SYSTEM tablespace.

        SQL> select default_tablespace from dba_users where   username=’SYSTEM’;

      DEFAULT_TABLESPACE
      ——————————
      SYSTEM

Run the below Script as sysdba user.

      SQL> conn / as sysdba
      SQL> @$ORACLE_HOME/rdbms/admin/catqueue.sql 
      SQL> exit


3. Then execute the DROP USER command again.

4.   SQL> Drop user Username;

      User Dropped

ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration

Cause : DB_UNIQUE_NAME is not in the Data Guard Configuration. We can check current DB_UNIQUE_NAME in the configuration.

SQL> show parameter log_archive_config;

The list of valid DB_ UNIQUE_NAMEs can be seen with the V$DATAGUARD_CONFIG view. This problem can also occur when specifying a non-standby destination with an DB_ UNIQUE_NAME attribute that does not match the DB_UNIQUE_NAME initialization parameter for the current instance.

Action:

Amend the   DB_UNIQUE_NAME in LOG_ARCHIVE_CONFIG parameter . Below example shows  physical standby database as ORCL_STBY

1) ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL, ORCL_STBY)';

2)  Re – enable Standby Archive Location Parameter;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE= ORCL_STBY  REOPEN=5 NOAFFIRM  ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= ORCL_STBY;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

3) Check that status of the  standby destination.

SQL>  select dest_name,status,error from v$archive_dest_status where dest_id=2;

Cheers !!!