Oracle Apps DBA

Oracle eBusiness 12.2 clone : OHS config fails for UpdateOhsT2PMovePlan.java:1063

2017-06-29T02:36:00.002-07:00

While running clone we faced this issue. Multiple runs but every-time it failed at OHS config.
Under txkSetOHSConfig directory provison_<timestamp>.log has following error.

Updating /YMS-UI-context-root and setting WeblogicCluster to : u060supcw801.kroger.com:6801Inside run context
StackTrace: java.lang.NullPointerException at oracle.apps.fnd.txk.util.UpdateOhsT2PMovePlan.updateModWLSDirectives(UpdateOhsT2PMovePlan.java:812) at oracle.apps.fnd.txk.util.UpdateOhsT2PMovePlan.updateModWLSConfig(UpdateOhsT2PMovePlan.java:721) at oracle.apps.fnd.txk.util.UpdateOhsT2PMovePlan.updateModWLSConfig(UpdateOhsT2PMovePlan.java:698) at oracle.apps.fnd.txk.util.UpdateOhsT2PMovePlan.updateModWLSConfig(UpdateOhsT2PMovePlan.java:698) at oracle.apps.fnd.txk.util.UpdateOhsT2PMovePlan.processMoveplan(UpdateOhsT2PMovePlan.java:631) at oracle.apps.fnd.txk.util.UpdateOhsT2PMovePlan.main(UpdateOhsT2PMovePlan.java:1063)

Solution:

It was not a recommended way but we had to solve it asap. So I looked for start of the error (just prior to that) in the log.
It was completing until the point /YMS-UI-context-root.

Now searched same string in the comn/clone/FMW/OHS/moveplan.xml.
Took a backup of it and removed the section which was just next to /YMS-UI-context-root. For us this was /console with further details host,port etc. so removed it totally.
p.s. Stick to the indentation and identifiers to remove the section exactly point to point. If not done correctly this may say invalid moveplan.xml in next execution.

So post removing the particular part from moveplan.xml I executed the clone again, and it completed with some warnings. Some services did not come up correctly but we were able to solve those small issues and bring application up.

Reason:
Later found that we had some customization on console URL which was causing the issue. Customization should be removed, or this is supposed to throw error.

[ERROR] Unable to continue as an existing adop session was found.

2017-02-06T20:27:00.000-08:00

Using ADOP on 12.2.5 environment. Last session had some issues so we aborted the same. the adop -status was clearly showing aborted.

Node Name Node Type Phase Status Started Finished Elapsed
--------------- ---------- --------------- --------------- -------------------- -------------------- ------------
xxxxxxxxx101 master PREPARE SESSION ABORTED 2017/02/06 15:52:37 6:55:14
APPLY SESSION ABORTED
FINALIZE SESSION ABORTED
CUTOVER SESSION ABORTED
CLEANUP NOT STARTED

So now cleanup should be run and I tried the same. It gave me following messages.

Validating system setup.
Node registry is valid.

Checking for existing adop sessions.
[ERROR] Unable to continue as an existing adop session was found.

[STATEMENT] Please run adopscanlog utility, using the command
"adopscanlog -latest=yes"

Surprisingly the cleanup was not proceeding and checking logs with above command and the directories was giving nothing

Even checked from database side. The status was already "C" so nothing could be done here.

SQL> select ADOP_SESSION_ID , abort_status, prepare_status,apply_status , status, edition_name from ad_adop_sessions where ADOP_SESSION_ID=13;

ADOP_SESSION_ID A P A S EDITION_NAME
--------------- - - - - ------------------------------
13 Y R N C

Digging more found following processing in execution. Looks like were started to abandon the patch but have still been there.

applmgr 18809034 20381948 58 22:57:32 - 0:00 /..../bin/adzdoptl.pl phase=prepare,apply patches=24390794,23708596 abandon=yes restart=no
applmgr 20381948 1 120 15:49:28 - 71:52 /..../bin/adzdoptl.pl phase=prepare,apply patches=24390794,23708596 abandon=yes restart=no

So killed these processes and tried cleanup phase again and it completed successfully.

Oracle eBusiness 12.1.3 to 12.2.5 upgrade - Part 3 (Upgrade to 12.2 - B)

2016-08-04T01:04:00.001-07:00

Finishing 12.2 Upgrade

1. Back up the binaries and database.

2. Cleanup Nodes info (Optional).

SQL>exec fnd_conc_clone.setup_clean ;

3. Verify env variables on DB

LD_LIBRARY_PATH = $ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib
ORACLE_SID = <instance name for current database node>
TNS_ADMIN=$ORACLE_HOME/network/admin/<CONTEXT_NAME>
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/appsutil/jre/bin:$PATH
PERL5LIB=$ORACLE_HOME/perl/lib/5.14.1:$ORACLE_HOME/perl/lib/site_perl/5.14.1:$ORACLE_HOME/appsutil/perl

4. Run autoconfig on database

Make appsutil again from apps using
perl <AD_TOP>/bin/admkappsutil.pl

Unzip on Databzse
unzip -o appsutil.zip

or copy database context file
cd <RDBMS ORACLE_HOME>/appsutil/bin
perl adbldxml.pl

Run Autoconfig
<RDBMS ORACLE_HOME>/appsutil/bin/adconfig.sh contextfile=<context file>

5. Run autoconfig on App.

Drop table ADX_PRE_AUTOCONFIG from APPS schema, will be recreated during autoconfig.
cd <AD_TOP>/patch/115/sql
sqlplus APPS/$APPS_PASS @txkDropAdxPreAutoConfig.sql

Run AutoConfig on all the Application Tier nodes using the following Commands

$ADMIN_SCRIPTS_HOME/adautocfg.sh

6. Start Rapidwiz and run Configuration.

Provide Context File, then in further screens provide inputs as asked.

This will start configuration post confirmation. At the end you will get following screen for validation.

Here we are done with 12.2 Upgrade. But there is a major step due is ADOP enablement.
ADOP is the new feature introduced with 12.2 making it too much different for earlier version. It is AD Online Patching, and so it needs specific versions of Database to support Edition Based Redefinition.

Following are the steps to enable Adop, which is also a part of Upgrade activity.

Enable ADOP

1. Apply E-Business Suite:Online Patching Readiness and GSCC Report patch 22071026:R12.AD.C.

2. Consider Localization if Any.

3. Run Readiness Scripts. (Doc ID 1531121.1)

sqlplus system @$AD_TOP/sql/ADZDPSUM.sql
sqlplus system @$AD_TOP/sql/ADZDPMAN.sql
sqlplus system @$AD_TOP/sql/ADZDPAUT.sql

Resolve all the issues reported by scripts.

4. Fix Data Dictionary or Time Stamp mismatch.
$AD_TOP/patch/115/sql/adzddtsfix.sql (Following the usage instructions provided in it).

