Oracle Database Patches

This video gives an introduction to Oracle Database patches. 

How to start a GUI session without VNC?

a)  Download  & Install "XManager", You can download a free trial version from the internet.
b) Launch the Xstart and fill in the details as below:


It opens up a window


In the Xterm window, test if the GUI is working with "xclock" utility.

MAX SCN / High SCN vulnerability

What is SCN?

This is a number that increments sequentially with every database commit and is crucial to normal Oracle database operation.  The SCN is also incremented through linked database activities.

The Vulnerability?

Some customers expressed concerns that they may be getting closer to the current maximum SCN limit faster than the data processing they are doing would warrant.

What is the Max SCN?

Max SCN is not a hard limited number. The max scn constantly increases every second.

The current Max SCN limit is the number of seconds elapsed since 1988, multiplied by 16,384. So the max scn keeps increasing constantly.

What is SCN Headroom?

The difference between the current SCN the database is using, and the current MAX SCN, is known as the SCN headroom. For almost all Oracle Databases, this headroom is constantly increasing every second.

What is the SCN Bug we are talking about?

Oracle has determined that some software bugs could cause the database to attempt to exceed the current maximum SCN value (or get closer to the limit than was warranted).

Example: When a database “A” (whose current SCN is lets say 10) has a db link to database “B”  (whose current SCN is lets say 100). When database “A” attempts to do a DML activity on one of the tables in database “B” via a DB Link, then Database “B” SCN (which is higher than A’s) is reflected in Database “A”.

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME

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

www.oracledatabase12g.com

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    5072553

SQL> select current_scn from v$database@PROD;

CURRENT_SCN

-----------

    7798262

SQL> insert into testlink@PROD values(1);

1 row created.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    7798282

b) There is also another bug found – where hot backups (alter database begin backup) causes SCN numbers to jump extremely high

Oracle released a patch to fix the SCN growth rate bug in hot backup listed as 12371955: “High SCN growth rate from ALTER DATABASE BEGIN BACKUP in 11g.”

What happens if a database does hit the Max SCN Limit?

Generally if the database does try to exceed the current maximum SCN value, the transaction that caused this event would be cancelled by the database, and the application would see an error. The next second the limit increases, so typically the application then continues with a slight hiccough in processing. However, in some very rare cases, the database does need to shut down to preserve its integrity. In no cases is data lost or corrupted.

How to check if a database is close to hitting MAX SCN?

Instructions in metalink doc:

Installing, Executing and Interpreting output from the "scnhealthcheck.sql" script [ID 1393363.1]

Fix?

Apply the Jan 2012 CPU Patch. Customers that have applied the fixes find that their SCN headroom starts to increase again.

How to enable tracing based on the type of clients connecting to database ?

Our applimation application was having performance issues. The application team engaged the DBAs to find out why the steps involving database operations were taking a long time. The 1st thing I had to do was enable tracing on these sessions to dig more into the problem. However, I noticed that in each step of its execution - the application spawned multiple database sessions. So the traditional

EXECUTE dbms_system.set_sql_trace_in_session (24,13,TRUE); would not be of much use.

I had two options in hand - a) to execute a logon trigger to enable tracing at user level (The application always connect to the database using the same user - lets say 'INFRA_USER').

b) Enable tracing based on
i)  Service, Module, and Action
ii) Client Identifier

I like the second approach.. than dealing with logon triggers.

So here is what I did..

1) First identify the Client ID, service, modules, action etc.


SQL> select sid, username, SERVICE_NAME,CLIENT_IDENTIFIER ,  module, action from v$session where username like 'INFRA_USER%'
  2  ;

   SID USERNAME     SERVICE_NA CLIENT_IDENTIFIER         MODULE             ACTION
------ ------------ ---------- ------------------------- ------------------ ----------
  2192 INFRA_USER   TEST       applmatn:14.296.88.155    JDBC Thin Client


SQL>


I can either enable tracing using the " Service, Module, and Action" or using "Client Identifier". There were several other sessions from different applications having module as "JDBC Thin Client" and SERVICE_NAME = TEST (The SID Name). So to uniquely identify my session - I used the CLIENT_IDENTIFIER instead which is "applmatn:14.296.88.155".


2) Enable tracing on the sessions having client ID applmatn:14.296.88.155

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => 'applmatn:144.226.90.35', waits => TRUE, binds => TRUE);


3) Disable tracing

Once you have sufficient information/or the problamatic query has been run, Disable tracing.

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => 'applmatn:144.226.90.35');


4) Use trcsess utility to consolidate all trace files generated and then use tkprof to analyze the trace file.
More info can be found here

How to give responsibilites of one user to another in Ebusiness Suite?

Recently I came across a requirement where I had to create a user (lets say NEW_USER) similar to an existing user (lets say FINANCE_USER). After creating the new users, I realized that the FINANCE_USER had over 100 responsibilities - which had to be given to 35 new users. Assigning responsibilities one by one seemed like a mountainous task.

So here is how we can assign responsibilities using FND_LOAD:

1) Download using FNDLOAD
FNDLOAD apps/appspassword 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct SOURCE_USER.ldt FND_USER USER_NAME='SOURCE_USER'

