Change Public/SCAN/Virtual IP/Name in 11g/12c RAC

When working with Real Application Cluster DB, changing the infrastructure properties is bit tricky if not difficult. There is dependency chain with several network & name properties and dependent component from Oracle perspective are also needed to be modified. 

I recently undertook the exercise to do so for one of our RAC Cluster which resulted in this post. There are several use cases which I have covered as follows. 

Case I.   Changing Public Host-name

Public hostname is recorded in OCR, it is entered during installation phase. It can not be modified after the installation. The only way to modify public hostname is by deleting the node, then add the node back with a new hostname, or reinstall the clusterware.

Case II.  Changing Public IP Only Without Changing Interface, Subnet or Netmask

If the change is only public IP address and the new ones are still in the same subnet, nothing needs to be done on clusterware layer, all changes need to be done at OS layer to reflect the change.

1. Shutdown Oracle Clusterware stack
2. Modify the IP address at network layer, DNS and /etc/hosts file to reflect the change
3. Restart Oracle Clusterware stack

Above change can be done in rolling fashion, eg: one node at a time.

Case II.  Changing SCAN / SCAN IP

SCAN is used to access cluster as whole from oracle database clients and can redirect your connection request to any available node on the cluster where the requested service is running. This resource is cluster resource and can fail over to any other node if the node where it is running should fail. The entry of SCAN is in OCR and IP is configured at DNS level. 
So to change to SCAN IP / Name one has to first populate the changes on DNS to take it into effect. Once the changes are in effect, one can modify the SCAN resource in OCR as follows. Remember SCAN acts as cluster entry point and load balancing process , the restart to SCAN will require a brief outage. However the existing connection will not have any impact. 

[oracle@dbrac2 ~]$ srvctl  status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node dbrac1
[oracle@dbrac2 ~]$ srvctl  status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node dbrac1

[oracle@dbrac2 ~]$ srvctl config  scan
SCAN name: dbrac-scan.localdomain, Network: 1
Subnet IPv4: 192.168.2.0/255.255.255.0/eth1, static
Subnet IPv6:
SCAN 0 IPv4 VIP: 192.168.2.110
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:

[oracle@dbrac2 ~]$ srvctl stop scan_listener
[oracle@dbrac2 ~]$ srvctl stop scan
[oracle@dbrac2 ~]$ srvctl  status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is not running
[oracle@dbrac2 ~]$ srvctl  status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is not running

-- MODIFY THE SCAN IP AT OS LEVEL

[root@dbrac2 ~]$ srvctl  modify scan -scanname dbrac-scan.localdomain

[oracle@dbrac2 ~]$ srvctl  config scan
SCAN name: dbrac-scan.localdomain, Network: 1
Subnet IPv4: 192.168.2.0/255.255.255.0/eth1, static
Subnet IPv6:
SCAN 0 IPv4 VIP: 192.168.2.120
SCAN VIP is enabled.

[oracle@dbrac2 ~]$ srvctl start scan_listener
[oracle@dbrac2 ~]$ srvctl start scan

Since this is my test cluster, I have configured only one SCAN, but regardless of it, the process remains the same for SCAN with 3 IPs.

Case II.  Changing Virtual IP / Virtual Host Name

-- CHANGING NODE VIP FROM 192.168.2.103 TO 192.168.2.203 ON DBRAC1
-- SINCE NODE VIP IS PART OF NODE APPS ONE NEEDS TO MODIFY THE IP ADDRESS ON OS LEVEL AND THEN USE SRVCTL TO MODIFY NODEAPPS

[oracle@dbrac1 automation]$ srvctl  config vip -node dbrac1
VIP exists: network number 1, hosting node dbrac1
VIP Name: dbrac1-vip.localdomain
VIP IPv4 Address: 192.168.2.103
VIP IPv6 Address:
VIP is enabled.
VIP is individually enabled on nodes:
VIP is individually disabled on nodes:

[oracle@dbrac1 automation]$ srvctl  stop vip -node dbrac1
PRCR-1065 : Failed to stop resource ora.dbrac1.vip
CRS-2529: Unable to act on 'ora.dbrac1.vip' because that would require stopping or relocating 'ora.LISTENER.lsnr', but the force option was not specified

[oracle@dbrac1 automation]$ srvctl  stop vip -node dbrac1 -force
[oracle@dbrac1 automation]$ srvctl  status  vip -node dbrac1
VIP dbrac1-vip.localdomain is enabled
VIP dbrac1-vip.localdomain is not running

-- NOW MODIFY THE ADDRESS OF NODE VIP ON OS LEVEL USING EITHER /etc/hosts OR DNS.
-- Once done, use SRVCTL to modify OCR resource. 
-- Here I am not changing the name, but only IP 

[oracle@dbrac1 automation]$ srvctl modify nodeapps -node dbrac1 -address dbrac1-vip.localdomain/255.255.255.0/eth1
[oracle@dbrac1 automation]$ srvctl config   vip -node dbrac1
VIP exists: network number 1, hosting node dbrac1
VIP Name: dbrac1-vip.localdomain
VIP IPv4 Address: 192.168.2.203
VIP IPv6 Address:
VIP is enabled.
VIP is individually enabled on nodes:
VIP is individually disabled on nodes:

[root@dbrac2 ~]# srvctl  config nodeapps
Network 1 exists
Subnet IPv4: 192.168.2.0/255.255.255.0/eth1, static
Subnet IPv6:
Ping Targets:
Network is enabled
Network is individually enabled on nodes:
Network is individually disabled on nodes:
VIP exists: network number 1, hosting node dbrac1
VIP Name: dbrac1-vip.localdomain
VIP IPv4 Address: 192.168.2.203
VIP IPv6 Address:
VIP is enabled.
VIP is individually enabled on nodes:
VIP is individually disabled on nodes:
VIP exists: network number 1, hosting node dbrac2
VIP Name: dbrac2-vip.localdomain
VIP IPv4 Address: 192.168.2.104
VIP IPv6 Address:
VIP is enabled.
VIP is individually enabled on nodes:
VIP is individually disabled on nodes:
ONS exists: Local port 6100, remote port 6200, EM port 2016, Uses SSL false
ONS is enabled
ONS is individually enabled on nodes:
ONS is individually disabled on nodes:

[oracle@dbrac1 automation]$ srvctl  start vip -node dbrac1
[root@dbrac2 ~]# srvctl  status  nodeapps
VIP dbrac1-vip.localdomain is enabled
VIP dbrac1-vip.localdomain is running on node: dbrac1
VIP dbrac2-vip.localdomain is enabled
VIP dbrac2-vip.localdomain is running on node: dbrac2
Network is enabled
Network is running on node: dbrac1
Network is running on node: dbrac2
ONS is enabled
ONS daemon is running on node: dbrac1
ONS daemon is running on node: dbrac2

-- ON NODE2

[oracle@dbrac2 ~]$ srvctl  stop vip -node dbrac2 -force
[oracle@dbrac2 ~]$ srvctl  status  vip -node dbrac2
VIP dbrac2-vip.localdomain is enabled
VIP dbrac2-vip.localdomain is not running

[oracle@dbrac2 ~]$ srvctl modify nodeapps -node dbrac2 -address dbrac2-vip.localdomain/255.255.255.0/eth1

[oracle@dbrac2 ~]$ srvctl  config vip  -n dbrac2
VIP exists: network number 1, hosting node dbrac2
VIP Name: dbrac2-vip.localdomain
VIP IPv4 Address: 192.168.2.204
VIP IPv6 Address:
VIP is enabled.
VIP is individually enabled on nodes:
VIP is individually disabled on nodes:

[oracle@dbrac2 ~]$ srvctl  start vip -node dbrac2

[oracle@dbrac2]$ srvctl status  nodeapps
VIP dbrac1-vip.localdomain is enabled
VIP dbrac1-vip.localdomain is running on node: dbrac1
VIP dbrac2-vip.localdomain is enabled
VIP dbrac2-vip.localdomain is running on node: dbrac2
Network is enabled
Network is running on node: dbrac1
Network is running on node: dbrac2
ONS is enabled
ONS daemon is running on node: dbrac1
ONS daemon is running on node: dbrac2

[oracle@dbrac1 automation]$ crs_stat -t  | grep vip

ora.dbrac1.vip ora....t1.type ONLINE    ONLINE    dbrac1     
ora.dbrac2.vip ora....t1.type ONLINE    ONLINE    dbrac2     
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    dbrac1


A special case for 11gR2  VIP Name Change - 
modifying the VIP hostname only without changing the IP address.

For example: only VIP hostname changes from dbrac1-vip to dbrac1-nvip, IP and other attributes remain the same.

If IP address is not changed, above modify command will not change the USR_ORA_VIP value in 'crsctl stat res ora.dbrac1.vip -p' output. Please use the following command:

# crsctl modify res ora.dbrac1.vip -attr USR_ORA_VIP=ora.dbrac1.nvip

Verify the changes for USR_ORA_VIP field:
# crsctl stat res ora.dbrac1.vip -p |grep USR_ORA_VIP

Three important flag for crsctl stat res command are as follows. 
   -p                Print static configuration
   -v                Print runtime configuration
   -f                Print full configuration

De-Configure & Re-configure GI / Cluster Nodes

when you are De-configuring the cluster there are various reasons. One of the most common case is when root.sh failed on the node. In this case one has to deconfigure the cluster node with -force option and has to run root.sh manually. 

Also there are other use cases for De-configure - 

This procedure applies only when all the followings are true:

• One or partial nodes are having problem, but one or other nodes are running fine - so there's no need to deconfigure the entire clustere

• And GI is a fresh installation (NOT upgrade) without any patch set (interim patch or patch set update(PSU) is fine).

• And cluster parameters have not been changed since original configuration, eg: OCR/VD on same location, network configuration has not been changed etc

Steps to de-configure-

As root, on each problematic node, execute:

# <$GRID_HOME>/crs/install/rootcrs.pl -deconfig -force

Steps to reconfigure       

         # <$GRID_HOME>/root.sh
