Monday, October 19, 2009

Upgrade Oracle database from 10.2.0.x to 10.2.0.4

Follow the simple steps to upgrade your database from 10.2.0.x to 10.2.0.4

Here are the Upgrade paths:-
Upgrade 10.2.0.1. to 10.2.0.4
Upgrade 10.2.0.2. to 10.2.0.4
Upgrade 10.2.0.3. to 10.2.0.4

If you have updated your Oracle version then you must upgrade your database using any one of the below methods, otherwise you will get the following error during Database startup.

ORA-01092: ORACLE instance terminated.
ORA-39700: database must be opened with UPGRADE option

1. Database Upgrade assistant
2. Database Upgrade assistant in Noninteractive mode
3. Manual Upgrade Method

1. Upgrade Database using Database Upgrade assistant:
Step 1: Set the ORACLE_HOME and ORACLE_SID and start DBUA
$ ORACLE_SID=testdb; export ORACLE_SID
$ ORACLE_HOME=/oracle/v10204; export ORACLE_HOME
$ dbua

Step 2: Follow the upgrade steps
(i) Select the database that you want to upgrade
(ii) On the Recompile Invalid Objects screen, select the Recompile the invalid objects then click next.
(iii) On the backup screen, select “I would like to take this tool to backup the database”, if you have not taken database backup.
(iv) Click finish

2. Upgrade Database using Database Upgrade assistant in Noninteractive mode:
Run the following command to upgrade Oracle Database in silent mode:
$ dbua -silent -dbname $ORACLE_SID -oracleHome $ORACLE_HOME
-sysDBAUserName UserName -sysDBAPassword SYS_password
-recompile_invalid_objects true

3. Upgrade Database using Manual Upgrade Method:

Step 1: Backup the database.
Backup the databases using RMAN or command line file level backup

Step 2: Set the ORACLE_HOME and ORACLE_SID and run Pre-upgrade checks
$ ORACLE_SID=testdb; export ORACLE_SID
$ ORACLE_HOME=/oracle/v10204; export ORACLE_HOME

SQL> SPOOL Pre_checks.log
SQL> @?/rdbms/admin/utlu102i.sql
SQL> SPOOL OFF

Review the spool file and make the necessary database/parameter changes based on the recommendation.

If you are upgrading a RAC database, then set CLUSTER_DATABASE to “FALSE”.

Step 3: Start the database in UPGRADE mode and run catupgrd.sql
SQL> STARTUP UPGRADE
SQL> SPOOL upgrade_db.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF

Review the log for errors.

Step 4: Restart database and recompile invalid objects
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> @?/rdbms/admin/utlrp.sql

If RAC database, then set CLUSTER_DATABASE to “TRUE” after upgrade.

Step 5: Check components status after upgrade
Run the following command to check the status of all the components
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

The status of all the components should be VALID for a successful upgrade

Step 6: Upgrade RMAN  Catalog
If you are using the Oracle Recovery Manager catalog, enter the following command:
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;

Step 7: Startup all other processes and services.

Click here for steps to upgrade oracle version to 10.2.0.4

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Oracle version upgrade from 10.2.0.x to 10.2.0.4

Follow the steps to upgrade oracle version from 10.2.0.x to 10.2.0.4.

Step1: Download the patch 6810189 from Metalink and extract the file.
$ unzip p6810189_10204_LINUX-x86-64.zip (ex:- for linux 64 bit)

Step2: - Set the ORACLE_HOME and ORACLE_SID and backup the database
$ ORACLE_SID=testdb; export ORACLE_SID
$ ORACLE_HOME=/oracle/v10201; export ORACLE_HOME

Backup the databases using RMAN or command line file level backup, if any

Step 3: Shutdown database and all other processes/services.
Shutdown listener, if running on same version
$ lnsrctl stop

Stop dbconsole, if any
$ emctl stop dbconsole

Shutdown database
SQL> SHUTDOWN IMMEDIATE

Step 4:- Backup Oracle home and Inventory.
Oracle recommends that you take a backup of Oracle Inventory and Oracle home.

Step 5: Start the OUI and install the 10.2.0.4
For Unix:- $ ./runInstaller
Start Oracle Universal Installer from patch set and select the ORACLE_HOME that you want to patch from the list and click next…next…to complete the installation. Run the $ORACLE_HOME/root.sh script as the root user, when prompted.

For Windows:- Start the OUI by running SETUP.EXE from patch set and select the ORACLE_HOME that you want to patch from the list and click next… next…to complete the installation

