Database PSU + OJVM Combo Patch Activity

Database PSU + OJVM Combo Patch Activity

Steps for applying PSU + OJVM Patch

1.    Check OS version

2.    Make a directory to take all the pre checks and binary backup

mkdir <location where you have enough space>

cd <location where you have enough space>

3.    Take crontab backup and comment it

crontab –l > crontab_bkup

crontab –e

To Disabled Entry $:%s/^/##VAIBHAV##/g

To Enable Entry $:%s/## VAIBHAV ##//g

4.    Set path for opatch

export PATH=$ORACLE_HOME/OPatch:$PATH

opatch lsinventory > opatch_bkup

5.    Now need to take DB precheck

spool precheck.log

set echo on

col host_name for a15

select name, open_mode,host_name,log_mode from v$database,v$instance;

select * from v$version;

col owner for a15

col object_name for a35

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

select count(*), status  from dba_objects  group by status;

====================================

select OWNER,OBJECT_NAME,OBJECT_TYPE,status from DBA_OBJECTS where status = 'INVALID';

select owner,count(1) from dba_objects where status='INVALID' group by owner;

> to compile invalid objects :

select 'ALTER ' || OBJECT_TYPE || ' ' || owner || '.' || OBJECT_NAME || ' COMPILE;' from dba_objects where status = 'INVALID';

==============================

set lines 200

col COMMENTS for a20

col ACTION for a10

col ACTION_TIME for a30

col NAMESPACE for a10

col BUNDLE_SERIES for a20

col VERSION for a10

select * from registry$history;

=========================================

col COMP_NAME for a40

col VERSION for a20

col STATUS for a10

set lines 150

select comp_name,version,status from dba_registry;

=========================================

spool off

6.    Shut down the database

shut immediate

7.    Take binary backup

cd $ORACLE_HOME/..

tar -cvf - 11.2.0.4 | compress > <location where you have enough space>/11.2.0.4.tar.gz

8.    Now download the Combo patch from Oracle Support and unzip to your location

Check its README file first,

Then check its conflict with your opatch inventory

opatch prereq CheckConflictAgainstOHWithDetail –phBaseDir < patch location >

Example : opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/app/Vaibhav/Vaibhav_PSUApril_2016/22378146/21948347

9.    Now if precheck succeded successfully, then fire command opatch apply

10. Same needs to do for OJVM also, precheck and apply

11. For applying the PSU at db level, start the database in normal mode

startup

@$ORACLE_HOME/rdbms/admin/catbundle.sql psu apply

@$ORACLE_HOME/rdbms/admin/utlrp.sql

12. Shut down the database and start it in upgrade mode as per README if applying OJVMs for 2015 onwards

startup upgrade

@<OJVM Patch location>/postinstall.sql

13. Now after successfully applying OJVM, shut down the database and startup normally

14. Do post checks as done in prechecks and check INVALID components and INVALID objects. It should be as same as precheck

All done !!!

Database Refresh Activity

Database Refresh Activity

On Source Database (Example: abc)

1. Take RMAN Level 0 backup

2. Create pfile from spfile

3. Transfer all the backup pieces fo datafile,controlfile,pfile & Archive logs generated during backup to the destination server.

4. Collect the info of no.of datafiles,controlfiles,redo logs etc.

5. select dbid from v$database;

                        DBID

                        ----------

                        633518243

6. select member from v$logfile;

On Destination Database (Example: xyz)

1. Collect the info of no.of datafiles,controlfiles,redo logs etc.

2. select dbid from v$database;

SQL> select dbid from v$database;

             DBID

----------

1954691695

3. Take backup of init.ora file or spfile.

4. Shutdown the database if it's open.

5. startup restrict mount;

  SQL> drop database;

6. Modify the db_name parameter of xyz as abc in init.ora parameter file

7. . oraenv

            xyz

8. echo $ORACLE_SID ( it should display xyz )

9. sqlplus '/as sysdba'

SQL> startup nomount

10. rman target /

RMAN > set dbid=633518243 ( dbid of source database )

11. Restore controlfile from backup

restore controlfile from '<backup piece which contains your controlfile backup’>

You can check this detail on source database abc by firing below command

RMAN > list backup of controlfile;

12. RMAN > alter database mount;

13. Catalog all the backups

RMAN> catalog backuppiece '< all backup pieces >’;

14. Use below query in source database abc and change the filenames as per the path of destination database xyz

=======================================================================

******* First need to get output from ABC database and modify this as per XYZ database *******

set lines 200 pages 999

select 'SET NEWNAME FOR DATAFILE ' || file# || ' to ' || name || ';' from v$datafile;

set lines 150 pages 999

select 'SET NEWNAME FOR TEMPFILE ' || file# || ' to ' || name || ';' from v$tempfile;

=======================================================================

15. Set newname to all the datafiles of ABC as per XYZ

RMAN>

run

{

SET NEWNAME FOR DATAFILE 1 to ‘new location for datafile 1’;

SET NEWNAME FOR DATAFILE 2 to ‘new location for datafile 2’;

SET NEWNAME FOR DATAFILE 3 to ‘new location for datafile 3’;

.

.

.

.

SET NEWNAME FOR DATAFILE last to ‘new location for datafile last’;

RESTORE DATABASE;

SWITCH DATAFILE ALL;

SWITCH TEMPFILE ALL;

}

16. Recover database

RMAN> recover database; (if all backups and archives are cataloged)

Or

sqlplus '/as sysdba'

SQL> recover database using backup controlfile until cancel;

Now provide the archive log of dap1 whichever it asks until last archive.

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

*** If we need to apply more than 100 archive log files,it would be better to take archive logs backup from Production FROM SEQUENCE <seq> UNTIL SEQUENCE <seq>***

run {

BACKUP ARCHIVELOG FROM SEQUENCE <seq> UNTIL SEQUENCE <seq> tag = '<tag_name>';

}

17. SQL>alter database open RESETLOGS;

(ORA-27040: file create error, unable to create file

Linux-x86_64 Error: 2: No such file or directory)

If you get above error then need to do below else not required

SQL> select member from v$logfile;

Then Run the below command

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

alter database rename file '<abc/redo.log>’ to ‘<xyz/redo.log>’;

.

.

.

alter database rename file '<abc/redo.log>’ to ‘<xyz/redo.log>’;

18. alter database open resetlogs;  --first check without resetlog at step 17.

If needed clear logfile group like:1/2/3/4

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

ALTER DATABASE CLEAR LOGFILE GROUP 4;

19. Change DB-ID using nid com

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

cd $ORACLE_HOME/bin

nid TARGET=/ DBNAME=<new name, here xyz>

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

20. Shut down the database and startup normally.

21. Check client connectivity 

All done !!!