Purging trace and dump files with 11g ADRCI

In previous versions of Oracle prior to 11g, we had to use our own housekeeping scripts to purge the udump, cdump and bdump directories.

In Oracle 11g, we now have the ADR (Automatic Diagnostic Repository) which is defined by the diagnostic_dest parameter.

So how are unwanted trace and core dump files cleaned out in 11g automatically?

This is done by the MMON background process.

There are two time attributes which are used to manage the retention of information in ADR. Both attributes correspond to a number of hours after which the MMON background process purges the expired ADR data.

LONGP_POLICY (long term) defaults to 365 days and relates to things like Incidents and Health Monitor warnings.

SHORTP_POLICY (short term) defaults to 30 days and relates to things like trace and core dump files

The ADRCI command show control will show us what the current purge settings are as shown below.

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/ttrlwiki/ttrlwiki:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1095473802           720                  8760                 2010-07-07 08:46:56.405618 +08:00        2010-08-22 22:14:11.443356 +08:00                                                 1                    2                    76                   1                    2010-07-07 08:46:56.405618 +08:00

In this case it is set to the defaults of 720 hours (30 days) for the Short Term and 8760 hours (One year) for the long term category.

We can change this by using the ADRCI command ‘set control’

In this example we are changing the retention to 15 days for the Short Term policy attribute (note it is defined in Hours)

adrci> set control (SHORTP_POLICY =360)
adrci> show control
ADR Home = /u01/app/oracle/diag/rdbms/ttrlwiki/ttrlwiki:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1095473802           360                  8760                 2010-08-27 09:36:09.385370 +08:00        2010-08-22 22:14:11.443356 +08:00                                                 1                    2                    76                   1                    2010-07-07 08:46:56.405618 +08:00

We can also manually purge information from the ADR using the ‘purge’ command from ADRCI (note this is defined in minutes and not hours!).

In this example we are purging all trace files older than 6 days. We see that the LAST_MANUPRG_TIME column is now populated.

adrci> purge -age 8640 -type TRACE  
adrci> show control
ADR Home = /u01/app/oracle/diag/rdbms/ttrlwiki/ttrlwiki:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1095473802           360                  8760                 2010-08-27 09:36:09.385370 +08:00        2010-08-22 22:14:11.443356 +08:00        2010-08-27 09:50:07.399853 +08:00        1                    2                    76                   1                    2010-07-07 08:46:56.405618 +08:00

Using DBMS_XPLAN to obtain the EXPLAIN PLAN of a SQL Statement

Very often we run AWR, ASH and ADDM reports which does highlight the Top SQL statements by disk reads, CPU usage and elapsed time. But an important piece of information is missing which is the Explain Plan.

Using GUI tools like Enterprise Manager will enable us to drill down to the Explain Plan from an individual SQL statement, but how do we do it from the command line?

The answer is simply using DBMS_XPLAN.DISPLAY_AWR and provide to it as a parameter the SQL_ID in question (which can be picked up from the AWR or ASH report).

For example in the ASH report we see this section related to the Top SQL

Top SQL with Top Events     DB/Inst: FILESDB/filesdb  (Jul 19 13:23 to 13:38)

                                                       Sampled #
                SQL ID             Planhash        of Executions     % Activity
----------------------- -------------------- -------------------- --------------
Event                          % Event Top Row Source                    % RwSrc
------------------------------ ------- --------------------------------- -------
         a9j69t1bh6982           2008213504                    1           8.33
SQL*Net more data to client       8.33 TABLE ACCESS - FULL                  8.33
SELECT x."CUST_ID",x."CUST_FIRST_NAME",x."CUST_LAST_NAME",x."CUST_GENDER",x."CUS
T_YEAR_OF_BIRTH",x."CUST_MARITAL_STATUS",x."CUST_STREET_ADDRESS",x."CUST_POSTAL_

We obtain the SQL_ID which is “a9j69t1bh6982″ and now to view the Explain Plan for this SQL statement we provide it as a parameter to the query as shown below.

