Skip to content
March 27, 2014 / Shivananda Rao P

RAC database upgrade from 11.2.0.1 to 11.2.0.3

In this post, I’m demonstrating on the steps involved in upgrading the RAC database from version 11.2.0.1 to 11.2.0.3

RAC nodes: 10gnode1, 10gnode2
Current Database version: 11.2.0.1 Enterprise Edition
11.2.0.1 Oracle HOME Path: /u01/app/oracle/product/11.2.0/db_1

As per Oracle’s recommendations, the database is upgraded using an out-of-place upgrade option. So, I’m installing 11.2.0.3 database software
on a new path (/u01/app/oracle/product/11.2.0/db3)
Below are the steps involved in installing the Oracle Database Software version 11.2.0.3 on a new home.

On the first node (10gnode1):
1. Run the runInstaller from the unzipped 11.2.0.3 database software.
2. Select the Skip software updates option.
3. Select install database software only option.
4. Select all the cluter nodes on which the software needs to be installed.
5. Select the lanugage as English and the Edition as per your requirements. My requirement here is that I’m upgrading database from 11.2.0.1
Enterprise Edition database to 11.2.0.3 Enterprise Edition and hence selecting Enterprise Edition.
6. Provide the path where the new installation needs to be installed.
7. Proceed with the pre-requisite checks and if everything success, then proceed with the installation. Else, fix the errors given during the
pre-requisite check and proceed with the installation.
8. As root, run the root.sh on node1 followed by node2.


[root@10gnode1 ~]# /u01/app/oracle/product/11.2.0/db3/root.sh

Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/db3

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.

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.
yFinished product-specific root actions.
[root@10gnode1 ~]#

Run the root.sh script on 2nd node (10gnode2):


[root@10gnode2 ~]# /u01/app/oracle/product/11.2.0/db3/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.2.0/db3

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.

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.
Finished product-specific root actions.

Now let’s perform the pre-upgrade prerequisite checks. Copy the utlu112i.sql script from the newly installed 11.2.0.3 home to a temporary location. Here, I’ve copied the script to location “/u03”.
Execute this pre-upgrade script (utlu112i.sql) from the 11.2.0.1 database.


[oracle@10gnode1 ~]$ sqlplus sys/oracle@srprim1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 31 09:49:32 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create pfile='/u03/upgrd11201_to_11203/initsrprim1.ora' from spfile;

File created.

SQL>
SQL> spool '/u03/preupgd.txt'
SQL>
SQL> @/u01/app/oracle/product/11.2.0/db3/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 12-31-2013 09:55:26
Script Version: 11.2.0.3.0 Build: 001
.
*************************************************************
Database:
*************************************************************
--> name: SRPRIM
--> version: 11.2.0.1.0
--> compatible: 11.2.0.0.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V11
.
*************************************************************
Tablespaces: [make adjustments in the current environment]
*************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 685 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 450 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 78 MB
.
*************************************************************
Flashback: OFF
*************************************************************
*************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 32-bit database.
*************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
*************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
*************************************************************
-- No renamed parameters found. No changes are required.
.
*************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
*************************************************************
-- No obsolete parameters found. No changes are required
.

