Oracle Database 10g Release 2 (10.2.0.3) for Microsoft Windows Vista

Dear Folks,

This is to share the information with you that Oracle has released the Oracle 10g R2 (10.2.03) for Windows Vista Operating System.

One can download this from the Oracle official website. Below is an URL for the same.

http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10203vista.html

Best Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

Oracle Database 11g is going to be launched on Wednesday, July 11, 2007.

Dear Friends,

Oracle has announced that “Oracle Database 11g is going to be launched on Wednesday, July 11, 2007”.

To read more about this event, please take a look at an URL given below.

http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=66665

Best Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com

Clone of a database on the same Host i.e. Server (Linux RHEL -4)

Dear friends,

As we all know, Cloning of a Database can be done using different backup methods i.e. using cold backup/hot backup (ALTER TABLESPACE <tablespace_name> BEGING BACKUP), and RMAN Backup with DUPLICATE TARGET DATABASE.

Recently I have performed the cloning for one of our University databases using the RMAN backup and DUPLICATE TARGET DATABASE commands, on the same host i.e. same server.

Important Note: It is recommended NOT to clone the production database on the same server or host. But as a practice you can do it for any non-prod instances.

Below are the software details:

DB Name: PROD
DB Version: Oracle 10g Release 2 (10.2.0.1)
OS Version: Red Hat Enterprise Linux (RHEL) – 4

The steps which I have followed for cloning:

Perform the database backup using RMAN.

Login as ORACLE on Linux

$ su – oracle Password: xxxxxx (Issue the password here)

Check and set the ORACLE_HOME, ORACLE_SID, and other environment variables.

$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0 $ export ORACLE_SID=prod $ echo $ORACLE_HOME

Note: Oracle user has a profile (.bash_profile) in which all required environment variables are set, whenever the oracle user logs in to the server the environment variables are set automatically.

Below are the .bash_profile contents.

$ vi. .bash_profile

# User specific environment and startup programs
PATH=$HOME/bin:/usr/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin
export PATH
ORACLE_HOME=/u01/app/oracle/product/10.2.0
TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
ORACLE_SID=prod
export ORACLE_HOME
export TNS_ADMIN
export LD_LIBRARY_PATH
export ORACLE_SID

:q!

Check if the target database is in Archive log mode or not.

$ sqlplus / as sysdba

SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 59
Next log sequence to archive 61
Current log sequence 61
SQL>

Here in our case the database is already configured for Archive log mode, and the archive log destination is used by Flash Recovery Area.

If your DB is in no Archive log mode then change the mode to Archive log.

Set FRA (Flash Recovery Area) in your init/spfile.ora

DB_RECOVERY_FILE_DEST=/u03/FRA
DB_RECOVERY_FILE_DEST_SIZE=4G

Startup up the database in mount state

$ sqlplus / as sysdba

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVE LOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST;

Connect to RMAN and target instance to perform RMAN backup

$ export ORACLE_SID=prod
$ rman target /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

-: BACKUP DATABASE PLUS ARCHIVELOG:-

This command will take the backup of all database files and archived log files, and place them in flash recovery area (/u03/FRA/PROD/backupsets/__

/, all the backup files contain the extension with .bkp

To check and list the backup taken previously using RMAN’s LIST commands.

RMAN> LIST BACKUP;

Prepare the initialization parameter file (init.ora) for clone database.

Copy the target database init.ora file and rename it corresponding to you clone instance.

$ cd $ORACLE_HOME/dbs

Note: If you do not find the init.ora file for your target instance under the directory $ORACLE_HOME/dbs, may be you are using spfile or the init.ora file might have kept somewhere in another location;

If the spfile is being used, then create the pfile from spfile

SQL> CREATE PFILE FROM SPFILE;

$ cp –p initPROD.ora initTEST.ora

Edit the initialization parameter file (initTEST.ora) and update the following parameters accordingly to your clone instance.

USER_DUMP_DEST=
BACKGROUND_DUMP_DEST=
AUDIT_FILE_DEST=
CORE_DUMP_DEST=
USER_DUMP_DEST=
CONTROL_FILES=
DB_NAME=
INSTANCE_NAME=
SERVICE_NAMES=
DB_FILE_CONVERT_NAME=
LOG_FILE_CONVERT_NAME=

Note: In general, cloned instances are not kept in archive log mode, so you can remove or comment both parameters related to FRA, DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE

Create the password file for clone (TEST) instance.

$ export ORACLE_SID=test

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwtest password=sys

Note: Make sure that the Production (Target) password and Test (Clone) password for orapwd file must be same.

Prepare the listener and tnanemes.ora entries for clone (TEST) instance.

$ cd $ORACLE_HOME/network/admin

Open listener.ora file and add the TEST instance entry as below.

(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
)

Open tnsnames.ora file and add the TEST instance entry as below.

test =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = unicorn.kware) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =test)
)
)

