ORA-28017: The password file is in the legacy format.

Today I was trying to change password for one of my 12c database and got below error :-

SQL> alter user dbsnmp identified by "*************"
*
ERROR at line 1:
ORA-28017: The password file is in the legacy format.

Cause :- 

Looks like update to password file failed as the file format is wrong. 

Administrative privileges like sysbackup, syskm, sysdg cannot be granted unless the password file is in 12c Format

Solution :-

This error generally comes when you migrate your database from 11g to 12c

Regeneration of password will resolve this error.

PFB steps :-

1. Check which users have access to password file before regenerating it.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

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

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

DBSNMP                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

2. Recreate your password file :-

==>orapwd file=$ORACLE_HOME/dbs/orapwamit entries=5 force=y

Enter password for SYS:

[Target_server][oracle][amit]

3. Now by default only sys have access to it

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

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

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

4. Grant sysdba privilege to DBSNMP user to have access to password file

grant sysdba to dbsnmp;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

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

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

DBSNMP                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

5. Now again go for the password change, this time it will be successful.

SQL> alter user dbsnmp identified by "*************";

User altered.

I hope this article helped you.

Thanks
Amit Rath

How to Clone Grid Infrastructure binaries for Oracle Server(Standalone)

Yesterday I was applying patch on one of our development servers and patch on Grid Home failed with space issue and binaries got corrupted.

I thought of reinstalling the grid binaries by cloning it from a fresh development server. PFB steps for that :-

1. Stop the source(fresh) grid binaries. Hostname is Source_server
2. Tar the binaries 
3. Scp to the target(corrupted) server.  Hostname is Target_server

4. Detach the grid home from the Central inventory in Corrupted server :-

==>./runInstaller.sh -silent -detachHome ORACLE_HOME=/opt/grid/12.1.0.2 ORACLE_HOME_NAME=" Ora11g_gridinfrahome1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'DetachHome' was successful.
[target_server][oracle][]

5. Clone the grid home so that binaries are linked and grid home is attached to Central inventory

./runInstaller -clone -waitForCompletion  "ORACLE_HOME=/opt/grid/12.1.0.2" "ORACLE_HOME_NAME=OraHome1Grid" "INVENTORY_LOCATION=/opt/orainv/oraInventory" "LOCAL_NODE=target_server" "CRS=FASLE" "ORACLE_BASE=/opt/oracle" -silent -paramFile /opt/grid/12.1.0.2/clone/clone_oraparam.ini
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 233206 MB    Passed
Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-10-03_05-47-08AM. Please wait ...You can find the log of this install session at:
 /opt/orainv/oraInventory/logs/cloneActions2017-10-03_05-47-08AM.log
..................................................   5% Done.
..................................................   10% Done.
..................................................   15% Done.
..................................................   20% Done.
..................................................   25% Done.
..................................................   30% Done.
..................................................   35% Done.
..................................................   40% Done.
..................................................   45% Done.
..................................................   50% Done.
..................................................   55% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   70% Done.
..................................................   75% Done.
..................................................   80% Done.
..................................................   85% Done.
..........Could not backup file /opt/grid/12.1.0.2/root.sh to /opt/grid/12.1.0.2/root.sh.ouibak
Could not backup file /opt/grid/12.1.0.2/rootupgrade.sh to /opt/grid/12.1.0.2/rootupgrade.sh.ouibak

Copy files in progress.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup successful.
The cloning of OraHome1Grid was successful.
Please check '/opt/orainv/oraInventory/logs/cloneActions2017-10-03_05-47-08AM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
..................................................   95% Done.

As a root user, execute the following script(s):
        1. /opt/grid/12.1.0.2/root.sh


..................................................   100% Done.
[target_server][oracle][]

6. Execute the root.sh script

7. Configure your CRS instance on the cluster

While Configuring , I got below error :-

[root@target_server grid]# /opt/grid/12.1.0.2/perl/bin/perl -I/opt/grid/12.1.0.2/perl/lib -I/opt/grid/12.1.0.2/crs/install /opt/grid/12.1.0.2/crs/install/roothas.pl

Using configuration parameter file: /opt/grid/12.1.0.2/crs/install/crsconfig_params

2017/06/03 06:00:59 CLSRSC-350: Cannot configure two CRS instances on the same cluster

2017/10/03 06:00:59 CLSRSC-352: CRS is already configured on this node for the CRS home location /opt/grid/12.1.0.2

