Invincible DBA!

Purging trace and dump files with 11g ADRCI

2010-09-06T20:48:00.000-07:00

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

2010-09-06T20:46:00.000-07:00

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

2010-09-06T20:44:00.000-07:00

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.oncalldba.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.oncalldba.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.oncalldba.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.oncalldba.com) 11> dblogin userid ggs_owner, password ggs_owner
Successfully logged into database.

GGSCI (linux01.oncalldba.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.oncalldba.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.oncalldba.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.oncalldba.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.oncalldba.com) 1>
GGSCI (linux01.oncalldba.com) 2> START MANAGER

Manager started.

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

EXTRACT EXT3 is already running.

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

EXTRACT DPUMP is already running.

GGSCI (linux01.oncalldba.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

2010-09-06T20:40:00.000-07:00

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

2010-09-06T20:36:00.000-07:00

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

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

2010-09-05T19:38:00.000-07:00

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

2010-09-05T19:25:00.000-07:00

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

2010-09-05T19:19:00.000-07:00

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

Oracle backup replication with Oracle Streams, Data Guard & RAC

2009-09-08T22:15:00.000-07:00

As the world’s leading database, Oracle offers a wealth of options to replicate geographically distributed systems. Each method has its own features and it is the job of the Oracle professional to choose the one that makes sense for their business requirements:

Data Guard – Data Guard standby databases provide standby databases for failover in system that do not have a high downtime cost. In a data guard failover, the last redo log must be applied (up to 20 minutes to re-open the standby database), and if the last redo cannot be recovered from the crashed instance, the most recent updates may be lost.

Multi-Master Replication – See the excellent book for implementing multi-master replication. Uses advanced queuing to cross-pollinate many databases with updates. Has an advantage over Data Guard because the standby database can be used for updates, and a disadvantage that replication is not instantaneous and update collisions may occur.

Oracle Streams – See the comprehensive book on Oracle Streams Technology by Madhu Tumma. Ready for production use in Oracle 10g, Oracle Streams allows near real-time replication and support for master-to-master replication. Oracle Streams has no licensing costs (RAC costs extra) and it is less complex to configure than a RAC database. This disadvantage over RAC is that there is no continuous availability, update collisions may occur, replication is not instant, and application failover is usually done manually.

Real Application Clusters – The Cadillac of Oracle continuous availability tools, RAC allows for Transparent Application Failover (TAF) and RAC is de-rigueur for systems with a high cost of downtime and continuous availability on RAC.

Most shops will combine these technologies to provide replication that is easy to manage and reliable. Let’s explore such a scenario.

Why Replicate?

Oracle has sophisticated mechanisms to manage data concurrency and replication is normally used when we have geographically distributed systems with unreliable network connectivity between systems. In a perfect world, database would not be replicated, but the nature of worldwide networking mandates that global eCommerce system have replicated database to provide fast response time worldwide.

Because the Oracle Streams product is new, Oracle professionals are only now recognizing how Streams can be used to in a master-master replication architecture. Let’s take a closer look.

Oracle Streams for master-master replication

Oracle Streams is an ideal solution for systems that are geographically distributed and have a high-speed connection (e.g. a T1 line) between servers. As long as the server interconnect can keep-up with the changes you can implement systems that provide failover and disaster recovery, simply and reliably. However Oracle Streams replication is not for every database:

High update systems – Busy update database may spend excessive resources synchronizing the distributed databases.

Real-time replication required – If you require a two-phase commit (where the changes are committed on each database at exactly the same time), then RAC or on-commit replication are a better choice. For example, a banking system would require that all databases update each other simultaneously, and Oracle Streams might not be an ideal solution.

On the other had, Oracle Streams is perfect for geographically distributed applications where real-time synchronization is not required. Let’s explore master-master replication with a hypothetical example. Assume that we have a online database serving the US Federal government which is deployed using APEX making it accessible anywhere in the world with an internet-enabled web browser. Federal agencies will connect with either the California or the Texas server, based on their APEX shortcut URL. The two systems are cross-fed updates via Oracle Streams and the Texas server will use Data Guard to replicate to standby database in Kittrell North Carolina (Figure 1).

Figure 1 – Combining Oracle Streams and Oracle data Guard

Oracle Streams will provide near real-time replication of important information and in case of a server outage, updates are automatically stored in update queues and applied automatically when service is restored to the crashed Oracle server.

Providing reliable connectivity during network outages is very challenging and sophisticated, and Oracle has several tools that will aid TCI in their goals. In case of any type of outage we see alternative connection options:

Interconnect is down – All end-users will continue to operate independently and when the interconnect is restored the updates queues will synchronize each database.

California or Texas Server down – The end-user will have three icons on their desktop, and a California end-user simply clicks on the URL for the Texas database and continues working.

Major Disaster – In this case the Kittrell NC server will be synchronized using Data Guard (with a possible loss of the most recent redo log updates) and end-users with access to the internet can continue to work on the standby server. Upon restoration of service, the Kittrell server will flush all updates back to the main California and Texas databases.

Establishing the replication mechanisms is only part of the task. We also need detailed checks to alert the DBA staff when an outage has occurred. The end-user will have it easier, and know to simply log in to the surviving Oracle database. Let’s look at the replication alert mechanisms.

Streams replication failure alerts

This is a critical component of the replication so that Oracle staff can be aware when there has been a server or network failure. The alerts will include checks from the Kittrell, checks between Texas and California, and checks of the replication queues. Because the connectivity checks must be done outside from Oracle, the checks can be done using Linux cron entries.

Ping from standby server - The Kittrell standby server will check every 15 minutes for connectivity to all servers with a “ping” command. If the servers fail to respond a pager alert will be sent to support staff.

California and Texas connectivity checks – Each server will cross-check each other, first checking with tnsping, then ping, and sending appropriate alerts in cases of network failure.

Creating a self-synchronizing replication architecture

Once the Oracle Streams, Data Guard and alerts are in-place, the only challenge for the Oracle DBA is ensuring that the Streams replication queues have enough room to hold updates on the surviving Oracle instance.

For more information on using Oracle Streams and Multimaster Replication, we recommend these books and links:

Streams Replication Links:

Configuring Oracle Streams. http://www.praetoriate.com/t_oraclestreams_171_dbms_flashback_.htm

Oracle Streams Capture and propagate configuration. http://www.praetoriate.com/t_oraclestreams_178_rman_duplicate_.htm

A phased approach to multi-master replication. http://www.dba-oracle.com/art_dbazine_mm_repl.htm

Oracle Data guard broker architecture. http://www.rampant-books.com/art_kumar_dg_broker_architiecture.htm

Somethings about EXPLAIN PLAN

2009-09-07T22:49:00.000-07:00

1. Creation of plan table:
@?/rdbms/admin/utlxplan.sql

2. Explaining an sql statment:

EXPLAIN PLAN FOR
SELECT ..............................statement....

3. View EXPLAIN PLAN out put:

set pagesize 0
set linesize 130
@?/rdbms/admin/utlxpls.sql

The above sql shows plan table output for serial processing.

@?/rdbms/admin/utlxpls.sql

The above sql shows output with parallel execution colums.

Delete Operation Explain Plan

DELETE FROM EPDBA01.t_bo_sec_new
WHERE ROWID IN (SELECT tbo.ROWID
FROM EPDBA01.t_bo_sec_new tbo, EPDBA01.t_plan tp
WHERE tbo.plan_id = tp.plan_id
AND NOT tbo.person_id IN (SELECT person_id
FROM EPDBA01.t_sys_admin)
AND tbo.role_id = 2000
AND tp.plan_access = 'S')
Plan hash value: 4027940639

Step Explaination:

1. This plan step retrieves all rows from table T_PLAN.
2. This plan step retrieves all rows from table T_BO_SEC_NEW.
3. This plan step accepts two sets of rows, each from a different table.
A hash table is built using the rows returned by the first child.
Each row returned by the second child is then used to probe the hash table to find row pairs
which satisfy a condition specified in the query's WHERE clause.
Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables.
It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make
the wrong choice.
4. This plan step retrieves all ROWIDs from the B*-tree index PK_T_SYS_ADMIN by walking the index starting with its smallest key.
5. This plan step accepts multiple sets of rows. Rows from the first set are eliminated using the data found in the second through n sets.
6 This plan step accepts a row set (its only child) and sorts it in order to identify and eliminate duplicates.
7. This plan step represents the execution plan for the subquery defined by the view VW_NSO_1.
8. This plan step retrieves rows from table T_BO_SEC_NEW through ROWID(s) specified in the WHERE clause of the statement.
9. This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.
10. This plan step deletes rows from table T_BO_SEC_NEW which satisfy the WHERE clause of the DELETE statement.
11. This plan step designates this statement as a DELETE statement.

Oracle powers world's largest commercial database

2009-09-07T22:39:00.001-07:00

Oracle has set a new record by powering 100 TERABYTE DATA WAREHOUSE. Their customers run four top 10 largest support system databases and powers nine top 10 UNIX OLTP. Survey determined that the largest transaction processing system on UNIX runs an Oracle Database, containing over 16 TB of data. Oracle is used by three of the 10 largest UNIX DATA WAREHOUSES.

http://www.oracle.com/corporate/press/2005_sep/091305_wintertopten_finalsite.html

The article says it's the first time Linux had its own categories for DSS and OLTP:

"This year's survey marks the first time that the Linux operating system had its own categories for DSS and OLTP. In total, 12 systems (eight DSS and four OLTP systems) running Linux were measured ? all of them running Oracle Database. Two of these Linux data warehouse systems cracked the TopTen All Environments in the database size category. The world's largest Linux data warehouse measures 24.8 TB, good enough for sixth overall in the top 10 largest data warehouse category. Oracle is the only database vendor whose customers posted top results on Linux, UNIX and Windows platforms."

World's largest commercial Linux data warehouse runs Oracle Database

2009-09-07T22:36:00.000-07:00

Oracle is the leading database for decision support and online transaction processing in real-world customer environments, according to the Winter Corporation 2005 Top Ten Program survey. Only Oracle Database produces leading results on Linux, UNIX, and Windows.

The survey, which identifies the world's largest and most heavily used databases, found that the largest commercial data warehouse in the world runs a 100 terabyte Oracle Database. That's more than triple the size of the largest database in the previous TopTen Program survey, which was also powered by Oracle.

The survey's newest category covering Linux DSS and OLTP databases was completely dominated by Oracle customers. Overall, the survey found that Oracle is the only database vendor whose customers posted top results on Linux, UNIX, and Windows platforms.

Other survey findings:

- The world's largest commercial database runs Oracle Database, with 100TB of data.
- The world's largest commercial data warehouse runs Oracle Database.
- The world's largest commercial UNIX data warehouse runs Oracle Database.
- The world's largest commercial Linux data warehouse runs Oracle Database.
- The world's largest scientific database runs Oracle Database.
- Oracle Database powers nine of the world's top 10 UNIX OLTP systems.
- Oracle Database powers 100 percent of all Linux DSS and OLTP measured in the Winter Corporation 2005 TopTen program.
- Oracle customers represent 58 percent of the all validated participants in the Winter Corporation 2005 TopTen program.

The Winter Corporation 2005 TopTen Program surveyed customers from 20 countries on five continents, in 11 industries from government to healthcare to retail to telecommunications, among others.

The Program identifies the world's leading database implementations based on Database Size and Most Rows/Records. As part of the rigorous survey process, respondents must have a validated database size that meets the survey's requirements.

Source: http://www.ameinfo.com/72074.html
Largest Oracle database tops 100 trillion bytes : This articles notes the world's largest data warehouses are running Oracle with Linux

Database Failover Steps (Oracle Dataguard / Physical Standby Database)

2009-09-07T22:20:00.000-07:00

1. Sync last archive logfile.

2. Activate standby database by using the following statements:

* Recover managed standby database cancel;
* alter database activate standby database;
* shutdown immediate;
* startup;

3. Hot backup immediately.

4. Build new standby database

Installing the XML & JAVA Components on Oracle Database

2009-09-07T21:10:00.000-07:00

Shutdown the instance and then create and run the following sql script from a new sqlplus session:

-- Start of File full_rmjvm.sql
spool full_rmjvm.log
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter system enable restricted session;
alter database open;
@?/rdbms/admin/catnoexf.sql
@?/rdbms/admin/catnojav.sql
@?/xdk/admin/rmxml.sql
@?/javavm/install/rmjvm.sql
truncate table java$jvm$status;
select * from obj$ where obj#=0 and type#=0;
delete from obj$ where obj#=0 and type#=0;
commit;
select owner, count(*) from all_objects
where object_type like '%JAVA%' group by owner;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
select o1.name from obj$ o1,obj$ o2
where o1.type#=5 and o1.owner#=1 and o1.name=o2.name and o2.type#=29;
shutdown immediate
set echo off
spool off
exit
-- End of File full_rmjvm.sql

OR

- Start of File full_jvminst.sql
spool full_jvminst.log;
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
@?/javavm/install/initjvm.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/initxml.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/xmlja.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catjava.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catexf.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
shutdown immediate
set echo off
spool off
exit
-- End of File full_jvminst.sql

You can also get the same tips from original Oracle metalink notes:
Subject: How to Reload the JVM in 10.1.0.X and 10.2.0.X
Doc ID: Note:276554.1

How to deinstall and install Oracle XML database (XMLDB/XDB)?

2009-09-07T20:59:00.000-07:00

If you are on database release 10.1.x or 10.2.x the XDB Feature is Mandatory in order to use any of the member functions of the XMLTYPE. This is true even if you are not using the repository, or registered schema aspects of the XDB feature. Before we begin to install it, let's check the steps of how to remove it manually.

====> Removal of XML DB

1) Shutdown and restart the database

