Skipping Tablespace During Backup and Recovery

Oracle provides skip and exclude options to keep a tablespace(s) out of a full database backup. However when the restore happens this exclusion must be handled before the database could be used by the application, and this handling depends on what effect this exclusion has on the application data structures.
Scenario given in this post shows where a table is using two tablespaces for storage, while the table remain in one tablespace the lob column related segments of that table are stored in a different tablespace. This type of table configuration is used to store transient data such as application session information. The session id is stored in a 2k tablespace while the session data (java serialized objects) stored in a 32kB tablespace. However for the testing 8k block size tablespaces are used for both table and lob segments. Database version is 11.2.0.3.
Set up the test case by creating the two tablespaces. Here the excbackup tablespace will be excluded from the backup.

create tablespace EXCBACKUP DATAFILE size 10m;
create tablespace INCBACKUP DATAFILE size 10m;

alter user asanga quota unlimited on excbackup quota unlimited on incbackup;

Create the table and populate it with data. The disable storage in row is important as the inserted data is less than 4k without disabling storage in row the lob segment will also get stored in the inbackup tablespace.

create table y (a number, b blob) tablespace incbackup lob(b) store as lobseg (tablespace excbackup DISABLE STORAGE IN ROW);
 
 begin
    for i in 1 .. 100
    loop
    insert into y values (i,utl_raw.cast_to_raw('ahgahgaashaghag'));
    end loop;
    end;
    /

select segment_name,tablespace_name,bytes from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME                     BYTES
------------------------------ ------------------------------ ----------
Y                              INCBACKUP                           65536
SYS_IL0000105332C00002$$       EXCBACKUP                          131072
LOBSEG                         EXCBACKUP                         8388608

It could be seen that lob segments are stored in the excbackup tablespace.
Configure RMAN such that excbackup tablespace is excluded when a full database backup is done.

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE excbackup;

using target database control file instead of recovery catalog
Tablespace EXCBACKUP will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

RMAN> show exclude;

RMAN configuration parameters for database with db_unique_name FGACDB are:
CONFIGURE EXCLUDE FOR TABLESPACE 'EXCBACKUP';

Once the configuration is done run a full database backup including archive logs. The truncated output below shows that datafile belonging to excbackup is not part of the backup

RMAN> backup database plus archivelog delete all input;
...
...
archived log file name=/opt/app/oracle/fast_recovery_area/FGACDB/archivelog/2014_06_06/o1_mf_1_7_9s3s0jxs_.arc RECID=62 STAMP=849545633
Finished backup at 06-JUN-14

Starting backup at 06-JUN-14
using channel ORA_DISK_1
file 5 is excluded from whole database backup
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cp_9rs1nq2y_.dbf
input datafile file number=00013 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_dumps_9rs1pg40_.dbf
input datafile file number=00014 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cms_9rs1pgkf_.dbf
input datafile file number=00015 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_monitor_9rs1ph0s_.dbf
input datafile file number=00017 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cachetbs_9rs1rjlm_.dbf
input datafile file number=00001 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_system_9rs1rll5_.dbf
input datafile file number=00002 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_sysaux_9rs1rm61_.dbf
input datafile file number=00003 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_undotbs1_9rs1smgo_.dbf
input datafile file number=00004 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_users_9rs1ss96_.dbf
input datafile file number=00006 name=/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_incbacku_9s3rrzr3_.dbf
channel ORA_DISK_1: starting piece 1 at 06-JUN-14
channel ORA_DISK_1: finished piece 1 at 06-JUN-14
piece handle=/opt/app/oracle/fast_recovery_area/FGACDB/backupset/2014_06_06/o1_mf_nnndf_TAG20140606T165402_9s3s0tyd_.bkp tag=TAG20140606T165402 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 06-JUN-14
...
...

Next step is the restore and recovery.



To simulate a situation where all data files are lost, database was shutdown and all data files removed (using OS utility) before the restore. The restore steps are given below.

RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
....
....
file 5 is excluded from whole database backup
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/FGACDB/datafile/o1_mf_system_9rs1rll5_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_sysaux_9rs1rm61_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_undotbs1_9rs1smgo_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_users_9rs1ss96_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_incbacku_9s3rrzr3_.dbf
channel ORA_DISK_1: restoring datafile 00012 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cp_9rs1nq2y_.dbf
channel ORA_DISK_1: restoring datafile 00013 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_dumps_9rs1pg40_.dbf
channel ORA_DISK_1: restoring datafile 00014 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cms_9rs1pgkf_.dbf
channel ORA_DISK_1: restoring datafile 00015 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_monitor_9rs1ph0s_.dbf
channel ORA_DISK_1: restoring datafile 00017 to /opt/app/oracle/oradata/FGACDB/datafile/o1_mf_cachetbs_9rs1rjlm_.dbf
channel ORA_DISK_1: reading from backup piece /opt/app/oracle/fast_recovery_area/FGACDB/backupset/2014_06_06/o1_mf_nnndf_TAG20140606T165402_9s3s0tyd_.bkp
channel ORA_DISK_1: piece handle=/opt/app/oracle/fast_recovery_area/FGACDB/backupset/2014_06_06/o1_mf_nnndf_TAG20140606T165402_9s3s0tyd_.bkp tag=TAG20140606T165402
....
....

Again the skipping of the file belonging to excbackup tablespace is mentioned during the restore. Since the backupset used here doesn't contain the excbackup it is possible to use the restore database command. However if the restore process is using a backupset that has a particular tablespace and it need to be excluded then use RESTORE DATABASE SKIP TABLESPACE command.
Unlike the restore command the recover database will not work

RMAN> recover database;

Starting recover at 06-JUN-14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/06/2014 16:58:43
RMAN-06094: datafile 5 must be restored

Recovery will complain the excluded file must be restored. To overcome this use recover command with skip tablespace. The tablespace name must be given in upper case.

RMAN> recover database skip tablespace 'excbackup'; # failure due to tablespace name being specified in lower case

Starting recover at 06-JUN-14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/06/2014 16:59:00
RMAN-06094: datafile 5 must be restored

RMAN> recover database skip tablespace 'EXCBACKUP';

Executing: alter database datafile 5 offline
starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_4_86mco7kb_.log
archived log for thread 1 with sequence 9 is already on disk as file /opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_1_86mcp3kk_.log
archived log file name=/opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_4_86mco7kb_.log thread=1 sequence=8
archived log file name=/opt/app/oracle/fast_recovery_area/FGACDB/onlinelog/o1_mf_1_86mcp3kk_.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-JUN-14

Finally open the database with reset logs

RMAN> alter database open resetlogs;

At this stage the database is open and could be used as long as the segments that were in the excluded tablespace are not needed. All table data that were part of the incbackup tablespace could be access without any error.

SQL> select a from y;

         A
----------
         1
         2
         3
         4
         5

Any attempt to access the blob segment which was in the excluded tablespace results in an error

SQL> select b from y;
ERROR:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5:
'/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf'

It is not possible to delete or truncate the table either

SQL> delete from y;
delete from y
            *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5:
'/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf'


SQL> truncate table y;
truncate table y
               *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5:
'/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf'

During this time the table segment information is listed as below.

SQL> select segment_name,tablespace_name,bytes from user_segments;

SEGMENT_NAME               TABLESPACE_NAME                     BYTES
-------------------------- ------------------------------ ----------
Y                          INCBACKUP                           65536
SYS_IL0000105365C00002$$   EXCBACKUP                           65536
LOBSEG                     EXCBACKUP

Dropping the table doesn't remove the lob segments either (it could be that these don't occupy any space but entry is there under a different name)

SQL> drop table y purge;

SQL>  select segment_name,tablespace_name,bytes from user_segments;

SEGMENT_NAME   TABLESPACE_NAME                     BYTES
-------------- ------------------------------ ----------
5.130          EXCBACKUP
5.138          EXCBACKUP                           65536

It is also possible to drop the missing data file offline and add another data file to the tablespace and recreate the table.

SQL>  alter database   datafile '/opt/app/oracle/oradata/FGACDB/datafile/o1_mf_excbacku_9s3rryro_.dbf' offline drop;

SQL> alter tablespace excbackup add DATAFILE size 10m;

SQL> create table y (a number, b blob) tablespace incbackup lob(b) store as lobseg (tablespace excbackup DISABLE     STORAGE IN ROW);


 begin
    for i in 1 .. 100
    loop
    insert into y values (i,utl_raw.cast_to_raw('ahgahgaashaghag'));
    end loop;
    end;
    /

But still the segment information from the earlier table remains.

SQL> select segment_name,tablespace_name,bytes from user_segments;

SEGMENT_NAME               TABLESPACE_NAME                     BYTES
-------------------------- ------------------------------ ----------
Y                          INCBACKUP                           65536
SYS_IL0000105337C00002$$   EXCBACKUP                           65536
LOBSEG                     EXCBACKUP                          983040
5.130                      EXCBACKUP
5.138                      EXCBACKUP