*************************************************************
Components: [The following database components will be upgraded or installed]
*************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Real Application Clusters [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
... APEX will only be upgraded if the version of APEX in
... the target Oracle home is higher than the current one.
--> Oracle OLAP API [upgrade] VALID
.
*************************************************************
Miscellaneous Warnings
*************************************************************
WARNING: --> The "cluster_database" parameter is currently "TRUE"
.... and must be set to "FALSE" prior to running a manual upgrade.
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 11.2.0.1.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
*************************************************************
Recommendations
*************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

*************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'

Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

*************************************************************

Perform the recommendations given out by the execution of the pre-upgrade (utlu112i.sql) script.


SQL> PURGE DBA_RECYCLEBIN;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;

Also, create a pfile to a temporary location to make necessary changes in order to remove the Cluster parameters.Following are the cluster parameters which need to be commented out before proceeding with the DB upgrade.

 


cluster_database=true
srprim2.instance_number=2
srprim1.instance_number=1
srprim2.thread=2
srprim1.thread=1
srprim2.undo_tablespace='UNDOTBS2'

Stop the database running on all the nodes.


[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ srvctl stop database -d srprim

Copy the password file from the 11.2.0.1 RDBMS_HOME/dbs (/u01/app/oracle/product/11.2.0/db_1/dbs) to the newly installed 11.2.0.3
RDBMS_HOME/dbs (/u01/app/oracle/product/11.2.0/db3/dbs) location. Also copy the above created pfile from temporary location (/u03/upgrd11201_to_11203/initsrprim1.ora) to the newly installed 11.2.0.3 RDBMS_HOME/dbs location.


[oracle@10gnode1 ~]$ cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwsrprim1 /u01/app/oracle/product/11.2.0/db3/dbs/
[oracle@10gnode1 ~]$ cp /u03/upgrd11201_to_11203/initsrprim1.ora /u01/app/oracle/product/11.2.0/db3/dbs/

Now, remove the cluster parameters set in the PFILE copied to 11.2.0.3 RDBMS_HOME/dbs location.

[oracle@10gnode1 ~]$ vi /u01/app/oracle/product/11.2.0/db3/dbs/initsrprim1.ora

Comment out the following cluster parameters.

 


#*.cluster_database=true
#srprim2.instance_number=2
#srprim1.instance_number=1
#srprim2.thread=2
#srprim1.thread=1
#srprim2.undo_tablespace='UNDOTBS2'

Startup only 1 instance (SRPRIM1) in upgrade mode on 10gnode1 using the newly modified pfile placed at 11.2.0.3 RDBMS_HOME/dbs location.


[oracle@10gnode1 ~]$ export ORACLE_SID=srprim1
[oracle@10gnode1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db3
[oracle@10gnode1 ~]$ cd /u01/app/oracle/product/11.2.0/db3/bin
[oracle@10gnode1 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 31 10:26:53 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade pfile=$ORACLE_HOME/dbs/initsrprim1.ora
ORACLE instance started.

Total System Global Area 1272213504 bytes
Fixed Size 1344680 bytes
Variable Size 822086488 bytes
Database Buffers 436207616 bytes
Redo Buffers 12574720 bytes
Database mounted.
Database opened.

Execute the CATUPGRD.SQL script to upgrade the database dictionaries.


SQL> spool '/u03/upgrd11201_to_11203/catupgrade.txt'
SQL> @/u01/app/oracle/product/11.2.0/db3/rdbms/admin/catupgrd.sql

Once the above script is executed, the database is shutdown automatically.Cross check if there were any errors reported in the execution of
the above script.

Startup the instance in normal mode now using the same PFILE.

[oracle@10gnode1 bin]$ export ORACLE_SID=srprim1
[oracle@10gnode1 bin]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db3
[oracle@10gnode1 bin]$ cd $ORACLE_HOME/bin
[oracle@10gnode1 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 31 11:07:25 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup pfile=$ORACLE_HOME/dbs/initsrprim1.ora
ORACLE instance started.

Total System Global Area 1272213504 bytes
Fixed Size 1344680 bytes
Variable Size 822086488 bytes
Database Buffers 436207616 bytes
Redo Buffers 12574720 bytes
Database mounted.
Database opened.
SQL>
SQL> spool '/u03/upgrd11201_to_11203/catuppst.sql'
SQL> @/u01/app/oracle/product/11.2.0/db3/rdbms/admin/catuppst.sql

Execute the CATUPPST.SQL script to perform remaining upgrade actions that do not require the database to be in UPGRADE mode.
Check if the components have been upgraded and also the status.


SQL> select comp_id,comp_name,version,status from dba_registry;

COMP_ID  COMP_NAME                     VERSION      STATUS
-------  ----------------------------- -----------  --------
OWB      OWB                           11.2.0.1.0   VALID
APEX     Oracle Application Express    3.2.1.00.10  VALID
EM       Oracle Enterprise Manager     11.2.0.3.0   VALID
AMD      OLAP Catalog                  11.2.0.3.0   VALID
SDO      Spatial                       11.2.0.3.0   VALID
ORDIM    Oracle Multimedia             11.2.0.3.0   VALID
XDB      Oracle XML Database           11.2.0.3.0   VALID
CONTEXT  Oracle Text                   11.2.0.3.0   VALID
EXF      Oracle Expression Filter      11.2.0.3.0   VALID
RUL      Oracle Rules Manager          11.2.0.3.0   VALID
OWM      Oracle Workspace Manager      11.2.0.3.0   VALID

COMP_ID  COMP_NAME                         VERSION    STATUS
-------  --------------------------------- ---------- -------
CATALOG Oracle Database Catalog Views      11.2.0.3.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.3.0 VALID
JAVAVM  JServer JAVA Virtual Machine       11.2.0.3.0 VALID
XML     Oracle XDK                         11.2.0.3.0 VALID
CATJAVA Oracle Database Java Packages      11.2.0.3.0 VALID
APS     OLAP Analytic Workspace            11.2.0.3.0 VALID
XOQ     Oracle OLAP API                    11.2.0.3.0 VALID
RAC     Oracle Real Application Clusters   11.2.0.3.0 VALID

19 rows selected.

Check for the INVALID objects and validate them by executing UTLRP.SQL script.


SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
6189

SQL> @/u01/app/oracle/product/11.2.0/db3/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
0

Now, once the DB is upgraded, we need to add the other instance (srprim2) into cluster. Hence we need to revert the cluster changes made to
the PFILE. For this shutdown the srprim1 instance and uncomment the cluster parameters in initsrprim1.ora PFILE that were commented earlier.


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

#*.cluster_database=true
#srprim2.instance_number=2
#srprim1.instance_number=1
#srprim2.thread=2
#srprim1.thread=1
#srprim2.undo_tablespace='UNDOTBS2'

Also, create a global SPFILE that is placed on a shared location to be accessed by all the instances within the cluster.


[oracle@10gnode1 bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 31 11:25:16 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile='+DATA/srprim/spfilesrprim.ora' from pfile;

File created.

Backup the pfile initsrprim1.ora in the 11.2.0.3 DB Home by renaming it to initsrprim1.ora.bak. Create a new pfile “initsrprim1.ora” in the
same location and add only one entry to it.


SPFILE='+DATA/srprim/spfilesrprim.ora'

We are making this so that the instances (srprim1 and srprim2) are using a global spfile and not local PFILES.


[oracle@10gnode1 bin]$ cd $ORACLE_HOME/dbs
[oracle@10gnode1 dbs]$ mv initsrprim1.ora initsrprim1.ora.bak
[oracle@10gnode1 dbs]$
[oracle@10gnode1 dbs]$ vi initsrprim1.ora
SPFILE='+DATA/srprim/spfilesrprim.ora'

[oracle@10gnode1 dbs]$ ls -lrt
total 20
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1536 Dec 31 10:09 orapwsrprim1
-rw-rw---- 1 oracle oinstall 1544 Dec 31 10:27 hc_srprim1.dat
-rwxr-xr-x 1 oracle oinstall 1467 Dec 31 11:24 initsrprim1.ora.bak
-rw-r--r-- 1 oracle oinstall 39 Dec 31 11:27 initsrprim1.ora
[oracle@10gnode1 dbs]$

Copy the PFILE on 10gnode1 to 10gnode2 and rename it to initsrprim2.ora


[oracle@10gnode1 dbs]$ scp initsrprim1.ora oracle@10gnode2:/u01/app/oracle/product/11.2.0/db3/dbs/initsrprim2.ora
initsrprim1.ora 100% 39 0.0KB/s 00:00
[oracle@10gnode1 dbs]$

Remove the SRPRIM database configuration from 11.2.0.1 RDBMS HOME and configure it on 11.2.0.3 RDBMS HOME.


[oracle@10gnode1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/bin
[oracle@10gnode1 bin]$ ./srvctl remove database -d srprim
Remove the database srprim? (y/[n]) y
[oracle@10gnode1 bin]$
Configuring SRPRIM database on 11.2.0.3 RDBMS HOME.
[oracle@10gnode1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db3
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ cd /u01/app/oracle/product/11.2.0/db3/bin
[oracle@10gnode1 bin]$
[oracle@10gnode1 bin]$
[oracle@10gnode1 bin]$ ./srvctl add database -d srprim -o /u01/app/oracle/product/11.2.0/db3
[oracle@10gnode1 bin]$ ./srvctl add instance -i srprim1 -n 10gnode1 -d srprim
[oracle@10gnode1 bin]$ ./srvctl add instance -i srprim2 -n 10gnode2 -d srprim
[oracle@10gnode1 bin]$ ./srvctl config database -d srprim
Database unique name: srprim
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db3
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: srprim
Database instances: srprim1,srprim2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed

In a simpler method and recommended one, the database configuration and its’ services can be upgraded at srvctl using the “srvctl upgrade database -d db_unique_name -o
$new_ORACLE_HOME” syntax than following the method done above. The above method would not upgrade the services and will have to be created manually on the new oracle home.

[oracle@10gnode1 ~]$ srvctl upgrade database -d srprim -o /u01/app/oracle/product/11.2.0/db3</pre>
<pre></pre>
<pre>

Run srvctl from the database Home to which the database configuration needs to be upgraded.
Startup both the instances and cross check the functionality.


[oracle@10gnode1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db3
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ echo $ORACLE_SID
srprim1
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 31 11:36:39 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1272213504 bytes
Fixed Size 1344680 bytes
Variable Size 822086488 bytes
Database Buffers 436207616 bytes
Redo Buffers 12574720 bytes
Database mounted.
Database opened.

SQL>
SQL> show parameter spfile

NAME   TYPE   VALUE
------ ------ ---------------------------------
spfile string +DATA/srprim/spfilesrprim.ora
SQL>
SQL> show parameter cluster_

NAME                       TYPE      VALUE
-------------------------- --------- -----------
cluster_database           boolean   TRUE
cluster_database_instances integer   2
cluster_interconnects      string

[oracle@10gnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 31 11:59:14 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
-------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

[oracle@10gnode1 ~]$ srvctl status database -d srprim -v -f
Instance srprim1 is running on node 10gnode1. Instance status: Open.
Instance srprim2 is not running on node 10gnode2


[oracle@10gnode1 ~]$ srvctl start instance -i srprim2 -d srprim
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ srvctl status database -d srprim -v -f
Instance srprim1 is running on node 10gnode1. Instance status: Open.
Instance srprim2 is running on node 10gnode2. Instance status: Open.
[oracle@10gnode1 ~]$

Note that there were services running on both the instances before upgrading. We need to start them as well after the database is upgraded.


[oracle@10gnode1 ~]$ srvctl config database -d srprim
Database unique name: srprim
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db3
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: srprim
Database instances: srprim1,srprim2
Disk Groups: DATA,FRA
Mount point paths:
Services: newsrv
Type: RAC
Database is administrator managed


[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ srvctl config service -s newsrv -d srprim
Service name: newsrv
Service is enabled
Server pool: srprim_newsrv
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: srprim1,srprim2
Available instances:

Now that the databases have been upgraded, we can remove the 11.2.0.1 DB Home. But before removing it, make sure that you have copied the
tnsnames.ora file from the 11.2.0.1 DB Home to the 11.2.0.3 HOME. Also make necessary changes to the Environment settings by specifying the
new 11.2.0.3 ORACLE HOME path in the profile files.

 

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

January 22, 2014 / Shivananda Rao P

CRS upgrade from 11.2.0.1 to 11.2.0.3

Before upgrading CRS to 11.2.0.3 version, pre-requisite patch 9413827 needs to be applied.
Once the patch is applied, unset the ORACLE_HOME, ORACLE_BASE and CRS Home path values and start the GI installation of 11.2.0.3 from the unzipped directory.

[oracle@10gnode1 ~]$ unset ORA_CRS_HOME
[oracle@10gnode1 ~]$ unset ORACLE_HOME
[oracle@10gnode1 ~]$ unset ORACLE_BASE

Run the runInstaller file from the unzipped 11.2.0.3 grid file on 10gnode1.

1. select skip software Updates and proceed NEXT.
2. Select Upgrade Grid Infrastructure or Oracle Automatic Storage Management
3. Select the preferred language.
4. Select the nodes where the GI needs to be upgraded.
5. Select the appropriate OS groups.
6. Select the software location (home path) where the new 11.2.0.3 GI needs to
be installed.
7. Look out for the prerequisites check and work on the failed checks. If
there are no failed checks, then proceed with the installation.
8. Run the rootupgrade.sh scripts on each node separately as root user.


[root@10gnode1 ~]# /u01/app/11.2.0/grid3/rootupgrade.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/11.2.0/grid3

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: n
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it?(y/n)
[n]: n
Copying coraenv to /usr/local/bin ...

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.
Using configuration parameter file:
/u01/app/11.2.0/grid3/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation

ASM upgrade has started on first node.

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on '10gnode1'
CRS-2673: Attempting to stop 'ora.crsd' on '10gnode1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on '10gnode1'
CRS-2673: Attempting to stop 'ora.registry.acfs' on '10gnode1'
CRS-2673: Attempting to stop 'ora.srprim.newsrv.svc' on '10gnode1'
CRS-2677: Stop of 'ora.registry.acfs' on '10gnode1' succeeded
CRS-2677: Stop of 'ora.srprim.newsrv.svc' on '10gnode1' succeeded
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on '10gnode1'
CRS-2673: Attempting to stop 'ora.srprim.db' on '10gnode1'
CRS-2677: Stop of 'ora.srprim.db' on '10gnode1' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on '10gnode1'
CRS-2673: Attempting to stop 'ora.FRA.dg' on '10gnode1'
CRS-2677: Stop of 'ora.DATA.dg' on '10gnode1' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on '10gnode1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on '10gnode1'
CRS-2677: Stop of 'ora.asm' on '10gnode1' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on '10gnode1' succeeded
CRS-2673: Attempting to stop 'ora.10gnode1.vip' on '10gnode1'
CRS-2677: Stop of 'ora.10gnode1.vip' on '10gnode1' succeeded
CRS-2673: Attempting to stop 'ora.eons' on '10gnode1'
CRS-2673: Attempting to stop 'ora.ons' on '10gnode1'
CRS-2677: Stop of 'ora.ons' on '10gnode1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on '10gnode1'
CRS-2677: Stop of 'ora.net1.network' on '10gnode1' succeeded
CRS-2677: Stop of 'ora.eons' on '10gnode1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on '10gnode1' has completed
CRS-2677: Stop of 'ora.crsd' on '10gnode1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on '10gnode1'
CRS-2673: Attempting to stop 'ora.asm' on '10gnode1'
CRS-2673: Attempting to stop 'ora.cssdmonitor' on '10gnode1'
CRS-2673: Attempting to stop 'ora.ctssd' on '10gnode1'
CRS-2673: Attempting to stop 'ora.evmd' on '10gnode1'
CRS-2673: Attempting to stop 'ora.mdnsd' on '10gnode1'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on '10gnode1'
CRS-2677: Stop of 'ora.cssdmonitor' on '10gnode1' succeeded
CRS-2677: Stop of 'ora.mdnsd' on '10gnode1' succeeded
CRS-2677: Stop of 'ora.gpnpd' on '10gnode1' succeeded
CRS-2677: Stop of 'ora.evmd' on '10gnode1' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on '10gnode1' succeeded
CRS-2677: Stop of 'ora.ctssd' on '10gnode1' succeeded
CRS-2677: Stop of 'ora.asm' on '10gnode1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on '10gnode1'
CRS-2677: Stop of 'ora.cssd' on '10gnode1' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on '10gnode1'
CRS-2673: Attempting to stop 'ora.gipcd' on '10gnode1'
CRS-2677: Stop of 'ora.gipcd' on '10gnode1' succeeded
CRS-2677: Stop of 'ora.diskmon' on '10gnode1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on '10gnode1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
OLR initialization - successful
Replacing Clusterware entries in inittab
clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 11g Release 2.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Preparing packages for installation...
cvuqdisk-1.0.9-1
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
[root@10gnode1 ~]#

Once the rootupgrade.sh is run on 10gnode1, run the same on 10gnode2. This would upgrade the ASM as well.


[oracle@10gnode1 bin]$ ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

 


[oraclee@10gnode1 bin]$ ./crsctl stat res -t
--------------------------------------------------------------------------------
NAME         TARGET STATE SERVER      STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
             ONLINE ONLINE 10gnode1
             ONLINE ONLINE 10gnode2
ora.FRA.dg
             ONLINE ONLINE 10gnode1
             ONLINE ONLINE 10gnode2
ora.LISTENER.lsnr
             ONLINE ONLINE 10gnode1
             ONLINE ONLINE 10gnode2
ora.asm
             ONLINE ONLINE 10gnode1  Started
             ONLINE ONLINE 10gnode2  Started
ora.eons
             ONLINE OFFLINE 10gnode1
             ONLINE OFFLINE 10gnode2
ora.gsd
             OFFLINE OFFLINE 10gnode1
             OFFLINE OFFLINE 10gnode2
ora.net1.network
             ONLINE ONLINE 10gnode1
             ONLINE ONLINE 10gnode2
ora.ons
             ONLINE ONLINE 10gnode1
             ONLINE ONLINE 10gnode2
ora.registry.acfs
             ONLINE ONLINE 10gnode1
             ONLINE ONLINE 10gnode2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.10gnode1.vip
      1     ONLINE ONLINE 10gnode1
ora.10gnode2.vip
      1     ONLINE ONLINE 10gnode2
ora.LISTENER_SCAN1.lsnr
      1     ONLINE ONLINE 10gnode2
 ora.oc4j
      1     OFFLINE OFFLINE
 ora.scan1.vip
      1     ONLINE ONLINE 10gnode2
 ora.srprim.db
      1     ONLINE ONLINE 10gnode1 Open
      2     ONLINE ONLINE 10gnode2 Open
 ora.srprim.newsrv.svc
      1     ONLINE ONLINE 10gnode1
      2     ONLINE ONLINE 10gnode2

 


[oracle@10gnode1 upgrd11201_to_11203]$ cd /u01/app/11.2.0/grid3/bin/
[oracle@10gnode1 bin]$ ./crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.3.0]
[oracle@10gnode1 bin]$

 


[oracle@10gnode1 bin]$ ./crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.3.0]
[oracle@10gnode1 bin]$

 

[oracle@10gnode1 bin]$ ./crsctl query crs softwareversion
Oracle Clusterware version on node [10gnode1] is [11.2.0.3.0]

 


[oracle@10gnode1 bin]$ srvctl status database -d srprim

[oracle@10gnode1 bin]$ srvctl status database -d srprim -v -f
Instance srprim1 is running on node 10gnode1 with online services newsrv
Instance srprim2 is running on node 10gnode2 with online services newsrv
[oracle@10gnode1 bin]$

 

[oracle@10gnode1 bin]$ cd
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid3 is /u01/app/oracle
[oracle@10gnode1 ~]$
[oracle@10gnode1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 30 10:40:09 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select status,instance_name from v$instance;

STATUS   INSTANCE_NAME
-------- -------------------
STARTED  +ASM1

SQL> exit

 


[oracle@10gnode1 bin]$ srvctl status database -d srprim

[oracle@10gnode1 bin]$ srvctl status database -d srprim -v -f
Instance srprim1 is running on node 10gnode1 with online services newsrv
Instance srprim2 is running on node 10gnode2 with online services newsrv

 


[oracle@10gnode1 ~]$ . oraenv
ORACLE_SID = [srprim1] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@10gnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 30 10:41:42 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

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

STATUS INSTANCE_NAME HOST_NAME
------ ------------- ---------------------------------------------
 OPEN  srprim1       10gnode1.mydomain
 OPEN  srprim2       10gnode2.mydomain

SQL>
SQL> select * from v$version;

BANNER
------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

December 31, 2013 / Shivananda Rao P

2013 in review

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 73,000 times in 2013. If it were an exhibit at the Louvre Museum, it would take about 3 days for that many people to see it.

Click here to see the complete report.

 

COPYRIGHT

© Shivananda Rao P, 2012, 2013, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

December 30, 2013 / Shivananda Rao P

ORA-15221: ASM operation requires compatible.asm of string or higher

While trying to create an SPFILE for ASM instance on a diskgroup, oracle threw ORA-15221: ASM operation requires compatible.asm of 11.2.0.0.0 or higher.

COMPATIBLE.ASM is an attribute associated with every diskgroup. This attribute describes the minimum software version required for the ASM instance that can access the
diskgroup.
Here, the ASM instance version that was used was 11.2.0.1.0 and the error threw that the compatible parameter must be set to a minimum of 11.2.0.0.0


[oracle@10gnode1 bin]$ ./sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 27 11:54:13 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> create spfile='+DATA/ASM/spfileASM.ora' from pfile;
create spfile='+DATA/ASM/spfileASM.ora' from pfile
*
ERROR at line 1:
ORA-17502: ksfdcre:4 Failed to create file +DATA/ASM/spfileASM.ora
ORA-15221: ASM operation requires compatible.asm of 11.2.0.0.0 or higher

SQL> select group_number, name,compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME  COMPATIBILITY DATABASE_COMPATIBILITY
------------ ----- ------------- -------------------------
 1           DATA  10.1.0.0.0    10.1.0.0.0
 2           FRA   10.1.0.0.0    10.1.0.0.0

Here you can see that the compatibility was set to 10.1.0.0 and which is why ASM instance was unable to access the diskgroup.
After changing the COMPATIBILITY to 11.2.0.0.0, the issue resolved.


SQL> alter diskgroup DATA set attribute 'compatible.asm'='11.2.0.0.0';

Diskgroup altered.

SQL> alter diskgroup FRA set attribute 'compatible.asm'='11.2.0.0.0';

Diskgroup altered.

 


SQL> select group_number, name,compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY
------------ ---- ------------  --------------------------------
 1           DATA 11.2.0.0.0    10.1.0.0.0
 2           FRA  11.2.0.0.0    10.1.0.0.0

There is also another attribute called “compatible.rdbms” which determines the minimum COMPATIBLE database initialization parameter setting for the database instance
to access the diskgroup. Here, in my case, the attribute was set to 10.1.0.0.0 but the Dataabse instance compatiblity that had to access the diskgroup was 11.2.0.1.0


SQL> alter diskgroup DATA set attribute 'compatible.rdbms'='11.2.0.0.0';

Diskgroup altered.

SQL> alter diskgroup FRA set attribute 'compatible.rdbms'='11.2.0.0.0';
Diskgroup altered.

 


SQL> select group_number, name,compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY
------------ ---- ------------- -------------------------
 1           DATA 11.2.0.0.0    11.2.0.0.0
 2           FRA  11.2.0.0.0    11.2.0.0.0

After setting the “compatible.asm” attribute to 11.2.0.0.0, ASM instance was able to access the diskgroup and was able to create the spfile on the diskgroup.


[oracle@10gnode1 bin]$ ./sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 27 12:05:07 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Real Application Clusters and Automatic Storage Management options

SQL> create spfile='+DATA/ASM/spfileASM.ora' from pfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
Production
With the Real Application Clusters and Automatic Storage Management options

 


[oracle@10gnode1 bin]$ ./asmcmd
ASMCMD> cd DATA/ASM
ASMCMD> ls
spfileASM.ora

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

November 3, 2013 / Shivananda Rao P

Create RAC Standby Database for RAC Primary Database

This article speaks of creating a RAC physical standby database for RAC Primary database.

Primary and Standby databases are of 2 node RAC.

Primary Database : PRIM
DBNAME : PRIM
DB UNIQUE NAME : PRIM
Instances : PRIM1 on node1
PRIM2 on node2

Standby Database : SRPSTB
DBNAME : PRIM
DB UNIQUE NAME : SRPSTB
Instances : SRPSTB1 on drnode1
SRPSTB2 on drnode2

Database version: Oracle 11.2.0.1

Below are the steps to create a RAC standby database for a RAC primary database.

Step 1: Add the following standby parameters on the primary database.

log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=prim'
log_archive_dest_2='service=srpstb valid_for=(online_logfiles,primary_role) db_unique_name=srpstb'
fal_client='prim' #Oracle net service name of primary database
fal_server='srpstb' #oracle net service name of standby database.

Step 2: Create a pfile of the primary database and copy this file to the standby server.
The contents of the pfile of primary database is as below. (This is the pfile taken from instance prim1 of primary DB.)

[oracle@node1 u02]$ cat initprim1.ora
prim1.__db_cache_size=385875968
prim2.__db_cache_size=436207616
prim1.__java_pool_size=16777216
prim2.__java_pool_size=16777216
prim1.__large_pool_size=16777216
prim2.__large_pool_size=16777216
prim1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prim1.__pga_aggregate_target=520093696
prim2.__pga_aggregate_target=520093696
prim1.__sga_target=754974720
prim2.__sga_target=754974720
prim1.__shared_io_pool_size=0
prim2.__shared_io_pool_size=0
prim1.__shared_pool_size=318767104
prim2.__shared_pool_size=268435456
prim1.__streams_pool_size=0
prim2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prim/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/prim/controlfile/current.260.826037247'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='prim'
*.db_unique_name='prim'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='+FRA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.fal_client='prim'
*.fal_server='srpstb'
prim1.instance_number=1
prim2.instance_number=2
prim1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521))))'
prim2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521))))'
*.log_archive_dest_1='location=use_db_recovery_file_dest
valid_for=(all_logfiles,all_roles) db_unique_name=prim'
*.log_archive_dest_2='service=srpstb
valid_for=(online_logfiles,primary_role) db_unique_name=srpstb'
*.memory_target=1264582656
*.open_cursors=300
*.processes=150
*.remote_listener='node-scan:1521'
*.remote_login_passwordfile='exclusive'
prim2.thread=2
prim1.thread=1
prim2.undo_tablespace='UNDOTBS2'
prim1.undo_tablespace='UNDOTBS1'

Step 3. Listener File contents on Primary Database server.

[oracle@node1 admin]$ cat listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

SID_LIST_LISTENER=
(SID_LIST=
 (SID_DESC=
   (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db1)
   (SID_NAME=prim1)
 )
)

Step 4: TNS entries on primary database server.
### PRIMARY ENTRIES ###

PRIM =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = node-scan)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = prim)
  )
)
PRIM1 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT=1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = prim)
    (INSTANCE_NAME = prim1)
  )
)