SQL> set linesize 120
SQL> set pagesize 500
SQL> select * from TABLE(dbms_xplan.display_awr('a9j69t1bh6982'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a9j69t1bh6982
--------------------
SELECT x."CUST_ID",x."CUST_FIRST_NAME",x."CUST_LAST_NAME",x."CUST_GENDER
",x."CUST_YEAR_OF_BIRTH",x."CUST_MARITAL_STATUS",x."CUST_STREET_ADDRESS"
,x."CUST_POSTAL_CODE",x."CUST_CITY",x."CUST_CITY_ID",x."CUST_STATE_PROVI
NCE",x."CUST_STATE_PROVINCE_ID",x."COUNTRY_ID",x."CUST_MAIN_PHONE_NUMBER
",x."CUST_INCOME_LEVEL",x."CUST_CREDIT_LIMIT",x."CUST_EMAIL",x."CUST_TOT
AL",x."CUST_TOTAL_ID",x."CUST_SRC_ID",x."CUST_EFF_FROM",x."CUST_EFF_TO",
x."CUST_VALID" FROM "SH"."CUSTOMERS" x

Plan hash value: 2008213504

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   414 (100)|          |
|   1 |  TABLE ACCESS FULL| CUSTOMERS | 55500 |  9755K|   414   (1)| 00:00:05 |
-------------------------------------------------------------------------------

We can see that the query is performing a full table scan of the Customers table – something which is not very evident just my reading the AWR or ASH report.

Useful GoldenGate commands - SHELL, OBEY, HELP, HISTORY,VERSIONS

There are a number of other useful GoldenGate commands which we can run from the GGSCI interface. The GoldenGate Reference Guide has information in more detail, but I have tried out a few and found some to be very useful which I am highlighting below.


HISTORY: To view a list of the most recently issued GGSCI commands since the GGSCI session started


GGSCI (linux01.kim.com) 4> history
GGSCI Command History
1: show all
2: info all
3: start extract ext3
4: history


! : Use the ! command to execute a previous GGSCI command without modifications.


GGSCI (linux01.kim.com) 3> info extract ext3
EXTRACT EXT3 Last Started 2010-07-21 14:29 Status RUNNING
Checkpoint Lag 01:07:22 (updated 00:00:02 ago)
Log Read Checkpoint Oracle Redo Logs
2010-07-21 13:22:28 Seqno 188, RBA 49576960


GGSCI (linux01.kim.com) 4> !
info extract ext3
EXTRACT EXT3 Last Started 2010-07-21 14:29 Status RUNNING
Checkpoint Lag 01:07:22 (updated 00:00:04 ago)
Log Read Checkpoint Oracle Redo Logs
2010-07-21 13:22:28 Seqno 188, RBA 49576960

We can also use a combination of “!” and HISTORY to run a particular command listed in the history. For example, to run the command 3 listed in the history, we can just run “! 3″.


VERSIONS: to display operating system and database version information. Use DBLOGIN to connect to the database first


GGSCI (linux01.kim.com) 11> dblogin userid ggs_owner, password ggs_owner
Successfully logged into database.
GGSCI (linux01.kim.com) 12> versions
Operating System:
Linux
Version #1 SMP Mon Mar 29 20:19:03 EDT 2010, Release 2.6.18-194.el5PAE
Node: linux01.kim.com
Machine: i686
Database:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
PL/SQL Release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production


SHELL: to execute shell commands from within the GGSCI interface.


GGSCI (linux01.kim.com) 13> shell ls -l /home/oracle/goldengate/dirdat/
total 50588
-rw-rw-rw- 1 oracle oinstall 985 Jul 16 14:57 lt000000
-rw-rw-rw- 1 oracle oinstall 9999629 Jul 19 13:09 lt000001


HELP: to obtain information about a GoldenGate command. The basic command returns a list of command categories and the associated commands.

GGSCI (linux01.kim.com) 15> help

GGSCI Command    Summary
SUBDIRS          CREATE SUBDIRS
ER               INFO ER, KILL ER, LAG ER, SEND ER, STATUS ER,
                START ER, STATS ER, STOP ER
EXTRACT          ADD, ALTER, CLEANUP, DELETE, INFO, KILL,
                LAG, SEND, START, STATS, STATUS, STOP
EXTTRAIL         ADD, ALTER, DELETE, INFO
GGSEVT           VIEW
MANAGER          INFO, REFRESH, SEND, START, STOP, STATUS
MARKER           INFO
PARAMS           EDIT, VIEW
REPLICAT         ADD, ALTER, CLEANUP, DELETE, INFO, KILL,
                LAG, SEND, START, STATS, STATUS, STOP
REPORT           VIEW
RMTTRAIL         ADD, ALTER, DELETE, INFO
TRACETABLE       ADD, DELETE, INFO
TRANDATA         ADD, DELETE, INFO
Database         DBLOGIN, LIST TABLES,
                ENCRYPT PASSWORD
DDL              DUMPDDL
CHECKPOINTTABLE  ADD CHECKPOINTTABLE, DELETE CHECKPOINTTABLE,
                CLEANUP CHECKPOINTTABLE, INFO CHECKPOINTTABLE
Miscellaneous    FC, HELP, HISTORY, INFO ALL, INFO MARKER, OBEY,
                SET, SHELL, SHOW, VERSIONS, !

For help on a specific command, type HELP  [command] [object]

Example: HELP ADD REPLICAT

OBEY: to process a file that contains a list of GoldenGate commands. OBEY is useful for executing commands that are frequently used in sequence. Suppose we want to run some Goldengate commands and execute those from a Unix shell script which can be called via say cron. So we have a start_goldgate.sh shell script which will use the GGSCI command OBEY to call a text file which has the Goldengate commands which we would like to run in sequence.


[oracle@linux01 goldengate]$ cat start_goldgate.sh
cd /home/oracle/goldengate
./ggsci < EOF
OBEY /home/oracle/goldengate/startup.txt
EOF


[oracle@linux01 goldengate]$ cat startup.txt
START MANAGER
START EXTRACT EXT3
START EXTRACT DPUMP
INFO ALL


[oracle@linux01 goldengate]$ ./start_goldgate.sh

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x86, 32bit (optimized), Oracle 11 on Sep 29 2009 08:50:50

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.

GGSCI (linux01.kim.com) 1>
GGSCI (linux01.kim.com) 2> START MANAGER

Manager started.

GGSCI (linux01.kim.com) 3> START EXTRACT EXT3

EXTRACT EXT3 is already running.

GGSCI (linux01.kim.com) 4> START EXTRACT DPUMP

EXTRACT DPUMP is already running.

GGSCI (linux01.kim.com) 5> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP       00:00:00      00:00:01
EXTRACT     ABENDED     EXT2        00:00:00      172:36:58
EXTRACT     RUNNING     EXT3        00:00:00      00:00:04

Oracle GoldenGate - how to connect to a particular database if there are multiple databases on the source or target server

Steps to connect to GoldenGate if there are a number of databases running on the source server or on the target server. Or there could be a case where we have installed GoldenGate for Oracle 10g and we have both 10g as well as 11g Oracle Homes on the same machine and we want to connect to the Oracle 10g environment in particular.

We can do that in 2 ways, as far as my knowledge goes. Below are the steps:

1) Either set the right environment using .oraenv
or
2) Specify the right TNS alias in the Manager, Extract or Replicat parameter file where we have used the USERID keyword

