Upgrade to 11.2.0.3 Grid Infra and Rdbms with Psu October 2013

Introduction

It is always a pleasure to be challenged with a new puzzle to solve. This time i was asked to take part in  the following scenario which will be implemented on various boxes:

  • Linux box(es) being upgraded from Red Hat Linux  5.5 to 5.9 .
  • Grid infra structure (GI) to be updated from 11.2.0.2 to 11.2.0.3 ( this is a need to work with Rdbms 11.2.0.3).
  • Database software (Rdbms) 11.2.0.3 Installation.
  • Implement Psu October 2013 on both GI and Rdbms
  • Upgrade the databases ( 24 in total ) to  11.2.0.3.

Environment: Linux Server with RH 5.5, 11.2 Grid Infra installed together with 11.2.0.2 Rdbms  on a single server ( Oracle Restart).

Quite a packed program right ?  Well the Linux part i was merely a spectator i  could only wait and see how the server was bounced  and see that  My Oracle Environment ( both the databases and the listener) came back up after bouncing  so the Restart environment was performing well.  After the third reboot it became time to do my actions. Below you will see a case study which i wanted to start at first cause  I thought that would save time .. In the end I implemented Plan B ( always good to have one available). Well better buckle up and let’s get started with it.

Summary:

Below you will find three scenarios you could follow, Scenario 1 would enable you to do installation in parallel and even a week in advance but  would need extra steps ( relinking software, enable the Grid Infra structure in a number of steps). In the End when setting this up and testing with it i came to the conclusion that it does not save that much time to do the installs before  the Linux upgrade .  Average installation is about  10 – 15 Minutes so i recommend to first have the Linux upgrade in place and after the Databases and listeners come back online when they (Linux) boot the server proceed with the Installations and patching .  The scenario is less complicated and i think even more error prone. So  this will be the scenario 2 and i will follow it on the environments where i am asked to do.

Addendum

Meanwhile i have implemented the plan and upgraded the databases ( 25 in total ). I was not very pleased with the  use of DBUA  (used it in a script with silent option) because  i indeed felt less control on the process. Two major setbacks i witnessed during the upgrade with DBU: 1) in my case it bugged me that it added a local listener to the init.oras while doing the upgrade cause that crashed the upgrade ( at a restart of database with that new generated – altered init.ora the db would of course not restart )  2) the Grid agent kept altering  the Oracle Home of the databases ( so was pointing to wrong env.). Well  Together with a colleague in the end we did save the day . But that was because we failed back to the manual upgrade Method. I have listed the activities and i will add it to the  Scenario : Real Life Implemented plan.

PS . by customers Request and due to the fact that the following Parameter altered its Default behavior ( was FALSE in 11.2.0.3 became TRUE)  i had to make sure the following parameter was set again in spfile:

alter system set “_use_adaptive_log_file_sync”=FALSE scope = both;

Important Add on. In all scenarios as a baseline  i ran utlu112i.sql on all Databases  in scope. And the good news was that all components  installed where  valid! AND i created a list of invalid objects per schema to compare to the situation after the upgrade ( as proof that this dba did not break the application).

As always happy reading,

Mathijs

Scenario 1  Installing software only  as a preparation:

When I started my preparations it seemed like the best thing to install  both the Software parts ( GI and Rdbms) as software only and perform the needed steps after that.  In this case following would have been performed:

  1. Install 11.2.0. 3 Rdbms as “software only”
  2. Install 11.2.0.3 GI as software only
  3. After  The Linux upgrade would have to relink the software (described below)
  4. Would have to perform various steps to activate  the 11.2.0. GI.
  5. Would have to implement  PSU October 2013
  6. Upgrade the Databases.

Scenario 1 After the Linux upgrade would have to relink my software in full again:

Stopping the databases under control in an easy way:
 As prep for the relinking of the software I performed following step:
srvctl status home -o /opt/oracle/product/112_ee_64/db -s /var/tmp/state_file.status
srvctl stop home -o /opt/oracle/product/112_ee_64/db -s /var/tmp/state_file.dmp
In Order to relink the Rdbms software:
After shutting down the databases (see above):
Had the ORACLE_HOME  set properly
$ORACLE_HOME/bin/relink all
Note: writing relink log to: /opt/oracle/product/112_ee_64/db/install/relink.log
In Order to relink the Oracle Restart software:
 Prepare the Oracle Grid Infrastructure for a Standalone Server home for modification using the following procedure:
  1. Log in as the Oracle Grid Infrastructure software owner user and change the directory to the path Grid_home/bin, where Grid_home is the path to the Oracle Grid Infrastructure home:
cd /opt/crs/product/112_ee_64/crs/bin
  1. Shut down the Oracle Restart stack using the following command:
crsctl stop has –f
 This will show:
 CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'MySrvr1hr'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'MySrvr1hr'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'MySrvr1hr' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'MySrvr1hr'
CRS-2677: Stop of 'ora.evmd' on 'MySrvr1hr' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'MySrvr1hr' has completed
CRS-4133: Oracle High Availability Services has been stopped.
oracle@MySrvr1hr:/opt/crs/product/112_ee_64/crs/bin [+ASM]#
oracle@MySrvr1hr:/opt/crs/product/112_ee_64/crs/bin [+ASM]#
 Then:
 Relink Oracle Grid Infrastructure for a Standalone Server using the following procedure:
  1. Login as root
  2. Log in as the Oracle Grid Infrastructure for a Standalone Server owner:
  3. Login as root again:
4.     # cd /opt/crs/product/112_ee_64/crs/crs/install
5.     # perl roothas.pl -unlock
7.     $ export ORACLE_HOME=/opt/crs/product/112_ee_64/crs
8.     $ $ORACLE_HOME/bin/relink
This will show: oracle@MySrvr1hr:/opt/oracle [+ASM]# $ORACLE_HOME/bin/relink
writing relink log to: /opt/crs/product/112_ee_64/crs/install/relink.log
10.  cd/opt/crs/product/112_ee_64/crs/rdbms/install/
11.  ./rootadd_rdbms.sh 
Note. Rootadd_rdbms  came back very fast  without any output.
12.  cd /opt/crs/product/112_ee_64/crs/crs/install
13.  perl roothas.pl -patch
Checked with:
  1. export $ORACLE_HOME=/opt/crs/product/112_ee_64/crs/
  2. ./crsctl check has
 This showed:CRS-4638: Oracle High Availability Services is online Starting the databases under control in an easy way: srvctl start home -o /opt/oracle/product/112_ee_64/db -s /var/tmp/state_file.dmp

Scenario 1 After the Linux upgrade would have to do various steps to activate the GI environment:

1)      First of all, please shutdown the current database & ASM instances.

oracle@MySrvr1hr:/opt/oracle/admin/tools [+ASM]# stop.ksh

2) You need to install the new 11.2.0.<#> Patchset Grid Infrastructure Standalone  “Software Only” first (on a separated Oracle Home/directory AKA “out of place”).

3) Then configure the CSS & OHAS services as root user:

/opt/oracle/product/112_ee_64_a/asm/crs/install/roothas.pl -deconfig -force
and
/opt/crs/product/112_ee_64/crs/crs/install/roothas.pl

4) Please perform the next steps as oracle or grid OS user (Grid Infrastructure OS owner):

/opt/crs/product/112_ee_64/crs/bin/crsctl modify resource “ora.cssd” -attr “AUTO_START=1”

/opt/crs/product/112_ee_64/crs/bin/crsctl modify resource “ora.diskmon” -attr “AUTO_START=1”

Note: On release & onwards 11.2.0.3 (non-Exadata), “ora.diskmon” is not required anymore since this is an Exadata related process responsible for the I/O fencing.

5) Restart the OHAS stack as grid or oracle OS user:

/opt/crs/product/112_ee_64/crs/bin/crsctl stop has

/opt/crs/product/112_ee_64/crs/bin/crsctl start has

6) Check the CSS & OHAS state as grid or oracle OS user:

/opt/crs/product/112_ee_64/crs/bin/crsctl check has

/opt/crs/product/112_ee_64/crs/bin/crsctl check css

/opt/crs/product/112_ee_64/crs/bin/ crsctl stat resource

/opt/crs/product/112_ee_64/crs/bin/crsctl stat res –t

Note: If the CSS & OHAS service did NOT start, then you will need to reboot the Linux box and check them again.

7) Remove the Old listener running under the old Grid Infrastructure Oracle Home, thru the NETCA GUI (from the old Grid Infrastructure Oracle Home).
ps -ef|grep inherit

oracle   23430     1  0  2012 ?        1-08:34:36 /opt/oracle/product/112_ee_64_a/db/bin/tnslsnr LISTENER_MYSRVR1HR -inherit

8) Recreate the default listener (LISTENER) using port 1521, thru the NETCA GUI on the new Grid Infrastructure Oracle Home.

9) Please create the init+ASM.ora file on the $ Grid Infrastructure Oracle Home>/dbs directory with the next parameters:

asm_diskgroups= <list of diskgroups>