Step 6: Startup the listener.
$ lsnrctl start

Click here for steps to Upgrade Oracle Database to 10.2.0.4

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com/

Monday, October 5, 2009

Create, Drop and Alter ASM disk groups

Crete Disk Group:
Create Disk groups using the CREATE DISKGROUP statement and specify the level of redundancy.

Disk group redundancy types:-
NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware RAID or mirroring.

SQL> CREATE DISKGROUP data NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK '/dev/sda1' NAME dataa1,'/dev/sda2' NAME dataa2,
FAILGROUP failure_group_2 DISK '/dev/sdb1' NAME datab1,'/dev/sdb2' NAME datab2;

Drop Disk Group:
Drop disk group using DROP DISKGROUP statement.
SQL> DROP DISKGROUP data INCLUDING CONTENTS;

Alter Disk Group:
Add or remove disks from disk groups Using ALTER DISKGROUP statement. You can also use wildcard "*" to reference disks.

Add a disk.
SQL> ALTER DISKGROUP data ADD DISK '/dev/datac1', '/dev/datac2';

Add all reference disks
SQL> ALTER DISKGROUP data ADD DISK '/dev/datad*;

Drop/remove a disk.
SQL> ALTER DISKGROUP data DROP DISK datab2;

The UNDROP command used to undo only pending drop of disks. After you drop the disks you cannot revert.
SQL> ALTER DISKGROUP data UNDROP DISKS;

Diskgroup Rebalance:
Disk groups can be rebalanced manually Using REBALANCE clause and you can modify the POWER clause default value.
SQL> ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;

MOUNT and DISMOUNT DiskGroups:
Normally Disk groups are mounted at ASM instance startup and dismounted at shutdown. Using MOUNT and DISMOUNT options you can make one or more Disk Groups available or unavailable.
SQL> ALTER DISKGROUP data MOUNT;
SQL> ALTER DISKGROUP data DISMOUNT;
SQL> ALTER DISKGROUP ALL MOUNT;
SQL> ALTER DISKGROUP ALL DISMOUNT;

DiskGroup Check:
Use CHECK ALL to verify the internal consistency of disk group metadata and repair in case of any error.
SQL> ALTER DISKGROUP data CHECK ALL;

DiskGroup resize:
Resize the one or all disks in the Diskgroup.

Resize all disks in a failure group.
SQL> ALTER DISKGROUP data RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 1024G;

Resize a specific disk.
SQL> ALTER DISKGROUP data RESIZE DISK dataa1 SIZE 1024G;

Resize all disks in a disk group.
SQL> ALTER DISKGROUP data RESIZE ALL SIZE 1024G;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Migrate Database to ASM Using RMAN

We are Using RMAN to relocate non-ASM files to ASM files. The ASM files cannot be accessed through normal OS interfaces.

Step1: Query V$CONTROLFILE and V$LOGFILE to get the file names.
SQL> select * from V$CONTROLFILE;
SQL> select * from V$LOGFILE;

Step 2: Shutdown the database.
SQL> SHUTDOWN IMMEDIATE;

Step3: Modify the target database parameter file:
(i) Remove the CONTROL_FILES parameter from the spfile, so the control files will be created automatically in ASM Disk group mentioned in DB_CREATE_FILE_DEST destination
Using a pfile then set CONTROL_FILES parameter to the appropriate ASM files or aliases.
(ii) Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.

Step 4: Startup nomount mode and Restore control file
RMAN> STARTUP NOMOUNT;

Restore the control file into the new location.
RMAN> RESTORE CONTROLFILE FROM 'old_controlfile_name';

Step 5: Startup mount mode and backup the Database.
RMAN> ALTER DATABASE MOUNT;

Backup database into ASM disk group.
RMAN> BACKUP AS COPY DATABASE FORMAT '+diskgroup1';

Step 6: Switch database and create or rename Redo log members
Switch all data files to the new ASM Diskgroup location.
RMAN> SWITCH DATABASE TO COPY;
RMAN> SQL “ALTER DATABASE RENAME ‘old_redolog_member’ to ‘+diskgroup2’;
or
Create new redo logs in ASM Disk group and delete the old redo log files.

Step 7: Open Database and create temporary tablespace.
Open database using resetlogs
SQL> ALTER DATABASE OPEN RESETLOGS;