we have to deconfigure the existing one before configuring it again

8. Deconfigure the existing CRS 

[root@target_server install]# ./roothas.pl -deconfig -force

Using configuration parameter file: ./crsconfig_params

2017/10/03 06:13:02 CLSRSC-337: Successfully deconfigured Oracle Restart stack

9. Configure it again :-

[root@target_server install]# /opt/grid/12.1.0.2/perl/bin/perl -I/opt/grid/12.1.0.2/perl/lib -I/opt/grid/12.1.0.2/crs/install /opt/grid/12.1.0.2/crs/install/roothas.pl

Using configuration parameter file: /opt/grid/12.1.0.2/crs/install/crsconfig_params

LOCAL ADD MODE

Creating OCR keys for user 'oracle', privgrp 'sigdba'..

Operation successful.

LOCAL ONLY MODE

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

CRS-4664: Node target_server successfully pinned.

2017/10/03 06:15:36 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.conf'

target_server 2017/10/03 06:16:02 /opt/grid/12.1.0.2/cdata/target_server/backup_20170603_061602.olr 3924561976

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'target_server'

CRS-2673: Attempting to stop 'ora.evmd' on 'target_server'

CRS-2677: Stop of 'ora.evmd' on 'target_server' succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'target_server' has completed

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Oracle High Availability Services has been started.

2017/10/03 06:16:33 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

9. Add the asm details and start the asm

==>srvctl add asm -d /dev/mapper/

[target_server][oracle][+ASM]

/dev/mapper$

==>

[target_server][oracle][+ASM]

/dev/mapper$

==>srvctl start asm

[target_server][oracle][+ASM]

/dev/mapper$

[target_server][oracle][+ASM]

/dev/mapper$

==>sqlplus  /as sysasm

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Automatic Storage Management option

SQL> alter diskgroup AMIT_GROUP mount;

Diskgroup altered.

10 . Add database to CRS 

==>srvctl add database -d amit -o /opt/oracle/product/database/12.1.0.2/

[target_server][oracle][amit]

/dev/mapper$

==>sqlplus /as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 8589934592 bytes

Fixed Size                  7663544 bytes

Variable Size            3288334408 bytes

Database Buffers         3137339392 bytes

Redo Buffers                9113600 bytes

In-Memory Area           2147483648 bytes

Database mounted.

SQL> alter database open;

Database altered.

Cloning of Grid home binaries completed.

I hope this article helped you

Thanks

Amit Rath

OMS decided to shutdown the agent because of the following reason sent from OMS: AGENT_TZ_MISMATCH

Today I was trying to start my OEM agent in one of my dev server and got below error while starting it :-

==>./emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Starting agent ...........failed.
Consult emctl.log and emagent.nohup in: /u01/oracle/product/agent/agent_inst/sysman/log

Cause :-

Crosschecked the logs as mentioned above and got below in emagent.nohup logfile 

highlighted one below is of our interest

 --- EMState agent

----- Wed Jun 14 11:58:16 2017::20552::Auto tuning the agent at time Wed Jun 14 11:58:16 2017 -----

----- Wed Jun 14 11:58:17 2017::20552::Finished auto tuning the agent at time Wed Jun 14 11:58:17 2017 -----

----- Wed Jun 14 11:58:17 2017::20552::Launching the JVM with following options: -Xmx128M -server -Djava.security.egd=file:///dev/./urandom -Dsun.lang.ClassLoader.allowArraySyntax=true -XX:+UseLinuxPosixThreadCPUClocks -XX:+UseConcMarkSweepGC -XX:+CMSClassUnloadingEnabled -XX:+UseCompressedOops -----

----- Wed Jun 14 11:58:17 2017::20552::Agent Launched with PID 20672 at time Wed Jun 14 11:58:17 2017 -----

----- Wed Jun 14 11:58:17 2017::20552::Previous Thrash State(-1,-1) -----

----- Wed Jun 14 11:58:17 2017::20672::Time elapsed between Launch of Watchdog process and execing EMAgent is 1 secs -----

2017-06-14 11:58:17,396 [1:main] WARN - Missing filename for log handler 'wsm'

2017-06-14 11:58:17,402 [1:main] WARN - Missing filename for log handler 'opss'

2017-06-14 11:58:17,403 [1:main] WARN - Missing filename for log handler 'opsscfg'