Run the $AD_TOP/sql/ADZDDBCC.sql script to identify whether the data dictionary corruption is still present.

In case yes, follow the doc 1531121.1.

5. Verify the tablespace Freespace.

Set the fs1 env.
Set HOSTNAME env variable without domain name.
Run adzdreport.pl.

Recommended for system to have 25 GB free space and APPS_TS_SEED to have 5 GB

6. Run the Online Patching Enablement - Status Report

sqlplus apps @$AD_TOP/sql/ADZDEXRPT.sql

7. Make sure Application Services are down.

8. Apply ADOP enablement patch. 13543062:R12.AD.C.

adpatch options=hotpatch,forceapply

Monitor the progress using sqlplus apps @$AD_TOP/sql/ADZDSHOWDDLS.sql

9. Check for invalids and compile.

exec sys.utl_recomp.recomp_parallel(n);

10. Re Run the online patching enablement report.

sqlplus <Apps Username> @$AD_TOP/sql/ADZDEXRPT.sql

Re run readiness reports for errors.

sqlplus system @$AD_TOP/sql/ADZDPSUM.sql
sqlplus system @$AD_TOP/sql/ADZDPMAN.sql
sqlplus system @$AD_TOP/sql/ADZDPAUT.sql

Run Compliance Checker again.

sqlplus <Apps Username> @$AD_TOP/sql/ADZDDBCC.sql

11. Resplice Localizations and perform additional activities.

12. Check following DB parameters.

service_names=%s_dbSid%,ebs_patch
recyclebin=off
_SYSTEM_TRIG_ENABLED=true

With this we are done with 12.2 Upgrade. This point onward whatever patches will go to the system will go using adop utility. So will be the 12.2.5 related patches and upgrade driver.

For 12.2.5 Upgrade please click on below link.

Oracle eBusiness 12.1.3 to 12.2.5 upgrade - Part 2 (Upgrade to 12.2 - A)

2016-08-04T00:22:00.004-07:00

Using 12.2 startCD 51 which has been latest by the time of upgrade.

Note:- There can be a lots of variation in terms of steps specific to your own environment. Please go through the Upgrade guide, release notes and relevant Oracle Support Notes before finalizing your upgrade steps.

Pre 12.2 Tasks

1. SubTypes Data Corruption Caused by csitxnst.ldt (Doc ID: 1681308.1)

Run scripts pre app upgrade.

- run dbmsxdbschmig.sql, prvtxdbschmig.sql, utlrp.sql scripts:

sqlplus / as sysdba

SQL> alter session set current_schema=SYS;

SQL> @?/rdbms/admin/dbmsxdbschmig.sql

SQL> @?/rdbms/admin/prvtxdbschmig.plb

SQL> @?/rdbms/admin/utlrp.sql

- run adgrants.sql:

sqlplus / as sysdba

SQL> @?/rdbms/admin/adgrants.sql [APPS schema name]

2. Backup the environment

3. Backup CUSTOM.pll

4. Check Guest account for user_id. Should be 6.

SQL> select user_name , user_id from fnd_user where user_name like 'GUEST';

5. Drop Custom Event Triggers.

Run alrdtrig.sql for every custom schema and verify and drop triggers after taking backup.

6. Check for module specific tasks from upgrade guide.

Financial and procurement Tasks, Sub Ledger Accounting, SCM Task, Order Management etc.

Install JRE on database tier.

7. Add SQLNET.ALLOWED_LOGON_VERSION_SERVER to sqlnet.ora Ifile on both the nodes, decide value for conditions given in upgrade guide.

Install 12.2 File-system

Start rapidwiz, go for upgrade and select for create upgrade file system.

Here you will be asked for many details related to ports, servers, paths and passwords.
Enter those and after initial validation it will come to following screen.

Proceeding with next will install the file system for 12.2 in the given path.

Once the installation is successful, following screen will ask to validate and next button will show details.

12.2 Upgrade

1. Shutdown application if anything is running.

2. Set FAILED_LOGIN_ATTEMPTS to UNLIMITED for all eBusiness related schemas.

3. Enable Maintenance Mode

4. Apply AD Patch 10117518
Check for Patch readme and follow instructions for pre tasks, merge and post tasks.Follow Doc: Oracle E-Business Suite Release Notes, Release 12.2 (Doc ID 1320300.1)
Section 3.

5. Download and merge preinstall patches.

Follow Doc: R12.1 and 12.2 Oracle E-Business Suite Preinstall Patches Report [Video] (Doc ID 1448102.2) for downloading patches.

6. Apply Following the Doc 1320300.1, exactly step by step. This will include preinstall, merge and apply steps of merged 12.2 Driver.

7 Disable Maintenance Mode.

Follow Link below for Next Steps of Upgrade

Oracle eBusiness 12.1.3 to 12.2.5 upgrade - Part 3 (Upgrade to 12.2 - B)

Oracle eBusiness 12.1.3 to 12.2.5 upgrade - Part 1 (Finalizing Path and DB Upgrade)

2016-08-03T23:41:00.001-07:00

We have to first be on 12.2 to move to any version ahead of it.

The supported paths till the time I did the upgrade were as below.

Direct upgrade for 11.5.10.2 to 12.2

Direct upgrade for 12.0 or 12.1 to 12.2.

The database supported were 11.2.0.4 or 12c.

The first step has been the database upgrade. The upgrade is simple database upgrade with DBUA so I am listing just the major steps to be taken care with EBS.

Please go through Interoperability notes for exact steps for your environments.

1. Install the Database Binaries.

2. Update to latest OPatch

3. Follow the interoperability notes to apply additional DB patches. This will save time later.

4. Shutdown application.

5. Drop SYS.ENABLED$INDEXES

SQL> drop table sys.enabled$indexes;

6. Remove the MGDSYS schema

$ sqlplus "/ as sysdba" @?/md/admin/catnomgdidcode.sql

7. Execute Dictionary stats.

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

8. In case of RAC, Run Cluster Verify.

$ cd $ORACLE_HOME/bin

$ cluvfy stage -pre dbinst -upgrade

-src_dbhome <11gHOME>

-dest_dbhome <12cHOME>

-dest_version 12.1.0.2.0

9. Run pre upgrade Script.

$ sqlplus / sysdba

SQL> @preupgrd.sql;

Fix if there are any issue.

10. Upgrade DST in new home.

Check existing DST and default DST that comes with new Orale 12c home.

SQL> SELECT * FROM v$timezone_file;

FILENAME VERSION

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

timezlrg_23.dat 23

Apply DST patch in new home,

Applying the DSTv23 update for the Oracle Database (Doc ID 1907093.1)

Sec. 2) Oracle RDBMS 12cR1 : Applying the RDBMS DSTv23 patch 19396455 on Oracle RDBMS 12cR1 ( 12.1.0.1 and 12.1.0.2) .

