Sunday, August 14, 2016

Resetlogs Fails with ORA-00349

During a database restore on a separate server from the source DB following error occurred during open resetlogs.
RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 08/10/2016 18:48:18
ORA-00349: failure obtaining block size for '/data/oradata/apcdb/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9
The source database was on a non-OMF setup and the DB was restored on OMF paths. As such the original redo directory paths does not exists on the restored sever. Typical solution is to clear the unarchived log file groups but option failed in this case.
SQL> alter database clear unarchived logfile group 1 ;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '/data/oradata/apcdb/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9
However it is possible to drop and add log file groups
SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database add logfile group 1;
Database altered.

SQL>  alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile group 3;
Database altered.
Yet was unable to drop and add one log group as it was considered current
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance apcdb (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/data/oradata/apcdb/redo02.log'

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         3 UNUSED
         2 CLEARING_CURRENT


Solution in this case is to backup the control file to trace and edit the log file locations.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control.sql' resetlogs ;
On the trace file the control file create was as follows
CREATE CONTROLFILE REUSE DATABASE "APCDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/opt/app/oracle/oradata/APCDB/onlinelog/o1_mf_1_ctptbqxp_.log',
    '/opt/app/oracle/flash_recovery_area/APCDB/onlinelog/o1_mf_1_ctptbr4t_.log'
  ) SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/data/oradata/apcdb/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/opt/app/oracle/oradata/APCDB/onlinelog/o1_mf_3_ctptc5mn_.log',
    '/opt/app/oracle/flash_recovery_area/APCDB/onlinelog/o1_mf_3_ctptc5tb_.log'
  ) SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_system_ctpskmcr_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_sysaux_ctpskmcd_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_undotbs1_ctpskmgg_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_system_ctpsnlq7_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_users_ctpskmhd_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_sysaux_ctpsnlp4_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_system_ctpsm0jd_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_sysaux_ctpsm0hp_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_system_ctpsmsmb_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_sysaux_ctpsmsm4_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_test_ctpsm0l1_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_test_ctpsmsnt_.dbf',
  '/opt/app/oracle/oradata/APCDB/datafile/o1_mf_newtest_ctpsm0m8_.dbf'
CHARACTER SET AL32UTF8
;
As seen on the trace the log file groups 1 and 3 are using the new OMF paths but group 2 still refer to source DB path where the backups were taken from. Two options are available to deal with the group 2, one is to replace the group 2 with an OMF path, in this case
GROUP 2 '/data/oradata/apcdb/redo02.log'  SIZE 50M BLOCKSIZE 512,
with
GROUP 2 (
    '/opt/app/oracle/oradata/APCDB/onlinelog/o1_mf_2_captbqxp_.log',
    '/opt/app/oracle/flash_recovery_area/APCDB/onlinelog/o1_mf_2_captbr4t_.log'
  ),
Or the second option is to completely omit the group 2 and then add group 2 once the control file is created and before resetlogs is run again. Once the control file create section is edited start the database in nomount and run the script
SQL> startup nomount;
SQL> @/tmp/control.sql
This will leave the DB on mount mode. If group 2 was omitted from the control file create then add the group 2 now.
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         3          1          0  104857600        512          2 YES CURRENT                      0                      0                    0
         1          1          0  104857600        512          2 YES UNUSED                       0                      0                    0

SQL> alter database add logfile group 2;
Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
         1          1          0  104857600        512          2 YES UNUSED                       0                      0                    0
         3          1          0  104857600        512          2 YES CURRENT                      0                      0                    0
         2          1          0  104857600        512          2 YES UNUSED                       0                      0                    0
Run a recovery using backup control file until cancel.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 4285165 generated at 08/10/2016 15:52:55 needed for thread 1
ORA-00289: suggestion :
/opt/app/oracle/flash_recovery_area/APCDB/archivelog/2016_08_10/o1_mf_1_206_%u_.
arc
ORA-00280: change 4285165 for thread 1 is in sequence #206

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
Finally run the open resetlogs
SQL> alter database open resetlogs;
Database altered.
Useful metalink note
ALTER DATABASE OPEN RESETLOGS fails with ORA-00392 [ID 1352133.1]