OMS decided to shutdown the agent because of the following reason sent from OMS:  AGENT_TZ_MISMATCH

----- Wed Jun 14 11:58:26 2017::20552::Checking status of EMAgent : 20672 -----

----- Wed Jun 14 11:58:26 2017::20552::EMAgent exited at Wed Jun 14 11:58:26 2017 with return value 0. -----

----- Wed Jun 14 11:58:26 2017::20552::writeAbnormalExitTimestampToAgntStmp: exitCause=NORMAL : restartRequired=0 -----

----- Wed Jun 14 11:58:26 2017::20552::EMAgent was shutdown normally. -----

----- Wed Jun 14 11:58:26 2017::20552::Exiting watchdog loop

We can clearly see that there is some timezone mismatch issue of the agent with the OMS repository server

Solution :-

As above error state that there is a timezone mismatch between the agent server and OMS repository server.

or OMS repository database must be having incorrect timezone related details for agent server

Below steps resolved my error:-

[Server1][oracle][amit1]

EM_HOME/bin$

==>./emctl resetTZ agent

Oracle Enterprise Manager Cloud Control 12c Release 4

Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.

Updating /u01/oracle/product/agent/agent_inst/sysman/config/emd.properties...

Successfully updated /u01/oracle/product/agent/agent_inst/sysman/config/emd.properties.

Login as the em repository user and run the  script:

exec mgmt_target.set_agent_tzrgn('Server1.test.com:9901','US/Eastern')

and commit the changes

This can be done for example by logging into sqlplus and doing

SQL> exec mgmt_target.set_agent_tzrgn('Server1.test.com:9901','US/Eastern')

SQL> commit

Login to OMS repository database and connect using SYSMAN and execute below procedure :-

exec mgmt_target.set_agent_tzrgn('Server1.test.com:9901','US/Eastern')

Do a clearstate and securing of your agent again and start it :-

==>./emctl clearstate agent

Oracle Enterprise Manager Cloud Control 12c Release 4

Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.

EMD clearstate completed successfully

[Server1][oracle][amit1]

EM_HOME/bin$

==>./emctl secure agent

Oracle Enterprise Manager Cloud Control 12c Release 4

Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.

Agent is already stopped...   Done.

Securing agent...   Started.

Enter Agent Registration Password :

Securing agent...   Successful.

[Server1][oracle][amit1]

EM_HOME/bin$

==>./emctl start agent

Oracle Enterprise Manager Cloud Control 12c Release 4

Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.

Starting agent ..... started.

Agent started successfully

I hope this article helped you

Thanks 

Amit Rath

Could not read EMD_URL from agent configuration files in 12c

Today after OS patching, got below error when trying to check the status of the agent :-

==>./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD_URL is null.
Could not read EMD_URL from agent configuration files

==>./emctl stop agent

Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
EMD_URL is null.
Could not read EMD_URL from agent configuration files

Cause :-

Configuration files for Agent corrupted or accidentally deleted.

Solution :-

Check for the emd.properties file in EM_AGENT_HOME/agent_inst/sysman/config folder
or check for the targets.xml file in EM_AGENT_HOME/agent_inst/sysman/emd folder

In my scenario , my emd.properties got corrupted

==>ls -ltr emd*
-rw------- 1 oracle sigdba 7709 Jan 19  2016 emd.properties.tzbak
-rw-r----- 1 oracle sigdba 7709 Jan 19  2016 emd.properties.2016-01-19-21-51-32
-rw-r----- 1 oracle sigdba 7734 Jan 19  2016 emd.properties.2016_01_19_09_51_36
-rw-r----- 1 oracle sigdba 7861 Oct 29 01:59 emd.properties.bak
-rw-r----- 1 oracle sigdba    0 Dec 16 01:01 emd.properties
[Hostname][oracle][AMIT2]

We can see the size of file is zero , by default Oracle keeps a backup of emd.properties file in the same directory, restore the file from that backup, I did the same

-rw-r----- 1 oracle sigdba  7861 Oct 29 01:59 emd.properties
-rw-r----- 1 oracle sigdba   155 Oct 29 01:59 private.properties
-rw-r----- 1 oracle sigdba   265 Dec 16 01:01 autotune.properties
-rw-r----- 1 oracle sigdba     0 Dec 16 01:01 emd.properties_20170113

Now again tried for a restart of the agent , it worked

==>./emctl stop agent