11. Purge Audit records

SQL> truncate table sys.aud$;

SQL> truncate table sys.fga_log$;

12. Enterprise Manager Database Control

SQL> rdbms/admin/emremove.sql;

13. Copy the old tns_admin to new tns_admin, if you do not want to change anything in ports.

14. Make sure application has been shutdown.

15. Start the upgrade.

16. Run datapatch as post step of patches applied.

$ORACLE_HOME/OPatch/datapatch

17. Change init parameters following the interoperability notes.

18. Modify listeners to move to new Oracle Home.
srvctl stop listener -l <ListenerName>

srvctl modify listener -l <ListenerName> -o <12cHome>

srvctl start listener -l <ListenerName>

19. Copy script from $APPL_TOP/admin/adgrants.sql and run as sysdba

20. CTXSYS procedure

copy $AD_TOP/patch/115/sql/adctxprv.sql to database server.

Run following from the database server where the file is placed.
$ sqlplus apps/$appspass @adctxprv.sql $SYSTEM_PASS CTXSYS

Then connect as sysdba and run following.
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');

21. Workflow Rule set Validation.Run $FND_TOP/patch/115/sql/wfaqupfix.sql from application server.

$ sqlplus [APPS user]/[APPS password] @wfaqupfix.sql [APPLSYS user] [APPS user]

22. Run adstats

$ sqlplus "/ as sysdba"

SQL> alter system enable restricted session;

SQL> @adstats.sql

$ sqlplus "/ as sysdba"

SQL> alter system disable restricted session;

SQL> exit;

23. Implement and Run autoconfig.

Create appsutil.zip using perl $AD_TOP/bin/admkappsutil.pl

Unzip that on Database Oracle Home.

24. Copy orai18n.jar

$ cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/jdk/jre/lib/ext/

After creating the XML context file, ensure the variable s_jretop points to $ORACLE_HOME/jdk/jre and is not altered to any other value.

25. Create MGDSYS schema.
$ sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql

25. Start Application and Validate.

The DB upgrade completes here, there may be some patches for 12c and for pre 12.2 need to be applied on same application here.

Also follow relevant documents for database, OS, Platform/Storage and eBusiness versions for exact patches to be applied on DB.

Click on below link for Next Part.

Oracle eBusiness 12.1.3 to 12.2.5 upgrade - Part 2 (Upgrade to 12.2)

IE11 - Changing encoding in browser throws Authentication Failed

2016-03-10T02:21:00.000-08:00

For some of our eBusiness users who generate report in languages other then English, by default it used to open in UTF-8 or Western European, and there Chinese or Japanese characters used not be visible.

For IE8 they used to change encoding from View> Encoding > Japanese/Chinese etc.

But IE 11 onward it started failing with error Authentication Failed.

Solution:

Go to Tools > Internet Options

In General Tab open Settings.

In Website Data Settings > Temporary Internet Files > Check for newer versions of stored pages:

Change the setting to Never, and press OK.

And now encoding can be changed locally as many times as desired.

Oracle Grid Infrastructure Upgrade to 12C using OUI with additional patches (Cluster upgrade 11.2.0.3 to 12.1.0.2 - tricky approach)

2016-01-06T05:55:00.000-08:00

In our recent cluster upgrade, we moved from 11.2.0.3 to 12.1.0.2.
The upgrade approach we chose was simple using OUI, but with some bugs we hit in available installable, we were asked to go for fixes as well as latest available patch-sets.

The approach should be to install software and then apply patches. Then start upgrade manually or using OUI. Or other way upgrade and apply patches.
But trying all this were appearing time taking and troublesome.

We kept the same approach for upgrade and path to upgrade is moved a little bit with couple of steps introduced in mid.

1. Run OUI with install and upgrade.
2. Hold it post install and do not press next to go to configure.
3. Apply patches from other window on recently installed software home.
4. Press next to let OUI go and do configuration.

With above approach the upgrade went pretty smooth in one go. Following are the detailed step for the same.

1. Verify grid with cluster verify utility.
./runcluvfy.sh stage -pre crsinst -upgrade -rolling -src_crshome /d00/app/11.2.0/grid -dest_crshome /d00/app/12.1.0/grid -dest_version 12.1.0.2.0 -fixup –verbose

In case of any issues this generates a fixup script. Running that from root solves the issue.

2. Keep on pressing next with your desired options, and at the window for batches, preferable divide task in batches.

3. In case of any issues shown in pre requisites check. Solve the same and move ahead.

4. Let the installation go and hold this when this pops up for running configuration scripts. (For me this appeared around 81% and in steps installation went successful and it is about to start the configuration script for Batch1 ).

5. Keeping the installtion window as it is, I logged in from other session. There I updated opatch to latest version available, created response file and also applied few patches as required.

$ unzip p6880880_121010_Linux-x86-64.zip -d <12C_ORACLE_HOME>

$ <12C_ORACLE_HOME>/OPatch/ocm/bin/emocmrsp -no_banner -output <Response File Name With Path>

$ <12C_ORACLE_HOME>/OPatch/opatch apply -oh <12C_ORACLE_HOME> -local <PATCH_PATH>/<patch_no> -ocmrf <Response File Name With Path>

6. Once done you can verify the patches with opatch lsinventory. And then you can go to the already open screen of OUI and press Yes there to move with upgrade. Later go with desired inputs as and when asked.

7. This will do execution of batch scripts and configuration on nodes one by one, and then Management DB creation. You will not be asked for any more inputs if everything goes smooth, until the below screen appears.

Thus upgrade completed quite fast as this saved time in applying patches as well, and it was smooth with OUI as it supposed to be.

RMAN : ORA-01861: literal does not match format string

2015-12-03T02:44:00.000-08:00

The issue occurred for some of the databases around the month end.
The backup started failing with same message and even connecting to database or catalog, no rman command was working.

$rman target / catalog=<catalog details>

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Dec 1 07:40:36 2015

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

connected to target database: ********
connected to recovery catalog database

RMAN> resync catalog;

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 12/01/2015 07:40:55
ORA-01861: literal does not match format string

RMAN>

Issue:

Taken the dump of controlfile with following commands.

SQL> Alter session set tracefile_identifier='dump';

SQL> alter session set events 'immediate trace name controlf level 9';

The dump showed something like following.

RECID #49661 Recno 1277 Record timestamp 11/30/15 12:45:55 piece #1 copy #1 pool 0
Backup set key: stamp=897133568, count=46318
V$RMAN_STATUS: recid=140735545929200, stamp=140735545929192
Flags:
Device: DISK
Handle: *******
Media-Handle: <Handle Name>
Comment:
Tag: ********
Completion time 11/31/15 01:37:53

