Ramesh B

Monday, 25 May 2015

PSU apply 11.2.0.2.3 to 11.2.0.2.4

Prerequisites:

Ensure that all the connections connected to the OH should be disconnected.

You must shutdown all instances and listeners associated with the Oracle Home that you are applying patch.

Ensure that the $PATH and $ORACLE_HOME been set

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/

[oracle@localhost ~]$ export PATH=$ORACLE_HOME/bin:$PATH

To apply the PSU 11.2.0.2.4 patch, the Oracle home must have the 11.2.0.2.0 Database installed.

1. You must use the Opatch utility version 11.2.0.1.3 or later to apply this patch.

[oracle@localhost ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opat ch version

Invoking OPatch 11.2.0.3.10

OPatch Version: 11.2.0.3.10

OPatch succeeded.

2. If you have lower version, download latest from metalink , patch 6880880 by selecting the 11.2.0.0.0 release, and unzip in the home location.

Unzip the patch, and cd to the patch number location as bellow.

[oracle@localhost ~]$ cd /opt/12827726/

[oracle@localhost 12827726]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opat ch apply

Oracle Interim Patch Installer version 11.2.0.3.10

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/11.2.0/dbhome_1//oraInst.loc

OPatch version : 11.2.0.3.10

OUI version : 11.2.0.2.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/1 2827726_May_15_2015_15_43_16/apply2015-05-15_15-43-15PM_1.log

Applying interim patch '12827726' to OH '/u01/app/oracle/product/11.2.0/dbhome_1 '

Verifying environment and performing prerequisite checks...

Conflicts/Supersets for each patch are:

Patch : 12827726

Bug Superset of 12419331

Super set bugs are:

10151017, 10158965, 11724916, 10190642, 12586486, 12586487, 101296 43, 12586488, 12586489, 10018789, 9744252, 10248523, 9956713, 10356513, 9715581, 9770451, 10378005, 10170431, 10425676, 10222719, 10126094, 95918 12, 10127360, 10132870, 10094201, 9443361, 10193846, 11664046, 11069199, 10324294, 10245086, 12586490, 10205230, 12586491, 10052141, 12586492, 12 586493, 12586494, 10142788, 11818335, 11830776, 12586495, 9905049, 118307 77, 12586496, 11830778, 6892311, 10040921, 10077191, 10358019, 12431716, 10219576, 10258337, 11707699, 10264680, 10209232, 11651810, 10102506, 11 067567, 9881076, 10278372, 10040531, 10621169, 10155605, 10082277, 103567 82, 10218814, 9078442, 9788588, 10157249, 9735237, 10317487, 12326246, 1 1707302, 10310299, 10636231, 10230571, 11065646, 12419321, 10368698, 1007 9168, 10013431, 10228151, 10233732, 10324526, 8223165, 10238786, 10217802 , 10061015, 9953542, 9572787, 10052956, 10080579, 11699057, 12620422, 10 332111, 10227288, 10329146, 10332589, 10110863, 10073683, 9869401, 100192 18, 10229719, 11664719, 9539440, 10373381, 9735282, 9748749, 11724984, 1 0022980, 10411618, 11800854, 12419331, 11674485, 10187168, 6523037, 10648 873, 9724970, 10053725, 10084145, 10367188, 11800170, 11695285, 10157402, 9651350, 10299224

Interim patch 12827726 is a superset of the patch(es) [ 12419331 ] in the Oracl e Home

OPatch will roll back the subset patches and apply the given patch.

All checks passed.

Provide your email address to be informed of security issues, install and

initiate Oracle Configuration Manager. Easier for you if you use your My

Oracle Support Email address/User Name.

Visit http://www.oracle.com/support/policies.html for details.

Email address/User Name:

You have not provided an email address for notification of security issues.

Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1')

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files...

Rolling back interim patch '12419331' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms.rsf, 11.2.0.2.0...

Patching component oracle.rdbms, 11.2.0.2.0...

Patching component oracle.sysman.console.db, 11.2.0.2.0...

Patching component oracle.sysman.oms.core, 10.2.0.4.3...

Patching component oracle.ldap.rsf, 11.2.0.2.0...

Deleting "ssl_getconnectionrenegotiationoptions.o" from archive "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.a"

Deleting "ssl_getrenegotiationoptions.o" from archive "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.a"

Deleting "ssl_hshk_priv_dooptionsallowrenegotoation.o" from archive "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.a"

Deleting "ssl_modifyrenegotiationoptions.o" from archive "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.a"

Deleting "ssl_setrenegotiationoptions.o" from archive "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.a"

Patching component oracle.rdbms.dv, 11.2.0.2.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.2.0...

Patching component oracle.sysman.plugin.db.main.repository, 11.2.0.2.0...

Patching component oracle.rdbms.rman, 11.2.0.2.0...

RollbackSession removing interim patch '12419331' from inventory

OPatch back to application of the patch '12827726' after auto-rollback.

Patching component oracle.rdbms.rsf, 11.2.0.2.0...

Patching component oracle.rdbms, 11.2.0.2.0...

Patching component oracle.sysman.console.db, 11.2.0.2.0...

Patching component oracle.sysman.oms.core, 10.2.0.4.3...

Patching component oracle.ldap.rsf, 11.2.0.2.0...

Patching component oracle.rdbms.dv, 11.2.0.2.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.2.0...

Patching component oracle.sysman.plugin.db.main.repository, 11.2.0.2.0...

Patching component oracle.rdbms.rman, 11.2.0.2.0...

Patching component oracle.sdo.locator, 11.2.0.2.0...

Verifying the update...

Patch 12827726 successfully applied

Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/12827726_May_15_2015_15_43_16/apply2015-05-15_15-43-15PM_1.log

OPatch succeeded.

[oracle@localhost 12827726]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/

[oracle@localhost OPatch]$ ls

crs emdpatch.pl ocm opatchauto-dir opatchdiag opatch.pl operr operr_readme.txt README.txt version.txt

docs jlib opatch opatch.bat opatchdiag.bat opatchprereqs operr.bat oplan scripts

once patch is successfully applied then ensure that the patch is applied by issuing ./opatch lsinventory in $ORACLE_HOME/OPatch/ location.

[oracle@localhost OPatch]$ ./opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.10

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/11.2.0/dbhome_1//oraInst.loc

OPatch version : 11.2.0.3.10

OUI version : 11.2.0.2.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-05-15_15-57-48PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-05-15_15-57-48PM.txt

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

Local Machine Information::

Hostname: localhost.localdomain

ARU platform id: 226

ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.2.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 12827726 : applied on Fri May 15 15:56:24 IST 2015

Unique Patch ID: 14016281

Created on 21 Sep 2011, 06:46:41 hrs PST8PDT

Bugs fixed:

10158965, 10031806, 12635537, 9746210, 9744252, 9956713, 10356513

10204358, 10378005, 10170431, 11853815, 10222719, 12639177, 9591812

10127360, 9443361, 10035737, 10384285, 12846268, 9873405, 12846269

11069199, 12670165, 10245086, 10159846, 10205230, 10052141, 10039731

11818335, 12371955, 10040921, 12366627, 10077191, 11827088, 11785938

10219576, 10258337, 11707699, 10264680, 10094823, 10209232, 11695333

9881076, 10278372, 10281887, 10621169, 10048701, 9078442, 11057263

10157249, 9735237, 10317487, 10219583, 10636231, 10310299, 11065646

10055063, 10368698, 12340939, 10079168, 10233732, 8223165, 10264696

10142909, 9953542, 10052956, 10080579, 11699057, 12620422, 10285022

10329146, 10332589, 11666137, 10110863, 9869401, 12828071, 10019218

10229719, 9539440, 10373381, 11724984, 10239480, 10411618, 11670161

9724970, 10399808, 10113990, 10312847, 11893621, 10084145, 10367188

11800170, 11695285, 10285394, 10157402, 9651350, 10299224, 10151017

11724916, 10190642, 12586486, 9564886, 12586487, 10129643, 12586488

12586489, 10018789, 10248523, 9715581, 9770451, 11889177, 10425676

9683047, 10180307, 10126094, 10396041, 10132870, 10094201, 10193846

11664046, 10324294, 9414040, 12586490, 9819805, 12586491, 9795214

12586492, 12586493, 12586494, 10142788, 12586495, 11830776, 9905049

11674898, 10419984, 12586496, 11830777, 6892311, 11830778, 10358019

12431716, 10422126, 10200404, 11651810, 10102506, 11067567, 12827726

11733179, 10229886, 10040531, 10155605, 10326338, 10356782, 10082277

10051315, 10218814, 9788588, 10278773, 12326246, 10622001, 11707302

10230571, 9966609, 12419321, 12546006, 10137324, 9771278, 10013431

10228151, 10324526, 11894889, 10238786, 10217802, 10061015, 9572787

10332111, 10227288, 10623249, 10073683, 12639234, 11664719, 9735282

9748749, 10022980, 10237773, 11800854, 12419331, 11674485, 10097711

10187168, 6523037, 10648873, 10053725, 8685446

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

OPatch succeeded.

The following steps load modified SQL files into the database. For a RAC environment, perform these steps on only one node.

For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:

cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba

SQL> STARTUP

SQL> @catbundle.sql psu apply

The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.

SQL> @catbundle.sql psu apply

PL/SQL procedure successfully completed.

Generating apply and rollback scripts...

Check the following file for errors:

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_GENERATE_2015May15_15_59_50.log

Apply script: /u01/app/oracle/product/11.2.0/dbhome_1//rdbms/admin/catbundle_PSU_ORCL_APPLY.sql

Rollback script: /u01/app/oracle/product/11.2.0/dbhome_1//rdbms/admin/catbundle_PSU_ORCL_ROLLBACK.sql

PL/SQL procedure successfully completed.

Executing script file...

SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT

SQL> SELECT '/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;

SQL> SPOOL &spool_file

SQL> exec dbms_registry.set_session_namespace('SERVER')

PL/SQL procedure successfully completed.

SQL> PROMPT Skipping Oracle Database Vault because it is not installed or versions mismatch...

Skipping Oracle Database Vault because it is not installed or versions mismatch...

SQL> ALTER SESSION SET current_schema = SYS;

Session altered.

SQL> PROMPT Updating registry...

Updating registry...

SQL> INSERT INTO registry$history

2 (action_time, action,

3 namespace, version, id,

4 bundle_series, comments)

5 VALUES

6 (SYSTIMESTAMP, 'APPLY',

7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),

8 '11.2.0.2',

9 4,

10 'PSU',

11 'PSU 11.2.0.2.4');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off