For example, just to illustrate the same, I wrongly set the ORACLE_SID variable to a nonexistent value. The manager process will not start since it will try to connect with the username and password used in the manager parameter file to the LOCAL database for which the environment has been now set up and since the ORACLE_SID is wrong, there is no database running on the server with that SID.

$ export ORACLE_SID=xyz

GGSCI (vixen) 2> start manager

Manager started.

GGSCI (vixen) 3> status manager

Manager is DOWN!

This is what we will see in the GoldenGate logs:

2010-08-19 11:04:37 GGS ERROR 182 OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory).

2010-08-19 11:04:37 GGS ERROR 190 PROCESS ABENDING.

To use a TNS alias, make sure that we are able to do a tnsping as well as connect via SQL*PLUS using that alias before we launch GGSCI.

In the relevant parameter file (in this example an extract parameter file) we just add the TNS alias to the USERID as shown below:

USERID ggs_owner@levengr2, PASSWORD ggs_owner

If the TNS connection is not defined properly, we can expect to see an error like this in the log file:

2010-08-19 11:09:46 GGS ERROR 182 OCI Error during OCIServerAttach (status = 12154-ORA-12154: TNS:could not resolve the connect identifier specified)
.
2010-08-19 11:09:46 GGS ERROR 190 PROCESS ABENDING.

We can also use the same TNS alias method when we use DBLOGIN keyword to establish a database connection via GoldenGate

GGSCI (devastator) 2> dblogin userid ggs_owner@levengr2, password ggs_owner
Successfully logged into database.

Using GoldenGate to replicate between databases on same host

Steps to set up GoldenGate when both the source and target database are physically located on the same server:

Basically all we have do is to set up TNS aliases to connect to the source and target databases and make sure that before we do launch GGSCI, we are able to connect to both source and target databases from say SQL*PLUS in the environment from where we are running the GoldenGate software via GGSCI.

If both the source and target databases are Oracle, it would make more sense to perform the initial load using Oracle utilities like say Import/Export, Data Pump, SQL Loader etc because the performance of those utilities are far better as compared to the GoldenGate initial load. I think GoldenGate should come into the picture after the initial data load is done to keep the online changes taking place after that synchronized between source and target.

However just to demonstrate the concept, I will be performing an initial data load using GoldenGate. The source database is an Oracle 11g R2 database ‘ora11g’ and the target database is ‘ora10g’, both running on Solaris 10 box, sun01.

While we could have idealy have two separate GoldenGate installations on the same machine – one for the Oracle 11g database and one for the Oracle 10g database, I am testing the same using a single GoldenGate for Oracle 11g on 64 bit Solaris software installation.

Note however, that I have setup the GoldenGate schema in both source as well as target database and granted the required roles and privileges as well as ensure that the GGS_OWNER schema has got the required privileges on the SYSTEM.MYOBJECTS table which we are replicating here.