Check the Prod and Test service can be pinged.

$ tnsping prod

$ tnsping test

Start the clone instance (TEST) in no mount state.

$ export ORACLE_SID=test
$ sqlplus / as sysdba

SQL> startup nomount;

SQL> select instance_name from v$instance;

INSTANCE_NAME
-------------------------
test

Connect to RMAN and target & auxiliary instances.

Open a new connection.

$ export ORACLE_SID=prod
$ rman target / auxiliary sys/sys@test

RMAN> RUN
2> {
3> ALLOCATE AUXILIARY CHANNEL AUX1 DEVICE TYPE DISK;
4> DUPLICATE TARGET DATABASE TO 'test';
5> }

Above command output will be a self explanatory, please go through the output generated.

RMAN> exit;

You are now done with clone process.

Checks to be performed after the clone process is finished

Execute the below steps in Test (Clone) instance, and cross check the result with the Production (Target) instance.

$ export ORACLE_SID=test

$ sqlplus / as sysdba

SQL> SELECT OPEN_MODE,NAME FROM V$DATABASE;

SQL> ARCHIVE LOG LIST;

SQL> SELECT NAME FROM V$TABLESPACE;

SQL> SELECT USERNAME FROM DBA_USERS;

SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';


If you find invalid objects then you can compile them using $ORACLE_HOME/rdbms/admin/utlrp.sql

Reference:

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb005.htm

Conclusion:

I have followed the above procedure/steps as per our requirement in the University. I would request you to follow the complete method and other options from referenced link above.

With Best Regards,

Sabdar Syed,
Oracle DBA/ Apps DBA,
Oracle Certified Professional (OCP) 8i, 9i & 10g

My Blog: http://sabdarsyed.blogspot.com

My Metalink Notes!!

Dear Friends,

I have submitted two articles in Oracle Metalink. They have published my articles in Metalink, below are the details.

Oracle Metalink URL:

https://metalink.oracle.com/

Note ID: 427974.1
Subject : "How We Identified and Corrected a Cloning Mistake"

Note ID: 429674.1
Subject : "How We Used the Oracle DBNEWID Utility to Change the Database Name"

I got a call from the Oracle US Metalink that my articles got selected in the first 20 articles and given gift check for the articles submitted.

Happy Reading !!

Regards,
Sabdar Syed.

How to install Oracle Database 10g Release 2 on Linux x86 (RHEL4 & SLES9)

Dear Friends,

I have come across an URL in Oracle Web Site for "Installing Oracle Database 10g Release 2 on Linux x86". Below is an URL.

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

This links talks about the below contents:

Overview
Part I: Installing Linux
RHEL4
SLES9
Part II: Configuring Linux for Oracle
Verify System Requirements
Create Directories
Create the Oracle Groups and User Account
Configure Linux Kernel Parameters
Set Shell Limits for the oracle User
Part III: Installing Oracle
Install the Software
Part IV: Configuring Storage
Filesystems
Automatic Storage Management
Conclusion
Appendix

Happy reading !!

With Best Regards,
Sabdar Syed.

All about the STATSPACK (Statistics Packages) in Oracle 8i, 9i & 10g