Create temporary tablespace in ASM disk group.
SQL> CREATE TABLESPACE temp1 ADD TEMPFILE ‘+diskgroup1’;

Step 8: Drop old database files.
1. SQL> DROP TABLESPACE ‘old_temporary_tablespace’ including contents and datafiles;
2. Remove all remaining Non-ASM database files using OS commands

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com/

Thursday, October 1, 2009

Cross Platform Transportable Tablespace using RMAN

When transporting tablespaces between databases where the endian format is different between source and destination platforms, the endian format of the datafiles must be converted to match the destination platform.

This conversion can be performed in two ways

1. Using RMAN CONVERT TABLESPACE command (converting on the source host)
2. Using RMAN CONVERT DATAFILE command(converting on the destination host)

Transportable tablespaces export is manageable across platforms and only meta data will be exported. In Cross platform transportable tablespace the data movement is simpler and faster.

This mode requires that you have the EXP_FULL_DATABASE role.

Please note that the
1. source and target database must use the same character/national character set.
2. You cannot transport a tablespace to a target database which already exists.
3. Transportable tablespace exports cannot be restarted once stopped
4. Target database must at same or higher release level as the source database.

For example, if you want to transport a tablespace “test_user_tbs” from a Linux 64 bit (Little Endian) machine TESTLINUX to Solaris 64 bit (Big Endian) machine TESTSOLARIS. Both the source and target platforms are of different endian type. The data file for the tablespace “test_user_tbs” is “test_user_tbs01.dbf.

Follow the below steps

Step 1: Find the Operating system byte order on Source and Target Database
SQL > select * from v$transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------- ----------------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows 64-bit for AMD Little

Step 2: Make the tablespace “READ ONLY”
SQL> alter tablespace test_user_tbs read only;

Step 3: Export metadata
(i) Using export utility
$ exp testuser/test123 tablespaces=test_user_tbs transport_tablespace=y file=exp_test_user_tbs.dmp log=exp_test_user_tbs.log

(ii) Using Export data pump utility
$ expdp system/password TRANSPORT_TABLESPACES=test_user_tbs TRANSPORT_FULL_CHECK=Y DIRECTORY=export_dir DUMPFILE=expdp_test_user_tbs.dmp logfile= expdp_test_user_tbs.log

TRANSPORT_FULL_CHECK= Y Specifies that check for dependencies between those objects inside the transportable Tablespace and those outside the transportable Tablespace

The file “exp_test_user_tbs.dmp” or ““expdp_test_user_tbs.dmp” contains only metadata.

Step 4: Different endian formats and convert using RMAN
The endian formats are different and then a conversion is necessary for transporting the tablespace.

RMAN Cross platform transportable tablespace.

Run the below command to covert the tablespace to Source (Linux 64 bit) to Target (Solaris 64 bit) platform.

Method 1: Using CONVERT Tablespace... FROM PLATFORM on Source host
RMAN> convert tablespace test_user_tbs
2> to platform ‘Solaris[tm] OE (64-bit)'
3> format='/oradata/rman_backups/%N_%f';

The data file “test_user_tbs01.dbf” is not touched and a new file will be created for Solaris platform under “/oradata/rman_backups“and copy the file to Target platform.

Method 2: Using CONVERT DATAFILE... FROM PLATFORM on Destination host
RMAN> convert datafile test_user_tbs01.dbf
2> from platform ‘Linux IA (64-bit)'
3> db_file_name_convert ‘/linux/oradata/’ ‘/solaris/oradata’

Use RMAN's CONVERT command to convert the datafiles to be transported to the destination host's format. The converted datafiles are stored in “/solaris/oradata”.

Step 5: Initiate import command to plug the tablespace into the database.
(i) Using import utility
$ imp test_user/test123 tablespaces=test_user_tbs transport_tablespace=y file=exp_test_user_tbs.dmp datafiles='test_user_tbs01.dbf' log=imp_test_user_tbs.log

(ii) Using impdp utility
$ impdp test_user/test123 TRANSPORT_DATAFILES='test_user_tbs01.dbf' DIRECTORY=import_dir DUMPFILE=expdp_test_user_tbs.dmp log= impdp_test_user_tbs.log

You can use REMAP_SCHEMA=(source:target), if you want to import into another schema.

Step 6: Put the tablespace in read/write mode.
SQL> ALTER TABLESPACE TEST_USER_TBS READ WRITE;

Now the database has a tablespace named “test_user_tbs” and the objects of the tablespace will be available.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/