The point to notice here was the date i.e. 31 Nov. This is a bug for database versions 10.2.0.4 and 11.0.2.1 and oracle has patches for them.
But the problem here is existing malicious backup which are not allowing to proceed.

To resolve those following were the step taken.

Check the target database for following.

SQL> select recid, stamp, handle, set_stamp, set_count, piece#
from v$backup_piece
where recid in (<recId>);

This can be recid for handle name or anything from above list to identify the backups taken on a malicious date.

Once we have the details. Take backup of controlfile before proceding futher.

SQL> alter database backup controlfile to '/tmp/backupcontrol.ct'

SQL> exec sys.dbms_backup_restore.deleteBackupPiece(recid, stamp, 'handle',set_stamp, set_count, piece#);

Note:- Need to run above for all backup pieces, and it will delete the entries from v$rman_status as well as backup piece from disk.

Now the rman can work without connecting catalog, but the catalog still throws the same error. Even resync catalog too fails with same error.

Later following commands were run on target database.

NOTE:- you loose all earlier backup information after following activities.

SQL> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(19); #delete piece

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13); #active piece

PL/SQL procedure successfully completed.

SQL>

Post the activity, there are two options.(you may try both in some cases).

1. Unregister database and register database in catalog database.

2. Connect catalog schema using sqlplus and run following.

SQL> update dbinc set high_do_recid =0 where db_name = '<your target's DB_NAME here>';
SQL> commit;

Now connect to rman using target and catalog, and do a resync catalog.

$rman target / catalog=<catalog details>

RMAN> resync catalog;

Finally this came back to a state where backups can work with catalog using existing scripts.

ORA 15032: ORA 15075: While adding new disk to ASM Disk Group

2015-02-25T01:23:00.000-08:00

When adding a new disk to an ASM disk group I found following error.

Failed to commit: ORA 15032: not all alterations performed
ORA 15075: disk(s) are not visible cluster-wide

I looked back and found that the scandisks was not done on all the nodes of cluster.

On all the database nodes did following.

sudo /etc/init.d/oracleasm scandisks
[sudo] password for oracle:
Scanning the system for Oracle ASMLib disks: [ OK ]

Tried adding disk again and it went through smoothly.

Moving SQL Profiles from one database to other in 11G.

2014-09-03T02:58:00.000-07:00

Following the SQL truing advisor we implemented some profiles to particular SQLs in our database.

That significantly improved the performance but the challenge is to apply the same to all the environments.

Oracle provides DBMS_SQLTUNE with multiple options to perform related tasks.

Following are the steps we followed.

1. Logion with SYSTEM and create a staging table for profile.

SQL> EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name => 'PROFILE_STGTAB');

2. Check the profile name you need to move and package that in the staging table.

SQL> select name, status from dba_sql_profiles where name like 'SYS_SQLPROF_01483a%';

NAME STATUS

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

SYS_SQLPROF_01483ab3b1530003 ENABLED

SYS_SQLPROF_01483aad5f6f0002 ENABLED

SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_01483a%', staging_table_name => 'PROFILE_STGTAB');

% is the wildcard, giving only % may help packaging all the profiles.

3. Export the staging table.

4. Import the staging table to destination.

5. Unpack the profiles from staging table.

This will overwrite existing profiles if replace will be TRUE in below command, or will throw error.

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_01483a%', replace => FALSE, staging_table_name => 'PROFILE_STGTAB');

6. Verify status on Destination.

SQL> select name, status from dba_sql_profiles where name like 'SYS_SQLPROF_01483a%';

NAME STATUS

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

SYS_SQLPROF_01483ab3b1530003 ENABLED

SYS_SQLPROF_01483aad5f6f0002 ENABLED

ORA-01031: insufficient privileges while doing / sysdba

2014-02-07T04:27:00.000-08:00

I copied one oracle home to some other server/mount and did the clone of binaries.
After completion when I was trying to log in to SQL as sysdba to create database here. There I found following message.

$sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 7 05:12:22 2014

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

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

Looking like some permission issue. Checked the user and OS permission of files, which were looking OK.
SQLNET.ORA too looked ok, then found following.

Reason:- Running clone on binaries has overwritten $ORACLE_HOME/rdbms/lib/config.c, and it was missing group values.

#define SS_DBA_GRP ""
#define SS_OPER_GRP ""

Solution:- Changed it to

#define SS_DBA_GRP "oinstall"
#define SS_OPER_GRP "oinstall"

Then run $ORACLE_HOME/bin/relink.

On completion,

$sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 7 07:02:55 2014

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

Connected to an idle instance.

SQL>

Worked well.

Autoconfig - ERROR: Could not retrieve mount point for product top

2013-09-20T00:39:00.000-07:00

While running autoconfig on my 11.5.10 eBusiness environment I found following error message.

Using Context file : <File Path>.........
Context Value Management will now update the Context file
ERROR: Could not retrieve mount point for product top - <Custom Top Path>

Checking that all, I found that our custom top (for which this was failing) was added to context file with other product tops.

<COMMON_TOP oa_var="s_com">.......path..........</COMMON_TOP>
<CUSTOM_TOP oa_var="s_hcttop" oa_type="PROD_TOP" oa_enabled="FALSE">........path...</CUSTOM_TOP>

Solution:-

I simply removed the CUSTOM TOP entry from context file and tried running autoconfig again. And yes it worked.

ORA-39181: Only partial table data may be exported due to fine grain access control

2013-07-26T01:44:00.002-07:00

While running datapump on some of the schemas I faced following error.

ORA-39181: Only partial table data may be exported due to fine grain access control on <owner>.<table>.
A direct hit Oracle Note "422480.1" says it is caused if an unprivileged user who tries to export a table with a fine grain access control policy applied.
I have been using SYSTEM for export, and that doesn't look under privilege user.

But for such case, whatever the user we are using to run export/import.

SQL> Grant Exempt Access Policy to <User>;

In my case it was

SQL> Grant Exempt Access Policy to System;

Do the export/import task and again do not forget to

SQL> Revoke Exempt Access Policy from <User>;

It's usually not advisable to keep it granted, until unless you have a specific admin user for import - export.

"ORA-00245: control file backup failed; target is likely on a local file system" Backup fails on oracle 11.2 RAC database

2013-04-25T01:49:00.002-07:00

On a multi node RAC database we were occasionally facing issue with RMAN backup.

RMAN> 2> 3> 4> 5>
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of allocate command at 04/25/2013 04:00:30
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 04/25/2013 04:00:30
ORA-00245: control file backup failed; target is likely on a local file system

The issue is intermittent and does not appear every time, and so we are not sure if this is actually some issue with configuration.

Reason:-

Oracle [Support Note ID 1472171.1] says that snapshot control file should be on shared location.

Solution:-

I logged into RMAN from each instance and checked the configuration