sun01:/export/home/oracle/goldengate $ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Solaris, sparc, 64bit (optimized), Oracle 11 on Sep 18 2009 12:50:18

SOURCE

GGSCI (sun01) 9> dblogin userid ggs_owner@ora11g, password ggs_owner
Successfully logged into database.

GGSCI (sun01) 10> versions
Operating System:
SunOS
Version Generic_141444-09, Release 5.10
Node: sun01
Machine: sun4v

Database:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production

TARGET

GGSCI (sun01) 7> dblogin userid ggs_owner@ora10g, password ggs_owner
Successfully logged into database.

GGSCI (sun01) 8> versions
Operating System:
SunOS
Version Generic_141444-09, Release 5.10
Node: sun01
Machine: sun4v

Database:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production

SOURCE

ADD EXTRACT load1, SOURCEISTABLE

edit params load1

EXTRACT load1
USERID ggs_owner@ora11g, PASSWORD ggs_owner
RMTHOST sun01, MGRPORT 7809
RMTTASK replicat, GROUP load2
TABLE system.myobjects;

TARGET

ADD REPLICAT load2, SPECIALRUN

edit params load2

REPLICAT load2
USERID ggs_owner@ora10g, PASSWORD ggs_owner
ASSUMETARGETDEFS
MAP system.myobjects, TARGET system.myobjects;

SOURCE

GGSCI (sun01) 10> start extract load1

After the extract starts we can see that the extract process is already reading records from the source table which is SYSTEM.MYOBJECTS

GGSCI (sun01) 10> info extract load1
EXTRACT LOAD1 Last Started 2010-08-24 10:22 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SYSTEM.MYOBJECTS
2010-08-24 10:23:21 Record 12269
Task SOURCEISTABLE

GGSCI (sun01) 12> info extract load1

EXTRACT LOAD1 Last Started 2010-08-24 10:22 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SYSTEM.MYOBJECTS
2010-08-24 10:24:03 Record 26004
Task SOURCEISTABLE

GGSCI (sun01) 13> info extract load1

EXTRACT LOAD1 Last Started 2010-08-24 10:22 Status RUNNING
Checkpoint Lag Not Available
Log Read Checkpoint Table SYSTEM.MYOBJECTS
2010-08-24 10:24:45 Record 40207
Task SOURCEISTABLE

TARGET

On the target we can see that the Replicat process is also running – we did not have to explicitly start this initial load replicat process

GGSCI (sun01) 9> info replicat load2

REPLICAT LOAD2 Initialized 2010-08-24 10:21 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:02:06 ago)
Log Read Checkpoint Not Available
Task SPECIALRUN

Once the initial load process has completed, we will see that both the extract as well as the replicat process has stopped.

SOURCE

GGSCI (sun01) 22> info extract load1

EXTRACT LOAD1 Last Started 2010-08-24 10:22 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Table SYSTEM.MYOBJECTS
2010-08-24 10:26:30 Record 76358
Task SOURCEISTABLE

TARGET

GGSCI (sun01) 23> send replicat load2 getlag

ERROR: REPLICAT LOAD2 not currently running.

Let us know test the same by connecting to the target database on the same machine

sun01:/export/home/oracle/goldengate $ sqlplus system@ora10g

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 24 10:27:45 2010

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

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from myobjects;

COUNT(*)
———-
76358

PART II: Oracle Database Upgrade & ASM Upgrade

PART II: Oracle Database Upgrade & ASM Upgrade

I don’t have separate ASM home. I’m keeping ASM in ORACLE_HOME itself; So Now going to continue my database & asm upgrade.
1. From oracle user you need to exeucte “runInstaller” then you will get below screen shot.
2. Please make sure your ORACLE_HOME in your path.
3. Please check any listener running from all nodes; (Else it’s will create problem)
4. Please check any database/asm instance running (If yes; pls shutdown those instance)

For example:
[oracle@linux1 bin]$ ps -ef | grep pmon
oracle 11993 22392 0 13:16 pts/2 00:00:00 grep pmon

[oracle@linux1 bin]$ ps -ef | grep lsn
oracle 5155 1 0 11:06 ? 00:00:08
/u01/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER_LINUX1 -inherit
oracle 12000 22392 0 13:16 pts/2 00:00:00 grep lsn

[oracle@linux1 bin]$ lsnrctl stop LISTENER_LINUX1
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-JUL-2009 13:16:42

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux1-vip.gen.com)(PORT=1521)(IP=FIRST)))

The command completed successfully

[oracle@linux2 bin]$ ps -ef | grep LISTENER_LINUX2
oracle 12726 1 0 13:29 ? 00:00:01
/u01/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER_LINUX2 -inherit
oracle 14894 8929 0 13:36 pts/1 00:00:00 grep LISTENER_LINUX2

