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

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

—> Potential RAC startup  problems and their solution

ORA-29760: instance_number parameter not specified during startup

Start the database instance with sqlplus
SQL> startup nomount
ORA-29760: instance_number parameter not specified

Checking instance paramter on grac43
SQL>  create pfile='/tmp/p.ora' from spfile;
File created.
SQL> !more /tmp/p.ora
..
grac42.instance_number=2
grac41.instance_number=1
grac42.thread=2
grac41.thread=1
grac42.undo_tablespace='UNDOTBS2'
grac41.undo_tablespace='UNDOTBS1'
--> Missing parameters for instance  grac43

Add instance parameters
SQL> alter system set instance_number=3 scope=spfile sid='grac43';
SQL> alter system set thread=3  scope=spfile sid='grac43';
SQL> alter system set undo_tablespace='UNDOTBS3'  scope=spfile sid='grac43';

SQL> startup
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925

--> Follow Node the notest bels - if this don't work start sqlplus with strace attache
Connect /as sysdba or Startup Fails With ORA-09925 When Instance Is Down (Doc ID 392643.1)
Database Authentication Is Failing With "ORA-09925: Unable To Create Audit Trail File" (Doc ID 1227964.1)

ORA-1618 during database startup

SQL> startup
ORACLE instance started.

Total System Global Area 1336176640 bytes
Fixed Size            2253024 bytes
Variable Size          469765920 bytes
Database Buffers      855638016 bytes
Redo Buffers            8519680 bytes
ORA-01618: redo thread 3 is not enabled - cannot mount

Check logfiles
SQL>  select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1         335   52428800       512        2 YES INACTIVE            56390322 30-SEP-14       56393525 30-SEP-14
     2        1         336   52428800       512        2 NO  CURRENT            56393525 30-SEP-14     2.8147E+14
     3        2         237   52428800       512        2 NO  CURRENT            56387052 30-SEP-14     2.8147E+14
     4        2         236   52428800       512        2 YES INACTIVE            56359748 30-SEP-14       56387052 30-SEP-14

--> Instance 3/ Thread 3 is missing REDO logs - Recreate REDO logs  http://www.hhutzler.de/blog/duplex-online-redo-logs-to-fra/

Add LOGFILES:
SQL> ALTER DATABASE ADD LOGFILE THREAD 3  GROUP 5 size 50m;
SQL> ALTER DATABASE ADD LOGFILE THREAD 3  GROUP 6 size 50m;
Database altered.

SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE      MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
     1        1         335   52428800       512        2 YES INACTIVE            56390322 30-SEP-14       56393525 30-SEP-14
     2        1         336   52428800       512        2 NO  CURRENT            56393525 30-SEP-14     2.8147E+14
     3        2         237   52428800       512        2 NO  CURRENT            56387052 30-SEP-14     2.8147E+14
     4        2         236   52428800       512        2 YES INACTIVE            56359748 30-SEP-14       56387052 30-SEP-14
     5        3           0   52428800       512        2 YES UNUSED               0              0
     6        3           0   52428800       512        2 YES UNUSED               0              0
6 rows selected.

Enable THREAD
SQL> alter database enable public thread 3;
SQL> select THREAD# , STATUS , ENABLED from v$thread;
   THREAD# STATUS ENABLED
---------- ------ --------
     1 OPEN   PUBLIC
     2 OPEN   PUBLIC
     3 CLOSED PUBLIC

SQL> startup force
ORACLE instance started.
..
Database mounted.
Database opened.

Start and stop instance with srvctl 
[oracle@grac43 adump]$ srvctl stop instance -d grac4 -i grac43
[oracle@grac43 adump]$ srvctl start instance -d grac4 -i grac43

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

PRCS-1007 , PRCR-1086   during  srvctl add database

[oracle@grac41 bin]$  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
PRCS-1007 : Server pool grac4 already exists
PRCR-1086 : server pool ora.grac4 is already registered

Remove the related server pools
[grid@grac41 ~]$ crsctl delete serverpool ora.grac4
CRS-2557: Server pool 'ora.grac4' cannot be unregistered as it is referenced by server pool 'ora.grac4_grac43'
[grid@grac41 ~]$ crsctl delete serverpool ora.grac4_grac43
[grid@grac41 ~]$ crsctl delete serverpool ora.grac4 
-------------------------------------

 

ORA-30012: undo tablespace ‘UNDOTBS1′ does not exist or of wrong type

Check UNDO tablespaces 
SQL>  select TABLESPACE_NAME, STATUS, CONTENTS from  dba_tablespaces where CONTENTS='UNDO';
TABLESPACE_NAME            STATUS     CONTENTS
------------------------------ --------- ---------
UNDOTBS2               ONLINE     UNDO
UNDOTBS3               ONLINE     UNDO
UNDOTBS4               ONLINE     UNDO

Verify undo_tablespace parameter 
SQL>  select inst_id,name,value from    gv$parameter where name like 'undo_tablespace' order by name,inst_id;
   INST_ID NAME               VALUE
---------- ------------------------------ ------------------------------
     1 undo_tablespace          UNDOTBS1
     2 undo_tablespace          UNDOTBS2
     3 undo_tablespace          UNDOTBS3
--> Recreate or Rename tablespace 
SQL>  ALTER TABLESPACE  UNDOTBS4 RENAME to  UNDOTBS1;

ORA-00205: error in identifying control file, check alert log for more info

Verify permissions of oracle executable 
[oracle@grac41 dbs]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x. 1 oracle oinstall 239662993 Oct  4 14:41 /u01/app/oracle/product/11204/racdb/bin/oracle
--> bin/oracle should have  asmadmin as group permissions

[root@grac41 Desktop]# chown oracle:asmadmin /u01/app/oracle/product/11204/racdb/bin/oracle
[root@grac41 Desktop]# chmod 6751  /u01/app/oracle/product/11204/racdb/bin/oracle

[oracle@grac41 dbs]$  ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x. 1 oracle asmadmin 239662993 Oct  4 14:41 /u01/app/oracle/product/11204/racdb/bin/oracle
[oracle@grac41 dbs]$ ls -l /dev/asm*
brw-rw----. 1 grid asmadmin 8,  17 Oct  5 10:10 /dev/asm_data_10g_disk1
brw-rw----. 1 grid asmadmin 8,  33 Oct  5 10:11 /dev/asm_data_10g_disk2
brw-rw----. 1 grid asmadmin 8,  49 Oct  5 10:10 /dev/asm_data_10g_disk3

 

ORA-09925: Unable to create audit trail file

SQL> startup
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925

[oracle@grac41 dbs]$ mkdir -p   /u01/app/oracle/admin/grac4/adump
Check following conditions for  audit_file_dest destination :
1) exists
2) is writable by oracle
3) is not full

Reference

  • Connect /as sysdba or Startup Fails With ORA-09925 When Instance Is Down (Doc ID 392643.1)
  • Database Authentication Is Failing With “ORA-09925: Unable To Create Audit Trail File” (Doc ID 1227964.1)

3 thoughts on “Manually add database/instance resources after a complete CRS reconfiguration”

  1. Your Site is very informative and helpful. You are a master
    I just wanted know what is the actual command for “crs” as i dont see there is “CRS” command.
    Following is one of the example you are using to find the svc details, but i could not use this command
    I tried crsctl stat res -t /p etc., but couldnt get output what you get from this
    crs | egrep ‘hr.svc|NAME|—-‘

    Appreciate if you could help with this

    1. Hi,
      crs [ and crsi ] are both shell scripts to display the CRS status.
      You can download these scripts by using Menu Path
      RAC -> RAC Generic -> RAC Scripts

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>