RMAN> show all;

RMAN> .
.
.
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '<oracle_home_dbs>/snapcf_DB01.f'; # default

Thus in different instances I found the value is different as snapcf_DB01.f/snapcf_DB02.f etc, also noticed "#default" at the end.

Though we did not have a shared location for oracle home, the path is similar on our servers, thus making rest of the path same except the DB01.f part.

So at the place of making it on shared location we just gave it a common name, by just giving following command from any one node.

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '<oracle_home_dbs>/snapcf_<SID>.f';

And running at one place makes the change working at all the instances. (its only default that can come different or else it should be same if you have common control files for all the instances).
Now not having the shared snapshot controlfile, but a same name is making it work and issue resolved.

Manual database upgrade to oracle 11g on windows

2012-04-11T00:08:00.004-07:00

DBUA is what mostly used for for upgrade specially on windows. That is good and oracle recommended option. But for some reason I chose to do a manual upgrade on windows.

Starting with that I had database 10.2.0.3 running on windows server.

First of all I need to have all the details of current state of database. Below is what all I checked.

set ORACLE_SID=TJAIN

sqlplus ‘/ as sysdba’
SQL> show parameter pfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      G:\ORACLE\PRODUCT\10.2.0\DB_1\
                                                 DATABASE\SPFILETJAIN.ORA

SQL> create pfile='G:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITTJAIN.ORA' from spfile;
File created.

SQL> show parameter dump
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      G:\ORACLE\ADMIN\TJAIN\BDUMP
core_dump_dest                       string      G:\ORACLE\ADMIN\TJAIN\CDUMP
max_dump_file_size                   string      20480
shadow_core_dump                     string      partial
user_dump_dest                       string      G:\ORACLE\ADMIN\TJAIN\UDUMP

SQL>show parameter control
control_files                        string      H:\ORADATA\TJAIN\TJAIN_C
                                                 ONTROL01.DBF, H:\ORADATA\TELTO
                                                 BAW\TJAIN_CONTROL02.DBF, G:
                                                 \ORADATA\TJAIN\TJAIN_CON
                                                 TROL03.DBF

Now we need to install oracle 11g software on windows. Installation is GUI based and a simple step by step approach.

After installation of oracle home you will find that in your path variable the location "11gOracleHOME/bin" comes prior to 10g one, so all executable you will use will be picked from this location.

To use 10g related binaries you may need to use the complete path of executable.

Pre Upgrade

Copy utlu112i.sql from 11g home to some location and run it in 10G home.

SQL> spool 'G:\upgrade_logs\utlu112i.log'

SQL> @utlu112i.sql

See the log and check the suggestions. In my case below were some messages

Database:

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

--> name: TJAIN

--> version: 10.2.0.3.0

--> compatible: 10.2.0.3.0

--> blocksize: 16384

--> platform:

--> timezone file: V3

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

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 801 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 400 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 992 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 60 MB

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

Flashback: ON

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

FlashbackInfo:

--> name: H:\oracle\oradata\teldobaw

--> limit: 30720 MB

--> used: 9667 MB

--> size: 30720 MB

--> reclaim: 876.072265625 MB

--> files: 341

WARNING: --> Flashback Recovery Area Set. Please ensure adequate disk space in recovery areas before performing an upgrade.

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

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

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

--> log_archive_start 10.1 DEPRECATED

--> cursor_space_for_time 11.1 DEPRECATED

--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"

--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"

--> cursor_space_for_time 11.2 DEPRECATED

--> plsql_native_library_dir 11.2 OBSOLETE

--> plsql_native_library_subdir_ 11.2 OBSOLETE

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

Components: [The following database components will be upgraded or installed]

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

--> Oracle Catalog Views [upgrade] VALID

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

Miscellaneous Warnings

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

WARNING: --> Database is using a timezone file older than version 14.

.... After the release migration, it is recommended that DBMS_DST package

.... be used to upgrade the 10.2.0.3.0 database timezone version

.... to the latest version which comes with the new release.

WARNING: --> Database contains INVALID objects prior to upgrade.

.... The list of invalid SYS/SYSTEM objects was written to

.... registry$sys_inv_objs.

.... The list of non-SYS/SYSTEM objects was written to

.... registry$nonsys_inv_objs.

.... Use utluiobj.sql after the upgrade to identify any new invalid

.... objects due to the upgrade.

.... USER SYSTEM has 2 INVALID objects.

WARNING: --> EM Database Control Repository exists in the database.

.... Direct downgrade of EM Database Control is not supported. Refer to the

.... Upgrade Guide for instructions to save the EM data prior to upgrade.

WARNING: --> Your recycle bin is turned on and currently contains no objects.

.... Because it is REQUIRED that the recycle bin be empty prior to upgrading

.... and your recycle bin is turned on, you may need to execute the command:

PURGE DBA_RECYCLEBIN

.... prior to executing your upgrade to confirm the recycle bin is empty.

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

Recommendations

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

.SQL> spool off;

After seeing this, I decided to perform below activities.

SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE UPPER(name) ='EVENT' AND isdefault='FALSE';
no rows selected
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';
no rows selected
SQL> select * from v$timezone_file;
FILENAME                VERSION
-------------------- ----------
timezlrg.dat          3

SQL> select count(1) from dba_objects where status = 'INVALID';
COUNT(1)
----------
         2

SQL> select distinct status from dba_indexes;
VALID
N/A

Create New Service

Open services.msc, there we check the properties of the service.

Check properties of respective oracle service, and this will be show path of oracle binary in 10g oracle home. For new database we need to delete and create a new service with below command.

Stop the service containing ORACLE and <SID>.

oradim -DELETE -SID TJAIN
oradim -new -SID TJAIN -startmode manual -pfile G:\Oracle11g\product\11.2.0\dbhome_1\database\initTJAIN.ORA

Now if you will see the properties the path of oracle executable must be from 11g home. The status should be started for particular service "OracleServiceTJAIN".

Now create the new parameter file for 11g database from the pfile created earlier, changing the parameters as suggested earlier in preupgrade script.

If the flashback is ON.

SQL> Startup mount pfile=initTJAIN.ORA
SQL> alter database flashback off;
SQL> shutdown immediate;

Starting the upgrade

SQL> startup upgrade pfile=initTJAIN.ORA
SQL> spool 'G:\upgrade_logs\catupgrd.log'
SQL> @catupgrd.sql

The script will take a while and then shuts down the database after completion of the activity.

After completion of script start the database and check the status for registry;

SQL>startup
SQL>select comp_name, status from dba_registry;

All of them should be valid, or as it was earlier in 10g.

Run utlrp.sql or you may compile the objects your own way.

SQL> exec utl_recomp.recomp_parallel(4);

After completion of above check for invalids.

SQL> select count(1) from dba_objects where status = 'INVALID';
COUNT(1)
----------
         2