[oracle@linux2 bin]$ lsnrctl stop LISTENER_LINUX2
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-JUL-2009 13:36:35

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux2-vip.gen.com)(PORT=1521)(IP=FIRST)))

The command completed successfully


[oracle@linux2 bin]$ ps -ef | grep pmon

oracle 14993 8929 9 13:36 pts/1 00:00:00 grep pmon


[oracle@linux2 bin]$ ps -ef | grep lsn

oracle 15002 8929 0 13:36 pts/1 00:00:00 grep lsn


Before upgrade cluster status & Listener status


[root@linux1 bin]# ./crs_stat -t


Name Type Target State Host

------------------------------------------------------------

ora.devdb.db application OFFLINE OFFLINE

ora....b1.inst application OFFLINE OFFLINE

ora....b2.inst application OFFLINE OFFLINE

ora...._taf.cs application OFFLINE OFFLINE

ora....db1.srv application OFFLINE OFFLINE

ora....db2.srv application OFFLINE OFFLINE

ora....SM1.asm application ONLINE ONLINE linux1

ora....X1.lsnr application ONLINE ONLINE linux1

ora.linux1.gsd application ONLINE ONLINE linux1

ora.linux1.ons application ONLINE ONLINE linux1

ora.linux1.vip application ONLINE ONLINE linux1

ora....SM2.asm application ONLINE ONLINE linux2

ora....X2.lsnr application ONLINE ONLINE linux2

ora.linux2.gsd application ONLINE ONLINE linux2

ora.linux2.ons application ONLINE ONLINE linux2

ora.linux2.vip application ONLINE ONLINE linux2


[oracle@linux1 bin]$ ps -ef | grep lsn


oracle 8117 1 0 03:17 ? 00:00:02

/u01/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER_LINUX1 -inherit

oracle 12039 10732 0 03:28 pts/3 00:00:00 grep lsn


[oracle@linux1 bin]$ lsnrctl stop LISTENER_LINUX1

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 25-JUL-2009 03:28:18

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux1-vip.gen.com)(PORT=1521)(IP=FIRST)))

The command completed successfully


[oracle@linux1 bin]$ ps -ef | grep lsn

oracle 12085 10732 0 03:28 pts/3 00:00:00 grep lsn


[oracle@linux1 bin]$ ps -ef | grep pmon

oracle 8024 1 0 03:17 ? 00:00:03 asm_pmon_+ASM1

oracle 12162 10732 0 03:28 pts/3 00:00:00 grep pmon


[oracle@linux2 ~]$ cd /u01/oracle/product/10.2.0/crs/bin/

[oracle@linux2 bin]$ ps -ef | grep lsn

oracle 7134 1 0 03:19 ? 00:00:01

/u01/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER_LINUX2 -inherit

oracle 10678 10552 0 03:29 pts/2 00:00:00 grep lsn


[oracle@linux2 bin]$ lsnrctl stop LISTENER_LINUX2

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 25-JUL-2009 03:29:45

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux2-vip.gen.com)(PORT=1521)(IP=FIRST)))

The command completed successfully


[oracle@linux2 bin]$ ps -ef | grep pmon

oracle 10784 1 21 03:29 ? 00:00:00 asm_pmon_+ASM2

oracle 10806 10552 0 03:30 pts/2 00:00:00 grep pmon


Step 2:
This is TWO Nodes cluster; that’s why you will get your cluster nodes names list.

Step 3:
If you want register you CSI Nr then you can otherwise ignore this slide

Step 4:
This is pre-request checking steps; in case any thing wrong here you will come to know all the details.

Step 5:
Once successfully completed your pre-request then continue the patch installation on cluster.

Step 6:
Now you need to execute below script for all nodes (Using root user)
/u01/oracle/product/10.2.0/db_1/root.sh

[root@linux1 bin]# /u01/oracle/product/10.2.0/db_1/root.sh

Running Oracle10 root.sh script...

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]: y

Copying dbhome to /usr/local/bin ...

The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]: y

Copying oraenv to /usr/local/bin ...

The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]: y

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.sh script.

Now product-specific root actions will be performed.


[root@linux2 bin]# /u01/oracle/product/10.2.0/db_1/root.sh

Running Oracle10 root.sh script...

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]: y

Copying dbhome to /usr/local/bin ...

The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]: y

Copying oraenv to /usr/local/bin ...

The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]: y

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.sh script.

Now product-specific root actions will be performed.

[oracle@linux1 bin]$ . oraenv

ORACLE_SID = [devdb1] ? devdb

[oracle@linux1 bin]$ export ORACLE_SID=devdb1

[oracle@linux1 bin]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 25 03:38:20 2009

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

Connected to an idle instance.


SQL> startup nomount

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1267044 bytes

Variable Size 100666012 bytes

Database Buffers 176160768 bytes

Redo Buffers 7118848 bytes