PRIM2 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT=1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = prim)
    (INSTANCE_NAME = prim2)
  )
)

### Standby TNS ENTRIES ###

SRPSTB =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = drnode-scan)(PORT = 1521)  )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = srpstb)
    (UR = A)
  )
)

SRPSTB2 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = drnode2-vip)(PORT = 1521)  )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = srpstb)
    (UR = A)
    (INSTANCE_NAME = srpstb2)
  )
)

SRPSTB1 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = drnode1-vip)(PORT = 1521)  )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = srpstb)
    (UR = A)
    (INSTANCE_NAME = srpstb1)
  )
)

Step 5: Copy the above TNS entries to standby database server node drnode1.

Step 6: Copy the password file of any instance (“orapwprim1” or “orapwprim2”) of primary database located at “$ORACLE_HOME/dbs” location to
the standby database server node drnode1 location “$ORACLE_HOME/dbs” and rename it to “orapwsrpstb1”.

Step 7: On the standby database server, perpare the pfile for srpstb1 instance as initsrpstb1.ora .
Contents of initsrpstb1.ora File:


[oracle@drnode1 dbs]$ cat initsrpstb1.ora
srpstb1.__db_cache_size=419430400
srpstb1.__java_pool_size=16777216
srpstb1.__large_pool_size=16777216
srpstb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
srpstb1.__pga_aggregate_target=520093696
srpstb1.__sga_target=754974720
srpstb1.__shared_io_pool_size=0
srpstb1.__shared_pool_size=285212672
srpstb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/srpstb/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+DATA','+FRA'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='prim'
*.db_unique_name='srpstb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srpstbXDB)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=srpstb'
*.log_archive_dest_2='service=prim valid_for=(online_logfiles,primary_role) db_unique_name=prim'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=2G
*.memory_target=1264582656
*.open_cursors=300
*.processes=150
*.remote_listener='drnode-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
srpstb1.undo_tablespace='UNDOTBS1'
srpstb1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=drnode1-vip)(PORT=1521))))'
srpstb1.fal_client='srpstb1'
*.fal_server='prim'