2) Copy the LDT file to NEW_USER.ldt
 cp SOURCE_USER.ldt NEW_USER.ldt

3) Edit the NEW_USER.ldt file to change the below entries:

BEGIN FND_USER "NEW_USER"
EMAIL_ADDRESS = "New_Users_Email_id"

4) Upload using FNDLOAD
FNDLOAD apps/appspassword 0 Y UPLOAD @FND:patch/115/import/afscursp.lct NEW_USER.ldt FND_USER USER_NAME='NEW_USER'

How to retrieve workflow mailer password?

The workflow mailer password, SMTP address and other configurations related to Workflow are stored in $XDO_TOP/resource/xdodelivery.cfg
So, if you ever loose your workflow mailer password - this is where you can find it..

Convert database from NON-ASM to ASM

CONVERTING DATABASE FROM NON-ASM to ASM
=======================================

1) Backup the database.

2) Shut down and startup the database in nomount mode.

$ sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 1 12:52:44 2010

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

Enter user-name: /as sysdba
Connected to an idle instance.



SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2127408 bytes
Variable Size 1610615248 bytes
Database Buffers 1.4361E+10 bytes
Redo Buffers 60174336 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


$

3) Copy the non-asm files to asm diskgroup.

[oracle]$ rman nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jan 1 12:57:14 2010

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

RMAN> connect target

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 01-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=383 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00379 name=/u10/oracle/data/TEST/undo01.dbf
output filename=+DATA/TEST/datafile/apps_undots1.257.707144559 tag=TAG20100101T130234 recid=1 stamp=707144573
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00353 name=/u10/oracle/data/TEST/system01.dbf
output filename=+DATA/TEST/datafile/system.258.707144581 tag=TAG20100101T130234 recid=2 stamp=707144595
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00406 name=/u10/oracle/data/TEST/a_txn_ind01.dbf
output filename=+DATA/TEST/datafile/apps_ts_tx_idx.259.707144605 tag=TAG20100101T130234 recid=3 stamp=707144616
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
......
.......
input datafile fno=00009 name=/u10/oracle/data/TEST/odm.dbf
output filename=+DATA/TEST/datafile/odm.298.707145081 tag=TAG20100101T130234 recid=42 stamp=707145080
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u10/oracle/data/TEST/owad01.dbf
output filename=+DATA/TEST/datafile/owapub.299.707145081 tag=TAG20100101T130234 recid=43 stamp=707145081
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-JAN-10

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/TEST/datafile/system.269.707144795"
datafile 2 switched to datafile copy "+DATA/TEST/datafile/system.270.707144811"
datafile 3 switched to datafile copy "+DATA/TEST/datafile/system.271.707144827"
.....
....
datafile 406 switched to datafile copy "+DATA/TEST/datafile/apps_ts_tx_idx.259.707144605"
datafile 407 switched to datafile copy "+DATA/TEST/datafile/apps_ts_seed.266.707144751"

RMAN>

4) Switch tempfiles

RMAN> run {
2> set newname for tempfile 1 to '+DATA';
3> set newname for tempfile 2 to '+DATA';
4> switch tempfile all;
5> }

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to +DATA in control file
renamed temporary file 2 to +DATA in control file

RMAN> Alter database open;

database opened

5) Now, restore controlfile from non-asm to asm

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area 1073741824 bytes

Fixed Size 2089336 bytes
Variable Size 427822728 bytes
Database Buffers 629145600 bytes
Redo Buffers 14684160 bytes

RMAN> restore controlfile to '+DATA' from '/u01/oracle/oradata/TEST/db/apps_st/data/cntrl01.dbf';

Starting restore at 01-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=384 devtype=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 01-JAN-10

RMAN> shutdown immediate

Oracle instance shut down

RMAN> exit


Recovery Manager complete.


6) Using asmcmd find out the complete path to the controlfile and edit the pfile/spfile to the path in the asm.


7) Add multiple logfile members to a group and drop non-asm files.

select memeber from v$logfile;

/u01/oracle/oradata/TESTlog02a.dbf
/u01/oracle/oradata/TESTlog02b.dbf
/u01/oracle/oradata/TESTlog01a.dbf
/u01/oracle/oradata/TESTlog01b.dbf

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;

Database altered.

SQL> /

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;

Database altered.

SQL> /

Database altered.

/u01/oracle/oradata/TESTlog02a.dbf
/u01/oracle/oradata/TESTlog02b.dbf
/u01/oracle/oradata/TESTlog01a.dbf
/u01/oracle/oradata/TESTlog01b.dbf



SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02a.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/TESTlog02b.dbf'
ORA-00312: online log 2 thread 1: '+DATA/TEST/onlinelog/group_2.305.707145955'
ORA-00312: online log 2 thread 1: '+DATA/TEST/onlinelog/group_2.306.707145963'


SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog01a.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog01b.dbf';

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE +DATA/TEST/onlinelog/group_1.303.707145913 NO
1 ONLINE +DATA/TEST/onlinelog/group_1.304.707145943 NO
2 ONLINE +DATA/TEST/onlinelog/group_2.305.707145955 NO
2 ONLINE +DATA/TEST/onlinelog/group_2.306.707145963 NO

8) Restart the database.