2) Connect as sysdba and run catnoqm.sql script.
connect / as sysdba
@?/rdbms/admin/catnoqm.sql
drop trigger sys.xdb_installation_trigger;
drop trigger sys.dropped_xdb_instll_trigger;
drop table dropped_xdb_instll_tab;

3) Modify parameter values in init.ora or spfile.
shared_pool_size =150 MB # Or larger value
java_pool_size =150 MB # Or larger value

====> Installation of XML DB
Create XDB tablespace as XMLDB repository storage, make sure it has 150MB free space. Restart the database to make the parameters take effect.

Now we are ready to install a new XDB:

1) Connect as sysdba and run catqm.sql script.
set echo on
spool xdb_install.log
@?/rdbms/admin/catqm.sql xdb_user_pass xdb_tbs temp_tbs

2) If you are using Oracle 9.2, reconnect as SYSDBA and run catxdbj.sql script. Oracle 10g also has this script, but have nothing to do.
@?/rdbms/admin/catxdbj.sql

3) Change database system parameters in init.ora or spfile.

a) Non-RAC
dispatchers="(PROTOCOL=TCP)(SERVICE=XDB)"

b) RAC
inst1.dispatchers="(PROTOCOL=TCP)(SERVICE=XDB)"
inst2.dispatchers="(PROTOCOL=TCP)(SERVICE=XDB)"