There is one caveat to this process and that is use of config.sh utility. The config.sh utility invokes the wizard that will ask you the properties of the cluster and prepare the rootcrs_param file, which will be copied to all cluster nodes. It will also prompt you to run root.sh( which will in turn call rootconfig script ).
config.sh only needs to be run on one node of the cluster – all required files are propagated to other nodes within the cluster

Cases that config.sh can be used:

• After GI cluster is deconfigured with rootcrs.pl on all nodes

• After GI is cloned from other cluster

• After GI is installed with software only option

Cases that config.sh is not the best tool:

For GI cluster environment, as it will configure/reconfigure all nodes in the cluster which means down time, it is not the best tool for the following scenarios as no down time is needed to accomplish these tasks:

• one or more nodes are having problem, but there is node or nodes that are running fine, in this case, node removal/addition procedure can be used to avoid downtime.

• one or more nodes are having problem, but there is node or nodes that are running fine, and the cluster is freshly installed without any patch set regardless how long it has been running - if patch set update(PSU) has been applied, that is fine, and cluster parameters are not changed since original configuration, eg: OCR/VD on same location, network configuration has not been changed etc, and GRID_HOME is intact. In this case, deconfig and reconfig on each problematic node can be used (as root, execute "$GRID_HOME/crs/install/rootcrs.pl -deconfig -force" then "$GRID_HOME/root.sh").

If the above doesn't fix the issue then, node removal/addition procedure should be used.

Some important points to remember when Re-running root.sh

• Logfiles
• root.sh log file :    $GRID_HOME/cfgtoollogs/crsconfig/rootcrs_grac31.log
• Checkpoint File    $GRID_HOME/u01/app/grid/Clusterware/ckptxxx.xml
• Voting disks and OCR are re-discovered rerunning root.sh
• User created resources like database resources or service resources are rediscovered
• root.sh reconfigures OLR / OHASD ( files in  /etc/rc.d/ /etc/init.d are recreated )
• Backup OCR / OLR before rerunning root.sh
• rootcrs.pl -deconfig -force -verbose  -lastnode does not  delete the +OCR DG
• If you need to cleanup your OCR DG  you may need to use dd command to erase the +OCR disks ( see reference section )

12c Pluggable DB Point In Time Recovery

While working with pluggable database in our test environment, we came across situation where we want to to point in time recovery of one of the pluggable database.

Version - 12.1.0.2
Container DB - ORCL1 & ORCL2 (RAC CDB)
Pluggable DB - PDB1

Command used to take backup is as follows -

backup pluggable database pdb1 plus archivelog tag='pdb1 backup';

BEFORE INITIATING RESTORE AND RECOVERY MAKE SURE PDB IS IN MOUNTED STATE -

SQL> select name, open_mode from gv$pdbs

NAME                   OPEN_MODE
------------------------------ ----------
PDB$SEED               READ ONLY
PDB1                   MOUNTED
PDB$SEED               READ ONLY
PDB1                   MOUNTED

PDB restore can be done either by directly connecting to PDB or thru CDB. In this case, I will be doing restore and recovery thru CDB, which is preferred way. Following are the steps to analyse and perform restore/recovery of PDB

-- To Identify the latest point to which I need to perform PITR.

RMAN> restore pluggable database pdb1 preview;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
33      Full    607.06M    DISK        00:00:30     10-02-15 21:37:41
        BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20150210T213722
        Piece Name: +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/BACKUPSET/2015_02_10/nnndf0_tag20150210t213722_0.495.871335443
  List of Datafiles in backup set 33
  Container ID: 3, PDB Name: PDB1
  File LV Type Ckp SCN    Ckp Time          Name
  ---- -- ---- ---------- ----------------- ----
  19      Full 9644899    10-02-15 21:36:26 +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/system.293.871263527
  20      Full 9644899    10-02-15 21:36:26 +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/sysaux.296.871263527
  21      Full 9644899    10-02-15 21:36:26 +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/users.292.871263605

archived logs generated after SCN 9646487 not found in repository
RMAN-05119: recovery can not be done to a consistent state.
Media recovery start SCN is 9646487
Recovery must be done beyond SCN 9646487 to clear datafile fuzziness
Finished restore at 10-02-15 21:56:09

-- Validate the ability of backup, if re-storable or not.

RMAN> restore pluggable database pdb1 validate;

Starting restore at 10-02-15 21:56:57
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/BACKUPSET/2015_02_10/nnndf0_tag20150210t213722_0.495.871335443
channel ORA_DISK_1: piece handle=+DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/BACKUPSET/2015_02_10/nnndf0_tag20150210t213722_0.495.871335443 tag=TAG20150210T213722
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:08
Finished restore at 10-02-15 21:57:05

[oracle@dbrac1 admin]$ rman target /
connected to target database: ORCL (DBID=1388310818)