asm_diskstring= ‘ORCL:*’ or ‘/dev/oracleasm/disks/*’

instance_type=’asm’

large_pool_size=12M

10) Add the ASM instance as grid or oracle user or the Grid Infrastructure installation owner and execute:

/opt/crs/product/112_ee_64/crs bin/srvctl add asm


11) Enable ASM instance Auto Start as follow:

/opt/crs/product/112_ee_64/crs /bin/crsctl modify resource “ora.asm” -attr “AUTO_START=1”


12) Make sure the disks are discovered by kfod:

Example:

/opt/crs/product/112_ee_64/crs /bin/kfod asm_diskstring=’/dev/mapper/asm-*p1′ disks=all

13) If so, then startup the ASM instance as follow:

export ORACLE_SID=+ASM

/opt/crs/product/112_ee_64/crs /bin/sqlplus “/as sysasm”

SQL> startup pfile=init+ASM.ora –#init file from point #9

SQL> show parameter asm

14) Validate that the original diskgroup(s) were mounted:

SQL> select name, state from v$asm_diskgroup;

15 Finally confirm the OHAS (autostart) services start as follows:

/opt/crs/product/112_ee_64/crs /bin/crsctl stop has

/opt/crs/product/112_ee_64/crs //bin/crsctl start has

/opt/crs/product/112_ee_64/crs //bin/crsctl stat res

/opt/crs/product/112_ee_64/crs //bin/crsctl stat res -t

16) Check the new HAS version as well as follows:

/opt/crs/product/112_ee_64/crs /bin/crsctl query has releaseversion
/opt/crs/product/112_ee_64/crs /bin/crsctl query has softwareversion


Scenario 1 And after that would be able to continue with applying the Psu October 2013 on the software:

Copy to GI home & extract /opt/oracle/product/11203_ee_64/stage/p6880880_112000_Linux-x86-64.zip
Create Response file oracle@mysrvr:/opt/oracle/product/11203_ee_64/db/OPatch/ocm/bin [11203]# ocm.rsp
/opt/crs/product/112_ee_64/crs/OPatch/ocm/bin [CRS]# ocm.rsp
As Root export PATH=/opt/crs/product/112_ee_64/crs/OPatch:$PATH
which opatch
opatch auto /opt/oracle/product/11203_ee_64/stage -ocmrf /opt/crs/product/112_ee_64/crs/OPatch/ocm/bin/ocm.rsp -oh /opt/crs/product/112_ee_64/crs, /opt/oracle/product/11203_ee_64/db

Note this command  performed an installation of the psu october both in my GI home and Rdbms home.).

After that Upgrade the databases with DBUA.

Scenario 2  ( Installing new Grid Infra and Rdbms AFTER Linux patching )

As i wrote this is my preferred scenario. It will involve less steps and the runInstaller will enable you to  Upgrade the existing environement. Please be aware that the 11.2.0.3 Installations are a so-called out-of-place installation requiring new Oracle Homes . For that purpose i have requested ( and got ) extra space  +15 Gb in /opt/oracle  and same space in /opt/crs. I followed the steps below bullet by bullet.

  • Create fresh copy init.ora /opt/oracle/admin/tools/cSpfile.ksh. (using scripts to create and save spfile).
  • Perform status and config /opt/oracle/admin/tools/cSrvctlAct.ksh status. (Checking and holding status of resources in logfiles).
  • /opt/oracle/admin/tools/cSrvctlAct.ksh config (Catching details on cluster config in files).
  • Stop the dbs / Listener /opt/oracle/admin/tools/cSrvctlAct.ksh stop (This was done by Grid Infra during GI install
  • Install Gi 1120.3 Option Oracle Grid infra structuren for a standalone server
  • Install Rdbms 1120.3 Software only. ( make sure no parallel installs before )
  • Copy to GI home & extract /opt/oracle/product/11203_ee_64/stage/p6880880_112000_Linux-x86-64.zip ( this is the most up to date opatch ).
  • Create Response file oracle@mysrvr1:/opt/oracle/product/11203_ee_64/db/OPatch/ocm/bin [11203]# ocm.rsp
  • /opt/crs/product/112_ee_64/crs/OPatch/ocm/bin/ocm.rsp (location and name are needed during opatch ).
  • As Root export PATH=/opt/crs/product/112_ee_64/crs/OPatch:$PATH
  • As Root which opatch
  • As Root opatch auto /opt/oracle/product/11203_ee_64/stage -ocmrf /opt/crs/product/112_ee_64/crs/OPatch/ocm/bin/ocm.rsp -oh /opt/crs/product/112_ee_64/crs, /opt/oracle/product/11203_ee_64/db (## will apply to these two homes)
  • As Oracle Perform DBUA per Instance cDbua.kshTwo odd things:
  • 1) created spfile in $ORACLE_HOME/dbs  (was before in ASM).
  • 2) had a local listener defined alll over sudden so needed get rid of it on this box again.
  • As oracle perform /opt/oracle/admin/tools/cCatBundle.ksh
  • As oracle perform /opt/oracle/admin/tools/cSrvctlmod.ksh (done by dbua so no need for it in sep script).
  • As oracle perform /opt/oracle/admin/tools/cSrvctlAct.ksh status (Checking the status in GI).
  • As oracle perform /opt/oracle/admin/tools/cSrvctlAct.ksh config  (Stop and start using the GI).
  • As oracle perform ln -s /opt/networker/lib/libnwora.so libobk.so in new oracle home  (Dont forget your Networker Library in the lib dir in the new oracle home like i did :)).
  • As oracle perform run an archive backup as check
  • As oracle perform  per Db alter system set "_use_adaptive_log_file_sync"=FALSE scope = both; (Needed in each upgraded db by customers request).
  • As oracle perform  per Db create new pfile for the database
  • As oracle perform  per Db remover local listener entry from created new pfile
  • As oracle perform  per Db Check spfile because after upgrade it is on $ORACLE_HOME/dbs again instead of in asm where i had expected it
  • As oracle perform  per Db create new spfile in asm
  • As oracle perform  per Db srvctl modify database -dMYDB1 -p '+DATA/MYDB1/spfileMYDB1.ora'

Scenario 3 The Real Life implemented Plan.

Install the software and patch
/opt/oracle/admin/tools/cSpfile.ksh Creating a copy of spfile to Init.ora
create Migration pfiles with larger settings for shared_pool Recommended setting Min. Value Shared_pool_size > 600M ( prefer 1024M)
/opt/oracle/admin/tools/cSrvctlAct.ksh status Check the current setup in the clusterware
/opt/oracle/admin/tools/cSrvctlAct.ksh config Check the current setup in the clusterware
Dryrun . Start ./runInstaller to check Prerequisites before install and correct if needed. check for warnings and errors and correct them
Option Oracle Grid infra structuren for a standalone server Install in separate Oracle Home And choose Upgrade
Software only. ( make sure no parallel installs before ) Oracle Home Needs 2 b empty
/opt/oracle/product/11203_ee_64/stage/p6880880_112000_Linux-x86-64.zip Copy and install the Latest Opatch to both GI and Rdbms
oracle@mysrvr1:/opt/oracle/product/11203_ee_64/db/OPatch/ocm/bin [11203]# ocm.rsp Create a response File  needed during the opatch .
/opt/crs/product/112_ee_64/crs/OPatch/ocm/bin [CRS]# ocm.rsp
export PATH=/opt/crs/product/112_ee_64/crs/OPatch:$PATH Opatch runs as root , Set Path
which opatch Check version of Opatch
opatch auto /opt/oracle/product/11203_ee_64/stage -ocmrf /opt/crs/product/112_ee_64/crs/OPatch/ocm/bin/ocm.rsp -oh /opt/crs/product/112_ee_64/crs, /opt/oracle/product/11203_ee_64/db Run Opatch
Upgrade the Databases
Perform DBUA per Instance DBUA messed up by adding local listener to Init.ora and continued altering the  oratab by the Grid agent. That is why i recommend against the DBU for bulk upgrades . I would script the Upgrade using a Fixed Oracle_HOME ( the new one ) and a dedicated initora / spfile  for the MIG.
Steps for Manual Upgrade: Preferred WAY !
Create a new spfile from a  migration pfile mig pfile has larger shared_pool-size
1) Start sqlplus and run catupgrd.sql script from the NEW  $ORACLE_HOME/rdbms/admin
sqlplus ” / as sysdba “
spool /tmp/upgrade<DB>.log
startup upgrade
set echo on
@?/rdbms/admin/catupgrd.sql; After Catupgrd.sql finishes it will shutdown the database
2) Check catupgrd.sql spool file for errors.
3) Restart the database in normal mode.
4)  @$ORACLE_HOME/rdbms/admin/catuppst.sql; Post steps for the migration
5)  @$ORACLE_HOME/rdbms/admin/utlrp.sql;
alter system set “_use_adaptive_log_file_sync”=FALSE scope = both; Requested by customer
set lines 2000
select instance_name from v$instance; Check sanity of upgrade
select * from v$version; Check sanity of upgrade
select COMP_NAME,VERSION,STATUS,MODIFIED from dba_registry order by 1; Check sanity of upgrade all the installed components should be valid !
select * from DBA_REGISTRY_HISTORY order by action_time desc Check if Catbundle Ran (shows most  recent Entry first.
Check $ORACLE_HOME/dbs for the presence / correct init.ora Should point to the Asm Diskgroup to an Spfile.
srvctl upgrade database -d <Db>  -o /opt/oracle/product/11203_ee_64/db Inform clusterware about altered  Oracle home
srvctl modify database -d<Db> -p ‘+DATA/<Db>/spfile<Db>.ora’ make sure Clusterware knows about the Spfile alter if needed
srvctl modify database  -d <Db>  -o ‘/opt/oracle/product/11203_ee_64/db’ make sure Clusterware knows about the New Oracle Home
If you have a listener per Database make sure it is started from the NEW oracle Home with the correct listener.ora
/opt/oracle/admin/tools/cSrvctlAct.ksh status Check status of db in cluster
/opt/oracle/admin/tools/cSrvctlAct.ksh config Check configuration of db in cluster
srvctl stop database -d  & start database -d as check . /opt/oracle/admin/tools/cSrvctlAct.ksh stop /opt/oracle/admin/tools/cSrvctlAct.ksh start As a test stop and start via srvctl stop/start database -d  <Db>
ln -s /opt/networker/lib/libnwora.so libobk.so in new oracle home Check For the Networker Lib present in the new Oracle Home
run an archive or control file  backup as check Run an archive backup as a  test

Mission completed.

Moving Your 11.2 Database to a new Diskgroup in ASM

Introduction:

I have been assigned with a new challenge with 11.2 Oracle On RedHat Linux with Grid Infrastructure and ASM.  Lets start with some background information. When I started building Real Application Clusters with ASM it was a standard to have merely one Disk group for data (often +DATA01) and one Disk group for the flash recovery area (+FRA01).  Over time that was altered as a standard by still having that one Data Disk group but using at least a dedicated disk group for the flash recovery area. When adding more databases to the same data disk group that might cause bottlenecks when one database grows fast claiming all space. Now a day standard would require a dedicated diskgroup both for data and fra. For one of the production databases I have been asked to deal with a scenario where there will be a new dedicated data disk group (the database already had a dedicated FRA disk group.

Below you will find the steps I performed in a sandbox as a scenario for upcoming change:

Details:

Working with the Spfile:

## First we start with finding location of the spfile that is being used.

SQL> show parameter spfile
NAME                                                      TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile                                                string       +DATA01/ASMdummy/spfileASMdummy.ora

## Next step is creating a pfile from this current spfile which will be used to recreate the spfile in the new disk group:

SQL> create pfile='/opt/oracle/ASMDUMMY/admin/pfile/initASMDUMMY.ora' from spfile ;
File created.

## Then create an spfile in the new disk group:

create spfile='+DATA02' from pfile='/opt/oracle/ASMDUMMY/admin/pfile/initASMDUMMY.ora';

## note alertlog shows:

Tue Nov 12 15:43:51 2013
SUCCESS: disk group DATA02 was mounted
Tue Nov 12 15:43:51 2013

NOTE: dependency between database ASMDUMMY and disk group resource ora.DATA02.dg is established

## Shutdown the database:

SQL> shutdown immediate;

## In a second screen make sure your environment is pointing to the ASM environment and go to ASMcmd  and go to the new disk group

asmcmd -p

## it shows:

ASMCMD [+DATA02/ASMDUMMY/PARAMETERFILE] > ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   NOV 12 15:00:00  Y    spfile.256.831311031

## Copy the file spfile.256.831311031 to the new disk group which will work as a more human readable file name:

cp +DATA02/ASMDUMMY/PARAMETERFILE/spfile.256.831311031 +DATA02/ASMDUMMY/spfileASMdummy.ora

### After doing that on Linux level alter the location of the spfile in the init.ora in the $ORACLE_HOME/dbs

cd /opt/oracle/product/11203_ee_64_a/db/dbs [ASMDUMMY]# ls -ltr  initASMDUMMY.ora
-rw-r----- 1 oracle dba       45 Nov 12 14:07 initASMDUMMY.ora
### current content: initASMDUMMY.ora
SPFILE='+DATA01/ASMDUMMY/spfileASMDUMMY.ora'

## After changing the disk group my new init.ora looks like this:

SPFILE='+DATA02/ASMDUMMY/spfileASMDUMMY.ora'

## Start the  database to find out that you did good thing :

SQL> startup

Working with the control files and Perform Backup:

SQL> show parameter control_files
NAME                                                      TYPE VALUE
---------------------------- ----------- ------------------------------
control_files                  string  +DATA01/ASMdummy/control01.ctl,+FRA01/ASMdummy/control02.ctl

## Set new location of controlfile in SPFILE:

SQL> alter system set control_files='+DATA02/ASMdummy/control01.ctl' scope=spfile sid='*';

## if you work with one control file , at the end you have to do extra step again to have a two control files again ( I have documented that step ) , but when re-reading this note I think would have been better to :

## Set new location of controlfile in SPFILE:

SQL> alter system set control_files='+DATA02/ASMdummy/control01.ctl', '+FRA01/ASMdummy/control02.ctl' scope=spfile sid='*';

## Shutdown your database

SQL> shutdown;

## Open ASMcmd again with the environment pointing to +ASM instance:

ASMCMD> cp +DATA01/ASMdummy/control01.ctl +DATA02/ASMdummy/control01.ctl

## check it:

ls -l  +DATA02/ASMdummy/control01.ctl
ASMCMD [+] > ls -l  +DATA02/ASMdummy/control01.ctl
Type         Redund  Striped  Time             Sys  Name
N    control01.ctl => +DATA02/ASM/CONTROLFILE/control01.ctl.258.831312753

##  Start your database with startup nomount

SQL> startup nomount;

## Start an rman session : Open “rman target /” and restore from old control and mount + open database:

rman target /
restore controlfile to '+DATA02/ASMdummy/control01.ctl' from '+DATA01/ASMdummy/control01.ctl';

##This will show

Starting restore at 12.11.2013 16:15:54
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 12.11.2013 16:15:55

##In Rman you should mount the database:

RMAN> sql 'alter database mount';

## this shows :

sql statement: alter database mount
released channel: ORA_DISK_1
## and in the alert:
Tue Nov 12 16:16:51 2013
SUCCESS: disk group DATA02 was mounted
Tue Nov 12 16:16:51 2013
NOTE: dependency between database ASMDUMMY and disk group resource ora.DATA02.dg is established
Tue Nov 12 16:16:55 2013
Successful mount of redo thread 1, with mount id 2267329971
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount

## Now it is time to make a backup of the database into the new Disk group (Data02). If you are in a rac environment make sure all other instances are down. Issue the following command in rman because this will create a one to one copy of the database in the new Disk group:

backup as copy database format '+DATA02';
##This will show:
Starting backup at 12.11.2013 16:18:54
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA01/ASMdummy/datafile/system.256.831304997
output file name=+DATA02/ASMdummy/datafile/system.259.831313135 tag=TAG20131112T161854 RECID=2 STAMP=831313144
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA01/ASMdummy/datafile/sysaux.257.831304997
output file name=+DATA02/ASMdummy/datafile/sysaux.260.831313151 tag=TAG20131112T161854 RECID=3 STAMP=831313157
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA01/ASMdummy/datafile/example.262.831305069
output file name=+DATA02/ASMdummy/datafile/example.261.831313165 tag=TAG20131112T161854 RECID=4 STAMP=831313170
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA01/ASMdummy/datafile/undotbs1.258.831304997
output file name=+DATA02/ASMdummy/datafile/undotbs1.262.831313173 tag=TAG20131112T161854 RECID=5 STAMP=831313173
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA02/ASMdummy/controlfile/backup.263.831313173 tag=TAG20131112T161854 RECID=6 STAMP=831313175
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA01/ASMdummy/datafile/users.259.831304997
output file name=+DATA02/ASMdummy/datafile/users.264.831313177 tag=TAG20131112T161854 RECID=7 STAMP=831313177
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 12.11.2013 16:19:38
channel ORA_DISK_1: finished piece 1 at 12.11.2013 16:19:39
piece handle=+DATA02/ASMdummy/backupset/2013_11_12/nnsnf0_tag20131112t161854_0.265.831313179 tag=TAG20131112T161854 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12.11.2013 16:19:39

## Once that has finished issue the following command in Rman (note this switch to command performs the  … set newname for you .. :

switch database to copy;
##This will show you:
datafile 1 switched to datafile copy "+DATA02/ASMdummy/datafile/system.259.831313135"
datafile 2 switched to datafile copy "+DATA02/ASMdummy/datafile/sysaux.260.831313151"
datafile 3 switched to datafile copy "+DATA02/ASMdummy/datafile/undotbs1.262.831313173"
datafile 4 switched to datafile copy "+DATA02/ASMdummy/datafile/users.264.831313177"
datafile 5 switched to datafile copy "+DATA02/ASMdummy/datafile/example.261.831313165"

##When that is finished issue following command in rman :

alter database open;

##Your alert file has been updated with following information:

Tue Nov 12 16:21:17 2013
Switch of datafile 1 complete to datafile copy
checkpoint is 1040434
Switch of datafile 2 complete to datafile copy
checkpoint is 1040434
Switch of datafile 3 complete to datafile copy
checkpoint is 1040434
Switch of datafile 4 complete to datafile copy
checkpoint is 1040434
Switch of datafile 5 complete to datafile copy
checkpoint is 1040434
Tue Nov 12 16:22:00 2013
alter database open
Tue Nov 12 16:22:00 2013
Thread 1 opened at log sequence 10
Current log# 1 seq# 10 mem# 0: +FRA01/ASMdummy/onlinelog/group_1.257.831305063
Current log# 1 seq# 10 mem# 1: +DATA01/ASMdummy/onlinelog/group_1.264.831305765
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Nov 12 16:22:00 2013
SMON: enabling cache recovery
[29312] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:3669618038 end:3669618518 diff:480 (4 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Tue Nov 12 16:22:03 2013
QMNC started with pid=33, OS id=8677
Tue Nov 12 16:22:06 2013
db_recovery_file_dest_size of 4122 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM disk group.
Completed: alter database open
Tue Nov 12 16:22:06 2013
Starting background process CJQ0
Tue Nov 12 16:22:06 2013
CJQ0 started with pid=34, OS id=8763

## At that moment it feels is if we are almost there ..

Create Destination(s)

## Open a Sqlplus session and check for the %create% parameter:

show parameter create
##This shows:
NAME                                                      TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size                   integer            8388608
create_stored_outlines                       string
db_create_file_dest                             string               +DATA01
db_create_online_log_dest_1           string               +FRA01
alter system set db_create_file_dest='DATA02' sid='*';
alter system set db_create_file_dest='DATA02' sid='*'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01261: Parameter db_create_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory
## pff typo  of course you should add the + in front of the disk group name
SQL> alter system set db_create_file_dest='+DATA02' sid='*';

Working with the temp files:

##Now it is time to work with the temp files. You will have to create a New temp tablespace in the new disk group, make that the default one and drop the old one:

SQL> select FILE_NAME from dba_temp_files;

## This shows:

FILE_NAME
--------------------------------------------------------------------------------
+DATA01/ASMdummy/tempfile/temp.261.831305069

##This will create a new temp tablespace , in the new disk group , make it the default tablespace and drop old

create temporary tablespace TEMP02 tempfile size 100m;
alter database default temporary tablespace TEMP02;
drop tablespace TEMP including contents;

##Check it again:

SQL> select FILE_NAME from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA02/ASMdummy/tempfile/temp02.266.831313635

Working with the redo logs:

In this step first we have to add new members to each group (to each thread (in a rac)). After that and after switching the log files you can delete the members in the old disk group

##First check the environment:

Set lines 2000
select * from v$log;
SQL>
GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE        MEMBERS ARC STATUS              FIRST_CHANGE# FIRST_TIME    NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1         1        10   52428800    512       2 YES INACTIVE                  1035793 12.11.2013 14:17:13      1067305 13.11.2013 06:00:48
2         1        11   52428800    512       2 NO  CURRENT            1067305 13.11.2013 06:00:48   2.8147E+14
3         1         9   52428800    512       2 YES INACTIVE                  1035790 12.11.2013 14:17:11      1035793 12.11.2013 14:17:13
## and
SQL> select GROUP#,MEMBER from v$logfile order by 1;
GROUP# MEMBER
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 +FRA01/ASMdummy/onlinelog/group_1.257.831305063
1 +DATA01/ASMdummy/onlinelog/group_1.264.831305765
2 +DATA01/ASMdummy/onlinelog/group_2.265.831305777
2 +FRA01/ASMdummy/onlinelog/group_2.258.831305065
3 +DATA01/ASMdummy/onlinelog/group_3.266.831305783
3 +FRA01/ASMdummy/onlinelog/group_3.259.831305065

##First we add new members to the correct , new disk group:

alter database add logfile member '+DATA02' to group 1;
alter database add logfile member '+DATA02' to group 2;
alter database add logfile member '+DATA02' to group 3;

###Check again:

select GROUP#,MEMBER from v$logfile order by 1;

## Perform several  switches to make sure the new members have been in use

SQL> alter system switch logfile;

##Alert showed:

Wed Nov 13 07:54:28 2013
Thread 1 advanced to log sequence 12 (LGWR switch)
Current log# 3 seq# 12 mem# 0: +FRA01/ASMdummy/onlinelog/group_3.259.831305065
Current log# 3 seq# 12 mem# 1: +DATA01/ASMdummy/onlinelog/group_3.266.831305783
Current log# 3 seq# 12 mem# 2: +DATA02/ASMdummy/onlinelog/group_3.269.831369169
Wed Nov 13 07:54:28 2013
Errors in file /opt/oracle/diag/rdbms/ASMdummy/ASMDUMMY/trace/ASMDUMMY_arc0_2197.trc:
ORA-00313: open failed for members of log group 2 of thread 1
Errors in file /opt/oracle/diag/rdbms/ASMdummy/ASMDUMMY/trace/ASMDUMMY_arc0_2197.trc:
ORA-00313: open failed for members of log group 2 of thread 1
Wed Nov 13 07:54:29 2013
Errors in file /opt/oracle/diag/rdbms/ASMdummy/ASMDUMMY/trace/ASMDUMMY_m000_22231.trc:
Errors in file /opt/oracle/diag/rdbms/ASMdummy/ASMDUMMY/trace/ASMDUMMY_m000_22231.trc:
Archived Log entry 8 added for thread 1 sequence 11 ID 0x8724b1a2 dest 1:
Wed Nov 13 07:55:38 2013
Thread 1 advanced to log sequence 13 (LGWR switch)
Current log# 1 seq# 13 mem# 0: +FRA01/ASMdummy/onlinelog/group_1.257.831305063
Current log# 1 seq# 13 mem# 1: +DATA01/ASMdummy/onlinelog/group_1.264.831305765
Current log# 1 seq# 13 mem# 2: +DATA02/ASMdummy/onlinelog/group_1.267.831369167
Wed Nov 13 07:55:38 2013
Archived Log entry 9 added for thread 1 sequence 12 ID 0x8724b1a2 dest 1:
Thread 1 cannot allocate new log, sequence 14
Checkpoint not complete
Current log# 1 seq# 13 mem# 0: +FRA01/ASMdummy/onlinelog/group_1.257.831305063
Current log# 1 seq# 13 mem# 1: +DATA01/ASMdummy/onlinelog/group_1.264.831305765
Current log# 1 seq# 13 mem# 2: +DATA02/ASMdummy/onlinelog/group_1.267.831369167
Thread 1 advanced to log sequence 14 (LGWR switch)
Current log# 2 seq# 14 mem# 0: +FRA01/ASMdummy/onlinelog/group_2.258.831305065
Current log# 2 seq# 14 mem# 1: +DATA01/ASMdummy/onlinelog/group_2.265.831305777
Current log# 2 seq# 14 mem# 2: +DATA02/ASMdummy/onlinelog/group_2.268.831369169
Wed Nov 13 07:55:45 2013
Archived Log entry 10 added for thread 1 sequence 13 ID 0x8724b1a2 dest 1:
Wed Nov 13 07:55:59 2013
Thread 1 advanced to log sequence 15 (LGWR switch)
Current log# 3 seq# 15 mem# 0: +FRA01/ASMdummy/onlinelog/group_3.259.831305065
Current log# 3 seq# 15 mem# 1: +DATA01/ASMdummy/onlinelog/group_3.266.831305783
Current log# 3 seq# 15 mem# 2: +DATA02/ASMdummy/onlinelog/group_3.269.831369169
Wed Nov 13 07:55:59 2013
Archived Log entry 11 added for thread 1 sequence 14 ID 0x8724b1a2 dest 1:

##It is time to drop the members from the old ( data01 )

select GROUP#,MEMBER from v$logfile order by 1;
GROUP# MEMBER
---------- -------------------------------------------------------------------------------- -------
1 +DATA01/ASMdummy/onlinelog/group_1.264.831305765
1 +DATA02/ASMdummy/onlinelog/group_1.267.831369167
1 +FRA01/ASMdummy/onlinelog/group_1.257.831305063
2 +DATA02/ASMdummy/onlinelog/group_2.268.831369169
2 +DATA01/ASMdummy/onlinelog/group_2.265.831305777
2 +FRA01/ASMdummy/onlinelog/group_2.258.831305065
3 +DATA02/ASMdummy/onlinelog/group_3.269.831369169
3 +DATA01/ASMdummy/onlinelog/group_3.266.831305783
3 +FRA01/ASMdummy/onlinelog/group_3.259.831305065
9 rows selected.

##So we have to drop the redo members that point to the old ( DATA01 ) Disk group:

alter database drop logfile member '+DATA01/ASMdummy/onlinelog/group_1.264.831305765';
alter database drop logfile member '+DATA01/ASMdummy/onlinelog/group_2.265.831305777';
alter database drop logfile member  '+DATA01/ASMdummy/onlinelog/group_3.266.831305783';
##When I did  this following error occurred:
SQL> alter database drop logfile member  '+DATA01/ASMdummy/onlinelog/group_3.266.831305783'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: '+FRA01/ASMdummy/onlinelog/group_3.259.831305065'
ORA-00312: online log 3 thread 1: '+DATA01/ASMdummy/onlinelog/group_3.266.831305783'
ORA-00312: online log 3 thread 1: '+DATA02/ASMdummy/onlinelog/group_3.269.831369169'
##So I need another switch logfile since group 3 was current.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile member  '+DATA01/ASMdummy/onlinelog/group_3.266.831305783';
Database altered.

##Checked again

select GROUP#,MEMBER from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
1 +FRA01/ASMdummy/onlinelog/group_1.257.831305063
1 +DATA02/ASMdummy/onlinelog/group_1.267.831369167
2 +DATA02/ASMdummy/onlinelog/group_2.268.831369169
2 +FRA01/ASMdummy/onlinelog/group_2.258.831305065
3 +DATA02/ASMdummy/onlinelog/group_3.269.831369169
3 +FRA01/ASMdummy/onlinelog/group_3.259.831305065
6 rows selected.

Working in the clusterware:

After these activities I tried Stopping and starting via srvctl ( this is an oracle restart environment crashed ) ..   Alertlog was having error messages and the start failed …   I did notice that the environment was using the old SPFILE in +Data01 again … I checked the spfile which was wrong again..

cd :/opt/oracle/product/11203_ee_64_a/db/dbs [ASMDUMMY]# cat initASMDUMMY.ora
SPFILE='+DATA01/ASMDUMMY/spfileASMDUMMY.ora'                        # line added by Agent

## so the clusteragent had altered my changes

##altered init.ora again and started that worked ..

##In sqlplus:
select name from v$controlfile
union
select name from v$datafile
union
select name from v$tempfile
union
select member from v$logfile
union
select filename from v$block_change_tracking
union
select name from v$flashback_database_logfile;
## This shows:
NAME
--------------------------------------------------------------------------------
+DATA02/ASMdummy/control01.ctl
+DATA02/ASMdummy/datafile/example.261.831313165
+DATA02/ASMdummy/datafile/sysaux.260.831313151
+DATA02/ASMdummy/datafile/system.259.831313135
+DATA02/ASMdummy/datafile/undotbs1.262.831313173
+DATA02/ASMdummy/datafile/users.264.831313177
+DATA02/ASMdummy/onlinelog/group_1.267.831369167
+DATA02/ASMdummy/onlinelog/group_2.268.831369169
+DATA02/ASMdummy/onlinelog/group_3.269.831369169
+DATA02/ASMdummy/tempfile/temp02.266.831313635
+FRA01/ASMdummy/onlinelog/group_1.257.831305063
+FRA01/ASMdummy/onlinelog/group_2.258.831305065
+FRA01/ASMdummy/onlinelog/group_3.259.831305065
14 rows selected.

## In an Oracle restart or Rac Environment you need to check the Clusterware setup now since it has knowledge about spfile, disk groups being used etc.

##First check the configuration in the Clusterware for the database:

srvctl config database -d ASMDUMMY

##This shows:

Database unique name: ASMDUMMY
Database name: ASMDUMMY
Oracle home: /opt/oracle/product/11203_ee_64_a/db
Oracle user: oracle
Spfile: +DATA01/ASMDUMMY/spfileASMDUMMY.ora <<- OLD DISK GROUP
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: ASMDUMMY
Disk Groups: DATA01,FRA01,DATA02 <<- OLD DISK GROUP
Services:

##So we have to perform two action points:

  • Make the spfile point to the correct disk group ( our new +data02)
  • Disk groups attribute still knows about that data01 disk group ( and it should not)

##First modification will be to inform the Clusterware which spfile to use:

srvctl modify database -d ASMdummy -p ' +DATA02/ASMDUMMY/spfileASMDUMMY.ora'

##After that similar action for the disk groups:

srvctl modify database -d ASMdummy -a 'DATA02,FRA01'

## don’t believe it check it
srvctl config database -d ASMdummy
Database unique name: ASMDUMMY
Database name: ASMDUMMY
Oracle home: /opt/oracle/product/11203_ee_64_a/db
Oracle user: oracle
Spfile: +DATA02/ASMDUMMY/spfileASMDUMMY.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: ASMDUMMY
Disk Groups: DATA02,FRA01

##This looks better so now let’s do a stop & start with srvctl

srvctl stop database -d ASMdummy
srvctl start database -d ASMdummy

##That worked !  happy dba

##Since I started the scenario with one control file I needed to go back to two control files:

##Set new location of both ctlfile in SPFILE:

alter system set control_files='+DATA02/ASMdummy/control01.ctl','+FRA01/ASMdummy/control02.ctl' scope=spfile sid='*';

##Perform a shutdown

SQL> shutdown;

## Open ASMcmd (make sure you point to the ASM instance) and copy the controlfile to the Fra01 Disk group to get them in sync again:

ASMCMD> cp +DATA02/ASMdummy/control01.ctl +FRA01/ASMdummy/control02.ctl

##Check it:

ls -l  +FRA01/ASMdummy/control02.ctl

##In Sqlplus startup nomount

SQL> startup nomount;

## Start an  “rman target /” and restore from old control and mount & open database:

RMAN> restore controlfile to '+FRA01/ASMdummy/control02.ctl' from '+DATA02/ASMdummy/control01.ctl';
sql 'alter database mount';
sql 'alter database open';

##In an Sqlplus session final check :

SQL> show parameter control
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA02/ASMdummy/control01.ctl, +FRA01/ASMdummy/control02.ctl

As always Happy reading,

Mathijs.

ASM 11.2.0.2 Is Not Releasing File Descriptors After Drop or Dismount Diskgroup. [ID 1306574.1]

Introduction.

One of my Linux environments is set up with 11.2.0.2.0. both Grid Infra structure and Rdbms together with ASM  using  luns that are part of EMC  BCVS copy  of production for reporting purposes.   This environment can be classified as a  Oracle – Restart environment. As part of a process that is supposed to run by  automated  Job during the night the Database should  go through various stages ( shutdown  abort,  dismount the dedicated ASM diskgroups  per database in ASM).  After that the BCVS are handed over to the source side again for syncing  after which the database will be rebuild using the fresh  BCV split.

Well as I said , supposed  to run automated…  cause at this point and time , during the day this is working  properly but during the night we get  per disk in the disk group a message similar to:

asm-diskgroupname-data-001: map in use

From Reading  and searching  metalink  it was suggested to implement the latest PSU Patch January 2013  due to:

ASM 11.2.0.2 Is Not Releasing File Descriptors After Drop or Dismount Diskgroup. [ID 1306574.1]

So i started my Journey on  patching  this environment.

Continue reading