SQL> SET echo off

Check the following log file for errors:

/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2015May15_15_59_54.log

SQL> desc registry$history

Name Null? Type

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

ACTION_TIME TIMESTAMP(6)

ACTION VARCHAR2(30)

NAMESPACE VARCHAR2(30)

VERSION VARCHAR2(30)

ID NUMBER

COMMENTS VARCHAR2(255)

BUNDLE_SERIES VARCHAR2(30)

SQL> select BUNDLE_SERIES,ACTION_TIME,VERSION,COMMENTS registry$history;

select BUNDLE_SERIES,ACTION_TIME,VERSION,COMMENTS registry$history

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

SQL> select BUNDLE_SERIES,ACTION_TIME,VERSION,COMMENTS from registry$history;

BUNDLE_SERIES ACTION_TIME VERSION

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

COMMENTS

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

PSU 05-SEP-10 06.22.14.370943 AM 11.2.0.2

Patchset 11.2.0.2.0

PSU 15-MAY-15 09.57.01.040699 AM 11.2.0.2

Patchset 11.2.0.2.0

PSU 15-MAY-15 02.34.21.042804 PM 11.2.0.2

PSU 11.2.0.2.3

BUNDLE_SERIES ACTION_TIME VERSION

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

COMMENTS

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

PSU 15-MAY-15 03.59.54.422167 PM 11.2.0.2