Step 8: Set up the listener.ora file on the standby database server drnode1.
Contents of Listener.ora file on DRNODE1:

[oracle@drnode1 admin]$ cat listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

SID_LIST_LISTENER=
(SID_LIST=
 (SID_DESC=
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/db1)
  (SID_NAME=srpstb1)
 )
)

Step 9: Nomount the standby instance srpstb1 using the above pfile “initsrpstb1.ora”.

Step 10: Now connect to the Primary database as target database and standby database as auxiliary instance through RMAN. Make sure that the
Primary database is open.


[oracle@drnode1 ~]$ rman target sys/oracle@prim auxiliary sys/oracle@srpstb1

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Oct 25 19:54:07 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: PRIM (DBID=4118338878)
connected to auxiliary database: PRIM (not mounted)

RMAN&amp;gt; duplicate target database for standby from active database;

Starting Duplicate Db at 25-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=28 device type=DISK

contents of Memory Script:
{
 backup as copy reuse
 targetfile '/u01/app/oracle/product/11.2.0/db1/dbs/orapwprim2' auxiliary format
 '/u01/app/oracle/product/11.2.0.1/db1/dbs/orapwsrpstb1' ;
}
executing Memory Script

Starting backup at 25-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 instance=prim2 device type=DISK
Finished backup at 25-OCT-13

contents of Memory Script:
{
 backup as copy current controlfile for standby auxiliary form at '+DATA/srpstb/controlfile/current.269.829771129';
 restore clone controlfile to '+FRA/srpstb/controlfile/current.261.829771129' from
 '+DATA/srpstb/controlfile/current.269.829771129';
 sql clone &amp;quot;create spfile from memory&amp;quot;;
 shutdown clone immediate;
 startup clone nomount;
 sql clone &amp;quot;alter system set control_files =
 ''+DATA/srpstb/controlfile/current.269.829771129'', ''+FRA/sr pstb/controlfile/current.261.829771129'' comment=
 ''Set by RMAN'' scope=spfile&amp;quot;;
 shutdown clone immediate;
 startup clone nomount;
}
executing Memory Script

Starting backup at 25-OCT-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db1/dbs/snapcf_prim2.f tag=TAG20131025T195848 RECID=2 STAMP=829771151
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
Finished backup at 25-OCT-13

Starting restore at 25-OCT-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 25-OCT-13

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1269366784 bytes

Fixed Size 2212976 bytes
Variable Size 855640976 bytes
Database Buffers 402653184 bytes
Redo Buffers 8859648 bytes

sql statement: alter system set control_files = ''+DATA/srpstb/controlfile/current.269.829771129'',
''+FRA/srpstb/controlfile/current.261.829771129'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1269366784 bytes

Fixed Size 2212976 bytes
Variable Size 855640976 bytes
Database Buffers 402653184 bytes
Redo Buffers 8859648 bytes

contents of Memory Script:
{
 sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
 set newname for clone tempfile 1 to new;
 switch clone tempfile all;
 set newname for clone datafile 1 to new;
 set newname for clone datafile 2 to new;
 set newname for clone datafile 3 to new;
 set newname for clone datafile 4 to new;
 set newname for clone datafile 5 to new;
 set newname for clone datafile 6 to new;
 backup as copy reuse
 datafile 1 auxiliary format new
 datafile 2 auxiliary format new
 datafile 3 auxiliary format new
 datafile 4 auxiliary format new
 datafile 5 auxiliary format new
 datafile 6 auxiliary format new
 ;
 sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 25-OCT-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/prim/datafile/system.256.826037011
output file name=+DATA/srpstb/datafile/system.266.829771513 tag=TAG20131025T200453
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/prim/datafile/sysaux.257.826037013
output file name=+DATA/srpstb/datafile/sysaux.277.829771637 tag=TAG20131025T200453
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/prim/datafile/undotbs1.258.826037015
output file name=+DATA/srpstb/datafile/undotbs1.276.829771735 tag=TAG20131025T200453
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/prim/datafile/example.264.826037271
output file name=+DATA/srpstb/datafile/example.275.829771771 tag=TAG20131025T200453
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/prim/datafile/undotbs2.265.826037561
output file name=+DATA/srpstb/datafile/undotbs2.274.829771805 tag=TAG20131025T200453
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/prim/datafile/users.259.826037015
output file name=+DATA/srpstb/datafile/users.273.829771831 tag=TAG20131025T200453
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 25-OCT-13

sql statement: alter system archive log current

contents of Memory Script:
{
 switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=829771843 file name=+DATA/srpstb/datafile/system.266.829771513
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=829771843 file name=+DATA/srpstb/datafile/sysaux.277.829771637
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=829771843 file name=+DATA/srpstb/datafile/undotbs1.276.829771735
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=829771843 file name=+DATA/srpstb/datafile/users.273.829771831
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=829771843 file name=+DATA/srpstb/datafile/example.275.829771771
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=829771843 file name=+DATA/srpstb/datafile/undotbs2.274.829771805
Finished Duplicate Db at 25-OCT-13

Step 11: Once the duplication is completed, close the RMAN prompt and connect to the standby database through SQL.


sqlplus sys/&amp;lt;password&amp;gt;@srpstb1 as sysdba

Check the status of the standby database by making sure it is in mount stage.

sql>select status,instance_name,database_role from v$instance,v$database;

Step 12: Now start the managed recovery process on the standby database.


sql>alter database recover managed standby database disconnect from session;

Step 13: Now check if the managed recovery process (MRP) has been started on the standby database or not.


SQL> select process,status,sequence#,thread# from v$managed_standby;

PROCESS STATUS       SEQUENCE# THREAD#
------- ------------ --------- -------
ARCH    CONNECTED    0         0
ARCH    CONNECTED    0         0
ARCH    CONNECTED    0         0
ARCH    CONNECTED    0         0
MRP0    WAIT_FOR_LOG 65        2
RFS     IDLE         0         0
RFS     IDLE         0         0
RFS     IDLE         0         0
RFS     IDLE         0         0
RFS     IDLE         75        1
RFS     IDLE         0         0
RFS     IDLE         0         0
RFS     IDLE         0         0
RFS     IDLE         0         0

14 rows selected.

Here, the MRP has been started and is waiting for the log sequence# 39. If MRP is not started, then the above query would not show up the
MRP0 under the process column.
Step 14: On the primary database, perform a few log switches and check if the logs are applied to the standby database.

Primary Database Archive Sequence:


sqlplus sys/<password>@prim1 as sysdba

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)
------- ----------------
1       76
2       67

Standby database SRPSTB1 archive sequence being applied:


sqlplus sys/<password>@srpstb1 as sysdba

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
------- -----------------
 1      75
 2      67

Here, the maximum sequence# generated on the Primary database from instance 1 (prim1) is 76 and from instance 2 (prim2) is 67 and the maximum sequence# applied on the standby database for thread 1 is 75 and thread 2 is 67 which means that the standby database is in sync with the primary database.
Now lets add the 2nd instance srpstb2 to the Standby Database srpstb.

Step 15: Create a pfile from the standby instance srpstb1 to add the cluster parameters.


cluster_database=TRUE
srpstb1.undo_tablespace='UNDOTBS1'
srpstb2.undo_tablespace='UNDOTBS2'
srpstb2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=drnode2-vip)(PORT=1521))))'
srpstb1.instance_number=1
srpstb2.instance_number=2
srpstb1.thread=1
srpstb2.thread=2
srpstb1.fal_client='srpstb1'
srpstb2.fal_client='srpstb2'