4) Make sure there is no invalid objects in XDB schema, and check XMLDB status in DBA_REGISTRY.

select count(*) from dba_objects
where owner='XDB' and status='INVALID';

select comp_name, status, version from DBA_REGISTRY
where comp_name= 'Oracle XML Database';

5) Bounce the database to enable the XMLDB protocol.

Manual Installation of XML DB

2009-09-07T20:53:00.001-07:00

To manually install Oracle XML DB without using DBCA, perform the following steps:

After the database installation, connect as SYS and create a new tablespace for the Oracle XML DB repository. Run the catqm.sql script in the ORACLE_HOME/rdbms/admin directory to create the tables and views needed for XML DB:

catqm.sql XDB_password XDB_Tablespace_Name TEMP_Tablespace

For Example:
XDB_password = 123456
XDB_Tablespace_Name = XDBTS
TEMP_Tablespace = TEMP

So now the syntax will look something like this:

SQL> catqm.sql 123456 XDBTS TEMP

How can one improve Import/ Export performance?

2009-03-18T04:57:00.000-07:00

EXPORT:

1) Set the BUFFER parameter to a high value (e.g. 2Mb -- entered as an integer "2000000")
2) Set the RECORDLENGTH parameter to a high value (e.g. 64Kb -- entered as an integer "64000")
3) Use DIRECT=yes (direct mode export)
4) Stop unnecessary applications to free-up resources for your job.
5) If you run multiple export sessions, ensure they write to different physical disks.
6) DO NOT export to an NFS mounted filesystem. It will take forever.

