AOMUG – All Open Mystical User Group

In order to restore the database until a specific time with your rman catalogue

RUN the following queries in the RMAN CATALOG

sqlplus <catlog user>/<password>@<catlog tns>

alter session set nls_date_format='DD-MM-yyyy hh:mi:ss';
select DB_NAME,STATUS,COMPLETION_TIME from RC_BACKUP_CONTROLFILE where DB_NAME='CRMPROD' and rownum < 11 order by CREATION_TIME;

now from here chose the date the time till when we need to restore the Database.

and place in the restoration script

export ORACLE_HOME={ORACLE HOME}
export ORACLE_SID={ORACLE SID}
$ORACLE_HOME/bin/rman catalog=<catlog user>/<password>@<catlog tns> << EOF
connect target /
run
{
set until time "to_date('16-08-2012 02:56:03','dd-mm-yyyy- hh24:mi:ss')";
allocate channel c1 device type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
restore database ;
release channel c1;
}
EOF

 

Upgrade 10.2.0.1 to 10.2.0.5

I had done this in  VMWARE in OEL REDHAT 5
the process remains same for almost all the other server act just can vary with some minute OS level difference.
download the patch 8202632 for your operating system.
in this case LINUX

p8202632_10205_LINUX.zip
unzip this file
Check that the
Step 1: Prerequisites check before applying the patch set (8202632).

Check your dba_registry view oracle database components status is valid or not, version is 10.2.0.1. Have a look at the below output

SQL> Select comp_name, version, status from sys.dba_registry;

Check your v$version view

SQL> select * from v$version;

Check your database objects are valid or not

SQL> select object_name,status from dba_objects where status='INVALID';
No rows selected

In my environment, here are no invalid objects. If you have any invalid objects then you have to really

Validate your database objects through the below scripts

SQL> exec utl_recomp.recomp_serial (); -> this is for all user’s objects across the database

Then stop all the process like sql, emctl, tnslistener and shutdown your database.

$ sqlplus / as sysdba
SQL> shutdown immediate

$ isqlplusctl stop

$ emctl stop dbconsole
$ lsnrctl stop

Step 2:

Do take Cold Backup of your oracle database 10g and $ORACLE_HOME using Operating system ( This is just in case of any failuir)

Install the patch set 10.2.0.5

$ cd <path of 8202632 path>
$ unzip p8202632_10205_Linux.zip
$ cd Disk1/
$ ./runInstaller

after this please follow the floowing step:

cd $ORACLE_HOME/rdbms/admin
sqlplus "/as sysdba"
SQL>startup upgrade;
SQL>@catupgrd.sql
SQL>shutdown immediate;
SQL>startup;
SQL>@utlrp.sql
SQL> select object_name,status from dba_objects where status='INVALID';

Validate INVALID Objests if any

Congratulation's now you have upgraded the Database from 10.2.0.1 to 10.2.0.5....

export ORACLE_HOME={ORACLE_HOME}
export ORACLE_SID={ORACLE_SID}
$ORACLE_HOME/bin/rman catalog=[username]/[PASSWORD]@[RMANCAT_TNS_NAME]  << EOF
connect target /
run
{
allocate channel c1 device type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
restore controlfile from autobackup;
alter database mount;
restore database;
release channel c1;
}
EOF

1. take new raw device name from your Unix team (like /dev/rdisk/disk20)
2. make sure that you get its ownership change to oracle:dba
3. Add the new device name in asm_diskstring parameter dynamically as like below statement; in this statement only /dev/rdisk/disk20 disk is being added, all other existed previously:

alter system set asm_diskstring='/dev/rdisk/disk4','/dev/rdisk/disk6','/dev/rdisk/disk10','/dev/rdisk/disk12','/dev/rdisk/disk14','/dev/rdisk/disk16','/dev/rdisk/disk18','/dev/rdisk/disk20';

4. To verify, that new disk has been detected by ASM, you can check the value of column HEADER_STATUS from V$ASM_DISK,
it will show CANDIDATE here(Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement).

SQL> select GROUP_NUMBER,NAME,state,HEADER_STATUS,PATH from v$asm_disk;

GROUP_NUMBER NAME STATE HEADER_STATU PATH
------------ ------------------------------ -------- ------------ ------------------------------
0 NORMAL CANDIDATE /dev/rdisk/disk20
1 DATA_0000 NORMAL MEMBER /dev/rdisk/disk4
1 DATA_0001 NORMAL MEMBER /dev/rdisk/disk6
2 REDO_0000 NORMAL MEMBER /dev/rdisk/disk10
2 REDO_0001 NORMAL MEMBER /dev/rdisk/disk12
2 REDO_0002 NORMAL MEMBER /dev/rdisk/disk14
2 REDO_0003 NORMAL MEMBER /dev/rdisk/disk16
2 REDO_0004 NORMAL MEMBER /dev/rdisk/disk18

8 rows selected.

5. Add the disk to diskgroup :

ALTER DISKGROUP DATA ADD DISK '/dev/rdisk/disk20' REBALANCE POWER 1;

6. Again query v$asm_disk to verify that disk has been added.

SQL> select GROUP_NUMBER,NAME,state,HEADER_STATUS,PATH from v$asm_disk;

GROUP_NUMBER NAME STATE HEADER_STATU PATH
------------ ------------------------------ -------- ------------ ------------------------------
1 DATA_0000 NORMAL MEMBER /dev/rdisk/disk4
1 DATA_0001 NORMAL MEMBER /dev/rdisk/disk6
2 REDO_0000 NORMAL MEMBER /dev/rdisk/disk10
2 REDO_0001 NORMAL MEMBER /dev/rdisk/disk12
2 REDO_0002 NORMAL MEMBER /dev/rdisk/disk14
2 REDO_0003 NORMAL MEMBER /dev/rdisk/disk16
2 REDO_0004 NORMAL MEMBER /dev/rdisk/disk18
1 DATA_0002 NORMAL MEMBER /dev/rdisk/disk20

8 rows selected.

SQL> select GROUP_NUMBER,NAME,state,HEADER_STATUS,PATH,total_mb,free_mb from v$asm_disk;

GROUP_NUMBER NAME STATE HEADER_STATU PATH TOTAL_MB FREE_MB
------------ ------------------------------ -------- ------------ ------------------------------ ---------- ----------
1 DATA_0000 NORMAL MEMBER /dev/rdisk/disk4 157184 2766
1 DATA_0001 NORMAL MEMBER /dev/rdisk/disk6 157184 2756
2 REDO_0000 NORMAL MEMBER /dev/rdisk/disk10 768 241
2 REDO_0001 NORMAL MEMBER /dev/rdisk/disk12 768 243
2 REDO_0002 NORMAL MEMBER /dev/rdisk/disk14 768 248
2 REDO_0003 NORMAL MEMBER /dev/rdisk/disk16 768 245
2 REDO_0004 NORMAL MEMBER /dev/rdisk/disk18 768 247
1 DATA_0002 NORMAL MEMBER /dev/rdisk/disk20 157184 154356

8 rows selected.

7. Free space added is now being reflected in v$asm_diskgroup:

SQL> select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

NAME TOTAL_MB FREE_MB
------------------------------ ---------- ----------
DATA 471552 159878
REDO 3840 1224