Monday, August 1, 2016

GUID Directory Not Created for PDBs During Full Restore in 12.1.0.2

Each database (non-CDB, CDB, PDB) on 12c has a generic unique identifier (GUID) associated with it. This GUID will not change throughout the life of the container/non-CDB. The 12c Admin guide says "the PDB GUID is primarily used to generate names for directories that store the PDB’s files, including both Oracle Managed Files directories and non-Oracle Managed Files directories". But it seems during RMAN restore the PDB related data files are not restored into a directory structure with GUID. There is a variant of this issue where "RMAN restore does not create GUID directory for pre-12c database" (16874123.8) which is fixed on 12.1.0.2 according to 1683802.1. The version used for testing is 12.1.0.2, so not sure if what's observed is a new bug or a different manifestation of the bug 16874123.
The source database has 3 PDBs (not counting the seed PDB) and data files are contained within a GUID directory.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBONE                         READ WRITE NO
         4 PDBTWO                         MOUNTED
         5 PDBTHREE                       MOUNTED

SQL> select con_id,name from v$datafile order by 1;

    CON_ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_bocndoww_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_rootbs_bq3ngj79_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_users_bocng186_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_bocnf2j0_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_bocndyz0_.dbf
         2 /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/o1_mf_system_bocnds90_.dbf
         2 /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/o1_mf_sysaux_bocnf0mr_.dbf
         3 /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_sysaux_bqc9rzvz_.dbf
         3 /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_system_bqc9rzvt_.dbf
         3 /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_test_bqc9yss9_.dbf
         4 /opt/app/oracle/oradata/CDB12C/1803968083DD030DE0535500A8C08A81/datafile/o1_mf_sysaux_bqcb6642_.dbf
         4 /opt/app/oracle/oradata/CDB12C/1803968083DD030DE0535500A8C08A81/datafile/o1_mf_system_bqcb663x_.dbf
         5 /opt/app/oracle/oradata/CDB12C/3643E46698E543F4E0535D00A8C0BDF3/datafile/o1_mf_system_cq2d54cz_.dbf
         5 /opt/app/oracle/oradata/CDB12C/3643E46698E543F4E0535D00A8C0BDF3/datafile/o1_mf_sysaux_cq2d54dx_.dbf
A full backup of the database is taken and restored in another server which is identical to the source DB (OS, Oracle version including patch level etc). db_create_file_dest parameter is same on both source and restored DBs pfiles.
The restore output is shown below which indicates that PDB related files are restored into a directory structure with GUID.
RMAN> restore controlfile from '/home/oracle/backup/control.ctl';

Starting restore at 28-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=243 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/oracle/oradata/CDB12C/controlfile/o1_mf_csn2c773_.ctl
output file name=/opt/app/oracle/fast_recovery_area/CDB12C/controlfile/o1_mf_csn2c7hf_.ctl
Finished restore at 28-JUL-16

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> run {
2> restore database;
3> recover database;
4> }

Starting restore at 28-JUL-16
Starting implicit crosscheck backup at 28-JUL-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=243 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 28-JUL-16

Starting implicit crosscheck copy at 28-JUL-16
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 28-JUL-16

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_bocndoww_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_bocndyz0_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_bocnf2j0_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_users_bocng186_.dbf
channel ORA_DISK_1: restoring datafile 00022 to /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_rootbs_bq3ngj79_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bkp5brboipc_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/bkp5brboipc_1_1 tag=TAG20160727T140115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00025 to /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_system_bqc9rzvt_.dbf
channel ORA_DISK_1: restoring datafile 00026 to /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_sysaux_bqc9rzvz_.dbf
channel ORA_DISK_1: restoring datafile 00027 to /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_test_bqc9yss9_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bkp5crboir3_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/bkp5crboir3_1_1 tag=TAG20160727T140115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:18
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00028 to /opt/app/oracle/oradata/CDB12C/1803968083DD030DE0535500A8C08A81/datafile/o1_mf_system_bqcb663x_.dbf
channel ORA_DISK_1: restoring datafile 00029 to /opt/app/oracle/oradata/CDB12C/1803968083DD030DE0535500A8C08A81/datafile/o1_mf_sysaux_bqcb6642_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bkp5drboirj_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/bkp5drboirj_1_1 tag=TAG20160727T140115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00039 to /opt/app/oracle/oradata/CDB12C/3643E46698E543F4E0535D00A8C0BDF3/datafile/o1_mf_system_cq2d54cz_.dbf
channel ORA_DISK_1: restoring datafile 00040 to /opt/app/oracle/oradata/CDB12C/3643E46698E543F4E0535D00A8C0BDF3/datafile/o1_mf_sysaux_cq2d54dx_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bkp5erboisf_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/bkp5erboisf_1_1 tag=TAG20160727T140115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/o1_mf_system_bocnds90_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/o1_mf_sysaux_bocnf0mr_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/bkp5frboisv_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/bkp5frboisv_1_1 tag=TAG20160727T140115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 28-JUL-16