IMPORT:

1) Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
2) Place the file to be imported on a separate physical disk from the oracle data files
3) Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
4) Set the LOG_BUFFER to a big value and restart oracle.
5) Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
6) Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
7) Use COMMIT=N in the import parameter file if you can afford it
8) Use STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics
9) Remember to run the indexfile previously created

Common Import/ Export Problems

2009-03-18T04:51:00.000-07:00

ORA-00001: Unique constraint (...) violated
You are importing duplicate rows. Use IGNORE=YES to skip tables that already exist (imp will give an error if the object is re-created).

ORA-01555: Snapshot too old
Ask your users to STOP working while you are exporting or try using parameter CONSISTENT=NO

ORA-01562: Failed to extend rollback segment
Create bigger rollback segments or set parameter COMMIT=Y while importing

IMP-00015: Statement failed ... object already exists...
Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

Enabling ARCHIVELOG Mode

2009-03-11T00:48:00.000-07:00

This is a good review of enabling archive log - an article coming from cuddletech(http://www.cuddletech.com/articles/oracle/node58.html)

Most of the High Availability features of Oracle require you to enable ARCHIVELOG mode for your database. When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system (NetBackup or whatever). Archive logs are utilized by RMAN, Data Guard, Flashback and many others.

If your going to enable archivelog mode on a real database thats important to you, I would recommend shutting down the database and doing a cold backup just in case. Keeping a “final noarchivelog mode backup” seems to be a good and excepted practice.

Enabling archive mode is simple, just connect to your database in mounted but closed mode (startup mount) and alter the database. But if you don’t tune alittle you’ll run into problems down the road, so lets specify some parameters too. Namely, consider LOG_ARCHIVE_DEST.

Lets start by checking the current archive mode.

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG

So we’re in NOARCHIVELOG mode and we need to change. We can use a database alter statement, but that won’t be perminant, so lets just update the pfile directly. The pfile should be in either $ORACLE_BASE/admin/SID/pfile or $ORACLE_HOME/admin/SID/pfile. I’ll add the following lines to the end of the file:

############################
# Archive Log Destinations -benr(10/15/04)
############################
log_archive_dest_1='location=/u02/oradata/cuddle/archive'
log_archive_start=TRUE

Note that we’re not actually required to specify the location of the log destination, but if you don’t it’ll end up in strange places (in my test it went to $ORACLE_HOME/dbs making a mess). You can specify as many as 10 diffrent archive log destinations by using the paramters log_archive_dest_1 through log_archive_dest_10. Remember, if you run out of space in your archive log destination the database will shut down!

Now we can startup the database in mount mode and put it in archivelog mode.

[oracle@vixen pfile]$sqlplus sys/passwd as sysdba;
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 15 16:00:58 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

You can see here that we put the database in ARCHIVELOG mode by using the SQL statement “alter database archivelog”, but Oracle won’t let us do this unless the instance is mounted but not open. To make the change we shutdown the instance, and then startup the instance again but this time with the “mount” option which will mount the instance but not open it. Then we can enable ARCHIVELOG mode and open the database fully with the “alter database open” statement.

There are several system views that can provide us with information reguarding archives, such as:

V$DATABASE - Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.
V$ARCHIVED_LOG - Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST - Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES - Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG - Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG - Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY - Contains log history information such as which logs have been archived and the SCN range for each archived log.

Using these tables we can verify that we are infact in ARCHIVELOG mode:

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG

SQL> select DEST_NAME,STATUS,DESTINATION from V$ARCHIVE_DEST;

Archivelog ORA-16014 log sequence not archived…

2009-03-11T00:37:00.000-07:00

One common mistake in 10g, when we enable archivelog mode, happens when we use the flash recovery area default settings as the repository for our archived redo log’s… The mistake consequences will happen 20-30 days after the database archivelog mode been enabled.
By default Oracle 10g Database Configuration Assistant (DBCA) sets flash recovery area size to 2GB (db_recovery_file_dest_size parameter) and when we enable DB archivelog mode it will use the default db_recovery_file_dest parameter for the destination of our archivelogs unless we set any other directory as the destination for them.

A common small 10g database generates an average 100MB of archivelog files daily… Sometimes even at weekend days when most applications DML is almost zero. Why this happens? The reason is Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM). AWR collects detailed performance-related metrics from the database at regular intervals, known as snapshots. After each snapshot is taken, ADDM is invoked to thoroughly analyze the data and metrics deriving from the difference between snapshots, and then recommend necessary actions. This generates lot’s of DML and consequently lot’s of redo data.