Oracle Enterprise Manager Cloud Control 12c Release 4

Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.

Agent is Not Running

[Hostname][oracle][amit2]

/opt/oracle/product/agent/core/12.1.0.4.0/bin$

==>./emctl start agent

Oracle Enterprise Manager Cloud Control 12c Release 4

Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.

Starting agent ..................... started.

If your emd.properties file is not corrupted but targets.xml is corrupted , then also Oracle keep a backup of that file in same folder , we can restore from that file.

I hope this article helped you.

Thanks

Amit Rath

MRP0: Background Media Recovery terminated with error 1111

Today while troubleshooting a DR database sync scenario issue, I got below error in alert log file :-

MRP0: Background Media Recovery terminated with error 1111
Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit1_pr00_1890.trc:
ORA-01111: name for data file 34 is unknown - rename to correct file
ORA-01110: data file 34: '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034'
ORA-01157: cannot identify/lock data file 34 - see DBWR trace file
ORA-01111: name for data file 34 is unknown - rename to correct file
ORA-01110: data file 34: '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034'

Cause :-

Above issue can occur in DR database in below scenarios :-

1. Space issue in DR database and a datafile is added in primary database.
2. Standby file management is set to manual
3. DB_create_file_dest or DB_file_name_convert parameter is not set in Standby database

Troubleshoot :-

When I crosscheck the primary database and standby database sync details , I saw Redo Apply was stopped.

I crosschecked the alert log files for standby database and got above mentioned error in alert logfile.

Checked the datafile details in DR database :-

File_id       name
34             /opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034

Checked the Primary alert log file and got to know that a datafile addition activity happened in primary database.

Crosschecked all the above mentioned parameters and got to know that space exhausted in BCP database due to that Oracle created an Unknown file in ORACLE_HOME location due to which Redo Apply stopped.

Solution :-

Checked the added file detail in Production database :-

File_id       name
34            +DATA/amit/datafile/data.321.931222251

Login to Standby database server :-

1. As space was exhausted on Diskgroup level, added space by logging in to ASM instance
2. Login to DR database instance :-

a. Change the standby_file_management parameter to Manual

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO
SQL> alter system set standby_file_management=MANUAL sid='*';

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
SQL>
SQL> select name from v$datafile where file#=34;

NAME
--------------------------------------------------------------------------------
/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034

SQL> alter database create datafile '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034' as '+DATA' size 10g;

Database altered.

After creation of the datafile in DR database , check the file detail in DR database :-

File_id       name
34             +DATA/amit_dr/datafile/data.278.293456881

Check the redo apply status, if not started , start it manually using dgmgrl.

I hope this article helped you.

Thanks
Amit Rath

RMAN restore fails with RMAN-06023 but backups are available

Today while I was restoring one RMAN backup to a new host , I got very different error while restoring even though backup pieces are available and cataloged also. PFB error details :-

Error:-

creating datafile file number=1 name=/u01/oracle/amit/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/27/2015 03:19:59
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u01/oracle/amit/system01.dbf'

Checked backup was also available for datafile 1

RMAN> list backup of datafile 1;


List of Backup Sets

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

.

.

   List of Backup Pieces for backup set 30299 Copy #1

    BP Key  Pc# Status      Piece Name

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

    30299   1   EXPIRED     /u00/oracle/admin/amit/backup/hot_amit_30306_1_20140701

  Backup Set Copy #2 of backup set 30299

  Device Type Elapsed Time Completion Time     Compressed Tag

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

  DISK        00:52:02     2015-07-27 03:18:48 YES        HOT_DISK_amit

    List of Backup Pieces for backup set 30299 Copy #2

    BP Key  Pc# Status      Piece Name

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

    30310   1   AVAILABLE   /ora_backup/test/amit/hot_amit_30306_1_20140701

Cause :-

When we restore using a backup controlfile and run the catalog command to catalog backup pieces, Oracle does two things, First it does an implicit crosscheck for files available in the recovery area and then it does an explicit crosscheck for the location you mentioned to catalog the pieces.

While doing an implicit catalog, if there is any file available(archive log, online log, any datafile) of different incarnation from the control file, Oracle register that file details to the controlfile and changes the incarnation value for the control file as per the available file in the Recovery area.

Now when we try to restore our datafiles from backup pieces, controlfile is not able to recognize the backup pieces as they are from different incarnation.