Starting recover at 28-JUL-16
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=221
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/arch5grboitr_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/arch5grboitr_1_1 tag=TAG20160727T140338
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/opt/app/oracle/fast_recovery_area/CDB12C/archivelog/2016_07_28/o1_mf_1_221_csn2jcf4_.arc thread=1 sequence=221
channel default: deleting archived log(s)
archived log file name=/opt/app/oracle/fast_recovery_area/CDB12C/archivelog/2016_07_28/o1_mf_1_221_csn2jcf4_.arc RECID=518 STAMP=918398107
unable to find archived log
archived log thread=1 sequence=222
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/28/2016 14:35:18
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 222 and starting SCN of 3544373
Running report schema shows that all the datafiles for PDBs are created at the same level as the CDB datafiles except for temp files used by PDBs which are listed with a directory path containing GUID.
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name CDB12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    810      SYSTEM               ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2cvqv_.dbf
2    260      PDB$SEED:SYSTEM      ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2hg6q_.dbf
3    980      SYSAUX               ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2cvqo_.dbf
4    595      PDB$SEED:SYSAUX      ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2hg6g_.dbf
5    835      UNDOTBS1             ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_csn2cvqr_.dbf
6    5        USERS                ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_users_csn2cvr5_.dbf
22   10       ROOTBS               ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_rootbs_csn2cvr2_.dbf
25   270      PDBONE:SYSTEM        ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2fpvd_.dbf
26   615      PDBONE:SYSAUX        ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2fpv4_.dbf
27   10       PDBONE:TEST          ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_test_csn2fpvh_.dbf
28   270      PDBTWO:SYSTEM        ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2g9g0_.dbf
29   605      PDBTWO:SYSAUX        ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2g6mg_.dbf
39   260      PDBTHREE:SYSTEM      ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2gz2w_.dbf
40   605      PDBTHREE:SYSAUX      ***     /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2gz2c_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_temp_bocnf32d_.tmp
2    20       PDB$SEED:TEMP        32767       /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/o1_mf_temp_bocnf33b_.tmp
3    20       PDBONE:TEMP          32767       /opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_temp_bqc9rzw2_.dbf
4    20       PDBTWO:TEMP          32767       /opt/app/oracle/oradata/CDB12C/1803968083DD030DE0535500A8C08A81/datafile/o1_mf_temp_bqcb6645_.dbf
5    20       PDBTHREE:TEMP        32767       /opt/app/oracle/oradata/CDB12C/3643E46698E543F4E0535D00A8C0BDF3/datafile/o1_mf_temp_cq2d54f1_.dbf
However these temp files or the GUID directory doesn't exists on the filesystem.
$ ls -l /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/
ls: cannot access /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/: No such file or directory

$ ls -l /opt/app/oracle/oradata/CDB12C/
total 8
drwxr-x--- 2 oracle oinstall 4096 Jul 28 14:32 controlfile
drwxr-x--- 2 oracle oinstall 4096 Jul 28 14:34 datafile


Opening the database in reset log mode, will dynamically create the GUID directories and temp files inside them
RMAN> alter database open resetlogs;

Statement processed