So, after some days, your flash recovery area will reach the default 2GB because we have our default settings database archiving there… When this happens our database will be unable to archive due to flash recovery area went full. This happens even if we have lot’s of disk space!

In our alert log file we’ll see something like this:

ORA-16038: log one sequence 3144 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log string thread string: 'string: '/u01/app/oracle/oradata/mydb/redo01.log'
Sat Oct 11 10:43:56 2008
ARCH: Archival stopped, error occurred. Will continue retrying
Sat Oct 11 10:43:56 2008
ORACLE Instance neo - Archival Error

Oracle saves all the information about what we place in the flash recovery area in the RMAN repository/controlfile. If it determines that there is not sufficient space in the recovery file destination, as set by db_recovery_file_dest_size then it will fail. Just deleting the old backups and archive logs from disk is not sufficient as it’s the rman repository/controlfile that holds the space used information.

How to fix this? If we google we see lot’s of places saying to execute “delete archivelog all” in RMAN. This is the fast easy, but dirty solution, as we don’t want to delete all our precious archivelogs by endanger the ability to do future database recovery. The fix can be done with any of the following solutions:

Solution 1 - Delete unwanted archive log files at OS side, then crosscheck archivelogs to marks the controlfile that the archives have been deleted and deleting expired ones.
1. [oracle@app oracle]$ cd /u01/app/oracle/flash_recovery_area/mydb/
2. [oracle@app oracle]$ rm archivelogs*
3. [oracle@app oracle]$ rman target /
4. connected to target database: MYDB (DBID=1649570311)
5. RMAN> crosscheck archivelog all
6. RMAN> delete expired archivelog all
[oracle@app oracle]$ cd /u01/app/oracle/flash_recovery_area/mydb/
[oracle@app oracle]$ rm archivelogs*
[oracle@app oracle]$ rman target /
connected to target database: MYDB (DBID=1649570311)
RMAN> crosscheck archivelog all
RMAN> delete expired archivelog all