PSU 11.2.0.2.4

SQL>quit

Wednesday, 29 April 2015

simple RMAN full backup script

oracle@dbtestserver $ cat /export/home/oracle/scripts/rman_ORCL.ksh
#!/bin/ksh
mailTo="dba_group@oratech.com"

logdir=/rman_bkp/oradata/ORCL/logs

#set the oracle environment

ORACLE_HOME=/ora_home/oradata/product/11.2.0.3/dbhome_1
ORACLE_SID=ORCL
export ORACLE_HOME
export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH
export PATH

#check if user is oracle

USER=`/usr/ucb/whoami`
if [ $USER != "oracle" ]
then
echo "ERROR: This script must be run as the oracle user"
echo "Exiting... `date`"
exit 1
fi

# --------------------------------------------------------------------------------

# backup now

# --------------------------------------------------------------------------------
rman target / catalog rman11g/rman11g@rmandnpd<< EOF >>$logdir/"rman_ORCL_full_`date +%d_%m_%Y`".log

CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE = 2G;
sql 'alter system switch logfile';
run
{
allocate channel dsk1 type disk format '/rman_bkp/oradata/ORCL/%U';
allocate channel dsk2 type disk format '/rman_bkp/oradata/ORCL/%U';
backup database;
crosscheck archivelog all;
backup archivelog all delete input;
}
exit
EOF