Dear Friends,

By default Oracle provides the STATSPACK information in the form of spdoc.txt under

Unix: $ORACLE_HOME/rdbms/admin/spdoc.txt

Windows: %ORACLE_HOME%\rdbms\admin\spdoc.txt

This document talks about the following contents.

TABLE OF CONTENTS
-------------------------------

0. Introduction and Terminology
1. Enterprise Manager (EM), Automatic Workload Repository (AWR) and Statspack
2. Statspack Configuration
2.1. Database Space Requirements
2.2. Installing the Tool
2.3. Errors during Installation
3. Gathering data - taking a snapshot
3.1. Automating Statspack Statistics Gathering
3.2. Using dbms_job
4. Running the Performance reports
4.1. Running the instance report
4.2. Running the instance report when there are multiple instances
4.3. Configuring the Instance Report
4.4. Running the SQL report
4.5. Running the SQL report when there are multiple instances
4.6. Configuring the SQL report
4.7. Gathering optimizer statistics on the PERFSTAT schema
5. Configuring the amount of data captured
5.1. Snapshot Level
5.2. Snapshot SQL thresholds
5.3. Changing the default values for Snapshot Level and SQL Thresholds
5.4. Snapshot Levels - details
5.5. Specifying a Session Id
5.6. Input Parameters for the SNAP and
MODIFY_STATSPACK_PARAMETERS procedures
6. Time Units used for Performance Statistics
7. Event Timings
8. Managing and Sharing performance data
8.1. Baselining performance data
8.1.1. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Snap Ids
8.1.2. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Dates
8.2. Purging/removing unnecessary data
8.2.1. Input Parameters for the PURGE procedure and function
which accept Begin Snap Id and End Snap Id
8.2.2. Input Parameters for the PURGE procedure and function
which accept Begin Date and End Date
8.2.3. Input Parameters for the PURGE procedure and function
which accept a single Purge Before Date
8.2.4. Input Parameters for the PURGE procedure and function
which accept the Number of Days of data to keep
8.2.5. Using sppurge.sql
8.3. Removing all data
8.4. Sharing data via export
9. New and Changed Features
9.1. Changes between 10.1 and 10.2
9.2. Changes between 9.2 and 10.1
9.3. Changes between 9.0 and 9.2
9.4. Changes between 8.1.7 and 9.0
9.5. Changes between 8.1.6 and 8.1.7
10. Compatibility and Upgrading from previous releases
10.1. Compatibility Matrix
10.1.1. Using Statspack shipped with 10.1
10.1.2. Using Statspack shipped with 10.0
10.1.3. Using Statspack shipped with 9.2
10.1.4. Using Statspack shipped with 9.0
10.1.5. Using Statspack shipped with 8.1.7 on 9i releases
10.2. Upgrading an existing Statspack schema to a newer release
10.2.1. Upgrading the Statspack schema from 10.1 to 10.2
10.2.2. Upgrading the Statspack schema from 9.2 to 10.1
10.2.3. Upgrading the Statspack schema from 9.0 to 9.2
10.2.4. Upgrading the Statspack schema from 8.1.7 to 9.0
10.2.5. Upgrading the Statspack schema from 8.1.6 to 8.1.7
10.2.6. Upgrading the Statspack schema from 8.1.6 to 9.2
10.2.7. Upgrading the Statspack schema from 8.1.6 to 9.0
10.2.8. Upgrading the Statspack schema from 8.1.7 to 9.2
11. Oracle Real Application Clusters specific considerations
11.1. Changing Instance Numbers
11.2. Cluster Specific Reports
11.3. Cluster Specific Data
12. Conflicts and differences compared to UTLBSTAT/UTLESTAT
12.1. Running BSTAT/ESTAT in conjunction to Statspack
12.2. Differences between Statspack and BSTAT/ESTAT
13. Removing the package
14. Supplied Scripts Overview
15. Limitations and Modifications
15.1. Limitations
15.2. Modifications


Happy reading!!!!

With Best Regards,
Sabdar Syed.