So we get "ORA-01110: data file 1: '/u01/oracle/amit/system01.dbf'" even if we have backup pieces available.

Solution :-

1. Clean up you recovery area before doing a catalog for backup pieces. Run a Crosscheck and delete expired backup details.
2. Disable Recovery area details before running catalog command, you can do it by removing db_recovery_file_dest and db_recovery_file_dest_size parameters in parameter file.

After doing any of the above try to restore the backup , it will complete without any issues.

I hope this article helped you

Thanks
Amit Rath

ORA-00060: deadlock detected while waiting for resource ( Deadlock due to Bitmap Indexes)

Yesterday I was testing the below scenario with development team using Bitmap indexes .

Concept :-

Generally Bitmap index are created in Data warehouse environments where database activity is mostly read and writing is significantly less.

Bitmap indexes are created on columns with less cardinality , so one row of a bitmap index points to multiple rowid's to the column in the table , irrespective of B-tree indexes which points to only one row id.

Now any DML on a column having bitmap index ends up locking many rows in the index.

Every record for a column having bitmap index, there will be a index record created for that which will point to the number of rows in that column having that value.

For Eg , Gender Column will have 2 distinct values, Male and Female. If we put a Bitmap index on Gender column then index created will have two index records, Male and Female , and each index records will have detail for all the column values.

If Column's 1st, 5th and 9th record has value as Male then the index record will have value '1' in 1st , 5th and 9th record and '0' in the other.

Same goes for the Female Index record also, if 2nd, 4th, 3rd and 6th have value as Female then the index record will have '1' in 2nd, 4th, 3rd and 6th record and 0 in other.

Especially concurrent single row DML operations goes for a deadlock.

What happens when we try to insert same value to a column having Bitmap index associated to it from different sessions. PFB detail:-

When we try to insert same value from different sessions, both session tries to aquire lock for the same region of Bitmap index to create a index record, resulting in Deadlock.

Below is a demo for that :-

SQL> create table test_bitmap ( name varchar(10),age number);

Table created.

SQL> create bitmap index test_bitmap_idx on test_bitmap(age);

Index created.

Session 1:-

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: rick
Enter value for age: 34
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('rick',34)

1 row created.

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: Ramse
Enter value for age: 24
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('Ramse',24)
Waiting ……………………


Session 2:-

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: Ramse
Enter value for age: 24
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('Ramse',24)

1 row created.

SQL> insert into test_bitmap values ('&name',&age);
Enter value for name: rick
Enter value for age: 34
old   1: insert into test_bitmap values ('&name',&age)
new   1: insert into test_bitmap values ('rick',34)
insert into test_bitmap values ('rick',34)
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Generally this is not the case with normal index while doing insert or any DML.

Solution :-

Prefer not to do insert/update/delete on columns having Bitmap indexes, if Business requirement is there then remove the Bitmap index from that column.

I hope this article helped you.

Thanks
Amit Rath

How to purge/flush a single SQL PLAN from shared pool in Oracle

Purging a SQL PLAN from shared pool is not a frequent activity , we generally do it when a query is constantly picking up the bad plan and we want the sql to go for a hard parse next time it runs in database.

Obviously we can pass a hint in the query to force it for a Hard Parse but that will require a change in query , indirectly change in the application code , which is generally not possible in a business critical application.

We can flush the entire shared pool but that will invalidate all the sql plans available in the database and all sql queries will go for a hard parse. Flushing shared pool can have adverse affect on your database performance.

Flush the entire shared pool :-

Alter system flush shared_pool;

Flushing a single SQL plan from database will require certain details for that sql statement like address of the handle and hash value of the cursor holding the SQL plan.

Steps to Flush/purge a particular sql plan from Shared pool :-

SQL>  select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '';

ADDRESS HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

Now we have the address of the handle and hash value of the cursor holding the sql. Flush this from shared pool.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL>  select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '';

no rows selected

SQL plan flushed for above particlar sql, Now next time above sql/query will go for a hard parse in database.

I hope this article helped you.

Thanks
Amit Rath

How to migrate SQL PLAN BASELINE from one database to another

We can migrate execution plan for a particular query from one database to another . This article will provide all the details on how to migrate and when to migrate the plan of a query

When to migrate the plan of a query :-

We came across with this kind of scenario when a query is running fine in pre prod/UAT and is not running properly in production.