The new pfile required for the standby database cluster would look as below.


[oracle@drnode1 dbs]$ cat initsrpstb1.ora
srpstb1.__db_cache_size=402653184
srpstb1.__java_pool_size=16777216
srpstb1.__large_pool_size=16777216
srpstb1.__pga_aggregate_target=520093696
srpstb1.__sga_target=754974720
srpstb1.__shared_io_pool_size=0
srpstb1.__shared_pool_size=285212672
srpstb1.__streams_pool_size=0
srpstb2.__db_cache_size=402653184
srpstb2.__java_pool_size=16777216
srpstb2.__large_pool_size=16777216
srpstb2.__pga_aggregate_target=520093696
srpstb2.__sga_target=754974720
srpstb2.__shared_io_pool_size=0
srpstb2.__shared_pool_size=285212672
srpstb2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/srpstb/adump'
*.audit_trail='DB'
*.cluster_database=TRUE
*.compatible='11.2.0.0.0'
*.control_files='+DATA/srpstb/controlfile/current.269.829771129','+FRA/srpstb/controlfile/current.261.829771129'#Set by RMAN
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='prim'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=2G
*.db_unique_name='srpstb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srpstbXDB)'
*.fal_client='srpstb1'
*.fal_server='prim'
srpstb1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCO=TCP)(HOST=drnode1-vip)(PORT=1521))))'
srpstb2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=drnode2-vip)(PORT=1521))))'
*.log_archive_dest_1='location=use_db_recovery_file_dest
valid_for=(all_logfiles,all_roles)
db_unique_name=srpstb'
*.log_archive_dest_2='service=prim
valid_for=(online_logfiles,primary_role)
db_unique_name=prim'
*.log_buffer=8491008# log buffer update
*.memory_target=1216M
*.open_cursors=300
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
srpstb1.undo_tablespace='UNDOTBS1'
srpstb2.undo_tablespace='UNDOTBS2'
srpstb1.instance_number=1
srpstb2.instance_number=2
srpstb1.thread=1
srpstb2.thread=2
srpstb1.fal_client='srpstb1'
srpstb2.fal_client='srpstb2'
*.fal_server='prim'

Step 16: Shutdown the instance srpstb1, mount it using the newly above pfile, create an spfile to be placed in the shared location (ASM
diskgroup, as it is being shared by both the instances srpstb1 and srpstb2.)

SQL> create spfile='+DATA/SRPSTB/spfilesrpstb.ora' from pfile;

File created.

Step 17: Create a new pfile initsrpstb1.ora in drnode1 located at $ORACLE_HOME/dbs with just one entry to point to the spfile location.


[oracle@drnode1 dbs]$ cat initsrpstb1.ora
SPFILE='+DATA/SRPSTB/spfilesrpstb.ora'

Step 17: Copy the password file of srpstb1 (orapwsrpstb1) to drnode2 location “$ORACLE_HOME/dbs” and rename it as orapwsrpstb2.

Step 18: Copy the newly created pfile (initsrpstb1.ora) fto drnode2 location “$ORACLE_HOME/dbs” and rename it as initsrpstb2.ora

Step 19: Mount both srpstb1 and srpstb2 instances.

Step 20: Start MRP on any one instance using the below query.


SQL> alter database recover managed standby database disconnect from session;

Step 21: Check the max archive sequence generated on primary and compare it with the max archive sequence applied on the standby.

Primary:


SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

Standby:


SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

Step 22: The next step would be to add the standby database SRPSTB to the cluster.


[oracle@drnode1 ~]$ srvctl add database -d srpstb -o /u01/app/oracle/product/11.2.0.1/db1 -r PHYSICAL_STANDBY -s MOUNT

Step 23: Now, we also need to add the instances entrires to the standby database.


[oracle@drnode1 ~]$ srvctl add instance -d srpstb -i srpstb1 -n drnode1
[oracle@drnode1 ~]$ srvctl add instance -d srpstb -i srpstb2 -n drnode2

Step 24: Now check the status of the standby database using srvctl.


[oracle@drnode1 ~]$ srvctl start database -d srpstb
[oracle@drnode1 ~]$ srvctl status database -d srpstb
Instance srpstb1 is running on node drnode1
Instance srpstb2 is running on node drnode2

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

October 5, 2013 / Shivananda Rao P

Recovering Corrupted Blocks

There were certain blocks corrupted in a datafile which did not allow me to query tables associated with the corrupted blocks

 SQL> select * from update7sp7.test1;
 select * from update7sp7.test1
 *
 ERROR at line 1:
 ORA-01578: ORACLE data block corrupted (file # 8, block # 138 )
 ORA-01110: data file 8: '+DATA_NEW/srpstb/datafile/myts.262.8 19379005'

 

SQL> select * from update7sp7.test2;
select * from update7sp7.test2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 146)
ORA-01110: data file 8: '+DATA_NEW/srpstb/datafile/myts.262.819379005'

The following errors were reported in alert log file:
Errors in file /u01/app/oracle/diag/rdbms/srpstb/srpstb/trace/srpstb_m000_3839.trc (incident=43378):