/bin/find /rman_bkp/oradata/ORCL/ \*_1 -mtime +1 -exec rm {} \;
AS_OF=`date +%d_%m_%Y`
#mail $mailTo <"$logdir/rman_ORCL_full_`date +%d_%m_%Y`".log
mail ${mailTo} <<EOF
From:dbtestserver
To: dba_ora@oratech.com
Subject: ORCL Delivery INFO - Full RMAN Backup of ORCL db
`cat $logdir/rman_ORCL_full_${AS_OF}.log`
EOF

Friday, 17 April 2015

Recovering missing system datafile in oracle

Prerequisite: need to take full RMAN backup of the database plus archivelogs.

Check the datafiles location.

SQL> select file_name from dba_data_files;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/sample01.dbf

remove the system datafile from physical location using os commands like rm

[oracle@localhost ~]$ cd /u01/app/oracle/oradata/orcl

[oracle@localhost orcl]$ rm system01.dbf

[oracle@localhost orcl]$ ls -lrth

total 2.3G
-rw-r----- 1 oracle oinstall 5.1M Apr 3 00:42 users01.dbf
-rw-r----- 1 oracle oinstall 51M Apr 8 03:56 redo02.log
-rw-r----- 1 oracle oinstall 21M Apr 8 03:58 temp01.dbf
-rw-r----- 1 oracle oinstall 249M Apr 8 03:59 example01.dbf
-rw-r----- 1 oracle oinstall 641M Apr 8 04:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 571M Apr 8 04:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 51M Apr 8 04:06 redo01.log
-rw-r----- 1 oracle oinstall 9.3M Apr 8 04:06 control01.ctl

If system datafile is missed or corrupted, database will not shut down, but no operations permissible

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 8 04:06:38 2015

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> create table rr(name varchar(10));
create table rr(name varchar(10))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

In order to restore system datafile we need to bring down the database using shut abort, bcz shut immediate will not work

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 8 04:17:27 2015

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

shutdown database by aborting the instance.

SQL> shut abort
ORACLE instance shut down.

connect to rman and start up instance in mount stage.

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 8 04:18:18 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 1033670656 bytes

Fixed Size 1341056 bytes
Variable Size 704645504 bytes
Database Buffers 322961408 bytes
Redo Buffers 4722688 bytes

Now restore datafile.

RMAN> restore datafile 1;

Starting restore at 08-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_04_08/o1_mf_nnndf_TAG20150408T035923_bl8pl3p8_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_04_08/o1_mf_nnndf_TAG20150408T035923_bl8pl3p8_.bkp tag=TAG20150408T035923
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 08-APR-15

recover datafile.

RMAN> recover datafile 1;

Starting recover at 08-APR-15
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 08-APR-15

open the database.

RMAN> alter database open;

database opened

Thursday, 16 April 2015

Disabling firewales in linux

You need to login as root user into linux

[root@localhost ~]# service iptables status
Table: filter
Chain INPUT (policy ACCEPT)
num target prot opt source destination
1 RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0

Chain FORWARD (policy ACCEPT)
num target prot opt source destination
1 RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0

Chain OUTPUT (policy ACCEPT)
num target prot opt source destination

Chain RH-Firewall-1-INPUT (2 references)
num target prot opt source destination
1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
2 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 icmp type 255
3 ACCEPT esp -- 0.0.0.0/0 0.0.0.0/0
4 ACCEPT ah -- 0.0.0.0/0 0.0.0.0/0
5 ACCEPT udp -- 0.0.0.0/0 224.0.0.251 udp dpt:5353
6 ACCEPT udp -- 0.0.0.0/0 0.0.0.0/0 udp dpt:631
7 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:631
8 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
9 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:21
10 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
11 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited

[root@localhost ~]# service iptables stop
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]

[root@localhost ~]# service iptables status
Firewall is stopped.

[root@localhost ~]# service iptables save

Saturday, 4 April 2015

Missing non system datafile recover using RMAN backup

Prerequisite: need to take full RMAN backup of the database.

-Connect to RMAN commnad prompt.