$ ls -l /opt/app/oracle/oradata/CDB12C/
total 28
drwxr-x--- 3 oracle oinstall 4096 Jul 28 14:37 161DBD44EAAA7AC0E0535500A8C01F27
drwxr-x--- 3 oracle oinstall 4096 Jul 28 14:37 18037D5BA62802CEE0535500A8C0EC8F
drwxr-x--- 3 oracle oinstall 4096 Jul 28 14:37 1803968083DD030DE0535500A8C08A81
drwxr-x--- 3 oracle oinstall 4096 Jul 28 14:37 3643E46698E543F4E0535D00A8C0BDF3
drwxr-x--- 2 oracle oinstall 4096 Jul 28 14:32 controlfile
drwxr-x--- 2 oracle oinstall 4096 Jul 28 14:37 datafile
drwxr-x--- 2 oracle oinstall 4096 Jul 28 14:37 onlinelog

$ ls -l /opt/app/oracle/oradata/CDB12C/161DBD44EAAA7AC0E0535500A8C01F27/datafile/
total 1024
-rw-r----- 1 oracle oinstall 20979712 Jul 28 14:37 o1_mf_temp_csn2n97z_.tmp
However the PDBs' datafiles are all in the same OMF path
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBONE                         READ WRITE NO
         4 PDBTWO                         MOUNTED
         5 PDBTHREE                       MOUNTED
SQL>  select con_id,name from v$datafile order by 1;

    CON_ID NAME
---------- ---------------------------------------------------------------------
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2cvqv_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_rootbs_csn2cvr2_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_users_csn2cvr5_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_csn2cvqr_.dbf
         1 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2cvqo_.dbf
         2 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2hg6q_.dbf
         2 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2hg6g_.dbf
         3 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2fpv4_.dbf
         3 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2fpvd_.dbf
         3 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_test_csn2fpvh_.dbf
         4 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2g6mg_.dbf
         4 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2g9g0_.dbf
         5 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2gz2w_.dbf
         5 /opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2gz2c_.dbf
If a new tablespace or data file is added to one of the PDBs this will be created in a directory path with GUID.
SQL> alter session set container=pdbone;

SQL>  show con_name

CON_NAME
------------------------------
PDBONE

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_csn2cvqr_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2fpvd_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2fpv4_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_test_csn2fpvh_.dbf

SQL> create tablespace newtbs DATAFILE SIZE 10M;

Tablespace created.

SQL>  select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_undotbs1_csn2cvqr_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_system_csn2fpvd_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_sysaux_csn2fpv4_.dbf
/opt/app/oracle/oradata/CDB12C/datafile/o1_mf_test_csn2fpvh_.dbf
/opt/app/oracle/oradata/CDB12C/18037D5BA62802CEE0535500A8C0EC8F/datafile/o1_mf_newtbs_csn2s0yz_.dbf
The restore was also tested by pre-creating the directory path with GUID, however there was no difference in the outcome. Also tested with latest PSU (July 2016) applied on source DB's home and new servers oracle home, and using a new backup taken from source DB after PSU applied. No difference in that case either. Also tested with patch 20464614 applied (mentioned in 1576755.1 in relation to OMF/ASM and PDBs). But didn't observe any difference in the final outcome.
A service request has been raised.

Useful metalink notes
Bug 16874123 - RMAN restore does not create GUID directory for pre-12c database [ID 16874123.8]
Step by Step Examples of Migrating non-CDBs and PDBs Using ASM for File Storage [ID 1576755.1]

Thursday, July 14, 2016

Removing a Failed Node From the Cluster