Everything related to query in both the environments are same, Plan which the query is using in production is not good enough and taking a lot of time.

Best approach in this kind of scenario is to purge the bad plan from the shared pool . PFB check here to purge a SQL PLAN from shared pool :-

SQL> exec DBMS_SHARED_POOL.PURGE ('0000000410E16308, 3635099784', 'C');

PL/SQL procedure successfully completed.

Now when we run the query again in production , it will go for a hard parse and Oracle will automatically select the best execution plan.

What if , execution plan which Oracle selected is not that good as it's in UAT/Preprod, then we will decide to migrate the plan from UAT/Pre prod to production.

How to migrate the plan of a query to another database :-

gather the sql_id and plan_hash_value for the query , we an get this value from gv$sqlarea

select sql_id,plan_hash_value, sql_text from gv$sql_area where sql_text like '%

For this article :-

Sql_id = '45g4pghgh9rt'

Plan_Hash_value :- 2904123173

1. Load the plan from shared pool

DECLARE

my_plans pls_integer;

BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id =>'45g4pghgh9rt', plan_hash_value=>'2904123173');

end;

/

PL/SQL procedure successfully completed.

2. Check the details of the baseline in dba_sql_plan_baselines :-

select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,fixed,to_char(last_executed,'dd-mon-yy HH24:MI') last_executed from dba_sql_plan_baselines;

3. Create a staging table, can’t create in sys schema :-

SQL> exec DBMS_SPM.CREATE_STGTAB_BASELINE('STGTAB', 'AMIT');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16

SQL> select table_name,owner from dba_tables where table_name='STGTAB';

TABLE_NAME                     OWNER

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

STGTAB                         AMIT

Elapsed: 00:00:00.07

4. Pack the baseline for the sql in the table :-

SQL>  var x number;

begin

:x := DBMS_SPM.PACK_STGTAB_BASELINE('STGTAB', 'AMIT', sql_handle => 'SQL_27c1b5a44008ee73', plan_name => 'SQL_PLAN_2ghdpnj00jvmm46bd3d4a' );

end;SQL>   2    3

  4  /

PL/SQL procedure successfully completed.

5. Take export backup of STGTAB table and import it to the target table and then unpack the baseline

6. Unpack the baseline in target database, before this purge the previous/bad sql plan available in target database.

SQL> var x number;

begin

:x := DBMS_SPM.UNPACK_STGTAB_BASELINE('STGTAB', 'AMIT');

end;SQL>   2    3

  4  /

PL/SQL procedure successfully completed.

7. Sqlplan baseline migration done, you will see the plan hash value for respective sql will be changed.

I hope this article helped you

Thanks

Amit Rath

Relocating Service in Oracle RAC Database

Relocating Services in Oracle RAC is mainly used for maintenance purpose.

Maintenance purpose :- When for Patching(OS/Oracle), we wanted to do it in a rolling fashion, one node at a time as we don't have downtime for the application, we have to relocate service temporarily to other nodes of the cluster.

We can also permanently move a service from one node to another node. We will show both the methods in this article 

Temporary relocation of a Service in Oracle :-

==>srvctl config service -d AMIT -s app_prod
Service name: app_prod
Service is enabled
Server pool: amit_app_prd
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SESSION
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: AMIT1
Available instances: AMIT2

==>srvctl status service -d AMIT
Service app_prod is running on instance(s) AMIT1

We can see from above that app_prod service is running on AMIT1 instance , now we wanted to do patching on AMIT1 instance.

We will relocate service temporarily to AMIT2 instance and once our maintenance work is done we will relocate it back. 

Relocating a service will not modify the preferred instance and available instance details for a service . This is a temporary job.

We can relocate service temporarily using below methods :-

Using Database instance :-

==>srvctl relocate service -d AMIT -s app_prod -i p1end1 -t p1end2

Using Node details :-

==>srvctl relocate service -d AMIT -s app_prod -c NODE1 -n NODE2

If you want to disconnect all session while relocating the service , we can use -f clause.

Now check the configuration of the service , it's the same :-

==>srvctl config service -d AMIT -s app_prod
Service name: app_prod
.......
TAF policy specification: NONE
Edition:
Preferred instances: AMIT1
Available instances: AMIT2

Once maintenance job is done, relocate it as it was before using same command.

Permanent relocation of a service :-

When we have to permanently relocate a service then we have to modify the config parameters for the service , in this case the preferred instance and available instance details will be changed.