ORA-01578: ORACLE data block corrupted (file # 8, block # 138)
ORA-01110: data file 8: '+DATA_NEW/srpstb/datafile/myts.262.819379005'
Errors in file /u01/app/oracle/diag/rdbms/srpstb/srpstb/trace/srpstb_m000_3839.trc (incident=43379):
ORA-01578: ORACLE data block corrupted (file # 8, block # 146)
ORA-01110: data file 8: '+DATA_NEW/srpstb/datafile/myts.262.819379005'
Checker run found 3 new persistent data failures
Fri Oct 04 18:29:25 2013

Now, let me check the same using DBV utility.

[oracle@ora1-2 trace]$ dbv file='+DATA_NEW/srpstb/datafile/myts.262.819379005' userid=sys/oracle

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Oct 4 18:38:34 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

DBVERIFY - Verification starting : FILE = +DATA_NEW/srpstb/datafile/myts.262.819379005

DBV-00111: OCI failure (4157) (ORA-00600: internal error code,arguments: [kudbvslfw1], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_DBVERIFY", line 24
ORA-06512: at line 1

Oops !! DBV is giving me out an internal error. Upon checking with Oracle Support, came to know that it was a bug
Bug 13811172 dbms_dbverify may fail with dbv-111: oci failure (4157) ora-600 [kudbvslfw1]

So, let me check for the corruption using RMAN “validate database”.

RMAN> validate database;

Starting validate at 04-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=+DATA_NEW/srpstb/datafile/system.261.819379005
input datafile file number=00002 name=+DATA_NEW/srpstb/datafile/sysaux.257.819378991
input datafile file number=00005 name=+DATA_NEW/srpstb/datafile/example.258.819378991
input datafile file number=00008 name=+DATA_NEW/srpstb/datafile/myts.262.819379005
input datafile file number=00003 name=+DATA_NEW/srpstb/datafile/undotbs1.263.819379005
input datafile file number=00006 name=+DATA_NEW/srpstb/datafile/myts.264.819379005
input datafile file number=00007 name=+DATA_NEW/srpstb/datafile/tbs.259.819378991
input datafile file number=00004 name=+DATA_NEW/srpstb/datafile/users.260.819378991
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              14671        92203           2505790
File Name: +DATA_NEW/srpstb/datafile/system.261.819379005
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              60610
Index      0              12734
Other      0              4145

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              18749        74365           2505788
File Name: +DATA_NEW/srpstb/datafile/sysaux.257.819378991
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              12822
Index      0              9345
Other      0              33324

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              1            6403            2505790
File Name: +DATA_NEW/srpstb/datafile/undotbs1.263.819379005
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              0
Index      0              0
Other 0 6399

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              18           669             1786990
File Name: +DATA_NEW/srpstb/datafile/users.260.819378991
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              106
Index      0              39
Other      0              477

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              31391        44242           2397708
File Name: +DATA_NEW/srpstb/datafile/example.258.819378991
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              4509
Index      0              1148
Other      0              7192

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              1            6401            1370949
File Name: +DATA_NEW/srpstb/datafile/myts.264.819379005
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              0
Index      0              0
Other      0              6399

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              1            1281            1348023
File Name: +DATA_NEW/srpstb/datafile/tbs.259.819378991
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              0
Index      0              0
Other      0              1279

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     3              1            12809           2396687
File Name: +DATA_NEW/srpstb/datafile/myts.262.819379005
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              26
Index      0              9
Other      0              12764

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
--------- ------ -------------- ---------------
SPFILE    OK     0              2
Control File OK 0 642
Finished validate at 04-OCT-13

We can even use “Validate datafile 8;” command at RMAN to check the corruption if we know the exact datafile ID.

From the above outcome, it is clear that datafile ID 8 (+DATA_NEW/srpstb/datafile/myts.262.819379005) has 3 corrupted blocks. Let me get the corrupted block# from v$database_block_corruption view.


SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ----------------
 8    137    2      0                  CORRUPT
 8    146    1      0                  CORRUPT

From the above outcome, it is clear that 2 blocks starting from block# 137 (block 137 and 138) of datafile 8 are corrupted and so is block 146 of datafile 8 corrupted.
Let’s recover them using RMAN (Block Recovery).


[oracle@ora1-2 trace]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 418:41:21 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: SRPRIM (DBID=216679430)

RMAN> recover datafile 8 block 137 to 138,146;

Starting recover at 04-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK
searching flashback logs for block images until SCN 2501389
finished flashback log search, restored 0 blocks

channel ORA_DISK_1: restoring block(s) from datafile copy /u02/bkp/myts_8
failover to previous backup

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backupset
restoring blocks of datafile 00008
channel ORA_DISK_1: reading from backup piece /u02/bkp/srprim_inc0_37olibpj_1_1.bak
channel ORA_DISK_1: piece handle=/u02/bkp/srprim_inc0_37olibpj_1_1.bak tag=TAG20131004T120611
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 04-OCT-13

Recovery has been completed successfully. Let’s verify the same from the view “V$DATABASE_BLOCK_CORRUPTION”.

[oracle@ora1-2 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 4 20:01:48 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

SQL> select status,instance_name,database_role from v$instance,v$database;

STATUS INSTANCE_NAME DATABASE_ROLE
------ ------------- --------------
OPEN   srpstb        PRIMARY

SQL> select * from v$database_block_corruption;

no rows selected

There are no corruption reported by the view and now we should be able to query the tables that was initially done.

SQL> select * from update7sp7.test1;

CODE NAME
---- ------
100  rama
101  raja
102  soma
103  ravi
104  shivu
105  adsf
106  ijasdf

7 rows selected.

 


SQL> select * from update7sp7.test2;

CODE
----------
200
201
202
203
204

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

July 10, 2013 / Shivananda Rao P

Data Guard Broker Configuration

Here is an example on creating Data Guard Broker configuration using DGMGRL.

Primary and standby database server details:

Primary Server: ora1-1.mydomain Database: SRPRIM
Standby Server: ora1-2.mydomain Database: SRPSTB
Primary Database Details:

 


SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- ---------------
OPEN   srprim        PRIMARY       MAXIMUM AVAILABILITY

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
350

Standby Database Details:

 

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS  INSTANCE_NAME DATABASE_ROLE    PROTECTION_MODE
------- ------------- ---------------- --------------------
MOUNTED srpstb        PHYSICAL STANDBY MAXIMUM AVAILABILITY

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
350

By default, the DG Broker configuration files are located at “$ORACLE_HOME/dbs” location. If you need to change it, then you can by using “alter system set dg_broker_config_file1=<required location>”.  Similarly for “dg_broker_config_file2”.

 

Remember that, on a RAC system, the DG broker configuration files need to be placed on a shared location where in it can be accessed by all the nodes.

 

On both primary and standby databases, enable the dataguard broker process by setting the value of the parameter “DG_BROKER_START” to TRUE.

 


SQL> alter system set dg_broker_start=true;

Add a static entry for the DGMGRL in the listener.ora file on both the primary and standby servers.

On Primary:


(SID_DESC=
 (GLOBAL_DBNAME=srprim_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db1)
 (SID_NAME=srprim)
)

On Standby:


(SID_DESC=
 (SID_NAME=srpstb)
 (GLOBAL_DBNAME=srpstb_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db1)
)

Listener.ora file contents on primary server:

LISTENER =
(DESCRIPTION_LIST =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ora1-1.mydomain)(PORT = 1   521))
 )
)

SID_LIST_LISTENER=
(SID_LIST=
 (SID_DESC=
  (GLOBAL_DBNAME=srprim_dgmgrl)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db1)
  (SID_NAME=srprim)
 )
 (SID_DESC=
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db1)
  (SID_NAME=krat)
 )
)

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

Listener.ora file contents on standby server:

LISTENER =
(DESCRIPTION_LIST =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
  (ADDRESS = (PROTOCOL = TCP)(HOST = ora1-2.mydomain)(PORT = 1   522))
 )
)

SID_LIST_LISTENER=
 (SID_LIST=
  (SID_DESC=
   (SID_NAME=srpstb)
   (GLOBAL_DBNAME=srpstb_dgmgrl)
   (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db1)
  )
  (SID_DESC=
   (SID_NAME=srpstb)
   (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db1)
  )
 )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

On the primary server, create the Dataguard Broker configuration.

[oracle@ora1-1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@srprim
Connected.
DGMGRL> create configuration 'dgtest'
> as primary database is 'srprim'
> connect identifier is srprim;
Configuration "dgtest" created with primary database "srprim"
DGMGRL>
DGMGRL> show configuration;

Configuration - dgtest

Protection Mode: MaxAvailability
 Databases:
 srprim - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Add the standby database “SRPSTB” to the configuration created above.

DGMGRL>
DGMGRL> add database 'srpstb'
> as connect identifier is srpstb
> maintained as physical;
Database "srpstb" added
DGMGRL>
DGMGRL> show configuration;

Configuration - dgtest

Protection Mode: MaxAvailability
 Databases:
 srprim - Primary database
 srpstb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

The configuration added, needs to be enabled.

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration

Configuration - dgtest

Protection Mode: MaxAvailability
 Databases:
 srprim - Primary database
 srpstb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Here we go !!

 

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

June 2, 2013 / Shivananda Rao P

Changing Protection Mode in a Dataguard Environment

Primary Database :  SRPRIM
Standby Database :  SRPSTB
Database version   :  11gR2

The database is running under Maximum Performance mode.

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- --------------------
OPEN   srprim        PRIMARY       MAXIMUM PERFORMANCE

In order to change the protection mode to either MAXIMUM AVAILABILITY/MAXIMUM PROTECTION, we need to have the standby redo logs configured on
the standby database. Also, the redo shippment parameter (log_archive_dest_2) on the primary database should be configured to use SYNCHRONOUS
(“SYNC”) mode.

Let’s check the number of online redo logs and it’s size on primary database

SQL> select group#,bytes/1024/1024 from v$log;

GROUP# BYTES/1024/1024
------ ------------------
1      100
2      100
3      100

It can be noticed from below that there are no standby redo log groups configured on the primary database.

SQL> select group#,bytes/1024/1024 from v$standby_log;

no rows selected

Add standby redo log groups on the primary database with the same size as that of the online redo log groups.

SQL> alter database add standby logfile group 4 size 100M;

Database altered.

SQL> alter database add standby logfile group 5 size 100M;

Database altered.

SQL> alter database add standby logfile group 6 size 100M;

Database altered.

SQL> alter database add standby logfile group 7 size 100M;

Database altered.

We can now notice that 4 standby redo log groups have been added with the same size as that of the online redo logs.
These standby redo logs will not be used on the primary database and will be used only when a switchover takes place.

SQL> select group#,bytes/1024/1024 from v$standby_log;

GROUP# BYTES/1024/1024
------ ---------------
4      100
5      100
6      100
7      100

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)
------- --------------
1       311

Standby:

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
------- --------------
1       311

Standby database is in sync with the primary database.

Standby database details:

SQL> select status,instance_name,database_role from v$database,v$Instance;

STATUS  INSTANCE_NAME DATABASE_ROLE
------- ------------- ------------------
MOUNTED srpstb        PHYSICAL STANDBY

On the standby database, there are 3 online redo log groups with the size 100M and there are no standby redo log groups.

SQL> select group#,bytes/1024/1024 from v$log;

GROUP# BYTES/1024/1024
------ ----------------
1      100
3      100
2      100

SQL> select group#,bytes/1024/1024 from v$standby_log;

no rows selected

Let’s add standby redo log groups on the standby database but before that, we need to check if MRP (Managed Recovery Process) is running on
the standby database and if running, then it needs to be cancelled.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS       SEQUENCE#
------- ------------ ----------
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
RFS     IDLE         0
RFS     IDLE         0
RFS     IDLE         0
MRP0    WAIT_FOR_LOG 312

8 rows selected.

Cancel the MRP process on the standby database:

SQL> alter database recover managed standby database cancel;

Database altered.

Add 4 Standby Redo Log (SRL) groups of size same as online redo log groups (100M) on the standby database:

SQL> alter database add standby logfile group 4 size 100M;

Database altered.

SQL> alter database add standby logfile group 5 size 100M;

Database altered.

SQL> alter database add standby logfile group 6 size 100M;