This post list steps for removing a failed node from a cluster. The steps differs from steps in the previous node deletion posts (11gR1,11gR2 and 12c) such that one node has suffered a catastrophic failure and is not available for any kind of command or script executions. Therefore all the activities involved in removing the failed node are executed from a surviving node.
The environment used in this case is a two node RAC with role separation(11.2.0.4). Under normal operation it has the following resources and status. (formatted status)
Resource Name                       Type                      Target             State              Host
-------------                       ------                    -------            --------           ----------
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m1
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m2
ora.MYLISTENER_SCAN1.lsnr           ora.scan_listener.type    ONLINE             ONLINE             rhel6m2
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m1
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m2
ora.cvu                             ora.cvu.type              ONLINE             ONLINE             rhel6m2
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m1
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m2
ora.oc4j                            ora.oc4j.type             ONLINE             ONLINE             rhel6m2
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m1
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m2
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m1
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m2
ora.rhel6m1.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m1
ora.rhel6m2.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m2
ora.scan1.vip                       ora.scan_vip.type         ONLINE             ONLINE             rhel6m2
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m2
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m1
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m2
ora.std11g2.abx.domain.net.svc      ora.service.type          ONLINE             ONLINE             rhel6m2
ora.std11g2.abx.domain.net.svc      ora.service.type          ONLINE             ONLINE             rhel6m1
After the node 2 (rhel6m2 node in this case) suffers a catastrophic failure, resources and status is as below. There are offline and failed over (vip) resources from rhel6m2.
Resource Name                       Type                      Target             State              Host
-------------                       ------                    -------            --------           ----------
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m1
ora.MYLISTENER_SCAN1.lsnr           ora.scan_listener.type    ONLINE             ONLINE             rhel6m1
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m1
ora.cvu                             ora.cvu.type              ONLINE             ONLINE             rhel6m1
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m1
ora.oc4j                            ora.oc4j.type             ONLINE             ONLINE             rhel6m1
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m1
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m1
ora.rhel6m1.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m1
ora.rhel6m2.vip                     ora.cluster_vip_net1.type ONLINE             INTERMEDIATE       rhel6m1
ora.scan1.vip                       ora.scan_vip.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             OFFLINE
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m1
ora.std11g2.myservice.svc           ora.service.type          ONLINE             OFFLINE
ora.std11g2.abx.domain.net.svc      ora.service.type          ONLINE             OFFLINE
ora.std11g2.abx.domain.net.svc      ora.service.type          ONLINE             ONLINE             rhel6m1
Removing of resources of the failed node begins at database resource level. There are two services running and they both have the DB instance on the failed node as a preferred instance (output is condensed)
srvctl config service -d std11g2
Service name: myservice
Service is enabled
Server pool: std11g2_myservice
Cardinality: 2
...
Preferred instances: std11g21,std11g22
Available instances:
Service name: abx.domain.net
Service is enabled
Server pool: std11g2_abx.domain.net
Cardinality: 2
...
Preferred instances: std11g21,std11g22
Available instances:
Modify the service configuration so that only the surviving instances are set as preferred instances.
$ srvctl modify service -s myservice -d std11g2 -n -i std11g21 -f
$ srvctl modify service -s abx.domain.net -d std11g2 -n -i std11g21 -f

$ srvctl config service -d std11g2
Service name: myservice
Service is enabled
Server pool: std11g2_myservice
Cardinality: 1
..
Preferred instances: std11g21
Available instances:
Service name: abx.domain.net
Service is enabled
Server pool: std11g2_abx.domain.net
Cardinality: 1
..
Preferred instances: std11g21
Available instances:

$ srvctl status service -d std11g2
Service myservice is running on instance(s) std11g21
Service abx.domain.net is running on instance(s) std11g21
Remove the database instance on the failed node
srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
...
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH
Mount point paths:
Services: myservice,abx.domain.net
Type: RAC
Database is administrator managed
This is done using DBCA's instance management option. If the listener has a non-default name and port then accessing the DB will fail with below message.
To fix this create a default listener (name listener and port 1521). Also if VNCR is used then remove the failed node from the registration list. Proceed to instance deletion by selecting the inactive instance on the failed node.
As node 2 is not available following warning will be issued. Click continue and proceed. During the execution various other warning will appear such as unable to remove /etc/oratab etc all of these could be ignored.
However DBCA didn't run till end, at 67% (observed through repeated runs on this 11.2.0.4 environment) following dialog box appeared. As seen on the screenshot it has no message, just an OK button. Clicking it doesn't end the DBCA session but goes to the beginning and exit the DBCA clicking cancel afterwards.
However this doesn't appear to be a failure on the DBCA to remove the instance. In fact instance is removed as subsequent instance operation only list the instance on the surviving node.
Querying the database also shows that instance 2 (std11g22 in this case) related undo tablespace and redo logs have been removed and only surviving instance related undo tablespace and redo logs are available.
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TEST