SQL> select distinct status from dba_indexes;
STATUS
--------
VALID
N/A

SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_3.dat              3

Upgrading the time zone

It should be 14 for 11.2.0.3

SQL> shutdowm immediate;
SQL> startup upgrade
SQL> exec dbms_dst.begin_upgrade(new_version => 14);
SQL> shutdown immediate;

SQL> startup;
SQL> set serveroutput on;

SQL> declare
num_of_failures number;
begin
dbms_dst.upgrade_database(num_of_failures);
dbms_output.put_line(num_of_failures);
dbms_dst.end_upgrade(num_of_failures);
dbms_output.put_line(num_of_failures);
end;
/

SQL> select * from v$timezone_file;
FILENAME                VERSION
-------------------- ----------
timezlrg_14.dat              14

SQL> select count(1) from dba_objects where status = 'INVALID';
COUNT(1)
----------
         2

The upgrade is completed. You can turn the flashback on now.

Compile Database Objects - 2

2012-03-30T01:07:00.008-07:00

I have explained about compiling invalid objects in my earlier post for the db objects like packages, functions, views etc.

There are some object types that has a little different approach, specific to object types.

I will start with indexes.

Indexes:-

Index are not compiled but rebuilt every-time. So syntax for rebuilding index will be

Alter index <owner>.<indexname> rebuild [online];

Online can be used if index is being being used, but the best approach is that no connection should be allow to database while rebuilding indexes.

Materialized Views:-

We can compile the MV as we do it for other objects, but another option is to refresh the MV.

For understanding this first we have to understand Materialized View, which is nothing but an image of a table with desired columns having data till a particular time (last refresh).

The MV has to be refreshed timely to get the data updated in sync with table. Like a view it doesn't just store definition but the data.

The syntax for refresh is

begin dbms_mview.refresh('<MVIEW_NAME>','C'); end; -- Complete Refresh

begin dbms_mview.refresh('<MVIEW_NAME>','F'); end; -- Fast Refresh

As appears with name the complete refreshes refreshes completely logically truncating the current data, while fast refresh checks only for the changes from last refresh.

For fast refresh the logging should be enabled and the advantage is it saves time while for complete refresh, its time consuming and doesn't need to have logging.

There are more as below for refresh type

If nothing specified, its default

? - force refresh

P - for effects of partition changes in detail table.

Public Synonyms:-

The syntax will be

Alter public Synonym <Synonym Name> Compile; -- Synonym

"SP2-1503: Unable to initialize Oracle call interface" in Oracle 11g

2012-03-27T02:00:00.005-07:00

Below error appears when we try to log in to 11g Database using sqlplus.

$ sqlplus '/ as sysdba'

SP2-1503: Unable to initialize Oracle call interface

SP2-0152: ORACLE may not be functioning properly

The issue does not appear when I logged in to a separate session and set environment like ORACLE_HOME and ORACLE_SID manually.

Comparing both and checking all the environments I found that on env variable was causing trouble which was
ORA_TZFILE=<ORACLE_HOME>/oracore/zoneinfo/timezlrg.dat

This is some default value and is actually not correct.
When I checked at the location then no such file was there. But other files for all timezones were there with names like timezone_<n>.dat. (See value <n> is missing in present value of ORA_TZFILE)

If we unset the variable (which is the best solution), by default it uses maximum value of n.

unset ORA_TZFILE

Other options are using the desired timezone file.
So if we wish to use any earlier timezone file then we can set the variable to that value with absolute path and file name.

export ORA_TZFILE=<ORACLE_HOME>/oracore/zoneinfo/timezlrg_<n>.dat

This works well in both the cases.

ORA-17627: ORA-01017: during standby creation with RMAN

2011-11-17T06:51:00.000-08:00

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/17/2011 07:38:34
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on p1 channel at 11/17/2011 07:38:34
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server

We need to see first the script we execute to create the standby.

As this is 11g, we have a feature that we can make a standby database using rman duplicate db, even when the backup of primary database is not available.

So to use that feature executed below RMAN commands on target server.

rman target / auxiliary sys/pass@STANDB

run
{
allocate channel p1 type disk;
allocate auxiliary channel sb1 type disk;
duplicate target database for standby from active database;
}

And this ended up with above error.

Two things to be checked in this case:

1. Simply try logging in to each of the database using service name from the other server.(remote login)
from standby - sqlplus sys/passs@PRIME
from primary - sqlplus sys/passs@STANDB

2. For executing this we have to connect both the databases using service name and if the target is connected as / then above error occurs.

Another error message will appear if we connect standby as /, but that states clearly to connect auxiliary database using service name.

So the right way of connecting using service name is

rman target sys/passs@PRIME auxiliary sys/pass@STANDB

Weblogic Console : another user already owns the lock

2011-09-23T08:14:00.000-07:00

Sometimes on Weblogic Console we see below message

"another user already owns the lock"

To come out of such a situation you need the user who has made the changes or if you have an admin user, you can take the lock.

The lock usually appears when some deployment has been made and is still not activated.

Below two options actually appears after deployment to complete the process.

1. Activate Changes

2. Undo all changes

a.) If you have logged in with the user who did the deployments, then you should be able to see those buttons for above options in left pane, or in the "Change Center" on home page of console.

b.) If you are an administrator, then at the Home page of weblogic console, at Change Center the button will be "Take lock and Edit".

Once you click on that, the warning message of error disappears and you gets the lock, and all the buttons activated, and there appears two buttons for above options.

Now just by pressing any of them to activate/undo the deployment; the deployment completes and locks gets released.

Table Fragmentation

2011-08-01T23:14:00.000-07:00

The delete from the table deletes the rows but doesn't frees up the memory neither changes the high water mark.

The best way to get the memory cleared is truncating the table, but this hardly gets done in practical systems, and most of the time the delete and insert operations takes place.

So due to delete operations table gets fragmented, and in simple words its size appears more then the actual size of data it has. ( This will appear only if the compression is not enabled, check compression in dba_tables against the table).

Below query can help in identifying the tables which has different tablesize and actualsize, order by the maximum of difference.

set echo off

set lines 120

set pages 20

set pause on;

PROMPT ~ ~ Enter Full or part of Table Name/Owner, press enter for all ~ ~;

PROMPT ~ ~ Press Enter for next page, to move from pause; Cntrl^C to cancel ~ ~;

PROMPT ~ ~ ~ ~ ~ ~ ~ ~ ~~ ~ ~ ~ ~ ~ ~ ~ ~~ ~ ~ ~ ~ ~ ~ ~ ~~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~;

select owner, table_name, round((blocks*8),2) table_size, round((num_rows*avg_row_len/1024),2) actual_size,

(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) as difference

from dba_tables where table_name like UPPER('%&Full_or_part_of_table_name%')