Solution 2 - Connect RMAN to backup and then delete your archivelogs… this is a much better solution.
1. [oracle@app oracle]$ rman target /
2. connected to target database: MYDB (DBID=1649570311)
3. RMAN> backup archivelog until logseq delete all input;
4. or
5. RMAN> backup archivelog until time 'sysdate-15' delete all input;
[oracle@app oracle]$ rman target /
connected to target database: MYDB (DBID=1649570311)
RMAN> backup archivelog until logseq delete all input;
or
RMAN> backup archivelog until time 'sysdate-15' delete all input;

Solution 3 - increase dynamically (without shutdown/startup database) the parameter db_recovery_file_dest_size.
1. [oracle@app oracle]$ sqlplus "/ as sysdba"
2. SQL> alter system set db_recovery_file_dest_size=4G
[oracle@app oracle]$ sqlplus "/ as sysdba"
SQL> alter system set db_recovery_file_dest_size=4G

Then, if needed (usually with solution 1 and 2), just shutdown and startup your database and you’ll get you database again up. We may have to shutdown abort if the database does not shutdowns normally.

To avoid the same problem in future when the archivelog size reachs the db_recovery_file_dest_size we should set the following parameters to set an alternate place to archive the redo logs.

log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2'
log_archive_dest_2='LOCATION=/other_destination_for_archiving'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area/mydb/'
db_recovery_file_dest_size=2G

Setup Oracle Database Archivelog mode

2009-03-10T23:23:00.000-07:00

A short musical video tutorial showing how easily you can configure Oracle Database to use archivelog mode. Using archivelog mode is highly recommended in any production database and allows RMAN hot backups (online backups) . You can also use RMAN without archivelog, but you’ll have to shutdown the database to backup it (cold backup).

Let’s system of a down :) Beware… highly advisable to TURN OFF sound if you don’t like hard rock :P

It can be done easily with the following statements:

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

If your database version is prior 10g then after shutdown and before open database you must set log_archive_start = TRUE in your init.ora and/or spfile.ora. After that, login as "sys" and you can check it out with:

SQL> archive log list;
SQL> select log_mode from v$database;

Note: In this case, we need to do a quick restart of the database to enable it to "Archive Log Mode". So please ensure your the required outage window.

Creating a Database in Oracle (Complete Steps & Procedures)

2008-11-02T00:03:00.000-07:00

Creating a Database in Oracle (Complete Steps & Procedures)

Every wondered how the Oracle database is structured? To learn more about the structure and how one would create an Oracle database using the latest features read this article. When creating a database, the main concept is to know how the database is structured in Oracle. You should be aware that ever since the release of Oracle 8i, the Oracle database support object orientated structures. This means that Oracle supports abstract data types and methods.

If you are new to Oracle, then perhaps you are unclear on how data is stored and what files are included internally and externally. With this article you will be clear about how to create a database in Oracle.

How Data is Stored:

Data in Oracle is stored in tables and accessed data via a relational model. This means that one may use the tables of data items inside the database in a manner such that the tables relate to one another with the use of primary and foreign keys.

Tables that contain a foreign key are often translated as being a lookup table. Each table should have a primary key, so that each row can be uniquely identified. The primary key is used to connect with the foreign key in another table to form a relationship.

Oracle also supports object-orientated structures. This allows the database to include abstract data types and methods. Due to this object orientation property, objects may related to other objects and that object may exist inside other objects.

Files are used to store data in Oracle. Oracle 9i removes the risk of having orphan file. That is, like the name suggests the file doesn’t have a parent, which means that the datafile was not removed when it’s tablespace is removed. To prevent this from ever happening to your database again, Oracle has introduced “Oracle Managed Files”.

Oracle Managed files, abbreviated as OMF, uses a brand new system of storing files. OMF uses file system directories as opposed to filenames for files that are associated with the tablespace.

This means that when a tablespace is created, the files are given a unique system-generated name, using the locations that have been assigned to them along with two new initialization parameters. So when the tablespace is removed, the files will be removed with it.

Another new feature in Oracle 9i is the System Managed Undo, abbreviated as SMO. The SMO was created to make the creation of the database simpler for the DBA and to reduce the problem relating to misused rollback segments. The DBA now creates a tablespace as an UNDO type to allow the Oracle database to dynamically and automatically manage the number of undoes, along with the size of the undo segments within the tablespace.

A database is partitioned using tablespaces. The tablespace named SYSTEM is the databases default tablespace. These tablespaces allows for the grouping of elements within the system that transact with the database.