SQL>


SQL> show parameter clus

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cluster_database boolean TRUE

cluster_database_instances integer 2

cluster_interconnects string


SQL> alter system set cluster_database=FALSE scope=spfile;

System altered.


SQL> shutdown

ORA-01507: database not mounted

ORACLE instance shut down.


SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options


[oracle@linux1 bin]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 25 03:40:20 2009

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

Connected to an idle instance.


SQL> startup upgrade

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1267044 bytes

Variable Size 100666012 bytes

Database Buffers 176160768 bytes

Redo Buffers 7118848 bytes

Database mounted.

Database opened.

SQL>


SQL> show parameter cluster

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cluster_database boolean FALSE

cluster_database_instances integer 1

cluster_interconnects string


SQL> spool /u01/patch.log


SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

...

..

......

No errors.

No errors.

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP RUL 2009-07-25 11:54:47

DBUA_TIMESTAMP RUL VALID 2009-07-25 11:54:47

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UPGRD_END 2009-07-25 11:54:50

.

Oracle Database 10.2 Upgrade Status Utility 07-25-2009 11:54:50

.

Component Status Version HH:MM:SS

Oracle Database Server VALID 10.2.0.4.0 01:23:50

JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:15:05

Oracle XDK VALID 10.2.0.4.0 00:02:21

Oracle Database Java Packages VALID 10.2.0.4.0 00:02:39

Oracle Text VALID 10.2.0.4.0 00:01:48

Oracle XML Database VALID 10.2.0.4.0 00:11:57

Oracle Real Application Clusters VALID 10.2.0.4.0 00:00:22

Oracle Workspace Manager VALID 10.2.0.4.3 00:04:23

Oracle Data Mining VALID 10.2.0.4.0 00:03:23

OLAP Analytic Workspace VALID 10.2.0.4.0 00:03:25

OLAP Catalog VALID 10.2.0.4.0 00:08:07

Oracle OLAP API VALID 10.2.0.4.0 00:06:20

Oracle interMedia VALID 10.2.0.4.0 00:31:09

Spatial VALID 10.2.0.4.0 00:13:33

Oracle Expression Filter VALID 10.2.0.4.0 00:02:04

Oracle Enterprise Manager VALID 10.2.0.4.0 00:12:57

Oracle Rule Manager VALID 10.2.0.4.0 00:01:11

.

Total Upgrade Time: 03:24:44

DOC>#######################################################################

DOC>#######################################################################

DOC>

DOC> The above PL/SQL lists the SERVER components in the upgraded

DOC> database, along with their current version and status.

DOC>

DOC> Please review the status and version columns and look for

DOC> any errors in the spool log file. If there are errors in the spool

DOC> file, or any components are not VALID or not the current version,

DOC> consult the Oracle Database Upgrade Guide for troubleshooting

DOC> recommendations.

DOC>

DOC> Next shutdown immediate, restart for normal operation, and then

DOC> run utlrp.sql to recompile any invalid application objects.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#


SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>


SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1267044 bytes

Variable Size 146803356 bytes

Database Buffers 130023424 bytes

Redo Buffers 7118848 bytes

Database mounted.

Database opened.


SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2009-07-25 12:12:32

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

.

...

.......

ERRORS DURING RECOMPILATION

---------------------------

0

PL/SQL procedure successfully completed.


SQL> show parameter cluster

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cluster_database boolean FALSE

cluster_database_instances integer 1

cluster_interconnects string

SQL>


SQL> alter system set cluster_database=TRUE scope=spfile;

System altered.


SQL> alter system set cluster_database=TRUE scope=spfile;

System altered.


SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>


SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1267044 bytes

Variable Size 163580572 bytes

Database Buffers 113246208 bytes

Redo Buffers 7118848 bytes

Database mounted.

Database opened.


SQL>


SQL> show parameter cluster

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cluster_database boolean TRUE

cluster_database_instances integer 2

cluster_interconnects string


[oracle@linux1 bin]$ ./srvctl start instance -d devdb -i devdb1


[oracle@linux1 bin]$ ./crs_stat -t

Name Type Target State Host

------------------------------------------------------------

ora.devdb.db application ONLINE ONLINE linux2

ora....b1.inst application ONLINE ONLINE linux1

ora....b2.inst application ONLINE OFFLINE

ora...._taf.cs application OFFLINE OFFLINE

ora....db1.srv application OFFLINE OFFLINE

ora....db2.srv application OFFLINE OFFLINE

ora....SM1.asm application ONLINE ONLINE linux1

ora....X1.lsnr application ONLINE ONLINE linux1

ora.linux1.gsd application ONLINE ONLINE linux1

ora.linux1.ons application ONLINE ONLINE linux1

ora.linux1.vip application ONLINE ONLINE linux1