RMAN> spool log to rman.log
RMAN> run  {
set until time "to_date('2015-10-02 21:36:26','YYYY-DD-MM HH24:MI:SS')";2>
restore pluggable database pdb1;
recover pluggable database pdb1;
}
2> RMAN>
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 instance=orcl1 device type=DISK

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 00019 to +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/system.293.871263527
channel ORA_DISK_1: restoring datafile 00020 to +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/sysaux.296.871263527
channel ORA_DISK_1: restoring datafile 00021 to +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/DATAFILE/users.292.871263605
channel ORA_DISK_1: reading from backup piece +DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/BACKUPSET/2015_02_10/nnndf0_tag20150210t213722_0.495.871335443
channel ORA_DISK_1: piece handle=+DATA/ORCL/0EAE548942B125F6E0536502A8C06EE1/BACKUPSET/2015_02_10/nnndf0_tag20150210t213722_0.495.871335443 tag=TAG20150210T213722
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 10-02-15 22:00:40

RMAN>
Starting recover at 10-02-15 22:00:42
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 10-02-15 22:00:45

RMAN> alter pluggable database pdb1 open ;Statement processed


SQL> select name, open_mode from gv$pdbs;

NAME                   OPEN_MODE
------------------------------ ----------
PDB$SEED               READ ONLY
PDB1                   READ WRITE
PDB$SEED               READ ONLY
PDB1                   MOUNTED

Hope this will help you in pluggable database recovery.

Remote ASM File Copy Issue

Recently during one of our restore exercise, we need to transfer the backup files from ASM  of Cluster A to ASM of Cluster B. When I tried to copy the file using following command if failed continuously. 

## To make error message more meaningful, need to enable the trace..
export   DBI_TRACE=1

ASMCMD [+] > cp +image_bkp/prd/autobackup/2015_02_05/s_870845182.578.870845183 sys@rac_tgt.1529.+ASM2:+RAC5_DATA/BACKUP_PIECES/prd/

Enter password: ******
    -> DBI->connect(dbi:Oracle:host=rac_tgt.1529;port=1521;sid=+ASM2, sys, ****, HASH(0x1b0dde70))
connect using '(DESCRIPTION=(ADDRESS=(HOST=rac_tgt.1529)(PROTOCOL=tcp)(PORT=1521))(CONNECT_DATA=(SID=+ASM2)))'    !! ERROR: '12545' 'ORA-12545: Connect failed because target host or object does not exist (DBD ERROR: OCIServerAttach)' (err#0)
    <- connect('host=rac_tgt.1529;port=1521;sid=+ASM2', 'sys', ...)= undef at DBI.pm line 637
       DBI connect('host=rac_tgt.1529;port=1521;sid=+ASM2','sys',...) failed: ORA-12545: Connect failed because target host or object does not exist (DBD ERROR: OCIServerAttach)
ASMCMD-8201: (Bad argc for RDBMS:ASMCMD-8201)

ASMCMD [+] > cp --port 1523 +image_bkp/prd/autobackup/2015_02_05/s_870845182.578.870845183 sys@rac_tgt.+ASM2:+RAC5_DATA/BACKUP_PIECES/prd/s_870845182.578.870845183 --port 1529


copying +image_bkp/prd/autobackup/2015_02_05/s_870845182.578.870845183 -> rac_tgt.intra.searshc.com:+RAC5_DATA/BACKUP_PIECES/prd/s_870845182.578.870845183
    !! ERROR: '15056' 'ORA-15056: additional error message
ORA-15046: ASM file name '+RAC5_DATA/BACKUP_PIECES/prd/s_870845182.578.870845183' is not in single-file creation form
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 413
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)' (err#1)
    <- execute= undef at asmcmdbase.pm line 3509
ASMCMD-8016: copy source->'+image_bkp/prd/autobackup/2015_02_05/s_870845182.578.870845183' and target->'+RAC5_DATA/BACKUP_PIECES/prd/s_870845182.578.870845183' failed


Checking Metalink Note # 452158.1 I saw that the ASMCMD  cp command fails because the ASM file name was not in a form that can be used to create an single file. File name should not contain the file number/incarnation.

ASMCMD [+] > cp --port 1523 +image_bkp/prd/autobackup/2015_02_05/s_870845182.578.870845183 sys@rac_tgt.+ASM2:+RAC5_DATA/BACKUP_PIECES/prd/test --port 1529
Enter password: ******

On target server -

ASMCMD [+RAC5_DATA/BACKUP_PIECES/prd] > ls -lt

Type        Redund  Striped  Time             Sys  Name
                                              N    AUG11/
                                              N    test => +RAC5_DATA/ASM/AUTOBACKUP/test.2365.870918155

As you can see the file is created with link pointing to file with incarnation and file number. 

Load SQL Plans into SQL Plan Management (SPM) 
from the Automatic Workload Repository (AWR) 

Managing performance can be tricky thing specially when one is dealing with SQL Performance. There are many varied reasons which could be result in performance degradation. Usually, when there is change in profile of data or introduction of new data structures or upgrades. This can be achieved by plan stability and it is widely used feature of Oracle DB. The Baseline is key to achieving plan stability. 

When one has performance issues with SQL, one can achieve the stable plan either from history or from cached statement, known to have good plan. 
This note provide steps for loading a SQL plan into SQL plan baseline from AWR.

As a starting point one has to be clear about the culprit causing performance bottleneck. 
you need to gather info about sql_id and other related info which can be used to achieve desired result. 