A tablespace contains files. These files are called datafiles, which are used to physically store data for the database. Notice this property suggests that datafiles are internal and external structure types, since they are parented by a tablespace (internal property) and are physical storage files (external). The datafile may be resized after it has been created.

Rules about Tablespaces and Datafiles:

A tablespace is parented by the database. So a tablespace cannot be a part of another database.
A datafile is parented by the tablespace. So a datafile cannot be part of another database.
A datafile may not be removed from a tablespace.

Now that we know how data is stored using Oracle, there are other special physical files that are associated with data storage and will assist in data recovery. The files that are created when the database in created and they are as follows:

Redo log files, which will monitor transactions in a chronological manner. These files are known as online redo log files. The reason why this is an important file to have associated with the database is evident when the database malfunctions.

According to the Oracle 9i DBA Handbook, a database should consist of 3 or more online redo files. To archive old online redo files before they are inevitably over written, you may set the database to run in ARCHIVELOG mode. This allows the DBA to look further back into the history of transactions with the database.

Control files, which holds the location of all the files the database will require. Multiple copies are made and stored on different disks to back up this operation-critical file. The CONTROL_FILES parameter specifies the names of the database control files. An instance must be shut down prior to an addition of a new control file.

Temp files, which acts as a temporary storage for a result set. If the result exceeds the memory available in the RAM’s buffer, then the result set will be stored into the temporary data file. Make sure that the size of the file is large enough to hold large sorting operations.

The other files associated with an Oracle 9i database are called parameter files. There are two types of parameter files. These are. Static parameter files – This is what the init.ora file is. This are common known as a PFILE and should be based on the init.ora file and renamed as init{SID}.ora, where SID is the system identifier. Server parameter files – This is a new feature in Oracle 9i named the SPFILE. These parameter files are written in binary and should not be altered manually. The files should be named as spfile{SID}.ora, where SID is again the system identifier. The Oracle server always maintains these files.To create the SPFILE we do the following,

CREATE SPFILE = ‘\spfile{SID}.ora’ FROM PFILE = ‘init{SID}.ora’

There are two types of parameter that are located in these parameter files. These are, Implicit parameter, which are parameters with no value. Oracle therefore assumes that the value for the parameter is the Oracle default values. Explicit parameter, which are parameter that have a value assigned to it.Now we know how the parameter files work. Well, to access data inside the database, Oracle uses a set of background processes that are shared between users.

Oracle states that an instance is “ a set of memory structures and background processes that access a set of database files.” Every instance has a large memory structure known as the System Global Area, abbreviated as SGA. The SGA will be explained in another article.

But where do the parameter fit in?

Well the parameters are used to set the size and composition of an instance and are the stored in one of the two specific parameter files as stated previously. The parameter file is read during the startup process. So, to access a database one must start an instance, which may call either the PFILE or the SPFILE. If there exist an SPFILE, then the STARTUP command will read that parameter file. If the SPFILE doesn’t exist, the default SPFILE is read. If that doesn’t exist than the PFILE is read. If you don’t wish for the server to read the PFILE, then simply override the default SPFILE with the PFILE.

STARTUP PFILE = ‘.init{SID}.ora’

Now you have a good understanding of architecture of an Oracle database, let us create one.

Creating a Database in Oracle - Procedure & Steps:

There are two ways that you can create a database in Oracle 10g. One way is to use the Oracle Database Configuration Assistant. Using the Oracle Database Configuration Assistant makes sure that the new database is optimized with Oracle 10g’s latest features. Simply read the instruction and follow the prompts. The other way to create a database is to do it manually. This will now be demonstrated.

Create a suitable directory structure for the new database. Once this is done, copy the init.ora file into a new file named init{SID}.ora into new directory.

Now you have created a parameter file, you’ll need to declare an Oracle SID name. Keep the SID consistent with the parameter filename. The command to do this action will depend on the platform you have the DBMS installed. If you are using Windows, simply type,

SET ORACLE_SID = {SID}

Alternatively, on the a unix platform, the DBA must type,

Export ORACLE_SID = {SID}

Sample Database Creation Script:

CREATE DATABASE
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/data_file_location/system01.dbf' SIZE 600M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/data_file_location/sysaux01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/temp_file_location/temp01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS" DATAFILE '/data_file_location/undotbs01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET UTF8
LOGFILE GROUP 1 ('/redolog_file_location/redog1m1.log', '/redolog_file_location/redog1m2.log') SIZE 150M,
GROUP 2 ('/redolog_file_location/redog2m1.log', '/redolog_file_location/redog2m2.log') SIZE 150M,
GROUP 3 ('/redolog_file_location/redog3m1.log', '/redolog_file_location/redog3m2.log') SIZE 150M;