ora....SM2.asm application ONLINE ONLINE linux2

ora....X2.lsnr application ONLINE ONLINE linux2

ora.linux2.gsd application ONLINE ONLINE linux2

ora.linux2.ons application ONLINE ONLINE linux2

ora.linux2.vip application ONLINE ONLINE linux2


[oracle@linux2 bin]$ ./srvctl start instance -d devdb -i devdb2


[oracle@linux1 bin]$ ./crs_stat -t

Name Type Target State Host

------------------------------------------------------------

ora.devdb.db application ONLINE ONLINE linux2

ora....b1.inst application ONLINE ONLINE linux1

ora....b2.inst application ONLINE ONLINE linux2

ora...._taf.cs application OFFLINE OFFLINE

ora....db1.srv application OFFLINE OFFLINE

ora....db2.srv application OFFLINE OFFLINE

ora....SM1.asm application ONLINE ONLINE linux1

ora....X1.lsnr application ONLINE ONLINE linux1

ora.linux1.gsd application ONLINE ONLINE linux1

ora.linux1.ons application ONLINE ONLINE linux1

ora.linux1.vip application ONLINE ONLINE linux1

ora....SM2.asm application ONLINE ONLINE linux2

ora....X2.lsnr application ONLINE ONLINE linux2

ora.linux2.gsd application ONLINE ONLINE linux2

ora.linux2.ons application ONLINE ONLINE linux2

ora.linux2.vip application ONLINE ONLINE linux2


[oracle@linux2 bin]$ . oraenv

ORACLE_SID = [devdb] ?


[oracle@linux2 bin]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 25 12:45:02 2009

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

Connected to an idle instance.


SQL> conn sys@devdb1 as sysdba

Enter password:

Connected.

SQL>


SQL> select * from v$version where rownum=1;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod


SQL> conn sys@devdb2 as sysdba

Enter password:

Connected.


SQL> select * from v$version where rownum=1;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

Real Application Cluster database upgrade from 10.2.0.1 to 10.2.0.4

Real Application Cluster database upgrade from 10.2.0.1 to 10.2.0.4

Database Version: 10.2.0.1
Operating System: Ret Hat linux 4 As & Vmware 5
Storage Type : ASM & RAW Storage
Pre-Request for upgrade:

Before upgrading any database we need take complete cold backup for database. Also we need to take backup of ORACLE_HOME, ORACLE_BASE & ORACLE_CRS_HOME

* In my case I have taken ORACLE_HOME & ORACLE_CRS_HOME backup’s using (for all nodes)

cd /u01/before_patch_backup

tar -cvf oracle_base_jul19.tar $ORACLE_BASE

tar -cvf oracle_home_jul19.tar $ORACLE_HOME

tar -cvf oracle_crs_home_jul19.tar $ORACLE_CRS_HOME

* Form storage level we need take backup’s. I used “dd” command for taking OCR & VOTING disk backups.
* Also using I have taken complete RMAN backup for my database & using “dd” command I taken RAW devices backups (I mean ASM)

PS: Make sure; before taking any backups you need to sotp your cluster don’t forgot. Using the below command you down your cluster.

[root@linux1 bin]# ./crsctl stop crs

Stopping resources.

Successfully stopped CRS resources

Stopping CSSD.

Shutting down CSS daemon.

Shutdown request successfully issued.


[root@linux1 bin]# ps -ef | grep pmon

root 27831 27206 0 08:26 pts/2 00:00:00 grep pmon

[root@linux1 bin]# ps -ef | grep lsn

root 27833 27206 0 08:26 pts/2 00:00:00 grep lsn


[root@linux2 bin]# ./crsctl stop crs

Stopping resources.

Successfully stopped CRS resources

Stopping CSSD.

Shutting down CSS daemon.

Shutdown request successfully issued.


[oracle@linux2 /]$ ps -ef | grep pmon

oracle 20613 19349 0 05:54 pts/1 00:00:00 grep pmon

[oracle@linux2 /]$

[oracle@linux2 /]$ ps -ef | grep lsn

oracle 20638 19349 0 05:54 pts/1 00:00:00 grep lsn

PS: In my test environment I don’t have separte ASM home; I’m using ORACLE_HOME itslef. If you have ASM home then you may need take backup of ASM home also.

Cluster Upgration: Now I’m going to upgrade my cluster database.

Step 1:
1. Down 10.2.0.4 patch set (Patch Nr: 6810189)
2. Extract your patch
3. From oracle user you need to exeucte “runInstaller” then you will get below screen shot.

Step 2:
Please confirm your CRS Home. Because first of all you need to upgrade cluster only (Not Oracle database or ASM Home)

Step 3:
This is TWO Nodes cluster; that’s why you will get your cluster nodes names list.

Step 4:
This is pre-request checking steps; in case any thing wrong here you will come to know all the details.

