Manually add database/instance resources after a complete CRS reconfiguration

Overview

• After a complete CRS reconfiguration database and instance resources are gone
• Of course you should have a script to recreate the resources – but if not this article should give you an idea how to recreate the database and instance resource

Recreate Database resource grac4 and add instance grac42 and grac43 ( host grac42 and host grac43 )

Locate SPFile from as running instance  
[oracle@grac42 ~]$ cat /u01/app/oracle/product/11204/racdb/dbs/initgrac42.ora
SPFILE='+DATA/grac4/spfilegrac4.ora'

Add database and database instance ( grac42 / grac43 - for simplicity hostname and instance name are equal ) 
[oracle@grac42 ~]$ srvctl add database -d grac4 -n grac4 -o /u01/app/oracle/product/11204/racdb -p '+DATA/grac4/spfilegrac4.ora'
       -s OPEN -y AUTOMATIC -a "DATA" -t IMMEDIATE
[oracle@grac42 ~]$ srvctl add instance -d grac4 -i grac42 -n  grac42
[oracle@grac42 ~]$ srvctl add instance -d grac4 -i grac43 -n  grac43
[oracle@grac42 ~]$ crs | egrep 'db|---|Name'
-------------------------      ---------- ----------      ------------ ------------------
ora.grac4.db                   OFFLINE    OFFLINE                       
ora.grac4.db                   OFFLINE    OFFLINE   

Start instances a first time  with sqlplus or srvctl and verify instance status
Instance grac42:
[oracle@grac42 ~]$ env | grep SID
ORACLE_SID=grac42
[oracle@grac42 ~]$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup
..
Instance grac43: 
[oracle@grac42 ~]$ srvctl start instance -d grac4 -i grac43
[oracle@grac42 ~]$   crs | egrep 'db|---|Name'
-------------------------      ---------- ----------      ------------ ------------------
ora.grac4.db                   ONLINE     ONLINE          grac42       Open 
ora.grac4.db                   ONLINE     ONLINE          grac43       Open 

Verify the current status database and instance status 
SQL> select to_char( INST_ID) INST_ID, to_char(INSTANCE_NUMBER) INST_NUM, INSTANCE_NAME INST_NAME, HOST_NAME,
  2   VERSION, to_char(STARTUP_TIME,'DD-MON HH:MI:SS') STARTUP_TIME , STATUS, PARALLEL,to_char(THREAD#) THREAD#,
  3   ARCHIVER, LOGINS, SHUTDOWN_PENDING, DATABASE_STATUS DB_STATUS, INSTANCE_ROLE, ACTIVE_STATE, BLOCKED
  4      from gv$instance;
INST_ID INST_NUM INST_NAME HOST_NAME          VERSION       STARTUP_TIME    STATUS     PAR THREAD#  ARCHIVE LOGINS     SHU DB_STATUS INSTANCE_ROLE      ACTIVE_ST BLO
------- -------- --------- ------------------ ------------ --------------- ---------- --- -------- ------- ---------- --- --------- ------------------ --------- ---
2    2     grac42    grac42.example.com 11.2.0.4.0   04-OCT 09:42:36 OPEN       YES 2       STARTED ALLOWED    NO  ACTIVE    PRIMARY_INSTANCE   NORMAL     NO
3    3     grac43    grac43.example.com 11.2.0.4.0   04-OCT 09:45:18 OPEN       YES 3       STARTED ALLOWED    NO  ACTIVE    PRIMARY_INSTANCE   NORMAL     NO

SQL> /*
SQL>      Don't use gv$log and gv$logfile - results can be misleading
SQL> */
SQL> 
SQL> col THREAD# format 99999999
SQL> select * from v$log order by THREAD#, GROUP#;
    GROUP#   THREAD#  SEQUENCE#      BYTES  BLOCKSIZE     MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- --------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
     3       2        245   52428800      512           2 YES INACTIVE         56715021 04-OCT-14     56729373 04-OCT-14
     4       2        246   52428800      512           2 NO  CURRENT         56729373 04-OCT-14   2.8147E+14
     5       3        257   52428800      512           2 YES INACTIVE         56715019 04-OCT-14     56715064 04-OCT-14
     6       3        258   52428800      512           2 NO  CURRENT         56717477 04-OCT-14   2.8147E+14 04-OCT-14
SQL> select * from v$logfile order by GROUP#;
    GROUP# STATUS     TYPE    MEMBER                         IS_
---------- ---------- ------- -------------------------------------------------- ---
     3          ONLINE  +FRA/grac4/onlinelog/group_3.1026.845590849     YES
     3          ONLINE  +DATA/grac4/onlinelog/group_3.262.845590841     NO
     4          ONLINE  +DATA/grac4/onlinelog/group_4.265.845590853     NO
     4          ONLINE  +FRA/grac4/onlinelog/group_4.1027.845590861     YES
     5          ONLINE  +DATA/grac4/onlinelog/group_5.270.859796931     NO
     5          ONLINE  +FRA/grac4/onlinelog/group_5.368.859796939     YES
     6          ONLINE  +DATA/grac4/onlinelog/group_6.266.859796961     NO
     6          ONLINE  +FRA/grac4/onlinelog/group_6.370.859796975     YES
8 rows selected.
SQL> select THREAD# , STATUS , ENABLED from v$thread order by THREAD#;
  THREAD# STATUS     ENABLED
--------- ---------- --------
    2 OPEN         PUBLIC
    3 OPEN         PUBLIC

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