Now we are about to create the database. When creating a new database, write the code into an sql file so that your have a copy of the database features as well as the ability to fix any mistakes in the create database command.

For example if you want to create a database by name "CHARMER" then the create database script will look something like this. The datafile, tempfile, logfile directory location will differ as per the mount point structure on the server/machine. Below given is for the one for the unix platform... Here is the code:

CREATE DATABASE CHARMER
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/u02/oradata/data/system01.dbf' SIZE 600M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u02/oradata/data/sysaux01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/temp/data/temp01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS" DATAFILE '/u02/oradata/data/undotbs01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET UTF8
LOGFILE GROUP 1 ('/u03/oradata/log/redog1m1.log', '/u03/oradata/log/redog1m2.log') SIZE 150M,
GROUP 2 ('/u03/oradata/log/redog2m1.log', '/u03/oradata/log/redog2m2.log') SIZE 150M,
GROUP 3 ('/u03/oradata/log/redog3m1.log', '/u03/oradata/log/redog3m2.log') SIZE 150M;

Save the above script something like cr8db.sql & then log in the database from sqlplus while connected to 'SYS AS SYSDBA' then start up the database in nomount state.

SQL> STARTUP NOMOUNT

So, if we wish to override the default spfile to start the new instance in nomount mode then type,

SQL> STARTUP NOMOUNT PFILE = ‘\pfile_location\init{SID}.ora’

SQL> @ cr8db.sql
Database created.

Oracle, should respond by saying that the database is created. Once this is done you are ready to load scripts that are needed to support your Oracle products by typing the following commands

@$ORACLE_HOME\rdbms\admin\catalog.sql

@$ORACLE_HOME\rdbms\admin\catproc.sql

@$ORACLE_HOME\sqlplus\admin\pubbld.sql

Okie now the database is configured. Change the SYSTEM and SYS passwords, to ensure better security for your database.

How does one rename a database? (For Starter DBA's / Juniors)

2008-10-31T02:52:00.000-07:00

How does one rename a database? (For Starter DBA's / Juniors)

Follow these steps to rename a database:

1. Start by making a full database backup of your database (in case you need to restore if this procedure is not working).

2. Execute this command from sqlplus while connected to 'SYS AS SYSDBA':
ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

3. Locate the latest dump file in your USER_DUMP_DEST directory (show parameter USER_DUMP_DEST) - rename it to something like dbrename.sql

4. Edit dbrename.sql, remove all headers and comments, and change the database's name. Also change "CREATE CONTROLFILE REUSE ..." to "CREATE CONTROLFILE SET ...".

5. Shutdown the database (use SHUTDOWN NORMAL or IMMEDIATE, don't ABORT!) and run dbrename.sql

6. Rename the database's global name:
ALTER DATABASE RENAME GLOBAL_NAME TO new_db_name;

How does one create a new database? (For Starter DBA's / Juniors)

2008-10-31T02:46:00.000-07:00

How does one create a new database? (For Starter DBA's / Juniors)

The purpose of this post to educate Starter DBA's / Junior DBA's as to how easy it is to create a database :)

One can create and modify Oracle databases using the Oracle "dbca" (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software to create the starter database.

One can also create databases manually using scripts. This option, however, is falling out of fashion as it is quite involved and error prone. Look at this example for creating and Oracle 9i or higer database:

CONNECT SYS AS SYSDBA

ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/oradata/';

ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata/';

ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/';

CREATE DATABASE;

Installing Oracle Database 10g Release 2 on Linux

2008-10-31T02:43:00.000-07:00

Installing Oracle Database 10g Release 2 on Linux

The procedure & steps for "Installing Oracle Database 10g Release 2 on Linux x86" can be found here on this link:

http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html

Build Your Own Oracle RAC 10g Release 2 Cluster on Linux and FireWire

2008-10-31T02:41:00.000-07:00

Build Your Own Oracle RAC 10g Release 2 Cluster on Linux and FireWire

One of the most efficient ways to become familiar with Oracle Real Application Clusters (RAC) 10g technology is to have access to an actual Oracle RAC 10g cluster. There's no better way to understand its benefits - including fault tolerance, security, load balancing, and scalability — than to experience them directly. To know more abt this just go thru this link:

http://www.oracle.com/technology/pub/articles/hunter_rac10gr2.html

Oracle Database 10g: The Top 20 Features for DBAs

2008-10-31T02:37:00.000-07:00

Oracle Database 10g: The Top 20 Features for DBAs

The can be found at the below given link:

http://www.oracle.com/technology/pub/articles/10gdba/index.html