Database altered.

SQL> alter database add standby logfile group 7 size 100M;

Database altered.

SQL> select group#,bytes/1024/1024 from v$standby_log;

GROUP# BYTES/1024/1024
------ ---------------
4      100
5      100
6      100
7      100

Once the SRLs are added, start the MRP on the standby database

SQL> alter database recover managed standby database disconnectfrom session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS       SEQUENCE#
------- -----------  ----------
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
RFS     IDLE         0
RFS     IDLE         0
RFS     IDLE         0
MRP0    WAIT_FOR_LOG 312

8 rows selected.

As said earlier, configure the redo shippment parameter (log_archive_dest_2) on the primary database to use SYNCHRONOUS mode.

Primary database:

SQL> show parameter log_archive_dest_2

NAME               TYPE   VALUE
------------------ ------ -----------------------------------
log_archive_dest_2 string service=srpstb valid_for=(online_logf                          iles,primary_role) db_unique_name=srp                          stb

SQL> alter system set log_archive_dest_2='service=srpstb LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=srpstb';

System altered.

SQL> show parameter dest_2

NAME                        TYPE   VALUE
--------------------------- ------ ----------------------------
db_create_online_log_dest_2 string
log_archive_dest_2          string service=srpstb LGWR AFFIRM                              SYNC valid_for=(online_logfiles,p
                            rimary_role) db_unique_name=srpstb

Shutdown the primary database and mount it.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 208769024 bytes
Fixed Size 2226936 bytes
Variable Size 180356360 bytes
Database Buffers 20971520 bytes
Redo Buffers 5214208 bytes
Database mounted.
SQL>

Now change the protection mode on the primary database according to the requirement using the below command

“alter database set standby database to maximize {AVAILABILITY | PROTECTION| PERFORMANCE}”

Here, I am changing the protection mode from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY

SQL> alter database set standby database to maximize availability;

Database altered.

Once the mode is changed, open the primary database and verify the same.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- ----------------------
OPEN   srprim        PRIMARY       MAXIMUM AVAILABILITY

Check if the standby database is in sync with the primary database

On primary:

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)
------- --------------
1       316

Standby:

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS  INSTANCE_NAME DATABASE_ROLE    PROTECTION_MODE
------- ------------- ---------------- --------------------
MOUNTED srpstb        PHYSICAL STANDBY MAXIMUM AVAILABILITY

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
------- --------------
 1      316

Standby database is in sync with the primary and also the PROTECTION mode has been changed to MAXIMUM AVAILABILITY.

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

May 4, 2013 / Shivananda Rao P

Recovering A Dropped Table Using Tablespace Point In Time Recovery (TSPITR)

Database Name    : SRPRIM
Tablespace Name : MYTS

A table associated with the tablespace MYTS was accidentally dropped on one our Test environment. There was no flashback enabled on this
database and the recyclebin too was purged out. As a result, the table could not be recovered using flashback method. The other methods to
recover the dropped table would be:

1. Duplicate/Clone the database in which the table is dropped until time before the table was dropped to a temporary database, export the
table from the temporary database and import it to the main database.
–This method is the preferrable one. I shall demonstrate using this method in my coming posts.

2. Using Tablespace Point In Time Recovery. Here, the tablespace associate with the table that is dropped would be recovered until the time.
The main disadvantage of this method is that any other successfull transactions that were carried out on this tablespace after the time the
table was dropped, would be lost when you do a point in time recovery.

As this was a test environment and due to lack of time, I had to opt this method but yes, “only upon cofirmation from the productivity
managment site.” 🙂

Schema name: Shivu
Table name : TEST

SQL> select * from test;

CODE
----------
100
200
300
400
500
600
700
800
900
1000

10 rows selected.

Make sure that you have a valid RMAN level 0 backup to proceed.

SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
04-MAY-2013 11:38:12

Table TEST has been dropped and also the recyclebin has been purged.

SQL> drop table shivu.test;

Table dropped.

SQL>
SQL> purge recyclebin;

Recyclebin purged.

SQL> select count(*) from shivu.test;
select count(*) from shivu.test
 *
ERROR at line 1:
ORA-00942: table or view does not exist

Now lets, perform the tablespace point in time recovery until the time before the table was dropped.
For TSPITR, we need to specify the auxiliary destination where RMAN would create a temporary database by restoring the controlfile, SYSTEM
tablespace, UNDO tablespace, SYSAUX tablespace and finally the tablespace that needs to be recovered.
Once the temporary database is restored and recovered, RMAN automatically exports the contents of tablespace to be recovered from the
temproary tablespace to a dump file and imports this dump into the Target(Main) database where the table was dropped.

[oracle@ora1-1 dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 4 14:07:23 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: SRPRIM (DBID=216679430)

RMAN> run
2> {
3> recover tablespace MYTS until time "to_date('2013-05-04:11:38:00','YYYY-MM:DD:HH24:MI:SS')" auxiliary destination '+FRA';
4> }

Starting recover at 04-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='snya'

initialization parameters used for automatic instance:
db_name=SRPRIM
db_unique_name=snya_tspitr_SRPRIM
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=+FRA
log_archive_dest_1='location=+FRA'
#No auxiliary parameter file used
starting up automatic instance SRPRIM

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2227744 bytes
Variable Size 100663776 bytes
Database Buffers 184549376 bytes
Redo Buffers 4837376 bytes
Automatic instance created
List of tablespaces that have been dropped from the target database:
Tablespace MYTS

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2013-05-04:11:38:00','YYYY-MM:DD:HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 04-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/autobackup/c-216679430-20130503-00
channel ORA_AUX_DISK_1: piece handle=/u01/autobackup/c-216679430-20130503-00 tag=TAG20130503T202409
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+FRA/srprim/controlfile/current.446.814543667
Finished restore at 04-MAY-13

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2013-05-04:11:38:00','YYYY-MM:DD:HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 6 to new;
set newname for datafile 8 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 6, 8;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +FRA in control file

Starting restore at 04-MAY-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to +FRA
channel ORA_AUX_DISK_1: reading from backup piece +FRA/srprim/backupset/2013_05_03/nnndn0_tag20130503t202042_0.425.814479643
channel ORA_AUX_DISK_1: piece handle=+FRA/srprim/backupset/2013_05_03/nnndn0_tag20130503t202042_0.425.814479643 tag=TAG20130503T202042
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +FRA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +FRA
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00008 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece +FRA/srprim/backupset/2013_05_03/nnndn0_tag20130503t202042_0.426.814479645
channel ORA_AUX_DISK_1: piece handle=+FRA/srprim/backupset/2013_05_03/nnndn0_tag20130503t202042_0.426.814479645 tag=TAG20130503T202042
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:35
Finished restore at 04-MAY-13

datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=814543825 file name=+DATA/srprim/datafile/myts.275.814543731
datafile 8 switched to datafile copy
input datafile copy RECID=14 STAMP=814543825 file name=+DATA/srprim/datafile/myts.276.814543733
datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=814543825 file name=+FRA/srprim/datafile/system.450.814543729
datafile 3 switched to datafile copy
input datafile copy RECID=16 STAMP=814543825 file name=+FRA/srprim/datafile/undotbs1.452.814543733
datafile 2 switched to datafile copy
input datafile copy RECID=17 STAMP=814543825 file name=+FRA/srprim/datafile/sysaux.448.814543685

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2013-05-04:11:38:00','YYYY-MM:DD:HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 6 online";
sql clone "alter database datafile 8 online";
# recover and open resetlogs
recover clone database tablespace "MYTS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 6 online

sql statement: alter database datafile 8 online

Starting recover at 04-MAY-13
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 283 is already on disk as file +FRA/srprim/archivelog/2013_05_03/thread_1_seq_283.427.814479821
archived log for thread 1 with sequence 284 is already on disk as file +FRA/srprim/archivelog/2013_05_03/thread_1_seq_284.429.814479833
archived log for thread 1 with sequence 285 is already on disk as file +FRA/srprim/archivelog/2013_05_04/thread_1_seq_285.433.814534647
archived log for thread 1 with sequence 286 is already on disk as file +FRA/srprim/archivelog/2013_05_04/thread_1_seq_286.434.814534651
archived log for thread 1 with sequence 287 is already on disk as file +FRA/srprim/archivelog/2013_05_04/thread_1_seq_287.435.814534887
archived log file name=+FRA/srprim/archivelog/2013_05_03/thread_1_seq_283.427.814479821 thread=1 sequence=283
archived log file name=+FRA/srprim/archivelog/2013_05_03/thread_1_seq_284.429.814479833 thread=1 sequence=284
archived log file name=+FRA/srprim/archivelog/2013_05_04/thread_1_seq_285.433.814534647 thread=1 sequence=285
archived log file name=+FRA/srprim/archivelog/2013_05_04/thread_1_seq_286.434.814534651 thread=1 sequence=286
archived log file name=+FRA/srprim/archivelog/2013_05_04/thread_1_seq_287.435.814534887 thread=1 sequence=287
media recovery complete, elapsed time: 00:00:08
Finished recover at 04-MAY-13

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace MYTS read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+FRA''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
+FRA''";
}
executing Memory Script

sql statement: alter tablespace MYTS read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+FRA''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+FRA''