==>srvctl config service -d AMIT -s app_prod
Service name: app_prod
Service is enabled
Server pool: amit_app_prd
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SESSION
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: AMIT1
Available instances: AMIT2

Now modify the service for preferred instance as AMIT2

=> srvctl modify service -d AMIT -s app_prod -i AMIT2 -a AMIT1

if multiple instances then we can use comma(,)

=>  srvctl modify service -d AMIT -s app_prod -i AMIT2,AMIT3 -a AMIT1,AMIT4

we can use -f to disconnect all active session in the relocation process.

Let's see if configuration modified or not :-

==>srvctl config service -d AMIT -s app_prod
Service name: app_prod
..................
Edition:
Preferred instances: AMIT2
Available instances: AMIT1

It's modified permanently

I hope this article helped you

Thanks

Amit Rath

ORA-65114: space usage in container is too high

Yesterday I was doing an import for a pluggable database and it ran out of space so I tried to resize one of the datafile and got below error while doing it :-

SQL> ALTER DATABASE DATAFILE '+DATA1/AMIT_CON/3933D120863A61B7E0531609900AE4EE/DATAFILE/AMIT.786.919009661' RESIZE 100g;
ALTER DATABASE DATAFILE '+DATA1/AMIT_CON/3933D120863A61B7E0531609900AE4EE/DATAFILE/AMIT.786.919009661' RESIZE 100g
*
ERROR at line 1:
ORA-65114: space usage in container is too high

Cause :-

This error generally comes when total size of PDB after resize of the datafile is more than the size you have allocated to a PDB while creating.

Solution :-

Change the allocation storage for a PDB and then try to resize the datafile.

Note :- while changing the storage for a PDB , check if space is available in ASM disks.


Allocation storage of a PDB can be changed by logging in to the PDB :-

SQL> alter session set container=AMIT;

Session altered.

Either change the allocation storage to a required value or change it to unlimited.

SQL> ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

Pluggable database altered.

OR

SQL> alter pluggable database storage (MAXSIZE 120G);

Pluggable database altered.

Now try to resize the datafile

SQL> ALTER DATABASE DATAFILE '+DATA1/AMIT_CON/3933D120863A61B7E0531609900AE4EE/DATAFILE/amit.786.919009661' RESIZE 100556633088;

Database altered.

I hope this article helped you.

Thanks
Amit Rath

How to recreate Oracle Inventory

Today I was applying patch on one of my development server and I got below error :-

[oracle@omr oraInventory]$ ./opatch lsinvinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/oracle/product/database/11.2.0.4
Central Inventory : /opt/orainv/oraInventory
   from           : /opt/oracle/product/database/11.2.0.4/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /opt/oracle/product/database/11.2.0.4/cfgtoollogs/opatch/opatch2016-03-11_04-33-59AM_1.log

Lsinventory Output file location : /opt/oracle/product/database/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2016-03-11_04-33-59AM.txt

OPatch failed to locate Central Inventory.
Possible causes are: 
    The Central Inventory is corrupted
    The oraInst.loc file specified is not valid.
LsInventorySession failed: OPatch failed to locate Central Inventory.
Possible causes are: 
    The Central Inventory is corrupted
    The oraInst.loc file specified is not valid.


OPatch failed with error code 73

Cause :- 

Checked the Inventory location, /opt/orainv/oraInventory , it was empty, seems someone accidentally deleted it.

Solution :-

We have to recreate our Inventory with all the ORACLE_HOME details available in the server before this blunder happened.

To crosscheck , what all products are installed, query the oratab file :-

grid:/opt/grid/11.2.0.4:N
agent:/opt/oracle/product/agent/core/12.1.0.4.0:N
+ASM:/opt/grid/11.2.0.4:N
amit:/opt/oracle/product/database/11.2.0.4:N        

We can see that , we have GRID_HOME, ORACLE_HOME and AGENT installed in this server.

Recreating the Oracle Inventory :-

1. Attach Oracle Home

Oracle Home :- /opt/oracle/product/database/11.2.0.4
Oracle Home Name :- Db11g_home1

$ ./runInstaller -silent -attachHome ORACLE_HOME="/opt/oracle/product/database/11.2.0.4" -invPtrLoc /etc/oraInst.loc ORACLE_HOME_NAME="Db11g_home1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/orainv/oraInventory
'AttachHome' was successful.