[oracle@localhost trace]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 1 12:06:44 2015

connected to target database: ORCL (DBID=1341150024)

-Put the data file in to offline, Here we can use missing datafile name or datafile number. I am using datafile number.

RMAN> sql 'alter database datafile 5 offline';

using target database control file instead of recovery catalog

sql statement: alter database datafile 5 offline

-Restore the datafile from backup.

RMAN> restore datafile 5;

Starting restore at 01-APR-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_04_01/o1_mf_nnndf_TAG20150401T114744_bkq3dbcn_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_04_01/o1_mf_nnndf_TAG20150401T114744_bkq3dbcn_.bkp tag=TAG20150401T114744

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 01-APR-15

-Recover datafile

RMAN> recover datafile 5;

Starting recover at 01-APR-15

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 01-APR-15

-put the datafile into online.

RMAN> sql 'alter database datafile 5 online';

sql statement: alter database datafile 5 online

RMAN> exit

-Recovery Manager complete.

SQL> select status from v$datafile;

STATUS

-------

SYSTEM

ONLINE

6 rows selected.

Recover non system datafile with backup

Recovering missing non system datafile.

Prerequisites: database must be in archivelog mode, and need to have hotbackup. Click Here to know how to take hot backup.

-For non system datafile recovery no need to shutdown database.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 09:11:09 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/example01.dbf

/u01/app/oracle/oradata/sample01.dbf

6 rows selected.

[oracle@localhost hotbk]$ cd /u01/app/oracle/oradata/ orcl

[oracle@localhost orcl]$ ls -lrt

total 2340652

-rw-r----- 1 oracle oinstall 20979712 Apr 1 08:20 temp01.dbf

-rw-r----- 1 oracle oinstall 52429312 Apr 1 09:39 redo02.log

-rw-r----- 1 oracle oinstall 5251072 Apr 1 09:45 users01.dbf

-rw-r----- 1 oracle oinstall 260186112 Apr 1 09:51 example01.dbf

-rw-r----- 1 oracle oinstall 765468672 Apr 1 10:04 system01.dbf

-rw-r----- 1 oracle oinstall 671096832 Apr 1 10:05 undotbs01.dbf

-rw-r----- 1 oracle oinstall 576724992 Apr 1 10:05 sysaux01.dbf

-rw-r----- 1 oracle oinstall 52429312 Apr 1 10:07 redo01.log

-rw-r----- 1 oracle oinstall 9748480 Apr 1 10:07 control01.ctl

-Now remove any one non system datafile, here i am deleting example01.dbf datafile from os level. If any non system datafile is missing database will not shut down it will run as usual, we can find error in alertlog.

[oracle@localhost orcl]$ rm example01.dbf

Lets check in alerlog file.

[oracle@localhost ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

[oracle@localhost trace]$ tail -20f alert_orcl.log

DELETE EXPIRED commands.

************************************************************************

Wed Apr 01 09:43:59 2015

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ckpt_3422.trc:

ORA-01171: datafile 5 going offline due to error advancing checkpoint

ORA-01116: error in opening database file 5

ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Wed Apr 01 09:44:01 2015

Checker run found 1 new persistent data failures

-Check the datafile status in database.

[oracle@localhost trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 09:44:27 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select FILE#,STATUS from v$datafile;

FILE# STATUS

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

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 RECOVER

6 ONLINE

6 rows selected.

SQL> exit

-Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

-restore datafile from backup location.

[oracle@localhost trace]$ cd

[oracle@localhost ~]$ cd /u01/app/hotbk/

[oracle@localhost hotbk]$ cp example01.dbf /u01/app/oracle/oradata/orcl/

-Connect to database and recover the datafile.

[oracle@localhost hotbk]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 09:49:51 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select FILE#,STATUS from v$datafile;

FILE# STATUS

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

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 RECOVER

6 ONLINE

SQL> alter database datafile 5 offline;

Database altered.

SQL> recover datafile 5;

Media recovery complete.

SQL> alter database datafile 5 online;

Database altered.

SQL> select FILE#,STATUS from v$datafile;

FILE# STATUS

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

1 SYSTEM

2 ONLINE

3 ONLINE

4 ONLINE

5 ONLINE

6 ONLINE

6 rows selected.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

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

Pages

Monday, 25 May 2015

Wednesday, 29 April 2015

Friday, 17 April 2015

Thursday, 16 April 2015

Saturday, 4 April 2015