and owner like UPPER('%&Full_or_Part_of_owner_name%')

and blocks is not null

order by difference desc;

set pause off;

set echo on

If we find some tables are fragmented, we can do reorg for them.

The simplest way of table reorg is

SQL> alter table <Table_Name> move <TableSpace_Name>;

After reorg all the dependent indexes gets in 'UNUSABLE' state, so we have to rebuild them.

SQL> Alter index <Index_Name> rebuild [parallel] [Online];

Clause in [] are optional.

At the end we have to analyze table, which has many ways to do the same. One of them is

SQL> Analyze Table < Table_Name> [compute/estimate Statistics];

Data Guard - 1 ( Physical Standby )

2011-07-25T23:12:00.000-07:00

Standby databases may be set up in two ways.

1. Physical

2. Logical

The characteristics and pros n cons can be discussed in parallel with setup details.

Physical Standby :- Its a physical copy of the primary database. Till 10g the physical standby is kept open in "mount" mode so of actually no use other then a disaster recovery. But 11g onwards the physical standby database can also be used for queries and reporting as the open mode for the standby database can be "read_only".

For creating standby using RMAN, below can be the steps.

( Primary database is ORCL and standby is STANDBY in below example which is the db_unique_name for them, while ORACLE_SID and the DB_NAME is same i.e.ORCL)

1. Create the oracle home and listener services for the dataguard. This may be on the different or on the same box.

2. Below is the set of parameters which has to be added to both Primary and the standby to make standby work as well to enable primary to work as standby in case of switchover.

a. On Primary:-

FAL_CLIENT='ORCL'

FAL_SERVER='STANDBY'

log_archive_config='DG_CONFIG=(ORCL,STANDBY)'

log_archive_dest_1=

'LOCATION=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/

valid_for=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=ORCL'

log_archive_dest_2=

'service=STANDBY LGWR ASYNC

valid_for=(online_logfiles,primary_role)

db_unique_name=standby'

DB_FILE_NAME_CONVERT=('/tjain/oracle/app/oracle/oradata/orcl','/home/oracle/app/oracle/oradata/orcl')

LOG_FILE_NAME_CONVERT=('/tjain/oracle/app/oracle/oradata/orcl','/home/oracle/app/oracle/oradata/orcl')

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

log_archive_max_processes=30

b. On standby:-

DB_FILE_NAME_CONVERT=('/home/oracle/app/oracle/oradata/orcl','/tjain/oracle/app/oracle/oradata/orcl')

LOG_FILE_NAME_CONVERT=('/home/oracle/app/oracle/oradata/orcl','/tjain/oracle/app/oracle/oradata/orcl')

fal_server=ORCL

fal_client=STANDBY

log_archive_config='DG_CONFIG=(ORCL,STANDBY)'

standby_file_management=auto

log_archive_dest_1=

'LOCATION=/tjain/oracle/app/oracle/flash_recovery_area/STANDBY/archivelog/

valid_for=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=STANDBY'

log_archive_dest_2=

'service=ORCL LGWR ASYNC

valid_for=(online_logfiles,primary_role)

db_unique_name=orcl'

3. Take the full backup of primary database.

4. Start Standby DB in nomount mode.

SQL> startup nomount pfile=initorcl.ora

ORACLE instance started.

5. Connect to standby and primary database and catalog if exists, and run rman duplicate database to create standby as below.

[standby@localhost dbs]$ rman target sys/oracle@ORCL auxiliary / catalog rman/rman@ORCL

connected to target database: ORCL (DBID=1229390655)

connected to recovery catalog database

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby;

Starting Duplicate ..

Finished Duplicate Db at ..

RMAN>

6. Connect to standby db with sqlplus and check the status.

SQL> select name , open_mode , database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

ORCL MOUNTED PHYSICAL STANDBY

7. Start recovery on standby.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name , open_mode , database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

ORCL MOUNTED PHYSICAL STANDBY

8. Switch log on primary and check if that gets shipped and applied to standby.

on primary -

SQL> Alter system switch logfile;

on standby -

SQL> select thread#, sequence#,applied from v$archived_log order by first_time desc;

THREAD# SEQUENCE# APPLIED

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

1 7 NO

1 6 NO

1 5 NO

1 4 NO

1 3 NO

1 2 NO

1 1 NO

Check alert log for Primary and standby to find out issues.

9. I found some issue with log arch destination due to listener in my alert log and after fixing I had to refresh that. So once the issue got fixed.

On Primary:-

SQL> select dest_name , status from v$archive_dest where dest_name like '%2';

DEST_NAME STATUS

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

LOG_ARCHIVE_DEST_2 ERROR

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> select dest_name , status from v$archive_dest where dest_name like '%2';

DEST_NAME STATUS

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

LOG_ARCHIVE_DEST_2 DISABLED

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> select dest_name, status from v$archive_dest where dest_name like '%2';

DEST_NAME STATUS

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

LOG_ARCHIVE_DEST_2 VALID

On standby:-

SQL> select thread#, sequence#,applied from v$archived_log order by first_time desc;

THREAD# SEQUENCE# APPLIED

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

1 7 NO

1 6 YES

1 5 YES

1 4 YES

We can even check the archvie logs gets shipped and is available to standby's archvie location.

11g onwards we can have the physical standby database in Readonly mode, so below are the steps to do that.

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

MOUNTED PHYSICAL STANDBY

Cancel the recovery

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

READ ONLY PHYSICAL STANDBY

By this time the recovery is not there in progress so the archvie may gets shipped but will not get applied.

SQL> select thread#, sequence#,applied from v$archived_log order by first_time desc;

THREAD# SEQUENCE# APPLIED

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

1 22 NO

1 21 NO

1 20 NO

1 19 YES

Start the recovery again

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL> select name , open_mode , database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

ORCL READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select thread#, sequence#,applied from v$archived_log order by first_time desc;

THREAD# SEQUENCE# APPLIED

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

1 22 NO

1 21 IN-MEMORY

1 20 YES

1 19 YES

Now you can run query from any of the tables even from physical standby.

SQL> select file_name from dba_data_files;

FILE_NAME

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

/tjain/oracle/app/oracle/oradata/orcl/users011.dbf

Like to thanks my colleague Puneeth for helping me out with this Standby Setup.

Drop Database

2011-07-04T00:46:00.000-07:00

To Drop a database we can directly use oracle command "Drop database". And this makes it easy enough clearing all the data and control files.

To drop the database we need to start the database in mount restricted mode.

The sequence of commands is as follows:-

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount restrict;
ORACLE instance started.

Total System Global Area 313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             201329752 bytes
Database Buffers          104857600 bytes
Redo Buffers                6336512 bytes
Database mounted.
SQL> drop database;

Database dropped.

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
SQL> select name, open_mode from v$database;
SP2-0640: Not connected