Performing export of metadata...
 EXPDP> Starting "SYS"."TSPITR_EXP_snya":
 EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
 EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
 EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
 EXPDP> Master table "SYS"."TSPITR_EXP_snya" successfully loaded/unloaded
 EXPDP> ******************************************************************************
 EXPDP> Dump file set for SYS.TSPITR_EXP_snya is:
 EXPDP> +FRA/tspitr_snya_51808.dmp
 EXPDP> ******************************************************************************
 EXPDP> Datafiles required for transportable tablespace MYTS:
 EXPDP> +DATA/srprim/datafile/myts.275.814543731
 EXPDP> +DATA/srprim/datafile/myts.276.814543733
 EXPDP> Job "SYS"."TSPITR_EXP_snya" successfully completed at 14:13:10
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

Performing import of metadata...
 IMPDP> Master table "SYS"."TSPITR_IMP_snya" successfully loaded/unloaded
 IMPDP> Starting "SYS"."TSPITR_IMP_snya":
 IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
 IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
 IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
 IMPDP> Job "SYS"."TSPITR_IMP_snya" successfully completed at 14:13:46
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace MYTS read write';
sql 'alter tablespace MYTS offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace MYTS read write

sql statement: alter tablespace MYTS offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file +FRA/srprim/tempfile/temp.456.814543869 deleted
auxiliary instance file +FRA/srprim/onlinelog/group_3.455.814543857 deleted
auxiliary instance file +FRA/srprim/onlinelog/group_2.454.814543849 deleted
auxiliary instance file +FRA/srprim/onlinelog/group_1.453.814543837 deleted
auxiliary instance file +FRA/srprim/datafile/sysaux.448.814543685 deleted
auxiliary instance file +FRA/srprim/datafile/undotbs1.452.814543733 deleted
auxiliary instance file +FRA/srprim/datafile/system.450.814543729 deleted
auxiliary instance file +FRA/srprim/controlfile/current.446.814543667 deleted
Finished recover at 04-MAY-13

RMAN> exit

Once the import is done successfully, RMAN automatically deletes the temporary database that it had created earlier.
Now, lets connect to the main database and check if we are able to access the dropped table. But, before that, you need to bring the
tablespace online.
This can be noticed here.

[oracle@ora1-1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 4 14:16:25 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> set linesize 300
SQL> col name for a60
SQL> select name,status from v$datafile;

NAME                                           STATUS
---------------------------------------------  ------------
+DATA/srprim/datafile/system.257.800915677     SYSTEM
+DATA/srprim/datafile/sysaux.258.800915679     ONLINE
+DATA/srprim/datafile/undotbs1.265.800915683   ONLINE
+DATA/srprim/datafile/users.256.800915683      ONLINE
+DATA/srprim/datafile/example.259.800915681    ONLINE
+DATA/srprim/datafile/myts.276.814543733       OFFLINE
+DATA/srprim/datafile/tbs.268.804023681        ONLINE
+DATA/srprim/datafile/myts.275.814543731       OFFLINE

8 rows selected.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME             STATUS
--------------------------- ---------
SYSTEM                      ONLINE
SYSAUX                      ONLINE
UNDOTBS1                    ONLINE
TEMP                        ONLINE
USERS                       ONLINE
EXAMPLE                     ONLINE
MYTS                        OFFLINE
TBS                         ONLINE

8 rows selected.

You can notice that the tablespace MYTS is offline. Before proceeding, this tablespace needs to be made online.

SQL> alter tablespace MYTS online;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME     STATUS
------------------- ---------
SYSTEM              ONLINE
SYSAUX              ONLINE
UNDOTBS1            ONLINE
TEMP                ONLINE
USERS               ONLINE
EXAMPLE             ONLINE
MYTS                ONLINE
TBS                 ONLINE

8 rows selected.

Now lets check the contents of the table “TEST” of schema “SHIVU”.

SQL> select count(*) from shivu.test;

COUNT(*)
----------
 10

SQL> select * from shivu.test;

CODE
----------
 100
 200
 300
 400
 500
 600
 700
 800
 900
 1000

10 rows selected.

We are able to get back the contents of the TEST table.

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

March 23, 2013 / Shivananda Rao P

Manually Upgrading RAC database from 10.2.0.1 to 10.2.0.4

After upgrading the CRS from 10.2.0.1 to 10.2.0.4 (link -> https://shivanandarao-oracle.com/2013/03/04/upgrade-crs-from-10-2-0-1-to-10-2-0-4/), now lets move on upgrading the database from 10.2.0.1 to 10.2.0.4

Step 1:
Lets stop the database running on both the nodes 10gnode1 and 10gnode2. Once the database is stopped, we shall stop the ASM instances and the nodeapps running on all the nodes.

[oracle@10gnode1 ~]$ srvctl stop database -d srprim
[oracle@10gnode1 ~]$ srvctl stop asm -n 10gnode1
[oracle@10gnode2 ~]$ srvctl stop asm -n 10gnode2
[oracle@10gnode1 ~]$ srvctl stop nodeapps -n 10gnode1
[oracle@10gnode2 ~]$ srvctl stop nodeapps -n 10gnode2

Step 2:
From the unzipped patch, run the runInstaller file on 10gnode1.
Select the 10g Database home that needs to be upgraded and verify the home path.

Once the patch has been applied, the installation would recommend you to run root.sh script from the 10g Database home as ROOT user. Run the script on both the nodes.

[root@10gnode1 ~]# /u01/app/oracle/product/10.2.0/db_1/root.sh
[root@10gnode2 ~]# /u01/app/oracle/product/10.2.0/db_1/root.sh

Step 3:
Now, lets start the Nodeapps and the ASM instances on both the nodes.

[oracle@10gnode1 ~]$ srvctl start nodeapps -n 10gnode1
[oracle@10gnode2 ~]$ srvctl start nodeapps -n 10gnode2
[oracle@10gnode1 ~]$ srvctl start asm -n 10gnode1
[oracle@10gnode2 ~]$ srvctl start asm -n 10gnode2

Step 4:
Database Upgrade.

On 10gnode1, start the instance “srprim1”. Since we are upgrading the database, we are starting up only one instance.

[oracle@10gnode1 dbs]$ export ORACLE_SID=srprim1
[oracle@10gnode1 dbs]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@10gnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Feb 8 13:02:552013

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 926941184 bytes
Fixed Size 1270748 bytes
Variable Size 247467044 bytes
Database Buffers 675282944 bytes
Redo Buffers 2920448 bytes
SQL>

Step 5:

Create pfile from SPFILE in order to remove the below mentioned Cluster Parameters.

SQL> create pfile='/u01/initsrprim1.ora' from spfile;

File created.

SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.

Step 6:
Now, remove or comment the below mentioned cluster parameters in the newly created pfile.


vi /u01/initsrprim1.ora
#REMOVE THESE PARAMETERS#
#*.cluster_database_instances=2
#*.cluster_database=true
#srprim2.instance_number=2
#srprim1.instance_number=1

Step 7:
Startup the instance “srprim1” in upgrade mode using the modified PFILE.

[oracle@10gnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Feb 8 13:09:212013

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup upgrade pfile='/u01/initsrprim1.ora';
ORACLE instance started.

Total System Global Area 926941184 bytes
Fixed Size 1270748 bytes
Variable Size 247467044 bytes
Database Buffers 675282944 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.

Step 8:
Once the database is started up in the UPGRADE mode, run the CATUPGRD.SQL script to carryout the upgrade process.


SQL> spool '/u01/10204upgrd.txt';
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catupgrd.sql

Step 9:
Once the script is executed successfully, shutdown the database and open it normally using the above pfile. Run the utlrp.sql script from the 10.2.0 Oracle DB home/rdbms/admin path to validate the invalid objects.


[oracle@10gnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Feb 8 13:33:082013

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production
With the Real Application Clusters option

SQL> select status,instance_name from v$instance;

STATUS       INSTANCE_NAME
------------ ----------------
OPEN MIGRATE srprim1

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.4.0 - Production
With the Real Application Clusters option

[oracle@10gnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Feb 8 13:33:532013

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile=/u01/initsrprim1.ora
ORACLE instance started.

Total System Global Area 926941184 bytes
Fixed Size 1270748 bytes
Variable Size 247467044 bytes
Database Buffers 675282944 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL>
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

STEP 10:
Now, lets start the instance “srprim1” using the global SPFILE rather than the temporary PFILE (“/u01/initsrprim1.ora”)


SQL> startup
ORACLE instance started.

Total System Global Area 926941184 bytes
Fixed Size 1270748 bytes
Variable Size 247467044 bytes
Database Buffers 675282944 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL>
SQL> select status,instance_name from v$instance;

STATUS INSTANCE_NAME
------ ---------------------
OPEN   srprim1

SQL> select * from v$version;

BANNER
---------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Step 11:
Now that the SRPRIM database is upgraded to 10.2.0.4, lets check the status of the instances using SRVCTL


[oracle@10gnode1 dbs]$ srvctl status database -d srprim
Instance srprim1 is running on node 10gnode1
Instance srprim2 is not running on node 10gnode2
[oracle@10gnode1 dbs]$

In STEP 6 through STEP 9, you can notice that the upgrade was carried out using a temporary PFILE located at “/u01/initsrprim1.ora” and that no changes were made to the original PFILE or SPFILE (globally located).

Hence after the upgrade was done successfully, I had no necessity of uncommenting the changes done to the CLUSTER parameters in the temporary PFILE. The instance “srprim1” has been started using the Global SPFILE which can be noticed in STEP 10.

You can notice above that the instance “srprim2” is not started.
Lets start it using the “SRVCTL start instance” command.

[oracle@10gnode1 dbs]$ srvctl start instance -d srprim -i srprim2

Lets crossverify once again the status of all the instances.

[oracle@10gnode1 dbs]$ srvctl status database -d srprim
Instance srprim1 is running on node 10gnode1
Instance srprim2 is running on node 10gnode2

Here you go !!

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.