7 rows selected.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1       1598   52428800        512          2 NO  CURRENT               68471125 07-JUL-16   2.8147E+14
         2          1       1597   52428800        512          2 YES INACTIVE              68467762 07-JUL-16     68471125 07-JUL-16

srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
...
Database instances: std11g21
Disk Groups: DATA,FLASH
Mount point paths:
Services: myservice,abx.domain.net
Type: RAC
Database is administrator managed
Once the database resources are removed next step is to remove the Oracle database home entry for the failed node from the inventory.



As the node is unavailable, there's no un-installation involved. Run the inventory update command with surviving nodes. Inventory content for the Oracle home before the failed node is removed.
<HOME NAME="OraDb11g_home2" LOC="/opt/app/oracle/product/11.2.0/dbhome_4" TYPE="O" IDX="4">
   <NODE_LIST>
      <NODE NAME="rhel6m1"/>
      <NODE NAME="rhel6m2"/>
   </NODE_LIST>
</HOME>
After the inventory update
 ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rhel6m1}"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4095 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/app/oraInventory
'UpdateNodeList' was successful.

<HOME NAME="OraDb11g_home2" LOC="/opt/app/oracle/product/11.2.0/dbhome_4" TYPE="O" IDX="4">
   <NODE_LIST>
      <NODE NAME="rhel6m1"/>
   </NODE_LIST>
</HOME>
Next step is to remove the cluster resources and the node itself. If any of the node is in pin stat, unpin them. In this case both nodes are unpinned
olsnodes -s -t
rhel6m1 Active  Unpinned
rhel6m2 Inactive        Unpinned
Stop and remove the VIP resource of the failed node
# srvctl stop  vip -i rhel6m2-vip -f
# srvctl remove vip -i rhel6m2-vip -f
Remove the failed node from the cluster configuration
#  crsctl delete node -n rhel6m2
CRS-4661: Node rhel6m2 successfully deleted.
Finally remove the grid home for the failed node from the inventory. Before inventory update
<HOME NAME="Ora11g_gridinfrahome2" LOC="/opt/app/11.2.0/grid4" TYPE="O" IDX="3" CRS="true">
   <NODE_LIST>
      <NODE NAME="rhel6m1"/>
      <NODE NAME="rhel6m2"/>
   </NODE_LIST>
</HOME>
After inventory update
./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rhel6m1}" CRS=TRUE
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4095 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/app/oraInventory
'UpdateNodeList' was successful.

<HOME NAME="Ora11g_gridinfrahome2" LOC="/opt/app/11.2.0/grid4" TYPE="O" IDX="3" CRS="true">
   <NODE_LIST>
      <NODE NAME="rhel6m1"/>
   </NODE_LIST>
</HOME>
Validate the node removal with cluvfy
cluvfy stage -post  nodedel -n rhel6m2

Performing post-checks for node removal
Checking CRS integrity...
Clusterware version consistency passed
CRS integrity check passed
Node removal check passed
Post-check for node removal was successful.
Remove the default listener if one was created during instance remove step. The final status of resource is as below.
Resource Name                       Type                      Target             State              Host
-------------                       ------                    -------            --------           ----------
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m1
ora.MYLISTENER_SCAN1.lsnr           ora.scan_listener.type    ONLINE             ONLINE             rhel6m1
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m1
ora.cvu                             ora.cvu.type              ONLINE             ONLINE             rhel6m1
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m1
ora.oc4j                            ora.oc4j.type             ONLINE             ONLINE             rhel6m1
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m1
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m1
ora.rhel6m1.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m1
ora.scan1.vip                       ora.scan_vip.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m1
ora.std11g2.abx.domain.net.svc      ora.service.type          ONLINE             ONLINE             rhel6m1
Useful metalink notes
How to remove/delete a node from Grid Infrastructure Clusterware when the node has failed [ID 1262925.1]
Steps to Remove Node from Cluster When the Node Crashes Due to OS/Hardware Failure and cannot boot up [ID 466975.1]
RAC on Windows: How to Remove a Node from a Cluster When the Node Crashes Due to OS/Hardware Failure and Cannot Boot [ID 832054.1]

Related Post
Deleting a Node From 12cR1 RAC
Deleting a Node From 11gR2 RAC
Deleting a 11gR1 RAC Node