The parameter file and archive logs will be present at their location and the log, data and control files will be deleted.

Rename datafiles

2011-07-01T05:25:00.000-07:00

Renaming may mostly mean moving a datafile. Moving a datafile may be a need of some scenario where the OS mount/drive is running out of space (as the datafile name involves the complete path), or files management etc.

For any of the case if we want to rename a datafile we have multiple approaches.
a. Taking Database Down
b. Taking Tablespace offline
c. Taking datafile offline

Going in Detail.

a. We have to take database down in few scenarios (e.g. moving any datafile of system tablespace). The steps are:-

SQL> startup mount
Copy file to new location
SQL> alter database rename file '<earlier name>' to '<new name>';
SQL> alter database open;

b. In other case we can take any of the tablespaces offline except the SYSTEM one. The sequence and commands will be:-

SQL> alter tablespace <TableSpace Name> offline;
copy <Datafile Name> <New Location/Name >
SQL> alter tablespace <TableSpace Name> rename datafile '<earlier name>' to '<new name>';
SQL> alter tablespace <TableSpace Name> online;

c. This is the case taking least of effort, and the condition is Media Recovery should be enabled. If it is then we can directly take the datafile offline and rename it.
The command and sequence will be:-

SQL> alter database datafile <Datafile Name> offline;
copy <Datafile Name> <New Location/Name >
SQL> alter database rename file '<earlier name>' to '<new name>';
SQL> alter database datafile <Datafile Name> online;

Once we have taken the new file online, earlier can be deleted.
This may ask for recovery in any of the above cases.
And we can use recover database, recover tablespace <Tablespace Name> or recover datafile <Datafile Name> according to error message appearing at prompt.

Locks in Database-2

2011-06-09T00:48:00.001-07:00

Below is the query that can help you with multiple enqueue.
set lines 120
set colsep " "
col "HOLDER - SID| Module| Event" for A55
col "WAITER - SID| Module| Event" for A55
select
(a.sid||'| '||substr(a.module,1,20)||'| '||substr(a.event,1,26)) as "HOLDER - SID| Module| Event",
(b.sid||'| '||substr(b.module,1,20)||'| '||substr(b.event,1,26)) as "WAITER - SID| Module| Event"
from v$session a,
(select sid, blocking_session,event,module from v$session where blocking_session_status='VALID') b
where a.sid=b.blocking_session;

For RAC databases:-

set linesize 100
column Waiting(inst|sid|module) format A25
column Blocking format A20
column id1 format 999999999
column id2 format 999999999
column Req-Mode format 99
column Hold-Mode format 99
select substr(la.inst_id,1,1)||'|'||la.sid||'|'||substr(sa.module,1,9) "Waiting(inst|sid|module)",
substr(lb.inst_id,1,1)||'|'||lb.sid||'|'||substr(sb.module,1,9) "Blocking",
la.id1 "ID1", la.id2 "ID2", la.request "Req-Mode" ,lb.lmode "Lock-Mode"
from gv$lock la, gv$lock lb , gv$session sa, gv$session sb
where
la.id1=lb.id1
and la.id2=lb.id2
and la.request=6
and lb.lmode=6
and sa.sid=la.sid
and sa.inst_id=la.inst_id
and sb.sid=lb.sid
and sb.inst_id=lb.inst_id
order by 2;

Query above doesn't show the library locks and pins, and that can be checked in the view dba_kgllock
Below query can be useful in that case

select s1.sid , s2.sid ,l1.kgllktype lock_or_pin,l1.kgllkhdl address,
decode(l2.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_held,
decode(l1.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_requested
from dba_kgllock l1, dba_kgllock l2, gv$session s1, gv$session s2
where (((l2.kgllkmod != 0) and (l2.kgllkmod != 1)
and ((l2.kgllkreq = 0) or (l2.kgllkreq = 1)))
and
(((l1.kgllkmod = 0) or (l1.kgllkmod= 1))
and ((l1.kgllkreq != 0) and (l1.kgllkreq != 1))))
and l1.kgllktype = l2.kgllktype
and l1.kgllkhdl = l2.kgllkhdl
and l1.kgllkuse = s1.saddr
and l2.kgllkuse = s2.saddr;

As we find the details of sessions the same can be checked further and the relevant can be killed as

SQL> select a.sid, b.spid , a.serial# from v$session a, v$process b where a.sid in (<sid>) and a.paddr=b.addr;
Get the spid and check as below on OS
$ ps –ef | grep spid
And then use below to kill
$ kill -9 <spid>
Or Kill the oracle session using
SQL> ALTER SYSTEM KILL SESSION '<sid>, <serial#>';

Sometimes killing session from either one may not be enough and you need to kill both the OS process and the oracle session.

Locks in Database

2011-06-08T23:00:00.001-07:00

Locks are mainly helpful to serialize jobs. Lock on a object is required by a session to update/edit the same, and if some other session wants to update it then it has to wait until earlier one releases the object and it comes to a consistent state.
This may be an object level enqueue to get the object edited by different sessions, or may be library lock/pin for the latch being used. But its dba who can decide what session should not be allowed to proceed or to be terminated taking others on priority.

Below are few of those tables and views that can help in identifying all kinds of locks
dba_ddl_locks
dba_dml_locks
gv$locked_object
gv$access
gv$lock
dba_kgllock

First four can show the details of the objects locked by some session. Such scenario of locks can show some error message like "ORA-04021: timeout occurred while waiting to lock object "

Following metalink note – 153994.1 below steps can be done in this case
1. Verify whether the package is locked by another user:
   SELECT * FROM v$access WHERE object = '&ObjectName';
   If there a row is returned, the package is still locked.
2. Use the SID which is returned and check in v$session which session is
   locking this package.
   SELECT SID, SERIAL#, OSUSER, USERNAME FROM V$SESSION WHERE sid = '&SID';
3. If you are sure that it is ok to kill the session then use:
   ALTER SYSTEM KILL SESSION '<SID>,<serial#>';

Similarly dba_ddl_locks and dba_dml_locks can also be checked to find out the locked objects and the session locking the same.
For the enqueues or blocks in database you can simply check below
SQL> select * from gv$lock where block = 1;
now you have the sid for the session and for 10g onwards you can use this sid as below
SQL> select inst_id, sid, serial# from gv$session where blocking_session = '<SID>';
So you have the details for the blocker and blocking session which can be further digged in with gv$session;

The description for the locks in the mode column is as below.
0 - 'None'
1 - 'Null'
2 - 'Row-S (SS)'
3 - 'Row-X (SX)'
4 - 'Share'
5 - 'Shared Row-X (SSX)'
6 - 'Exclusive'

All the description above can help you deal with locks and enqueues.

Click Here for some queries to check the enqueues and library locks for single and multinode databases.