ORA-00603 ORA-00600 + [kqlidchg0] ORA-00604 ORA-00001

Today we've encountered the below error, it seems to be,

============Alert log==============
Mon Aug 25 11:08:50 2014
Errors in file /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/trace/SID_NAME_ora_40108088.trc  (incident=19385):
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
Incident details in: /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/incident/incdir_19385/SID_NAME_ora_40108088_i19385.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/trace/SID_NAME_ora_40108088.trc  (incident=19386):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
Incident details in: /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/incident/incdir_19386/SID_NAME_ora_40108088_i19386.trc
Mon Aug 25 11:08:56 2014

============Trace file=============
Incident 19385 created, dump file: /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/incident/incdir_19385/SID_NAME_ora_40108088_i19385.trc
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
Incident 19386 created, dump file: /u01/app/oracle/diag/rdbms/SID_Server/SID_NAME/incident/incdir_19386/SID_NAME_ora_40108088_i19386.trc
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG
kgefec: fatal error 0
opitsk: network error occurred while two-task server trying tosend break; error code = 24365

Clause:

There are dependent objects in an invalid state.

Identify:

Please run the following query and review the results.

SQL> SET LINESIZE 100

SQL> SELECT object_id, object_name, object_type, owner, status 

     FROM   dba_objects 

     WHERE  object_name IN (SELECT symrep 

                            FROM   plscope_identifier$ 

                            WHERE  obj# IN (SELECT obj# 

                                            FROM   plscope_identifier$ 

                                            MINUS  

                                            SELECT object_id 

                                            FROM   dba_objects));

 

Solutions:

 

1. Compile the invalid objects to make it as valid or

2.  Drop the invalid objects.
3.  Purge the recyclebin to clear the dropped objects.
4.  Drop the table that was failing to drop.

ORA-01565: error in identifying file / ORA-27037: unable to obtain file status.

ORA-01565: error in identifying file / ORA-27037: unable to obtain file status.
When trying to create pfile from spfile getting below error.

ORA-01565: Unable to open Spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfile$SID.ora.

SQL> create pfile='/d01/oracle/initsetting.ora' from spfile;
create pfile='/d01/oracle/initsetting.ora' from spfile

ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Cause: Database is not running with spfile,It is running with memory.
issue seen  while doing rman duplicate clone or Unexpectedly spfile/pfile dropped/missing while database is up and running fine..

Solution:-

SQL> create pfile='/d01/oracle/initsetting.ora' from memory;

File created.

Generate DDL scripts using DBMS_METADATA.GET_DDL

 

Use DBMS_METADATA.GET_METADATA to copy the user scripts from one database to another database.
First, we will take the DDL scripts of the tablespace using the following command:

SQL> set head off echo off
SQL> select 'select dbms_metadata.get_ddl(''TABLESPACE'','''||  tablespace_name   || ''') from dual;' from dba_tablespaces;
select dbms_metadata.get_ddl('TABLESPACE','SYSTEM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','SYSAUX') from dual;
select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') from dual;
select dbms_metadata.get_ddl('TABLESPACE','TEMP') from dual;
select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','TOOLS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','RDPSDT01') from dual;

SQL> select dbms_metadata.get_ddl('TABLESPACE','RDPSDT01') from dual;  
  CREATE TABLESPACE "RDPSDT01" DATAFILE 
  '/apps/oradata/RAIMUMDI2/RDPSDT01/RDPSDT01_01.dbf' SIZE 4294967296, 
  '/apps/oradata/RAIMUMDI2/RDPSDT01/RDPSDT01_02.dbf' SIZE 4294967296   
  AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M   
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192   
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  
  SEGMENT SPACE MANAGEMENT AUTO

Now, Get the script for the user creation also for the roles & object grant & system grants provided to user.

set head off
set pages 0
set long 9999999
 15:26:43 SYS > select  dbms_metadata.get_ddl('USER', 'OPS$RPO') || '/' usercreate from DUAL;
 USERCREATE
----------------------------------------------------------------------
 CREATE USER "OPS$RPO" IDENTIFIED BY VALUES 'S:C952B1BD452B5E63692668680C22A8F
7C8E9F26557E808DADB396E85ADBF;01F95BD48BDDF041'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE/

Get all their roles and grants as well

15:27:05 SYS > SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','OPS$RPO') FROM DUAL;
 DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
  GRANT "CONNECT" TO "OPS$RPO"
  GRANT "RESOURCE" TO "OPS$RPO"
  GRANT "DBA" TO "OPS$RPO"
  GRANT "IVISION_USER" TO "OPS$RPO"

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','ADMIN') FROM DUAL;
 15:27:34 SYS > SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','OPS$RPO') FROM DUAL;
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
 GRANT EXECUTE ANY PROCEDURE TO "OPS$RPO"
 GRANT ALTER ANY PROCEDURE TO "OPS$RPO"
 GRANT CREATE PROCEDURE TO "OPS$RPO"
 GRANT GRANT ANY ROLE TO "OPS$RPO"
 GRANT DROP PUBLIC SYNONYM TO "OPS$RPO"
 GRANT CREATE PUBLIC SYNONYM TO "OPS$RPO"
 GRANT SELECT ANY TABLE TO "OPS$RPO"
 GRANT CREATE TABLE TO "OPS$RPO"
 GRANT DROP USER TO "OPS$RPO"
 GRANT ALTER USER TO "OPS$RPO"
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
 GRANT CREATE USER TO "OPS$RPO"
 GRANT UNLIMITED TABLESPACE TO "OPS$RPO"
 GRANT CREATE SESSION TO "OPS$RPO"

15:27:51 SYS > SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','OPS$RPO') FROM DUAL;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','OPS$RPO')
--------------------------------------------------------------------------------
 GRANT EXECUTE, DEBUG ON "SYS"."DBMS_PIPE" TO "OPS$RPO"
 GRANT EXECUTE ON "OPS$VTF"."DBK_C_ERR" TO "OPS$RPO"

You can use the following scripts to get the DDL scripts

SELECT 'SELECT DBMS_METADATA.GET_DDL(''TABLESPACE'','''||  TABLESPACE_NAME || ''') FROM DUAL;' FROM DBA_TABLESPACES;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','ADMIN_TBS')  FROM DUAL;
SELECT 'SELECT DBMS_METADATA.GET_DDL(''USER'','''||  USERNAME || ''') FROM DUAL;' FROM DBA_USERS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','ADMIN') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','ADMIN') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','ADMIN') FROM DUAL;
SELECT 'SELECT DBMS_METADATA.GET_DDL(''TABLE'','''||  TABLE_NAME|| ''') FROM DUAL;' FROM DBA_TABLES;
SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM all_db_links a;

ORA-00600 Metalink Notes

ORA - 600 -- Look at these Metalink notes for Help

ORA-600 Errors 1 to 3000
Note 138300.1 "ORA-600 [105]"
Note 263295.1 "ORA-600 [106]"
Note 28104.1 "ORA-600 [504]"
Note 138871.1 "ORA-600 [510]"
Note 138888.1 "ORA-600 [525]"
Note 138939.1 "ORA-600 [711]"
Note 39308.1 "ORA-600 [723]"
Note 31056.1 "ORA-600 [729]"
Note 131490.1 "ORA-600 [733]"
Note 217860.1 "ORA-600 [1013]"
Note 138123.1 "ORA-600 [1100]"
Note 41767.1 "ORA-600 [1113]"
Note 40514.1 "ORA-600 [1114]"
Note 137262.1 "ORA-600 [1115]"
Note 66387.1 "ORA-600 [1158]"
Note 138354.1 "ORA-600 [1236]"
Note 28045.1 "ORA-600 [2103]"
Note 28929.1 "ORA-600 [2662]"
Note 31057.1 "ORA-600 [2845]"
Note 138733.1 "ORA-600 [2865]"

ORA-600 Errors 3001 to 6000
Note 30866.1 "ORA-600 [3020]"
Note 93665.1 "ORA-600 [3668]"
Note 47456.1 "ORA-600 [4000]"
Note 96642.1 "ORA-600 [4036]"
Note 43914.1 "ORA-600 [4137]"
Note 28226.1 "ORA-600 [4146]"
Note 39282.1 "ORA-600 [4193]"
Note 39283.1 "ORA-600 [4194]"
Note 138822.1 "ORA-600 [4400]"
Note 29702.1 "ORA-600 [4414]"
Note 138836.1 "ORA-600 [4454]"
Note 39553.1 "ORA-600 [4511]"
Note 73455.1 "ORA-600 [4512]"
Note 27955.1 "ORA-600 [4519]"
Note 204536.1 "ORA-600 [4820]"
Note 41840.1 "ORA-600 [4882]"

ORA-600 Errors 6001 to 9000
Note 47449.1 "ORA-600 [6002]"
Note 116552.1 "ORA-600 [6034]"
Note 40640.1 "ORA-600 [6101]"
Note 99300.1 "ORA-600 [6122]"
Note 138913.1 "ORA-600 [6193]"
Note 39399.1 "ORA-600 [6731]"
Note 41719.1 "ORA-600 [6856]"

ORA-600 Errors 9001 to 15000
Note 138325.1 "ORA-600 [12209]"
Note 33174.1 "ORA-600 [12235]"
Note 138332.1 "ORA-600 [12261]"
Note 138340.1 "ORA-600 [12304]"
Note 35928.1 "ORA-600 [12333]"
Note 28229.1 "ORA-600 [12700]"
Note 67496.1 "ORA-600 [13009]"
Note:28185.1 "ORA-600 [13013]"

ORA-600 Errors 15001 to 17000
Note 138428.1 "ORA-600 [15160]"
Note 138430.1 "ORA-600 [15201]"
Note 138431.1 "ORA-600 [15203]"
Note 131186.1 "ORA-600 [15212]"
Note 137266.1 "ORA-600 [15419]"
Note 216277.1 "ORA-600 [15456]"
Note 138457.1 "ORA-600 [15709]"
Note 67490.1 "ORA-600 [15851]"
Note 76528.1 "ORA-600 [15868]"
Note 138499.1 "ORA-600 [16201]"
Note 106607.1 "ORA-600 [16365]"
Note 138523.1 "ORA-600 [16515]"
Note 138526.1 "ORA-600 [16607]"

ORA-600 Errors 17001 to 30000
Note 138537.1 "ORA-600 [17003]"
Note 138541.1 "ORA-600 [17012]"
Note 41472.1 "ORA-600 [17034]"
Note 138554.1 "ORA-600 [17059]"
Note 39616.1 "ORA-600 [17069]"
Note 29616.1 "ORA-600 [17090]"
Note 138565.1 "ORA-600 [17099]"
Note 47411.1 "ORA-600 [17112]"
Note 39453.1 "ORA-600 [17113]"
Note 34782.1 "ORA-600 [17114]"
Note 138576.1 "ORA-600 [17128]"
Note 138580.1 "ORA-600 [17147]"
Note 34781.1 "ORA-600 [17148]"
Note 138586.1 "ORA-600 [17172]"
Note 263110.1 "ORA-600 [17175]"
Note 34779.1 "ORA-600 [17182]"
Note 45725.1 "ORA-600 [17271]"
Note 138597.1 "ORA-600 [17274]"
Note 134139.1 "ORA-600 [17280]"
Note 39361.1 "ORA-600 [17281]"
Note 138602.1 "ORA-600 [17285]"
Note 138621.1 "ORA-600 [17585]"
Note 138640.1 "ORA-600 [18209]"
Note 216278.1 "ORA-600 [18261]"
Note 138678.1 "ORA-600 [20084]"
Note 100073.1 "ORA-600 [25012]"

ORA-600 Errors kc
Note 138981.1 "ORA-600 [kcbgcur_2]"
Note 70097.1 "ORA-600 [kcbgcur_3]"
Note 114058.1 "ORA-600 [kcbgcur_9]"
Note 138990.1 "ORA-600 [kcbgtcr_4]"
Note 138991.1 "ORA-600 [kcbgtcr_5]"
Note 261264.1 "ORA-600 [kcbgtcr]"
Note 248874.1 "ORA-600 [kcbgtcr_6]"
Note 233612.1 "ORA-600 [kcbgtcr_12]"
Note 204512.1 "ORA-600 [kcbnew_3]"
Note 216104.1 "ORA-600 [kcbrbo1]"
Note 139011.1 "ORA-600 [kcbzpb_1]"
Note 139012.1 "ORA-600 [kcbzpb_2]"
Note 229467.1 "ORA-600 [kcbzwb_4]"
Note 139013.1 "ORA-600 [kccsbck_first]"
Note 216108.1 "ORA-600 [kcllcu_0]"
Note 263225.1 "ORA-600 [kclwcrs_15]"
Note 76434.1 "ORA-600 [kcoapl_blkchk]"
Note 248718.1 "ORA-600 [kcratr1_lostwrt]"

ORA-600 Errors kd to kw
Note 139037.1 "ORA-600 [kdddgb2]"
Note 139042.1 "ORA-600 [kdisle:nrows]"
Note 139051.1 "ORA-600 [kghalo2]"
Note 139052.1 "ORA-600 [kghasp1]"
Note 139066.1 "ORA-600 [kghxhdr1]"
Note 229809.1 "ORA-600 [kgliep_1]"
Note 66501.1 "ORA-600 [kkrqtab2]"
Note 139095.1 "ORA-600 [kkslgop1]"
Note 139116.1 "ORA-600 [kohdtf048]"
Note 264061.1 "ORA-600 [kqludp2]"
Note 139162.1 "ORA-600 [kssrmp1]"
Note 247822.1 "ORA-600 [ksmals]"
Note 139153.1 "ORA-600 [ksmguard2]"
Note 233864.1 "ORA-600 [kteuproptime-2]"
Note 139180.1 "ORA-600 [ktsgsp5]"
Note 139193.1 "ORA-600 [kttmupd_segment-]"
Note 228480.1 "ORA-600 [kwqitnmptme:read]"
Note 228480.1 "ORA-600 [kwqitnmptme:ready]"
Note 228364.1 "ORA-600 [kwqitnmptme:wait]"

ORA-600 Errors q to z
Note 248095.1 "ORA-600 [qctcte1]"
Note 216273.1 "ORA-600 [qctstc2o1]"
Note 209363.1 "ORA-600 [qerrmofbu1]"
Note 237598.1 "ORA-600 [qertqtableallocate2]"
Note 226887.1 "ORA-600 [qkagby4]"
Note 222876.1 "ORA-600 [qmxiunppacked2]"
Note 244365.1 "ORA-600 [rworupo.1]"
Note 139263.1 "ORA-600 [srsget_1]"
Note 260951.1 "ORA-600 [ttcgcshnd]"
Note 216452.1 "ORA-600 [ttcgcshnd-1]"
Note 216453.1 "ORA-600 [ttcgcshnd-2]"

How to start/stop RAC componants

How to Stop/Start RAC components
In this article, I am going to explain how to stop/start rac components. This is my environment:

My db version : 11.2.0.3
My Operating System : AIX 6.1
My servers hostname : node1-node2
My database name : TEST
My instance name : TEST1-TEST2

Here is the some basic  commands, for commands details&options  please review Reference docs:


$$CRS_HOME/bin/crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   763e07f95ac84ff6bf7132f672c5c47e (/dev/voting01) [OCR_VOTE]
 2. ONLINE   2dc5840f8eeb4f6bbfe624bdf3f2c4a2 (/dev/voting02) [OCR_VOTE]
 3. ONLINE   8422c3282a1f4f55bfa6b1242ff7b967 (/dev/voting03) [OCR_VOTE]

$$CRS_HOME/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2808
         Available space (kbytes) :     259312
         ID                       :  597557072
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user

Checking CRS Status
[oracle@node1] $ORACLE_HOME/bin/crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[oracle@node2] $ORACLE_HOME/bin/crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

Checking Node Status

[oracle@node1] $ORACLE_HOME/bin/srvctl status nodeapps

VIP node1-vip is enabled
VIP node1-vip is running on node: node1
VIP 192.168.100.101 is enabled
VIP 192.168.100.101 is running on node: node2
Network is enabled
Network is running on node: node1
Network is running on node: node2
GSD is disabled
GSD is not running on node: node1
GSD is not running on node: node2
ONS is enabled
ONS daemon is running on node: node1
ONS daemon is running on node: node2

[oracle@node2] $ORACLE_HOME/bin/srvctl status nodeapps

VIP node1-vip is enabled
VIP node1-vip is running on node: node1
VIP 192.168.100.101 is enabled
VIP 192.168.100.101 is running on node: node2
Network is enabled
Network is running on node: node1
Network is running on node: node2
GSD is disabled
GSD is not running on node: node1
GSD is not running on node: node2
ONS is enabled
ONS daemon is running on node: node1
ONS daemon is running on node: node2

Checking Clusterware Resource Status
[oracle@node1]$ cd $CRS_HOME/bin
[oracle@node1]$ ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME                    TARGET  STATE        SERVER        STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA1.dg ONLINE  ONLINE       node1
                ONLINE  ONLINE       node2
ora.LISTENER.lsnr       ONLINE  ONLINE       node1
                ONLINE  ONLINE       node2
ora.OCR_VOTE.dg ONLINE  ONLINE       node1
             ONLINE  ONLINE       node2
ora.asm                 ONLINE  ONLINE       node1          Started
                        ONLINE  ONLINE       node2          Started
ora.gsd        OFFLINE OFFLINE      node1
                OFFLINE OFFLINE      node2
ora.net1.network        ONLINE  ONLINE       node1
                ONLINE  ONLINE       node2
ora.ons        ONLINE  ONLINE       node1
                ONLINE  ONLINE       node2
ora.registry.acfs ONLINE  ONLINE       node1
                ONLINE  ONLINE       node2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr     1        ONLINE  ONLINE       node2
ora.LISTENER_SCAN2.lsnr     1        ONLINE  ONLINE       node1
ora.LISTENER_SCAN3.lsnr     1        ONLINE  ONLINE       node1
ora.cvu        1        ONLINE  ONLINE       node1
ora.oc4j    1        ONLINE  ONLINE       node1
ora.scan1.vip    1        ONLINE  ONLINE       node2
ora.scan2.vip    1        ONLINE  ONLINE       node1
ora.scan3.vip      1        ONLINE  ONLINE       node1
ora.node1.vip    1        ONLINE  ONLINE       node1
ora.node2.vip    1        ONLINE  ONLINE       node2

You can use below command which is not recommended for 11g and which is depreciated

[oracle@node1]crs_stat -t
Name Type Target State Host
————————————————————
ora….DATA.dg ora….up.type ONLINE ONLINE node1
ora….ER.lsnr ora….er.type ONLINE ONLINE node1
ora….N1.lsnr ora….er.type ONLINE ONLINE node1
ora….N2.lsnr ora….er.type ONLINE ONLINE node2
ora.ORADATA.dg ora….up.type ONLINE ONLINE node1
ora.asm ora.asm.type ONLINE ONLINE node1
ora.cvu ora.cvu.type ONLINE ONLINE node2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora….SM1.asm application ONLINE ONLINE node1
ora….11.lsnr application ONLINE ONLINE node1
ora….b11.gsd application OFFLINE OFFLINE
ora….b11.ons application ONLINE ONLINE node1
ora….b11.vip ora….t1.type ONLINE ONLINE node1
ora….SM2.asm application ONLINE ONLINE node2
ora….12.lsnr application ONLINE ONLINE node2
ora….b12.gsd application OFFLINE OFFLINE
ora….b12.ons application ONLINE ONLINE node2
ora….b12.vip ora….t1.type ONLINE ONLINE node2
ora….network ora….rk.type ONLINE ONLINE node1
ora.oc4j ora.oc4j.type ONLINE ONLINE node2
ora.ons ora.ons.type ONLINE ONLINE node1
ora.TEST.db ora….se.type ONLINE ONLINE node1
ora….int.svc ora….ce.type ONLINE ONLINE node2
ora….int.svc ora….ce.type ONLINE ONLINE node2
ora….kis.svc ora….ce.type ONLINE ONLINE node2
ora….est.svc ora….ce.type ONLINE ONLINE node1
ora….ry.acfs ora….fs.type ONLINE ONLINE node1
ora.scan1.vip ora….ip.type ONLINE ONLINE node1
ora.scan2.vip ora….ip.type ONLINE ONLINE node2

Oracle High Availability Services

– disable/enable Oracle HAS.
Use the “crsctl enable/disable has” command to disable automatic startup of the Oracle High Availability Services stack when the server boots up.

To can see current settings for Oracle High Availability Services stack when the server boots up, follow:

[root@node1]crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.

or

[root@node1]cat /etc/oracle/scls_scr/node1/root/ohasdstr
enable

So as you can see my current setting is enable.If your system shown disable than :

For Disable:
[root@node1]crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.

[root@node1] crsctl config has
CRS-4621: Oracle High Availability Services autostart is disabled.

# cat /etc/oracle/scls_scr/node1/root/ohasdstr
disable

For Enable:
[root@node1]crsctl enable has
CRS-4621: Oracle High Availability Services autostart is enabled.

Check new setting:

[root@node1] crsctl config has
CRS-4621: Oracle High Availability Services autostart is enabled.

[root@node1] cat /etc/oracle/scls_scr/node1/root/ohasdstr
enable

Stop the Oracle clusterware stack

You can use below commands:
With root user:

crsctl stop crs or crsctl stop has

[root@node1]crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘node1′
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘node1′
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘node1′
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN2.lsnr’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.TEST.db’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN3.lsnr’ on ‘node1′
CRS-2677: Stop of ‘ora.LISTENER_SCAN2.lsnr’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.scan2.vip’ on ‘node1′
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.node1.vip’ on ‘node1′
CRS-2677: Stop of ‘ora.LISTENER_SCAN3.lsnr’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.scan3.vip’ on ‘node1′
CRS-2677: Stop of ‘ora.node1.vip’ on ‘node1′ succeeded
CRS-2672: Attempting to start ‘ora.node1.vip’ on ‘node2′
CRS-2677: Stop of ‘ora.scan2.vip’ on ‘node1′ succeeded
CRS-2672: Attempting to start ‘ora.scan2.vip’ on ‘node2′
CRS-2677: Stop of ‘ora.scan3.vip’ on ‘node1′ succeeded
CRS-2672: Attempting to start ‘ora.scan3.vip’ on ‘node2′
CRS-2676: Start of ‘ora.node1.vip’ on ‘node2′ succeeded
CRS-2677: Stop of ‘ora.TEST.db’ on ‘node1′ succeeded
CRS-2676: Start of ‘ora.scan2.vip’ on ‘node2′ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN2.lsnr’ on ‘node2′
CRS-2676: Start of ‘ora.scan3.vip’ on ‘node2′ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN3.lsnr’ on ‘node2′
CRS-2676: Start of ‘ora.LISTENER_SCAN2.lsnr’ on ‘node2′ succeeded
CRS-2676: Start of ‘ora.LISTENER_SCAN3.lsnr’ on ‘node2′ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.eons’ on ‘node1′
CRS-2677: Stop of ‘ora.ons’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘node1′
CRS-2677: Stop of ‘ora.net1.network’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.eons’ on ‘node1′ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘node1′ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.cssdmonitor’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘node1′
CRS-2677: Stop of ‘ora.cssdmonitor’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.mdnsd’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.gpnpd’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘node1′
CRS-2677: Stop of ‘ora.cssd’ on ‘node1′ succeeded
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘node1′
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘node1′
CRS-2677: Stop of ‘ora.gipcd’ on ‘node1′ succeeded
CRS-2677: Stop of ‘ora.diskmon’ on ‘node1′ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘node1′ has completed
CRS-4133: Oracle High Availability Services has been stopped.

Start the Oracle clusterware stack

You can use below commands:
With root user:
crsctl start crs or crsctl start has

[root@node1] crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

Start the Oracle Database
To start all Oracle RAC instances for a database:
[oracle@node1]
$ORACLE_HOME/bin/srvctl start database -d db_name

PS: db_name is the name of the database this command is starting all the instances

Stop the Oracle Database
To shut down all Oracle RAC instances for a database:
[oracle@node1]
$ORACLE_HOME/bin/srvctl stop database -d db_name
PS: db_name is the name of the databasethis command is starting all the instances

Start the Oracle Instance:

[oracle@node1]
$ORACLE_HOME/bin/srvctl start database -d db_name -i instance_name

Stop the Oracle Instance:

[oracle@node1]
$ORACLE_HOME/bin/srvctl stop database -d db_name -i instance_name
Stop/Start Listener-SCAN_LISTENER

srvctl stop/start listener -n node1
srvctl stop/start listener -n node2
srvctl stop scan_listener

Stop ASM

srvctl stop asm [-o stop_options] [-f]
srvctl stop asm -n node1

How to simulate block corruption and do RMAN block recovery

RMAN offers a blockrecover command to recover from a block corruption.Otherwise we would have to restore the entire datafile and thereby recover it.

Lets simulate block media recovery

1) Corrupt datafile users.dbf for instance

$ dd if=/dev/zero of=/u/oracle/oradata/test/users.dbf bs=8k conv=notrunc seek=20 count=1

2) verify the block corruption using dbverify utility:

$dbv file=/u/oracle/oradata/test/users.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Tue Jul 20 16:21:06 2010

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

DBVERIFY - Verification starting : FILE = /u/oracle/oradata/test/users.dbf
Page 20 is marked corrupt
Corrupt block relative dba: 0x01000014 (file 4, block 20)
Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined         : 131072
Total Pages Processed (Data) : 87614
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 5449
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1449
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 36559
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 301640 (0.301640)

Block 20 of datafile 4 is corrupted.

3) Lets do RMAN block recovery using blockrecover command.

RMAN> blockrecover datafile 4 block 20;

Starting blockrecover at 20-JUL-10
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u1/flash_recovery_area/test/backupset/2010_07_16/o1_mf_nnndf_TAG20100716T181457_640o2b16_.bkp
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u1/flash_recovery_area/test/backupset/2010_07_16/o1_mf_nnndf_TAG20100716T181457_640o2b16_.bkp tag=TAG20100716T181457
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:45

starting media recovery

archive log thread 1 sequence 17 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_17/o1_mf_1_17_642q4zbv_.arc
archive log thread 1 sequence 18 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_19/o1_mf_1_18_647vt1ps_.arc
archive log thread 1 sequence 19 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_19/o1_mf_1_19_647vvqrz_.arc
archive log thread 1 sequence 20 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_20/o1_mf_1_20_64byfgff_.arc
archive log thread 1 sequence 21 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_20/o1_mf_1_21_64byfgjf_.arc
archive log thread 1 sequence 22 is already on disk as file /u1/flash_recovery_area/test/archivelog/2010_07_20/o1_mf_1_22_64byfg8f_.arc
media recovery complete, elapsed time: 00:00:05
Finished blockrecover at 20-JUL-10

Recover Standby database With Missing Archivelogs on Dataguard setup

After stoping dataguard service for some maintanence purposes, some of the archive logs got lost on primary database that were produced meantime. RMAN incremental backup was used to recover dataguard and resyncronize. Primary and standby databases don't use same RMAN catalog, so the backupset that is taken from primary backup needed to transfer and register on the standby side. Also there have been some datafile creations on primary side that were not applied on standby. Because of this, we needed to re-create the control file on standby and transfer the newly created datafiles from primary side. Here is a detailed article about this recovery process.

1-determine last SCN on standby db

PRIMARY

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

-----------

3360225821

STANDBY

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

-----------

3215410716

2-Stop log apply and transport services.

2.1 stop redo sent on primary

alter system set log_archive_dest_state_2 ='defer' scope=both ;

2.2 stop redo apply on standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3-Backup primary database incremental ; from SCN last applied on standby db.

--for faster backup try with multi channel

run {

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

allocate channel ch3 device type disk;

allocate channel ch4 device type disk;

allocate channel ch5 device type disk;

allocate channel ch6 device type disk;

BACKUP INCREMENTAL FROM SCN 3215410716 DATABASE FORMAT '/intl_migration/cdrdb/backup/tmpForStandby_%U' tag 'FORSTANDBY';

release channel ch1;

release channel ch2;

release channel ch3;

release channel ch4;

release channel ch5;

release channel ch6;

}

4-Transfer backup sets to standby side.

because the incremental backup was 1 TB size ; I needed to seperate under different mount points.

Don't worry about keeping them in different folders. We will register them.

SOURCE FOLDERS

/intl_migration/cdrdb/backup/

DEST FOLDER

/medftp/backupDG

/app3/backupDG/

bin

prompt

lcd /intl_migration/cdrdb/backup/

cd /medftp/backupDG

mput tmpForStandby_rkk2lbe4_1_1 tmpForStandby_rlk2lbe5_1_1 tmpForStandby_rmk2lbe7_1_1 tmpForStandby_rnk2lbe9_1_1 tmpForStandby_rok2lbeb_1_1 tmpForStandby_rpk2lbed_1_1 tmpForStandby_rqk2m14c_1_1 tmpForStandby_rrk2m19j_1_1 tmpForStandby_rsk2m2bt_1_1 tmpForStandby_rtk2m2eu_1_1 tmpForStandby_ruk2m2km_1_1 tmpForStandby_rvk2m3m2_1_1

bin

prompt

lcd /intl_migration/cdrdb/backup/

cd /app3/backupDG/

mput tmpForStandby_s0k2mmu4_1_1 tmpForStandby_s1k2mn2d_1_1 tmpForStandby_s2k2mnlu_1_1 tmpForStandby_s3k2mnut_1_1 tmpForStandby_s4k2mob3_1_1 tmpForStandby_s5k2moee_1_1 tmpForStandby_s6k2nc22_1_1 tmpForStandby_s7k2ncda_1_1 tmpForStandby_s8k2nd6s_1_1 tmpForStandby_s9k2ne6n_1_1 tmpForStandby_sak2ne8i_1_1 tmpForStandby_sbk2nf3c_1_1 tmpForStandby_ssk2o28c_1_1

5-Register backup sets to stanby db

OnStandby db

rman target /

RMAN> CATALOG START WITH '/app3/backupDG/tmpForStandby';

RMAN> CATALOG START WITH '/medftp/backupDG/tmpForStandby';

6-Recover standby db ;

one important note ;

because this is a backup taken for only phisical standby db sync ; noredo key word is required.

See : http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#sthref955

RMAN>

run {

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

allocate channel ch3 device type disk;

allocate channel ch4 device type disk;

allocate channel ch5 device type disk;

allocate channel ch6 device type disk;

allocate channel ch7 device type disk;

allocate channel ch8 device type disk;

RECOVER DATABASE NOREDO;

release channel ch1;

release channel ch2;

release channel ch3;

release channel ch4;

release channel ch5;

release channel ch6;

release channel ch7;

release channel ch8;

}

7-Create new standby control file

Before re-starting log apply service on standby db; create a new standby controlfile in primary db , copy it to standby .Creating a new controlfile is my suggestion because during non transferred and applied logs ; some chages may be done affecting controlfile like adding redo members, adding datafile, adding new tablespaces...etc

7-1 shutdown standby db instance

7-2 create new standby control file move it to standby side destinations (generally 3).

SQL> alter database create standby controlfile as '/tmp/stby.ctl'; --on primary db

scp /tmp/stby.ctl oracle@stdbyserver:/oradata/ctl<1>/ctl.dbf

7-3 start standby db in mount , and start log apply service MenagedRecoveryProcess;

SQL> startup mount;

8-OPTIONAL - Transfer newly created files. 

If new datafiles were added during the time that dataguard had been stopped as it happened to me; you need to copy the newly created files .They were not included incremental backup set;

and not created cause of stopped MRP.

8-1 determine all datafiles from database (remember we have just created a new controlfile , both primary and standby has same information)

SQL> spool '/tmp/hede.txt';

SQL> select 'file ' ,name from v$datafile;

# sh /tmp/hede.txt > fileSatus.txt

# cat fileSatus.txt grep cannot

/oradata/file004.dbf : cannot open

/oradata/file005.dbf : cannot open

Means we have to copy these 2 files to standby side.

8-2 After determining missing datafiles ; backup them as image copy in primary db ,copy to standby side.

BACKUP AS COPY DATAFILE '/oradata/file004.dbf' FORMAT '/tmp/file004.dbf' TAG stdbyImgCopy;

BACKUP AS COPY DATAFILE '/oradata/file005.dbf' FORMAT '/tmp/file005.dbf' TAG stdbyImgCopy;

scp /tmp/file004.dbf oracle@stdbyserver:/oradata/file004.dbf

scp /tmp/file005.dbf oracle@stdbyserver:/oradata/file005.dbf

9-Re-start log apply and transfer services.

9.1 start redo sent on primary

alter system set log_archive_dest_state_2 ='enable' scope=both ;

9.2 start redo apply on standby

SQL> startup mount;

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

9-3 check if for any problems; you may encounter problems. Check alert.log and status of proceesses

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

After success of this operation We were freed of time and space to re-establish all 30 TB database.

A similar workaound is documented in metalink for Oracle 9i : Doc ID:290817.1