Step 5:
Once successfully completed your pre-request then continue the patch installation on cluster.

Step 6:
After succesfully installation you may need execute the below script from root user (in all nodes you need follow same steps)

/u01/oracle/product/10.2.0/crs/bin/crsctl stop crs
/u01/oracle/product/10.2.0/crs/install/root102.sh

Form Node 1:

[root@linux1 bin]# ./crsctl stop crs

Stopping resources.

Error while stopping resources. Possible cause: CRSD is down.

Stopping CSSD.

Unable to communicate with the CSS daemon.

[root@linux1 bin]# cd ..

[root@linux1 crs]# cd install

[root@linux1 install]# ./root102.sh

Creating pre-patch directory for saving pre-patch clusterware files

Completed patching clusterware files to /u01/app/oracle/product/10.2.0/crs

Relinking some shared libraries.

Relinking of patched files is complete.

WARNING: directory '/u01/oracle/product/10.2.0' is not owned by root

WARNING: directory '/u01/oracle/product' is not owned by root

WARNING: directory '/u01/oracle' is not owned by root

WARNING: directory '/u01' is not owned by root

Preparing to recopy patched init and RC scripts.

Recopying init and RC scripts.

Startup will be queued to init within 30 seconds.

Starting up the CRS daemons.

Waiting for the patched CRS daemons to start.

This may take a while on some systems.

.

.

.

.

10204 patch successfully applied.

clscfg: EXISTING configuration version 3 detected.

clscfg: version 3 is 10G Release 2.

Successfully accumulated necessary OCR keys.

Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.

node :

node 1: linux1 linux1-priv linux1

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

clscfg -upgrade completed successfully


From Node 2:

[root@linux2 bin]# ./crsctl stop crs

Stopping resources. This could take several minutes.

Successfully stopped CRS resources.

Stopping CSSD.

Shutting down CSS daemon.

Shutdown request successfully issued.

[root@linux2 bin]# cd ../install

[root@linux2 install]# ./root102.sh >> rootexecteation2.txt &

[1] 10282

[root@linux2 install]# tail -f rootexecteation2.txt

WARNING: directory '/u01/oracle/product/10.2.0' is not owned by root

WARNING: directory '/u01/oracle/product' is not owned by root

WARNING: directory '/u01/oracle' is not owned by root

WARNING: directory '/u01' is not owned by root

Preparing to recopy patched init and RC scripts.

Recopying init and RC scripts.

Startup will be queued to init within 30 seconds.

/etc/profile: line 55: ulimit: open files: cannot modify limit: Operation not permitted

/home/oracle/.bash_profile: line 30: ulimit: open files: cannot modify limit: Operation not

permitted

Starting up the CRS daemons.

Waiting for the patched CRS daemons to start.

This may take a while on some systems.

.

.

10204 patch successfully applied.

clscfg: EXISTING configuration version 3 detected.

clscfg: version 3 is 10G Release 2.

Successfully accumulated necessary OCR keys.

Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.

node :

node 2: linux2 linux2-priv linux2

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

clscfg -upgrade completed successfully


Cluster Status

[root@linux2 install]# cd ../bin

[root@linux2 bin]# ./crs_stat -t

Name Type Target State Host

------------------------------------------------------------

ora.devdb.db application ONLINE OFFLINE

ora....b1.inst application OFFLINE OFFLINE

ora....b2.inst application ONLINE OFFLINE

ora...._taf.cs application OFFLINE OFFLINE

ora....db1.srv application OFFLINE OFFLINE

ora....db2.srv application OFFLINE OFFLINE

ora....SM1.asm application OFFLINE OFFLINE

ora....X1.lsnr application OFFLINE OFFLINE

ora.linux1.gsd application ONLINE ONLINE linux1

ora.linux1.ons application ONLINE ONLINE linux1

ora.linux1.vip application ONLINE ONLINE linux1

ora....SM2.asm application ONLINE ONLINE linux2

ora....X2.lsnr application ONLINE ONLINE linux2

ora.linux2.gsd application ONLINE OFFLINE

ora.linux2.ons application ONLINE ONLINE linux2

ora.linux2.vip application ONLINE ONLINE linux2

I am having some problem in Node2 that’s why GSD is down in node2.

ORA-07445: exception encountered: core dump [qmkmfreeUga()+27] [SIGSEGV] [Address

ORA-07445: exception encountered: core dump [qmkmfreeUga()+27] [SIGSEGV] [Address mapped to object] [0x4C] [] []

When upgrading from 10.2.0.1.0 to 10.2.0.4.0, a user may see this error when running catupgrd.sql

Cause: This is unpublished bug 6957077

Action: The database instance needs to be shutdown and restarted, then catupgrd.sql can be run and should complete successfully

SQL> shutdown immediate

SQL> startup upgrade