Only way to get rid of these phantom segment information is to drop the tablespace and recreate it again.
Therefore to have a clean restore and recovery in this type of scenario best course of actions is, once the database is open, drop the table, drop the tablespace and then recreate the tablespace and the table as before.

ORA-20006: Number of RAC active instances and opatch jobs configured are not same

Following error could be observed when running the "Loading Modified SQL Files into the Database" section under the "Patch Post-Installation Instructions" during a PSU apply. In this case the system was a 2 node 12c RAC. and was installing the 12.1.0.1.3 Patch Set Update.

[oracle@rhel6m1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.1.0 on Wed May  7 17:04:06 2014
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...
Currently installed SQL Patches: 17552800
DBD::Oracle::st execute failed: ORA-20006: Number of RAC active instances and opatch jobs configured are not same
ORA-06512: at "SYS.DBMS_QOPATCH", line 1007
ORA-06512: at line 4 (DBD ERROR: OCIStmtExecute) [for Statement "DECLARE
       x XMLType;
     BEGIN
       x := dbms_qopatch.get_pending_activity;
       ? := x.getStringVal();
     END;" with ParamValues: :p1=undef] at /opt/app/oracle/product/12.1.0/dbhome_1/sqlpatch/sqlpatch.pm line 1227.

Patch apply was carried out in a rolling fashion. Node 1 patched and brought up and then node 2 patched. While the node 2 patch apply is going on the post patch installation steps are carried out on the already started node (as these steps only need to be carried out on a single instance).
From the error above it seems this method (which worked on 11g) is no longer possible with the new datapatch utility. Metalink notes 1530108.1 and 1599479.1 list "ORA-20006: Number of RAC active instances and opatch jobs configured are not same" as "error which might be encountered during execution of Queryable Patch Inventory"



Solution provided on the above notes for this issue is to obtain support from Oracle!
Other workaround is wait until all the instances to start and then apply the post patch steps which doesn't result in an error.

[oracle@rhel6m1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.1.0.1.0 on Wed May  7 17:15:00 2014
Copyright (c) 2014, Oracle.  All rights reserved.

Connecting to database...OK
Determining current state...
Currently installed SQL Patches: 17552800
Currently installed C Patches: 18031528
Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied: 18031528
  Bundle patch 17552800 is included in bundle patch 18031528, so it does not need to be rolled back
Installing patches...

Useful metalink notes
Oracle Database 12.1 : FAQ on Queryable Patch Inventory [ID 1530108.1]
Datapatch errors at "SYS.DBMS_QOPATCH" [ID 1599479.1]

Adding a Node to 12cR1 RAC

This post list the steps for adding a node to 12cR1 standard cluster (not to a flex cluster) which is similar to that of adding a node to 11gR2 RAC. Node addition is done in three phases. Phase one is to add the clusterware to the new node. Second phase will add the database software and final phase will extend the database to the new node by creating a new instance for it. It is possible to do the node additional in silent mode or in an interactive mode with the use of GUIs. This post uses the latter method (earlier post of 11gR2 used silent mode and steps for 12c are similar to that).
1. It is assumed that physical connections (shared storage connections, network) are made to the new node being added. The pre node add steps could be checked with cluvfy by executing the pre node add command from an existing node and passing the hostname of the new node (in this case rhel12c2 is the new node).

[grid@rhel12c1 ~]$ cluvfy stage -pre nodeadd -n rhel12c2

Performing pre-checks for node addition

Checking node reachability...
Node reachability check passed from node "rhel12c1"

Checking user equivalence...
User equivalence check passed for user "grid"
Package existence check passed for "cvuqdisk"

Checking CRS integrity...

CRS integrity check passed

Clusterware version consistency passed.

Checking shared resources...

Checking CRS home location...
Location check passed for: "/opt/app/12.1.0/grid"
Shared resources check for node addition passed

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity using interfaces on subnet "192.168.0.0"
Node connectivity passed for subnet "192.168.0.0" with node(s) rhel12c1,rhel12c2
TCP connectivity check passed for subnet "192.168.0.0"

Check: Node connectivity using interfaces on subnet "192.168.1.0"
Node connectivity passed for subnet "192.168.1.0" with node(s) rhel12c1,rhel12c2
TCP connectivity check passed for subnet "192.168.1.0"

Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "192.168.0.0".
Subnet mask consistency check passed for subnet "192.168.1.0".
Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "192.168.1.0" for multicast communication with multicast group "224.0.0.251"...
Check of subnet "192.168.1.0" for multicast communication with multicast group "224.0.0.251" passed.

Check of multicast communication passed.
Total memory check passed
Available memory check passed
Swap space check passed
Free disk space check passed for "rhel12c2:/usr,rhel12c2:/var,rhel12c2:/etc,rhel12c2:/opt/app/12.1.0/grid,rhel12c2:/sbin,rhel12c2:/tmp"
Free disk space check passed for "rhel12c1:/usr,rhel12c1:/var,rhel12c1:/etc,rhel12c1:/opt/app/12.1.0/grid,rhel12c1:/sbin,rhel12c1:/tmp"
Check for multiple users with UID value 501 passed
User existence check passed for "grid"
Run level check passed
Hard limits check passed for "maximum open file descriptors"
Soft limits check passed for "maximum open file descriptors"
Hard limits check passed for "maximum user processes"
Soft limits check passed for "maximum user processes"
System architecture check passed
Kernel version check passed
Kernel parameter check passed for "semmsl"
Kernel parameter check passed for "semmns"
Kernel parameter check passed for "semopm"
Kernel parameter check passed for "semmni"
Kernel parameter check passed for "shmmax"
Kernel parameter check passed for "shmmni"
Kernel parameter check passed for "shmall"
Kernel parameter check passed for "file-max"
Kernel parameter check passed for "ip_local_port_range"
Kernel parameter check passed for "rmem_default"
Kernel parameter check passed for "rmem_max"
Kernel parameter check passed for "wmem_default"
Kernel parameter check passed for "wmem_max"
Kernel parameter check passed for "aio-max-nr"
Package existence check passed for "binutils"
Package existence check passed for "compat-libcap1"
Package existence check passed for "compat-libstdc++-33(x86_64)"
Package existence check passed for "libgcc(x86_64)"
Package existence check passed for "libstdc++(x86_64)"
Package existence check passed for "libstdc++-devel(x86_64)"
Package existence check passed for "sysstat"
Package existence check passed for "gcc"
Package existence check passed for "gcc-c++"
Package existence check passed for "ksh"
Package existence check passed for "make"
Package existence check passed for "glibc(x86_64)"
Package existence check passed for "glibc-devel(x86_64)"
Package existence check passed for "libaio(x86_64)"
Package existence check passed for "libaio-devel(x86_64)"
Package existence check passed for "nfs-utils"
Check for multiple users with UID value 0 passed
Current group ID check passed

Starting check for consistency of primary group of root user

Check for consistency of root user's primary group passed
Group existence check passed for "asmadmin"
Group existence check passed for "asmoper"
Group existence check passed for "asmdba"

Checking ASMLib configuration.
Check for ASMLib configuration passed.

Checking OCR integrity...

OCR integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed
Time zone consistency check passed

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
No NTP Daemons or Services were found to be running

Clock synchronization check using Network Time Protocol(NTP) passed

User "grid" is not part of "root" group. Check passed
Checking integrity of file "/etc/resolv.conf" across nodes

"domain" and "search" entries do not coexist in any  "/etc/resolv.conf" file
All nodes have same "search" order defined in file "/etc/resolv.conf"
The DNS response time for an unreachable node is within acceptable limit on all nodes

Check for integrity of file "/etc/resolv.conf" passed

Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...
Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed

Pre-check for node addition was successful.

2. To extend the cluster by installing clusterware on the new node run the addNode.sh in the $GI_HOME/addnode directory as grid user from an existing node. As mentioned earlier this post uses the interactive method to add the node.
Click add button and add the hostname and vip name of the new node

Fix any pre-req issues and click install to begin the GI installation on the new node.

Execute the root scripts on the new node

Output from root script execution

[root@rhel12c2 12.1.0]# /opt/app/12.1.0/grid/root.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /opt/app/12.1.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Relinking oracle with rac_on option
Using configuration parameter file: /opt/app/12.1.0/grid/crs/install/crsconfig_params
2014/03/04 16:16:06 CLSRSC-363: User ignored prerequisites during installation

OLR initialization - successful
2014/03/04 16:16:43 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.conf'

CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rhel12c2'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'rhel12c2'
CRS-2677: Stop of 'ora.drivers.acfs' on 'rhel12c2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rhel12c2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-2672: Attempting to start 'ora.mdnsd' on 'rhel12c2'
CRS-2672: Attempting to start 'ora.evmd' on 'rhel12c2'
CRS-2676: Start of 'ora.evmd' on 'rhel12c2' succeeded
CRS-2676: Start of 'ora.mdnsd' on 'rhel12c2' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rhel12c2'
CRS-2676: Start of 'ora.gpnpd' on 'rhel12c2' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'rhel12c2'
CRS-2676: Start of 'ora.gipcd' on 'rhel12c2' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rhel12c2'
CRS-2676: Start of 'ora.cssdmonitor' on 'rhel12c2' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rhel12c2'
CRS-2672: Attempting to start 'ora.diskmon' on 'rhel12c2'
CRS-2676: Start of 'ora.diskmon' on 'rhel12c2' succeeded
CRS-2789: Cannot stop resource 'ora.diskmon' as it is not running on server 'rhel12c2'
CRS-2676: Start of 'ora.cssd' on 'rhel12c2' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rhel12c2'
CRS-2672: Attempting to start 'ora.ctssd' on 'rhel12c2'
CRS-2676: Start of 'ora.ctssd' on 'rhel12c2' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rhel12c2' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'rhel12c2'
CRS-2676: Start of 'ora.asm' on 'rhel12c2' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'rhel12c2'
CRS-2676: Start of 'ora.storage' on 'rhel12c2' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'rhel12c2'
CRS-2676: Start of 'ora.crsd' on 'rhel12c2' succeeded
CRS-6017: Processing resource auto-start for servers: rhel12c2
CRS-2672: Attempting to start 'ora.ons' on 'rhel12c2'
CRS-2676: Start of 'ora.ons' on 'rhel12c2' succeeded
CRS-6016: Resource auto-start has completed for server rhel12c2
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2014/03/04 16:22:11 CLSRSC-343: Successfully started Oracle clusterware stack

clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 12c Release 1.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
2014/03/04 16:22:37 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

This conclude the phase one. Next phase is to add the database software to new node.



3. To add database software run addNode.sh in the $ORACLE_HOME/addnode directory as oracle user from an existing node. When the OUI starts the new node comes selected by default.

At the end of the database software installation message is shown to invoke DBCA to extend the database to new node.

This conclude phase two
4. Final phase is to extend the database to the new node. Before invoking DBCA change the permission on the directory $ORACLE_BASE/admin to include write permission for the oinstall group so that oracle user is able to write into the directory. After the database software is installed the permission on this directory was as follows

[oracle@rhel12c2 oracle]$ ls -l
drwxr-xr-x. 3 grid   oinstall 4096 Mar  4 16:21 admin

Since oracle user doesn't have write permission (as oinstall group doesn't have write permission) the DBCA fails with the following.

Change permissions with

chmod 775 admin

and invoke the DBCA.
5. Select instance management from DBCA and then add instance.

Select which database to extend (if there are multiple databases in the cluster and confirm the new instance details (comes auto populated)

6. Check the instance is visible on the cluster

[oracle@rhel12c1 addnode]$ srvctl config database -d cdb12c
Database unique name: cdb12c
Database name: cdb12c
Oracle home: /opt/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/cdb12c/spfilecdb12c.ora
Password file: +DATA/cdb12c/orapwcdb12c
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: cdb12c
Database instances: cdb12c1,cdb12c2
Disk Groups: DATA,FLASH
Mount point paths:
Services: pdbsvc
Type: RAC
Start concurrency:
Stop concurrency:
Database is administrator managed

SQL> select inst_id,instance_name,host_name from gv$instance;

   INST_ID INSTANCE_NAME    HOST_NAME
---------- ---------------- -------------------
         1 cdb12c1          rhel12c1.domain.net
         2 cdb12c2          rhel12c2.domain.net


SQL> select con_id,name from gv$pdbs;

    CON_ID NAME
---------- ---------
         2 PDB$SEED
         3 PDB12C
         2 PDB$SEED
         3 PDB12C

The service created for this PDB is not yet available on the new node. As seen below only one instance appear as preferred instance and none on the available instance.

[oracle@rhel12c1 ~]$ srvctl config service -d cdb12c -s pdbsvc
Service name: pdbsvc
Service is enabled
Server pool: cdb12c_pdbsvc
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: pdb12c
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
Preferred instances: cdb12c1
Available instances:

Modify the service to include the instance on the newly added node as well

[oracle@rhel12c1 ~]$ srvctl modify service -db cdb12c -pdb pdb12c -s pdbsvc -modifyconfig -preferred "cdb12c1,cdb12c2"

[oracle@rhel12c1 ~]$ srvctl config service -d cdb12c -s pdbsvc
Service name: pdbsvc
Service is enabled
Server pool: cdb12c_pdbsvc
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: pdb12c
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
Preferred instances: cdb12c1,cdb12c2
Available instances:

7. Use cluvfy to perform the post node add checks

[grid@rhel12c1 ~]$ cluvfy stage -post nodeadd -n rhel12c2

With this concludes the addition of a new node to 12cR1 RAC.

Related Posts
Adding a Node to 11gR2 RAC
Adding a Node to 11gR1 RAC