1. As a first step one needs to create SQL Tuning Set. You can see it as a kind of container object that holds the statements and plan for your statement.                                                                                                                                                
exec dbms_sqltune.create_sqlset(sqlset_name => '76cmg604xv5gm_sqlset_test',description => 'sqlset test HS');

PL/SQL procedure successfully completed.

2. Next step is, since we want to load plan from AWR repository, is to identify the range of snap id which contains the sql statement and plan, we are after. 

One can use following two views to get that info. 
To find snapshot related info - dba_hist_snapshot 
To Identify the snap_id in which the sql belongs  - dba_hist_sqlstat 

Use following code to load data from AWR to SQL Tuning Set. 

declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(27437,27440,'sql_id='||CHR(39)||'76cmg604xv5gm'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET('76cmg604xv5gm_sqlset_test', baseline_ref_cur);
end;
/

PL/SQL procedure successfully completed.

To load all the sqls captured in AWR snapshot use “NULL” in basic_filter.

declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('all_sqlset_test', baseline_ref_cur);
end;
/

Now check if the SQL Set is populated with data or not...

SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='76cmg604xv5gm_sqlset_test';


NAME                           OWNER                          CREATED  
------------------------------ ------------------------------ ---------
STATEMENT_COUNT
---------------
76cmg604xv5gm_sqlset_test      SYS                            03-FEB-15
              1
1 row selected.

To check statements from SQL Set - 

select sql_id, substr(sql_text,1, 15) text from dba_sqlset_statements
where sqlset_name = '76cmg604xv5gm_sqlset_test';

To Check the contents of the SQL Set -                                                                              
select * from table(dbms_xplan.display_sqlset('76cmg604xv5gm_sqlset_test','&sql_id'));

Enter sql_id : 76cmg604xv5gm       

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Error: cannot fetch explain plan for the specified SQL tuning set and sql_id    
1 row selected.

If you see at the moment, the SQL Plan Baselines will be empty. 

select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
         0
1 row selected.

3. Next, load SQL execution plans from SQLSET into SQL Baseline 

Use following code to load sql plan from STS to baselines. 

declare
my_int pls_integer;
begin
my_int := dbms_spm.load_plans_from_sqlset (sqlset_name => '76cmg604xv5gm_sqlset_test', sqlset_owner => 'SYS',fixed => 'NO', enabled => 'YES');
end;
/

PL/SQL procedure successfully completed.

You can also set basic filter as follows

basic_filter => 'sql_id="76cmg604xv5gm"'

One done, check the SQL Plan Baselines, and you will see that it has baselines created for that specific SQL. 

SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE FROM   DBA_SQL_PLAN_BASELINES;

Hope this will help you in trouble shooting your severe performance hangs.

12.1.0.2 Bug When Starting Grid Infra Management Repository DB 

During start up 12.1.0.2 Grid Infrastructure Management Repository, we came across the following error.

Startup failed with following error..

[oracle@dbrac1 bin]$ srvctl start mgmtdb
PRCR-1079 : Failed to start resource ora.mgmtdb
CRS-5017: The resource action "ora.mgmtdb start" encountered the following error:
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/dbrac2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.mgmtdb' on 'dbrac2' failed
CRS-5017: The resource action "ora.mgmtdb start" encountered the following error:
ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/dbrac1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.mgmtdb' on 'dbrac1' failed
CRS-2632: There are no more servers to try to place resource 'ora.mgmtdb' on that would satisfy its placement policy

Upon checking alert logs we noticed the corruption, albeit logical... 

Beginning crash recovery of 1 threads

Tue Jan 13 21:22:21 2015

Started redo scan

Hex dump of (file 4, block 3301) in trace file /u01/app/oracle/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_ora_22748.trc

Reading datafile '+DATA/_MGMTDB/DATAFILE/undotbs1.259.860525535' for corruption at rdba: 0x01000ce5 (file 4, block 3301)

Reread (file 4, block 3301) found same corrupt data (logically corrupt)

Write verification failed for File 4 Block 3301 (rdba 0x1000ce5)

Tue Jan 13 21:22:36 2015

Errors in file /u01/app/oracle/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_m000_22997.trc:

ORA-00338: log 2 of thread 1 is more recent than control file

ORA-00312: online log 2 thread 1: '+DATA/_MGMTDB/ONLINELOG/group_2.262.860525589'

Errors in file /u01/app/oracle/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_ora_22748.trc  (incident=41020) (PDBNAME=CDB$ROOT):

ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/_mgmtdb/-MGMTDB/incident/incdir_41020/-MGMTDB_ora_22748_i41020.trc

Tue Jan 13 21:22:49 2015

Dumping diagnostic data in directory=[cdmp_20150113212249], requested by (instance=1, osid=22748), summary=[incident=41020].

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Tue Jan 13 21:22:51 2015

Aborting crash recovery due to error 600

Tue Jan 13 21:22:51 2015

Errors in file /u01/app/oracle/diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_ora_22748.trc:

ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [], [], [], [], [], [], [], [], []

However, our management listener was running fine. 

[oracle@dbrac1 trace]$ srvctl status  mgmtlsnr
Listener MGMTLSNR is enabled
Listener MGMTLSNR is running on node(s): dbrac1

Upon checking MOS, we figure this is a partially published bug which caused this behaviour. The solution to overcome to this is as follows.

export ORACLE_SID= -MGMTDB

SQL>  startup mount
SQL>  recover database;
SQL>  alter database open ;

Upon startup we hit the following error
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 29109
Session ID: 1 Serial number: 24261

Alert Logs shows... 

ORA-00338: log 2 of thread 1 is more recent than control file
ORA-00312: online log 2 thread 1: '+DATA/_MGMTDB/ONLINELOG/group_2.262.860525589'

This means the SCN of control file is older than SCN of logfile. This happens when you do recovery of old controlfile, which was not the case here. So something does not seem right. I also tried to start the DB on second node of cluster and again it failed.
I also tried to start MGMTCA tool, to reconfigure the management repository but it failed since my GIRM DB was not running.

There may be possibility of the wrong control file being used, but again that was ruled out. So ultimately, what I have to do is to simulate the cancel based recovery and then try to start the db with resetlogs option. 

SQL> startup mount
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size                  2929352 bytes
Variable Size             314576184 bytes
Database Buffers          465567744 bytes
Redo Buffers                5455872 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.

And as you can this approach worked. 

[oracle@dbrac1 bin]$ ps -ef | grep pmon
oracle    3320     1  0 20:30 ?        00:00:00 asm_pmon_+ASM1
oracle    3508     1  0 22:10 ?        00:00:00 mdb_pmon_-MGMTDB
oracle    4712 18509  0 22:13 pts/1    00:00:00 grep pmon
oracle    6378     1  0 20:34 ?        00:00:00 ora_pmon_orcl1
[oracle@dbrac1 bin]$ srvctl  start mgmtdb

[oracle@dbrac1 bin]$ srvctl  status mgmtdb

Database is enabled

Instance -MGMTDB is running on node dbrac1

Hope this helps...

MongoDB Upgrade From 2.4.x to 2.6.x

Recently, in our environment, we undertook an exercise  to test the new version of MongoDB and look n feel of the upgrade process. 

Our env has following components - 

2 MongoS processes.
3 config servers.
2 shard servers. 

All of the above was using version 2.4.8, which we intend to upgrade to latest version of 2.6.5. 

To begin the upgrade procedure, connect a 2.6 mongo shell to your MongoDB 2.4 mongos or mongod and run the db.upgradeCheckAllDBs() to check your data set for compatibility. This is a preliminary automated check. Assess and resolve all issues identified by db.upgradeCheckAllDBs().

mongos> use admin
switched to db admin
mongos> db.upgradeCheckAllDBs()

Checking database sharding

Checking collection sharding.col1
37055 documents processed
76072 documents processed
......
.....
.....
Checking database admin

Checking collection admin.system.indexes

Checking collection admin.system.users
Everything is ready for the upgrade!
true



-- Now one has to  Stop/Disable the Balancer, so that there are no cluster related operations.

mongos> sh.isBalancerRunning()
true
mongos> sh.stopBalancer()
Waiting for active hosts...
Waiting for the balancer lock...
Waiting again for active hosts after balancer is off...
mongos> sh.isBalancerRunning()
false


There is specific order of the upgrade when you wish to upgrade the sharded cluster.
1. Mongos upgrade
2. config server upgrade
3. shard server upgrade. 

1. Upgrade mongos.

-- unzip the binaries of 2.6 mongos and use it to start new mongos of 2.6 version

-- stop mongos 
root      2988  1941  0 16:24 pts/1    00:00:29 /usr/bin/mongos --configdb localhost:19001,localhost:19002,localhost:19003 --logpath /db/mongos1.log --port 30002

root@INPUNPCLX:~# kill 2988

root@INPUNPCLX:~/mongodb-linux-x86_64-2.6.5/bin# /usr/bin/mongos --version
MongoS version 2.4.8 starting: pid=4471 port=27017 64-bit host=INPUNPCLX (--help for usage)

root@INPUNPCLX:~/mongodb-linux-x86_64-2.6.5/bin# mv /usr/bin/mongos /usr/bin/mongos.bak

root@INPUNPCLX:~/mongodb-linux-x86_64-2.6.5/bin# scp ./mongos /usr/bin/mongos
root@INPUNPCLX:~/mongodb-linux-x86_64-2.6.5/bin# which mongos
/usr/bin/mongos
root@INPUNPCLX:~/mongodb-linux-x86_64-2.6.5/bin# /usr/bin/mongos --version
MongoS version 2.6.5 starting: pid=4491 port=27017 64-bit host=INPUNPCLX (--help for usage)


Start a single 2.6 mongos instance with the configDB pointing to the cluster’s config servers and with the --upgrade option.

To run a mongos with the --upgrade option, you can upgrade an existing mongos instance to 2.6, or if you need to avoid reconfiguring a production mongos instance, you can use a new 2.6 mongos that can reach all the config servers.

To upgrade the meta data, run:

mongos --configdb <configDB string>  --logpath  xxxx --upgrade --port nnnnn

-- to start a new mongos 
 /usr/bin/mongos --configdb "localhost:19001,localhost:19002,localhost:19003" --upgrade  --logpath /db/mongos2.log --port 30002 &

7ac7218077eb4215", server: "INPUNPCLX", clientAddr: "N/A", time: new Date(1417182378230), what: "finished upgrade of config database", ns: "config.version", details: { from: 4, to: 5 } }
2014-11-28T19:16:18.248+0530 [mongosMain] upgrade of config server to v5 successful
2014-11-28T19:16:18.385+0530 [mongosMain] distributed lock 'configUpgrade/INPUNPCLX:30002:1417182377:1804289383' unlocked.
2014-11-28T19:16:18.385+0530 [mongosMain] Config database is at version v5


The mongos will exit upon completion of the --upgrade process.

Upgrade the remaining mongos instances to v2.6.
Upgrade and restart without the --upgrade option the other mongos instances in the sharded cluster. After upgrading all the mongos, see Complete Sharded Cluster Upgrade for information on upgrading the other cluster components

root@INPUNPCLX:~# ps -ef | grep mongos
root      2925     1  1 16:23 pts/1    00:02:38 /usr/bin/mongos --configdb localhost:19001,localhost:19002,localhost:19003 --logpath /db/mongos1.log --port 30001
root      4596  4102  0 19:17 pts/5    00:00:00 grep --color=auto mongos
root@INPUNPCLX:~# kill 2925
root@INPUNPCLX:~# /usr/bin/mongos --configdb "localhost:19001,localhost:19002,localhost:19003" --logpath /db/mongos1.log --port 30001 &
[1] 4598
root@INPUNPCLX:~# 2014-11-28T19:17:56.370+0530 log file "/db/mongos1.log" 

-- start the another mongos 

root@INPUNPCLX:~# /usr/bin/mongos --configdb "localhost:19001,localhost:19002,localhost:19003" --logpath /db/mongos2.log --port 30002 &
[2] 4645
root@INPUNPCLX:~# 2014-11-28T19:18:19.123+0530 log file "/db/mongos2.log" exists; moved to "/db/mongos2.log.2014-11-28T13-48-19".

root@INPUNPCLX:~# ps -ef | grep mongos
root      4598  4102  0 19:17 pts/5    00:00:00 /usr/bin/mongos --configdb localhost:19001,localhost:19002,localhost:19003 --logpath /db/mongos1.log --port 30001
root      4645  4102  1 19:18 pts/5    00:00:00 /usr/bin/mongos --configdb localhost:19001,localhost:19002,localhost:19003 --logpath /db/mongos2.log --port 30002

-- Do a test of connection, with new mongos 
root@INPUNPCLX:~# mongo --port 30001 admin -u hardik -p xxxx
MongoDB shell version: 2.6.5
connecting to: 127.0.0.1:30001/admin

2. Upgrade config db 

Upgrade all 3 mongod config server instances, leaving the first system in the mongos --configdb argument to upgrade last.


--For example, on Linux, run 2.4 mongod with the --shutdown option as follows:
mongod --dbpath /var/mongod/data --shutdown

-- Start the new mongod instance. Ensure you start the 2.6 mongod with the same dbPath:
mongod --dbpath /var/mongod/data

root      2889     1  0 16:23 ?        00:01:39 /usr/bin/mongod --configsvr --config /etc/config3.conf
root      2893     1  0 16:23 ?        00:01:34 /usr/bin/mongod --configsvr --config /etc/config2.conf
root      4722  4102  0 19:20 pts/5    00:00:00 grep --color=auto mongod
root@INPUNPCLX:~# kill 2889

root@INPUNPCLX:~# /usr/bin/mongod --version
db version v2.4.8

root@INPUNPCLX:~/mongodb-linux-x86_64-2.6.5/bin# ./mongod --version
db version v2.6.5
root@INPUNPCLX:~/mongodb-linux-x86_64-2.6.5/bin# scp ./mongod /usr/bin/mongod

root@INPUNPCLX:~/# mongod --version
db version v2.6.5
root@INPUNPCLX:/db/mongo01/data# /usr/bin/mongod --configsvr --config /etc/config3.conf &

root@INPUNPCLX:/db/mongo01/data# ps -ef | grep mongod

root      5377     1  0 19:36 ?        00:00:00 /usr/bin/mongod --configsvr --config /etc/config3.conf


root@INPUNPCLX:/db/mongo01/data# mongo --port 19003
MongoDB shell version: 2.6.5
connecting to: 127.0.0.1:19003/test
configsvr> show dbs
admin   0.063GB
config  0.063GB
local   0.031GB
configsvr> use config
switched to db config

configsvr> db.version()
2.6.5
-- above displays the config server version

WHEN YOU CONNECT THE OTHER CONFIG SERVER YOU WILL SEE THE OLD VERSION

root@INPUNPCLX:/db/mongo01/data# mongo --port 19002
MongoDB shell version: 2.6.5
connecting to: 127.0.0.1:19002/test
configsvr> use config
switched to db config
configsvr> db.version()
2.4.8

-- shutdown config2 server
root@INPUNPCLX:/db/mongo01/data# mongod --dbpath /db/config2/data --shutdown
killing process with pid: 2893

root@INPUNPCLX:/db/mongo01/data# /usr/bin/mongod --configsvr --config /etc/config2.conf &
[3] 5522
root@INPUNPCLX:/db/mongo01/data# about to fork child process, waiting until server is ready for connections.
forked process: 5524
child process started successfully, parent exiting

[3]+  Done                    /usr/bin/mongod --configsvr --config /etc/config2.conf
root@INPUNPCLX:/db/mongo01/data# ps -ef | grep mongod

root      5524     1  1 19:40 ?        00:00:00 /usr/bin/mongod --configsvr --config /etc/config2.conf

root@INPUNPCLX:/db/mongo01/data# mongo --port 19002
MongoDB shell version: 2.6.5
connecting to: 127.0.0.1:19002/test
configsvr> use config
switched to db config
configsvr> db.version()
2.6.5

-- Shut down and start up the last config server 
root@INPUNPCLX:/db/mongo01/data# mongod --dbpath /db/config1/data --shutdown
killing process with pid: 2884
root@INPUNPCLX:~# /usr/bin/mongod --configsvr --config /etc/config1.conf &
[3] 6172
root@INPUNPCLX:~# about to fork child process, waiting until server is ready for connections.
forked process: 6174
child process started successfully, parent exiting

[3]+  Done                    mongod --configsvr --config /etc/config1.conf


root@INPUNPCLX:~# ps -ef | grep mongod
root      2852     1  3 16:23 ?        00:07:28 /usr/bin/mongod --shardsvr --config /etc/mongo02.conf
root      2856     1  1 16:23 ?        00:03:17 /usr/bin/mongod --shardsvr --config /etc/mongo01.conf
root      5377     1  0 19:36 ?        00:00:01 /usr/bin/mongod --configsvr --config /etc/config3.conf
root      5524     1  0 19:40 ?        00:00:01 /usr/bin/mongod --configsvr --config /etc/config2.conf
root      6174     1  0 19:49 ?        00:00:00 /usr/bin/mongod --configsvr --config /etc/config1.conf

root@INPUNPCLX:~# mongo --port 19001
MongoDB shell version: 2.6.5
connecting to: 127.0.0.1:19001/test
configsvr> use config
switched to db config
configsvr> db.version()
2.6.5

3. Upgrade shard servers

Same way upgrade the shard servers same way you do configdb..
WARNING
Do not upgrade mongod instances until after you have upgraded all mongos instances.

-- check the shard server version
root@INPUNPCLX:~# mongo --port 20001
MongoDB shell version: 2.6.5
connecting to: 127.0.0.1:20001/test
> use config
switched to db config
> db.version()
2.4.8

-- kill the shard server 
root      2856     1  1 16:23 ?        00:03:18 /usr/bin/mongod --shardsvr --config /etc/mongo01.conf

root@INPUNPCLX:~# kill 2856

root@INPUNPCLX:~# /usr/bin/mongod --shardsvr --config /etc/mongo01.conf  &
child process started successfully, parent exiting

root      6269     1  0 19:55 ?        00:00:00 /usr/bin/mongod --shardsvr --config /etc/mongo01.conf

root@INPUNPCLX:~# mongo --port 20001
MongoDB shell version: 2.6.5
connecting to: 127.0.0.1:20001/test
Server has startup warnings:
2014-11-28T19:55:03.847+0530 ** WARNING: --rest is specified without --httpinterface,
2014-11-28T19:55:03.847+0530 **          enabling http interface
> use config
switched to db config
> db.version()
2.6.5

root@INPUNPCLX:~# ps -ef | grep mongod
root      2852     1  3 16:23 ?        00:07:29 /usr/bin/mongod --shardsvr --config /etc/mongo02.conf

root@INPUNPCLX:~# kill 2852

root@INPUNPCLX:~#  /usr/bin/mongod --shardsvr --config /etc/mongo02.conf &
[3] 6331
root@INPUNPCLX:~# 2014-11-28T19:57:58.410+0530 ** WARNING: --rest is specified without --httpinterface,
2014-11-28T19:57:58.410+0530 **          enabling http interface
about to fork child process, waiting until server is ready for connections.
forked process: 6333
child process started successfully, parent exiting


root@INPUNPCLX:~# mongo --port 20002
MongoDB shell version: 2.6.5
connecting to: 127.0.0.1:20002/test
Server has startup warnings:
2014-11-28T19:57:58.410+0530 ** WARNING: --rest is specified without --httpinterface,
2014-11-28T19:57:58.410+0530 **          enabling http interface
> use config
switched to db config
> db.version()
2.6.5

AS you can see now that even the shard servers are also upgraded. So one can use this method to upgrade the sharded clusters. Here I did not installed the Mongo RPM but used the tar'red version and copied the executables to /usr/bin myself. This process was adopted to keep control with DBA rather than to system to make trouble shooting easy. 

The only remaining bit that was not covered in post is the upgrade of Auth Schema. Which I will cover in separate post. 

Happy Learning!!