Oracle Software Patching Using OPatch – Interim Patches

OPatch is an Oracle utility that assists you to apply interim patches to Oracle’s rdbms software. You can find opatch utility in $ORACLE_HOME/Opatch directory. For example, let’s apply patch number 8943287  to our 10.2.0.5 database.

Applying Patch:

1- Backup Oracle Home directory.

$ tar -cf ora10g.tar ora10g

2- Download patch file p8943287_10205_Linux-x86-64.zip via metalink. And copy it to database server.

3- Unzip the patch file.

$ unzip p8943287_10205_Linux-x86-64.zip

4- Apply patch with opatch utility.

$ cd 8943287

$ ORACLE_HOME/OPatch/opatch apply

To see list of applied patches :

$ORACLE_HOME/OPatch/opatch lsinventory

For example:

$ORACLE_HOME/OPatch/opatch lsinventory

Invoking OPatch 10.2.0.4.9

Oracle Interim Patch Installer version 10.2.0.4.9

Copyright (c) 2009, Oracle Corporation. All rights reserved.

Oracle Home : /oracle/ora10g

Central Inventory : /oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 10.2.0.4.9

OUI version : 10.2.0.5.0

OUI location : /oracle/ora10g/oui

Log file location : /oracle/ora10g/cfgtoollogs/opatch/opatch2011-10-28_12-14-12PM.log

Patch history file: /oracle/ora10g/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /oracle/ora10g/cfgtoollogs/opatch/lsinv/lsinventory2011-10-28_12-14-12PM.txt

——————————————————————————–

Installed Top-level Products (3):

Oracle Database 10g 10.2.0.1.0

Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0

Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0

There are 3 products installed in this Oracle Home.

Interim patches (2) :

Patch 8943287 : applied on Fri Oct 21 20:39:46 EEST 2011

Unique Patch ID: 12722995

Created on 23 Aug 2010, 11:45:16 hrs PST8PDT

Bugs fixed:

8943287

——————————————————————————–

Rollback applied patch:

Sometimes a patch is applied to the system may need to take back because of its effect. In this case, rollback is performed as follows.

$ORACLE_HOME/OPatch/opatch rollback -id 8943287

Talip Hakan ÖZTÜRK

RMAN Data Recovery Methods

Hi,

In this article, I will write about RMAN data recovery methods step by step

1-  If you lost all data files ;

SQL> startup mount;
RMAN> restore database;
RMAN> recover database;
SQL> alter database open;

2- If you lost a tablespace;

 SQL> alter tablespace users offline;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter tablespace users online;

if you can not offline tablespace;

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter database open;

3- if you lost a datafile;

SQL> alter database datafile '/oracle/oradata/users.dbf' offline;
RMAN> restore datafile '/oracle/oradata/users.dbf'
RMAN> recover datafile '/oracle/oradata/users.dbf'
SQL> alter database datafile '/oracle/oradata/users.dbf' online;

if you cannot offline datafile;

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore datafile '/oracle/oradata/users.dbf';
RMAN> recover datafile '/oracle/oradata/users.dbf';
SQL> alter database open;

4-  if you lost your controlfiles;

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> set dbid = 3970640872;
RMAN> restore controlfile;
SQL> alter database mount;
SQL> alter database open;

you will receive an error ORA-01589 when you open database

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Bu durumda aşağıdaki işlemler yapılmalıdır.

SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> reset database;

if you open database with resetlogs, SCN number will be zero. In this situation
all previous backups will be invalid. You must full backup.

5- May be a special situation. You need to incomplete recovery

A. Time-Based incomplete recovery;

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
SQL> alter database open resetlogs;

B. SCN-Based incomplete recovery;

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000;
SQL> alter database open resetlogs;

C. Archive log sequence based incomplete recovery;

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until sequence 9923;
RMAN> recover database until sequence 9923;
SQL> alter database open resetlogs;

6-  if you need some archive logs in your backup

$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';

OR

RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')
UNTIL TIME 'SYSDATE';

7- if your data block is corrupted you will receive an error below.

Error:

ORA-01578: ORACLE data block corrupted (file # 8, block # 13)

ORA-01110: data file 8: ‘/oracle/oradata/users.dbf’

for recover data block;

$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN>blockrecover datafile 8 block 13;

For Block-Level Media Recovery – Concept & Example (Doc ID 144911.1)

To recover, we can give a specific backup set;

# recovery from backupset
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
# recovery from image copy
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
      FROM DATAFILECOPY;
# recovery from backupset which have "FULL" tag
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199
       FROM TAG = FULL;

During backup or “Validate Backup” command, RMAN finds corrupted blocks and writes to V$DATABASE_BLOCK_CORRUPTION view. When the RMAN recover the corrupt block then automatically updates this view. List of all the corruption of the past, can be viewed over V$BACKUP_CORRUPTION and V$COPY_CORRUPTION views.  If you run the following command,  RMAN will recover all the corrupted blocks in view V$DATABASE_BLOCK_CORRUPTION.

RMAN>BLOCKRECOVER CORRUPTION LIST
  RESTORE UNTIL TIME 'SYSDATE-10';

8- if you have a image copy backup and your datafile number 2 has problems then you
can switch datafile number2 to image copy.

RMAN>sql ‘alter database datafile 2 offline’;
RMAN>switch datafile 2 to copy;
RMAN>recover datafile 2;
RMAN>sql ‘alter database datafile 2 online’;

Talip Hakan Ozturk

Oracle WRAP Utility

Oracle WRAP Utility

Hello Friends,

In this article, I will write about Oracle WRAP Utility. And how we can hide our PL/SQL codes. Oracle hides its packages (internal packages, also knows as DBMS packages). If we want to see body of the DBMS_JOB package, we can not see PL/SQL code. We will see the code is wrapped.

CREATE OR REPLACE PACKAGE BODY SYS.dbms_job wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
21a3 b16
lo1iuGKabyEptQ7AYEgtdK75BmEwg5Wr3l4FYKAPamS0YVj3PRYchH0hld1fVjvwrMuAW69P
IRpr66AU9anSde7G/s0SEqGnRq1lMtPIv5oS+LkLjWMsdviZ/8mTU+AsGapF4qqdz3JR4PZt
fzqcP2JOVo84jcefz+RAsEnvMxsTLRYBAxaBTlykY2wPpW6+XqZvj/lmCFhU18E/3TEWpRQb
5qZckusO2rpG787GwuV+s2zCeeXxFg4vs4uaQo05AYljfhxjOKSSbLg3TUk/VlckCE6PhXK/
...
/

We can hide our procedure, function or package codes with wrap utility. We can also hide our dynamic codes with using DBMS_DDL package in Oracle 10g R2. WRAP is a utility that runs on the operating system command. Use the prototype is as follows.

wrap iname=input_file  oname=output_file

Here, iname is the input file covering procedure,function or package that we want to hide. Oname is the output file.  You can write  only iname. In this situation the output file name will be input_file_name.plb .

For example, we have the procedure talip_test. And we want to hide the content of this procedure with wrap utility.

CREATE OR REPLACE PROCEDURE talip_test AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Test proseduru');
END;
/

Save the procedure as talip_test.sql  on the operating system. And use wrap utility as following

$ wrap iname= talip_test.sql
PL/SQL Wrapper: Release 11.2.0.1.0- Production on Thu Mar 10 13:50:13 2011
Copyright (c) 1993, 2009, Oracle.  All rights reserved.
Processing talip_test.sql to talip_test.plb

The output file will be saved as talip_test.plb. let’s open this file with vi

$ vi talip_test.plb

CREATE OR REPLACE PROCEDURE talip_test wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
4d 89
BnIWMXkTu2kwu8RXi1DM+jgMrZUwg5nnm7+fMr2ywFwWoUf6VuOWoWLRzLh0i8DAMv7Shglp
uFKbskr+KLK957KzHQYwLK4k6rKBL8jlrFHkBneJJVEJMi720eokH/Y5pqcyH4I=
/

You can deploy this code to database. After deployment you can use this procedure.

SQL>exec talip_test;

Talip Hakan Ozturk