Points to Remember.
==================
A) 11gR1 Rac - ONS is not coming up in one server (AIX).
========================================================
Check the file /etc/netsvc.conf
B) RMAN Backup / Restoration using SBT.
======================================
run
{
ALLOCATE CHANNEL chnl1 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=rhel11g1,NB_ORA_POLICY=bkp_policy,NB_ORA_SERV=rhelmastersrv,NB_ORA_SCHED=BKP_SCH';
BACKUP DATABASE PLUS ARCHIVELOG ALL;
BACKUP CURRENT CONTROLFILE FORMAT 'rman_arch_%d_%U_%t.ctl';
release channel chnl1;
}
run
{
ALLOCATE CHANNEL chnl1 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=rhel11g1,NB_ORA_POLICY=bkp_policy,NB_ORA_SERV=rhelmastersrv,NB_ORA_SCHED=BKP_SCH';
restore database;
recover database;
release channel chnl1;
}
For incomplete recovery.
run
{
allocate channel chnl1 type 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=rhel11g1,NB_ORA_POLICY=bkp_policy,NB_ORA_SERV=rhelmastersrv,NB_ORA_SCHED=BKP_SCH';
set until sequence 5000 thread 1;
restore controlfile;
alter database mount;
RESTORE DATABASE;
RECOVER DATABASE;
RELEASE CHANNEL chnl1;
}
C) Static Instance registration in Listener. Eg.
================================================
lsnr_mydb1 =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = tcp)
(HOST = rhel11g1.manzoor.com)
(PORT = 15000)
)
)
)
SID_LIST_lsnr_mydb1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = mydb1)
(ORACLE_HOME = /u01/app/oracle/product/11.2/db )
)
)
D) Logminer.
============
https://community.oracle.com/thread/948629?tstart=0
http://docs.oracle.com/cd/B14117_01/server.101/b10825/logminer.htm
alter database add supplemental log data;
SQL> alter system set utl_file_dir='/mydb1/oradata' scope=spfile;
Restart the db.
Build the dictionary:-
begin
dbms_logmnr_d.build
(
dictionary_filename => 'dictionary.ora',
dictionary_location => '/mydb1/oradata',
options => dbms_logmnr_d.store_in_flat_file
);
end;
/
Add the archived log files to be analyzed:-
begin
dbms_logmnr.add_logfile
(
logfilename => '/u01/app/oracle/mydb1/arch_728_1_774698775.arc',
options => dbms_logmnr.new
);
end;
/
begin
dbms_logmnr.add_logfile
(
logfilename => '/u01/app/oracle/mydb1/arch_729_1_774698775.arc',
options => dbms_logmnr.addfile
);
end;
/
Start the logminer:-
begin
dbms_logmnr.start_logmnr( dictfilename=>'/mydb1/oradata/dictionary.ora');
end;
/
Select the results from V$logmnr_contents
Stop the logminer once done
SQL> begin
DBMS_LOGMNR.END_LOGMNR();
end;
/
Logminer using Redo log files.
======================
SQL> desc dbms_logmnr_d;
PROCEDURE BUILD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DICTIONARY_FILENAME VARCHAR2 IN DEFAULT
DICTIONARY_LOCATION VARCHAR2 IN DEFAULT
OPTIONS NUMBER IN DEFAULT
PROCEDURE SET_TABLESPACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NEW_TABLESPACE VARCHAR2 IN
select sql_id, to_char(SQL_EXEC_START,'DD-MM-YY HH24:MI:SS'), sql_opname from dba_hist_active_sess_history where sql_opname = 'TRUNCATE TABLE';
SQL> select name , sequence# from V$archived_log where sequence# between 118 and 123;
NAME
--------------------------------------------------------------------------------
SEQUENCE#
----------
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_118_8tzwnyc5_.arc
118
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_119_8tzwo2kk_.arc
119
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_120_8tzwo8gt_.arc
120
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_121_8tzws9z6_.arc
121
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_122_8tzwsjpk_.arc
122
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_123_8tzy3wkl_.arc
123
SQL> EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_118_8tzwnyc5_.arc', options => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_119_8tzwo2kk_.arc', options => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_120_8tzwo8gt_.arc', options => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_121_8tzws9z6_.arc', options => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_122_8tzwsjpk_.arc', options => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_123_8tzy3wkl_.arc', options => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
Below are the redo log files - its is mandatory to add because the dictory is stored in the redo log files, or we will get an error as below.
ERROR at line 1:
ORA-01371: Complete LogMiner dictionary not found
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1
SQL> select member from V$logfile;
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/source/redo03.log
/u01/app/oracle/oradata/source/redo02.log
/u01/app/oracle/oradata/source/redo01.log
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/oradata/source/redo03.log',options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/oradata/source/redo02.log', options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/oradata/source/redo01.log' , options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS =>DBMS_LOGMNR.DDL_DICT_TRACKING + DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
PL/SQL procedure successfully completed.
SQL> select count(*) from V$logmnr_contents;
COUNT(*)
----------
603198
-- Now filter the data as per the requirement from V$logmnr_contents.
SQL> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
###############################
Analyzing the Log files of an database using Another database, e.g. Analyzing the logfiles of
Production database using the uat database on another server,
Note . The database in which we are analyzing (here uat) should be same / higher version of the database (here prod) from which we got the logfiles.
For this eg. the prod database is 10g and uat is 11g.
Prod database
-----------------
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 4 52428800 2 YES INACTIVE 531894 28-MAY-15
2 1 5 52428800 2 NO CURRENT 574844 28-MAY-15
3 2 5 52428800 2 NO CURRENT 615297 03-JUN-15
4 2 4 52428800 2 YES INACTIVE 594874 28-MAY-15
User created.
SQL> grant dba to manzoor;
Grant succeeded.
SQL> conn manzoor/ahamed
Connected.
SQL> create table emp (emp_id number, ename varchar2(30));
Table created.
SQL> create table dept (dept_id number, dname varchar2(30));
Table created.
SQL> begin
2 for i in 1..1000 loop
3 insert into emp values (i, dbms_random.string('U',30));
4 insert into dept values (i, dbms_random.string('U',30));
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
1000
SQL> select count(*) from dept;
COUNT(*)
----------
1000
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
SQL> EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.
SQL> select name, sequence# FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
NAME SEQUENCE#
------------------------------------------------------------ ----------
+DATA1/devdb/1_9_770241379.dbf 9
SQL> select name, sequence# FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
NAME SEQUENCE#
------------------------------------------------------------ ----------
+DATA1/devdb/1_9_770241379.dbf 9
-- Initially before performing the DML's the current sequence was 5 , the dictionary is created in sequence 9, so we required the sequence 5 to 9 for analyzing.
-- Here the filesystem is ASM in the target so we will make a copy to normal filesystem.
RMAN> copy archivelog '+DATA1/devdb/1_5_770241379.dbf' to '/home/oracle/sequence5.arc';
Starting backup at 04-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 instance=devdb1 devtype=DISK
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=5 recid=9 stamp=881471729
output filename=/home/oracle/sequence5.arc recid=16 stamp=881472890
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 04-JUN-15
RMAN> copy archivelog '+DATA1/devdb/1_6_770241379.dbf' to '/home/oracle/sequence6.arc';
RMAN> copy archivelog '+DATA1/devdb/1_7_770241379.dbf' to '/home/oracle/sequence7.arc';
RMAN> copy archivelog '+DATA1/devdb/1_8_770241379.dbf' to '/home/oracle/sequence8.arc';
RMAN> copy archivelog '+DATA1/devdb/1_9_770241379.dbf' to '/home/oracle/sequence9.arc';
RMAN> copy archivelog '+DATA1/devdb/1_9_770241379.dbf' to '/home/oracle/sequence9.arc';
Now scp the archive log files to the target server (UAT).
rhelrac1-> scp seq* oracle@192.168.0.30:/home/oracle/
The authenticity of host '192.168.0.30 (192.168.0.30)' can't be established.
RSA key fingerprint is ef:59:75:8b:95:5c:7f:2b:ce:56:01:e1:58:c0:f8:d0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.30' (RSA) to the list of known hosts.
oracle@192.168.0.30's password:
sequence5.arc 100% 13MB 13.2MB/s 00:01
sequence6.arc 100% 900KB 900.0KB/s 00:00
sequence7.arc 100% 12KB 12.0KB/s 00:00
sequence8.arc 100% 14MB 7.2MB/s 00:02
sequence9.arc 100% 9218KB 9.0MB/s 00:01
On the Target Server (UAT).
-- Add the logfiles from 5 to 6 to the logminer.
[oracle@standalone2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 4 00:07:51 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/home/oracle/sequence5.arc', Options=>dbms_logmnr.NEW);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/home/oracle/sequence6.arc', Options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/home/oracle/sequence7.arc', Options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/home/oracle/sequence8.arc', Options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile(LogFileName => '/home/oracle/sequence9.arc', Options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(OPTIONS =>DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*) FROM v$LOGMNr_CONTENTS WHERE seg_owner = 'MANZOOR';
COUNT(*)
----------
2003
SQL> select operation, count(*) from V$logmnr_contents where seg_owner = 'MANZOOR' group by operation;
OPERATION COUNT(*)
-------------------------------- ----------
DDL 2
INSERT 2000
SQL> select sql_redo from V$logmnr_contents where seg_owner = 'MANZOOR' and operation = 'DDL';
SQL_REDO
------------------------------------------------------------
create table emp (emp_id number, ename varchar2(30));
create table dept (dept_id number, dname varchar2(30));
=====================================
E. Patching in 11.1.0.7.
======================
1. Stop the service running in oracle home
$ srvctl stop database -d dbname
$ srvctl stop listener -n nodename
$ srvctl stop asm -n nodename
2. Stop the CRS - root user
# crsctl stop crs
3. Grant the read permission for other to /etc/init.cssd file - root user
chmod o+r /etc/init.cssd
4. Run the Preroot patch for on crs home as root user.
cd /u01/crs/PATCH/10248535
custom/scripts/prerootpatch.sh -crshome /u01/crs -crsuser oracle
5. Run the prepatch script as oracle user on crs/asm/db home.
$ cd /psu/PATCH/10248535
$ custom/scripts/prepatch.sh -crshome /u01/crs
$ custom/server/10248535/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/11.1/asm
$ custom/server/10248535/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/11.1/db
6. Apply the crs patch on crs home as oracle user.
$ export ORACLE_HOME=/u01/crs
$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
$ cd /psu/PATCH/10248535
$ opatch napply -local -oh /u01/crs -id 10248535 -invPtrLoc $ORACLE_HOME/oraInst.loc
$ opatch lsinventory -local -invPtrLoc $ORACLE_HOME/oraInst.loc
6. Apply the database patch on asm home as oracle user
a. Set the environment variable and point to asm home.
$ export ORACLE_HOME=/u01/app/oracle/product/11.1/asm
$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
$ cd /psu/PATCH/10248535
$ opatch napply custom/server/ -local -oh /u01/app/oracle/product/11.1/asm -id 10248535 -invPtrLoc $ORACLE_HOME/oraInst.loc
$ opatch lsinventory -local -invPtrLoc $ORACLE_HOME/oraInst.loc
7. Apply the database patch on db home as oracle user.
a. Set the environment variable and point to asm home.
$ export ORACLE_HOME=/u01/app/oracle/product/11.1/db
$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
$ cd /psu/PATCH/10248535
$ opatch napply custom/server/ -local -oh /u01/app/oracle/product/11.1/db -id 10248535 -invPtrLoc $ORACLE_HOME/oraInst.loc
$ opatch lsinventory -local -invPtrLoc $ORACLE_HOME/oraInst.loc
8. Run the post patch script as oracle user.
$ custom/scripts/postpatch.sh -crshome /u01/crs
$ custom/server/10248535/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/product/11.1/asm
$ custom/server/10248535/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/product/11.1/db
9. Run the post patch root script as root user.
# chmod o-r /etc/init.cssd
# cd /psu/PATCH/10248535
# custom/scripts/postrootpatch.sh -crshome /u01/crs
10. Continue the above procedure on the remaining nodes.
F. Highwater mark enque Contention "enq: HW – contention" record the high wait time in AWR report
=================================================================================================
Version : 11.1.0.7
Oracle bug : 6376915
Description :
HW enqueue contention can occur for LOB segments which are ASSM managed
as space allocation only acquires one block at a time.
With this fix ASSM lobs get a minimum number of chunks based
on the value of event 44951 (up to 1024) which should help
remove enqueue contention.
Fix :
The fix is already included in 11.1.0.7 and 10.2.0.4/10.2.0.5 but not implemented until the event is enabled
- for 10.2.0.3 Download and apply the patch for BUG 6376915:
for Spfile
ALTER SYSTEM SET EVENT='44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024' scope=spfile;
for pfile
EVENT="44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024"
G. Enable Rac.
==============
$ export ORACLE_HOME=/u01/app/oracle/product/11.1/db
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
$ make -f ins_rdbms.mk ioracle
H. Disable RAC.
===============
$ export ORACLE_HOME=/u01/app/oracle/product/11.1/db
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_off
$ make -f ins_rdbms.mk ioracle
I. Enable partitioning.
=======================
$ chopt enable partitioning
J. Disable partitioning.
========================
$ chopt disable partitioning
K. Attach an oracle home to inventory.
======================================
export ORACLE_HOME=/u01/app/oracle/product/11.1/db
cd $ORACLE_HOME/bin/oui
./attachHome.sh -silent -attachHome "CLUSTER_NODES={rhel11g1, rhel11g2}" LOCAL_NODE=rhel11g1 -invPtrLoc $ORACLE_HOME/oraInst.loc
./runInstaller.sh -silent -updateNodeList CLUSTER_NODES=rhel11g1,rhel11g2 ORACLE_HOME=$ORACLE_HOME -local -invPtrLoc $ORACLE_HOME/oraInst.loc
L. Pre-requisite for using lowerr version db with higher version crs.
=====================================================================
Grid version -11gr2 (11.2.0.2)
db version -11gr1 (11.1.0.7)
Inorder to use the lower version db with higher version crs we need
to pin all the nodes in the cluster.
We need the below to be executed on one node as root user.
# cd /u01/crs/bin
# ./crsctl pin css -n rhel11g1 rhel11g2
M. Auto startup in AIX.
a. Script to be enterd in a file eg. /etc/rc.oracle
b.this file has to be called in /etc/inittab.
M . Getting error as Below in RHEL 5
====================================
/etc/profile[40]: ulimit: pipe: is read only
solution :
if [ \$USER = "oracle" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
##ulimit -p 16384
ulimit -u 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
Patch 11gr2
1. Download the patch from Metalink.
2. Make a new directory and copy the patch to the location (Directory should be empty)
eg. $ mkdir -p /home/oracle/patch
$ cp p* /home/oracle/patch
3. For patching we can use the opatch auto utitlity to patch both GI & DB homes, this has to be run on each node.
$ cd /home/oracle/patch
$ unzip p*
$ opatch auto /home/oracle/patch -ocmrf ocm.rsp