$ ./opatch lsinventory -oh /opt/grid/11.2.0.4
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/grid/11.2.0.4
Central Inventory : /opt/orainv/oraInventory
   from           : /opt/grid/11.2.0.4/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.4.0
Log file location : /opt/grid/11.2.0.4/cfgtoollogs/opatch/opatch2016-03-10_23-34-48PM_1.log

List of Homes on this system:

  Home name= OraDb11g_home1, Location= "/opt/oracle/product/database/11.2.0.4"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

We have to attach grid home also.

2. Attach Grid Home to the Inventory

Grid Home :- /opt/grid/11.2.0.4
Grid Home Name :- 11g_gridinfrahome1

$ ./runInstaller -silent -attachHome ORACLE_HOME="/opt/grid/11.2.0.4" -invPtrLoc /etc/oraInst.loc ORACLE_HOME_NAME="11g_gridinfrahome1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/orainv/oraInventory
'AttachHome' was successful.

3. Attach Agent Home to the Inventory

Agent Home :- /opt/oracle/product/agent/core/12.1.0.4.0
Agent Home Name :- agent12c1

$ ./runInstaller -silent -attachHome ORACLE_HOME="/opt/oracle/product/agent/core/12.1.0.4.0" -invPtrLoc /etc/oraInst.loc ORACLE_HOME_NAME="agent12c1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/orainv/oraInventory
'AttachHome' was successful.

All Homes present in the server added to the Central Inventory.

I hope this article helped you.

Thanks
Amit Rath

OMS 12.1.0.4 installation failed with "The database details which you have provided doesn't contain valid Management Repository. Provide correct database details and retry"

Yesterday I was installing OMS 121.0.4 and got below error while attempting to connect to database:-

OMS 12.1.0.4 installation failed with "The database details which you have provided doesn't contain valid Management Repository. Provide correct database details and retry"

Cause :-

Generally this error comes when the database we are using as OMS database was already used as a database for OMS installation before or A failed OMS installation already happened using this database.

Solution :-

we have to properly clean the database before using it again as a OMS database :-

Below steps I did to clean my database :-

1. Drop sysman related schemas.

DROP USER SYSMAN CASCADE;  ( will take time)
DROP USER SYSMAN_OPSS CASCADE;
DROP USER SYSMAN_MDS CASCADE;
DROP USER SYSMAN_APM CASCADE;
DROP USER SYSMAN_RO CASCADE;

2. Remove Synonyms related to sysman accounts :-

DECLARE
  CURSOR l_syn_csr IS
    SELECT 'DROP ' ||
      CASE owner
        WHEN 'PUBLIC'
          THEN 'PUBLIC SYNONYM '
        ELSE 'SYNONYM ' || owner || '.'
      END ||
      synonym_name AS cmd
    FROM
      dba_synonyms 
    WHERE
      table_owner IN (
        'SYSMAN',
        'SYSMAN_MDS',
        'MGMT_VIEW',
        'SYSMAN_BIP',
        'SYSMAN_APM',
        'BIP',
        'SYSMAN_OPSS',
        'SYSMAN_RO'
      );
BEGIN
  FOR l_syn_rec IN l_syn_csr LOOP
    BEGIN
      EXECUTE IMMEDIATE l_syn_rec.cmd;
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line( '===> ' || l_syn_rec.cmd );
        dbms_output.put_line( sqlerrm );
    END;
  END LOOP;
END;
/

3. Removing remaining Objects and tablespaces :-

DROP USER mgmt_view CASCADE;
DROP TABLESPACE mgmt_ecm_depot_ts INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE mgmt_tablespace   INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE mgmt_ad4j_ts      INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

4.  As proper database cleaning using RepManager dropall didn't happen, so we have to clean up the registry details :-

DELETE
  FROM
    schema_version_registry
  WHERE
    (comp_name,owner) IN (
      ('Authorization Policy Manager','SYSMAN_APM'),
      ('Metadata Services','SYSMAN_MDS'),
      ('Oracle Platform Security Services','SYSMAN_OPSS')
    );
commit;

5. Now the database clean up completed, Please continue with the installation, I tried and it completed successfully.

I hope this article helped you, Please go through the below article also for proper error details :-
https://sites.google.com/site/yetanotherocm/home/system-builds/cronulla/07-complete-oms-12-1-0-3-1-installation

Thanks
Amit Rath