DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, December 4, 2017

ORA-39142: incompatible version number 5.1 in dump file

ORA-39142: incompatible version number 5.1 in dump file.


Recently I came across one issue while importing schema dump in 12c database.

My Scenario.
Schema Export taken from Database version 12.2.0.1.0
Schema Import needs to be done on Database version 11.2.0.4.0

While doing import to 11.2.0.4, I have received following error and import terminated.

ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file "/xxx/db/ schema_dump.dmp"

Further analysis.
As above error shows that there is some incompatibility with the versioning of Database and Dump File version.

Here are some facts as per oracle Doc related to DB versioning and Dump file compatibility.

Data Pump dumpfile compatibility

Solution To above problem.

While taking export from higher version of DB i.e. 12.2.0.1.0 use version parameter in expdp command.

Example
expdp scott/tiger@orcl directory=EXPIMP schemas=scott Version=11.2 dumpfile=Exp_Scott.dmp logfile=Exp_Scott.log

Now, you can import schema without any error.

impdp scott/tiger@orcldg directory=EXPIMP schemas=scott dumpfile=Exp_Scott.dmp logfile=Imp_Scott.log

Thursday, November 9, 2017

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Error:

ORA-39120: Table "OWNER"."TABLENAME" can't be truncated, data will be skipped. Failing error is:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Solution:

Please see from v$lock if the object is being used by other sessions
SQL
select obect_id1,object_id2 from dba_objects where owner='schemanname' and object_name='table_name';
select sid from v$lock where id1='id1_obtained from previous query' and id2='obtained from previous query';
select serial# from v$session where sid='sid obtained from previous query';

Kill those sessions or wait until user complete those sessions
alter system kill session 'serial#,sid';

Monday, November 6, 2017

                               UDI-31623: operation generated ORACLE error 31623
                               ORA-31623: a job is not attached to this session via the specified handle.

ISSUE:-


We encountered one issue while running the impdp job on one of our database with the below listed error:

oracle@bossWW:~$  impdp oracle/xxxx@Tnsname schemas=HPSEBL,MIS,CRM  directory=EXP_BKP  dumpfile=dbschema.dmp logfile=impdbschema.log

Import: Release 11.2.0.4.0 - Production on Mon Oct 30 15:07:28 2017

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

Username: / as sysdba

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

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1


Solution:-

As a workaround we are modify the parameters and just restarting the database and the impdp job was running fine.

Cause:

The problem is the stream pool size. It was configured to value

SQL> show parameter streams_pool

NAME                                 TYPE             VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer      0
Solution:

Increase the value of stream_pool_size initialization parameter

oracle@bossWW:~$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 16 14:44:23 2016

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

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

SQL> alter system set streams_pool_size=256M scope=both;
alter system set streams_pool_size=256M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

SQL> show parameter sga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 596M
sga_target                           big integer 0


Here we increase the SGA size and modify the streams_pool_size size.

QL> alter system set sga_max_size=700m scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2256832 bytes
Variable Size             591396928 bytes
Database Buffers           67108864 bytes
Redo Buffers               69951488 bytes
Database mounted.
Database opened.
SQL> alter system set streams_pool_size=20m scope=both;

System altered.

After increasing the stream_pool_size parameter all expdp and impdp jobs started working without issues.
Orscle datapump uses the stream_pool_size parameter, So we must ensure that this parameter is configured appropriately before running the data pump jobs.

ERROR: Invalid username and/or password LOG: Local Authentication failed...Attempt PAM authentication...PAM failed with error: Authentication failure

Issue:

Got the following error while setting preferred credentials for the host target

ERROR: Invalid username and/or password LOG: Local Authentication failed...Attempt PAM authentication...PAM failed with error: Authentication failure

Cause:
For LADP kind of authentication PAM setup should be done.

Solution:

Make sure that PAM libraries are installed 

rpm -ql pam-1.1.1-4.el6 | grep libpam.so

you should see the output like below.

/lib64/libpam.so.0        
/lib64/libpam.so.0.82.2   

If not installed, install the pam-1.1.1-4.el6 rpm and verify again.

And then  as root user, do the below.

cd /usr/lib64
ln -s /lib64/libpam.so.0.82.2 libpam.so

cd /etc/pam.d
cp sshd emagent

go to $AGENT_HOME/bin

backup commonenv and add the following line

if [ "$LD_LIBRARY_PATH" = "" ] ; then
LD_LIBRARY_PATH=/lib64
else
LD_LIBRARY_PATH=:$LD_LIBRARY_PATH
fi

Restart Agent
./emctl stop agent
./emctl start agent

Install 12.1.5 oem management agent in silent mode

Overview:

Installing a management agent in silent mode is only an alternative option to installing the agent using “Add Hosts” from console.

“Add Hosts” target wizard will be useful if you want to install the same OMS version of management agent. For example, if your OMS version is 13.2, you can install only 13.2 version agent on target hosts.  If your target host OS version is not compatible with 13.2 version agent then you must go for 12.1.5 agent, this scenario will be opt for this blog.

This post about install 12.1.5 management agent in silent mode,

High level steps:

1.       Download 12.1.0.5 agent software from OEM server.
2.       Update agent.rsp.
3.       Install the agent through agentDeploy.sh script.

Download 12.1.0.5 agent software from OEM server:

./emcli setup -url=https://omsserver.domain.com:7803/em -username="sysman" -password="pwd"
./emcli login -username=sysman
./emcli sync
./emcli get_supported_platforms
./emcli get_agentimage -destination=/installers/OEM/OEM_13c/12C_Agent -platform="Linux x86-64" -version=12.1.0.5.0

Update agent.rsp as below:

OMS_HOST= omsserver.domain.com
EM_UPLOAD_PORT=4903
AGENT_REGISTRATION_PASSWORD=pwd
AGENT_PORT=3874
b_startAgent=true

Install the agent through agentDeploy.sh script:

sh agentDeploy.sh AGENT_BASE_DIR=/u01/app/Agent12c05 RESPONSE_FILE=/installers/OEM/OEM_13c/archives/agent.rsp

Monday, October 23, 2017

Migrating Database from ASM to Non-ASM

Migrating ASM Database to Non-ASM

1.Take a note of files like spfile, controlfile, datafiles, tempfiles, and onlinelog files.

2.Create the pfile from spfile

SQL> create pfile from spfile=’+DATA1/orcl/parameterfile/spfile.786.9846012754′;

File created.

3.Shutdown the database

SQL> shutdown immediate

4. Edit the created pfile

$ vi $ORACLE_HOME/dbs/initorcl.ora

Change the below parameters as per file system.

*.control_files=’/u01/oracle/oradata/’

*.db_create_file_dest=’/u01/oracle/oradata’

*.db_create_online_log_dest_1=’/u01/oracle/oradata’

5.Start the database in NOMOUNT with the new parameter file

SQL> startup nomount pfile='$ORACLE_HOME/dbs/initorcl'

Now the database instance has been started with the new parameter file

6 .Create the Controlfile: Controlfile can be created using RMAN

RMAN> restore controlfile from ‘+DATA1/orcl/backup.268.737822625’;

7. Mount the database: Using the created controlfile mount the database using RMAN

RMAN> alter database mount;

8. Backup the datafiles.

RMAN> backup as copy database format ‘/u01/oracle/oradata/ORCL/datafile/%U’;

9. Switch the database

RMAN> switch database to copy;

10. Open the database: Open the database using backup controlfile

SQL> recover database using backup controlfile until cancel;

Once media recovery completed successfully.

11. Open the database: Open the database with resetlogs

SQL> alter database open resetlogs;

Database altered.

12. Create the TEMP files: Though the RMAN will backup only the datafiles, we need to manually drop the datafiles of ASM and adding new tempfiles.

Drop the tempfile which is part of ASM diskgroup

SQL> alter tablespace def_temp drop tempfile ‘+DATA1/orcl/tempfile/def_temp.264.737822965’;

Adding new tempfile

SQL> alter tablespace def_temp add tempfile '/u01/oracle/oradata/ORCL/datafile/temp1.dbf';

SQL> select name from v$tempfile;

13. Adding onlinelog files:

Identify the existing onlinelog files and they status

SQL> select group#, status from v$log;

GROUP# STATUS
———- —————-
1      ACTIVE
2      CURRENT

Add some more logfile group

SQL> alter database add logfile size 50m;

SQL> alter database add logfile size 50m;

14. Identify the Logfile group status and removing the groups which are part of ASM diskgroup

SQL> select group#, status from v$log;

GROUP# STATUS
———- —————-
1      ACTIVE
2      CURRENT
3      UNUSED
4      UNUSED

15. Perform the manual logswitch so that the logfiles which are part of ASM diskgroup becomes INACTIVE

SQL> alter system switch logfile;

15. Remove the INACTIVE groups which are part of ASM diskgroup

SQL> select group#, status from v$log;

GROUP# STATUS
———- —————-
1      INACTIVE
2      INACTIVE
3      INACTIVE
4      CURRENT

SQL> alter database drop logfile group 1;

SQL> alter database drop logfile group 2;

16. Identify the proper onlinelog files which are part of filesystem

SQL> select group#, member from v$logfile;

"ORA-01092:Oracle instance terminated. Disconnection forced & Corrupted Undo Segments"

Alert log shows lot of trace file and majorly shows

Errors in file d:\oracle\product\10.2.0\admin\hpebsdb\bdump\<instnacename>_smon_1596.trc:
ORA-00600: internal error code, arguments: [kdoirp-1], [2], [], [], [], [], [], []

Tried lot of changes like change the undo tablespaces, smon memory, pmon memory, sga structures all Possibilities.

But same error occurs again and again

Solution:-

So start the db once, and immediately find the recovery segments,
Run the below command.


Select    segment_name,   status from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME                   STATUS
------------------------------ ----------------
_SYSSMU41$                     NEEDS RECOVERY
_SYSSMU100$                    NEEDS RECOVERY

Shutdown & startup mount

Change the undo tablespace.

alter system set undo_tablespace=UNDOTBS2 ;

Accordingly above result some segments need to recovery, So offline the those segments into SGA  level and start the database

Keep the below parameter into pfile
*._offline_rollback_segments=_SYSSMU41$
*._offline_rollback_segments=_SYSSMU100$

And start the database using pfile. Then database successfully started and stable.

Then create spfile from pfile& shutdown database and startup.

  
*._offline_rollback_segments=_SYSSMU41$
==>#*._corrupt_rollback_segments=_SYSSMU41$

*._offline_rollback_segments=_SYSSMU100$ 

==>#*._corrupt_rollback_segments=_SYSSMU100$   

LSINVENTORYSESSION FAILED: RAWINVENTORY GETS NULL ORACLEHOMEINFO


error :

$  opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.8
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /TEST/oracle/TEST/db/tech_st/12.1.0
Central Inventory : /TEST/oracle/TEST/db/tech_st/12.1.0/oraInventory
   from           : /TEST/oracle/TEST/db/tech_st/12.1.0/oraInst.loc
OPatch version    : 12.2.0.1.8
OUI version       : 12.1.0.2.0
Log file location : /TEST/oracle/TEST/db/tech_st/12.1.0/cfgtoollogs/opatch/opatch2017-10-17_08-46-13AM_1.log

List of Homes on this system:

LsInventorySession failed: RawInventory gets null OracleHomeInfo

OPatch failed with error code 73


Solution :
=====

$ cd $ORACLE_HOME/oui/bin

$ cat attachHome.sh
#!/bin/sh
OHOME=/TEST/oracle/TEST/db/tech_st/12.1.0
OHOMENAME=OraDB12Home1
CUR_DIR=`pwd`
cd $OHOME/oui/bin
./runInstaller -detachhome ORACLE_HOME=$OHOME ORACLE_HOME_NAME=$OHOMENAME $* > /dev/null 2>&1
./runInstaller -attachhome ORACLE_HOME=$OHOME ORACLE_HOME_NAME=$OHOMENAME $*
cd $CUR_DIR
$

$ ./attachHome.sh
Starting Oracle Universal Installer...

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

After Fix :
========

$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.8
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /TEST/oracle/TEST/db/tech_st/12.1.0
Central Inventory : /TEST/oracle/TEST/db/tech_st/12.1.0/oraInventory
   from           : /TEST/oracle/TEST/db/tech_st/12.1.0/oraInst.loc
OPatch version    : 12.2.0.1.8
OUI version       : 12.1.0.2.0
Log file location : /TEST/oracle/TEST/db/tech_st/12.1.0/cfgtoollogs/opatch/opatch2017-10-17_08-55-06AM_1.log

Lsinventory Output file location : /TEST/oracle/TEST/db/tech_st/12.1.0/cfgtoollogs/opatch/lsinv/lsinventory2017-10-17_08-55-06AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: devebs.doyensys.com
ARU platform id: 212
ARU platform description:: IBM_AIX

Installed Top-level Products (1):

Oracle Database 12c                                                  12.1.0.2.0
There are 1 products installed in this Oracle Home.


Interim patches (4) :

Patch  23089357     : applied on Sat Sep 16 07:24:46 IST 2017
Unique Patch ID:  20160189
   Created on 10 May 2016, 20:43:37 hrs PST8PDT
   Bugs fixed:
     23089357

Patch  20204035     : applied on Fri Sep 15 22:26:51 IST 2017
Unique Patch ID:  18381868
   Created on 15 Dec 2014, 05:44:27 hrs PST8PDT
   Bugs fixed:
     20123899, 15894842

Patch  19627012     : applied on Fri Sep 15 22:23:48 IST 2017
Unique Patch ID:  18355531
   Created on 7 Dec 2014, 21:56:35 hrs PST8PDT
   Bugs fixed:
     19627012

Patch  19382851     : applied on Fri Sep 15 22:20:06 IST 2017
Unique Patch ID:  18355484
   Created on 7 Dec 2014, 21:36:19 hrs PST8PDT
   Bugs fixed:
     19382851



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

OPatch succeeded.
$

RC-00110: FATAL: ERROR OCCURRED WHILE RELINKING OF APPLYDBTECHSTACK



RC-00110: Fatal: Error occurred while relinking of ApplyDBTechStack
Problem:
I faced the following error while cloning one of our recently upgraded EBS version from 11.5.10 to 12.1.3
RC-00110: Fatal: Error occurred while relinking of ApplyDBTechStack

The contents of log file shows:
Executing script in InstantiateFile:
/app/TEST/testdb/11.2.0/perl/bin/perl -I /app/TEST/testdb/11.2.0/perl/lib/5.8.3 -I /app/TEST/testdb/11.2.0/perl/lib/site_perl/5.8.3 -I /app/TEST/testdb/11.2.0/appsutil/perl /app/TEST/testdb/11.2.0/appsutil/clone/ouicli.pl
script returned:
****************************************************
.end std out.
Perl lib version (5.10.1) doesn't match executable version (v5.10.0) at /usr/lib64/perl5/Config.pm line 50.
Compilation failed in require at /app/TEST/testdb/11.2.0/appsutil/clone/ouicli.pl line 35.
BEGIN failed--compilation aborted at /app/TEST/testdb/11.2.0/appsutil/clone/ouicli.pl line 35.
.end err out.

Cause:
The log file clearly states that issue is due to mismatch of perl lib version with executable version on database tier

Solution:

The temporary fix would be to set the correct version of PERL5LIB environment variable on the Database tier
First check the value of PERL5LIB variable on source system(let's say PROD) and then set the same value of PERL5LIB variable on target system(let's call it TEST).
In my case, the value was set to '5.10.0'. So the variable 'PERL5LIB' on the 11gR2 database tier (target system) needs to be set as follows:
            export PERL5LIB=<ORACLE_HOME>/perl/lib/5.10.0:<ORACLE_HOME>/perl/site_perl/5.10.0:<ORACLE_HOME>/appsutil/perl

To set the value of the context variable s_perl5lib in the Database tier context file and update the PERL5LIB value in the Database tier environment file as well.
•         Run AutoConfig on the Database tier using adconfig.pl script as follows:
1.     perl <ORACLE_HOME>/appsutil/bin/adconfig.pl contextfile=<CONTEXT_FILE>

The permanent fix for the above issue is to modify the correct perl version in file adxdbctx.tmp under:
            <ORACLE_HOME>/appsutil/clone/context/db
            <ORACLE_HOME>/appsutil/template

Again run the cloning process,
            perl adcfgclone.pl dbTier
                 ApplyDatabase Completed Successfully.

Check perl5lib version in context file under <ORACLE_HOME>/appsutil/<CONTEXT_NAME>.xml   -- {CONTEXT_NAME = hostname_SID}
If it shows 5.8.3 then stop the database  and application, modify the per5lib value 5.8.3 to 5.10.0 in XML file.
Start database and listener
Ran autconfig on DB Tier
Start application
Run autoconfig on Apps Tier
adpreclone.pl on db tier and apps tier.

References:
Rapid Clone Error : 11gR2 Database : Perl lib Version (v5.8.5) Doesn't Match Executable Version (v5.10.0) [ID 1276455.1]
Adpreclone Failed With Perl Lib Version (V5.8.3) Doesn'T Match Executable Version (V5.8.5) [ID 1166201.1]


Note: Please make sure to apply the latest AutoConfig and Rapid Clone patches on R12 instance.

Sunday, October 22, 2017

Script – Top SQL (Physical Reads)


set serverout on size 1000000

set feedback off

declare

top5 number;

text1 varchar2(4000);

x number;

len1 number;

Cursor c1 is

select disk_reads,substr(sql_text,1,4000)

from v$sqlarea

order by disk_reads desc;

begin

dbms_output.put_line('Reads'||'  '||'                          Text');

dbms_output.put_line ('-----'||'  '||'----------------------------------------------------');

dbms_output.put_line('      ');

open c1;

for i in 1 .. 5 loop

fetch c1 into top5, text1;

dbms_output.put_line(rpad(to_char(top5),9)|| ' '||substr(text1,1,66));

len1 :=length(text1);

x := 66;

while len1 > x-1 loop

dbms_output.put_line('"         '||substr(text1,x,64));

x := x+64;

end loop;

end loop;

end;

/

Data Guard Switchover Unix shell script



$ cd /var/opt/oracle/dataguard
$ ./pre_switchover_check.sh
$ ./make_me_standby.sh
On the machine where the Standby Database is running we need to run the following script:
$ cd /var/opt/oracle/dataguard
$ ./make_me_primary.sh
After the switchover is completed, we need to run the following script on the machine where the former Primary (now new Standby) database is running:
$ cd /var/opt/oracle/dataguard
$ ./start_recovery.sh
pre_switchover_check.sh

!/bin/ksh

if [ "$1" = "" ]
then
echo ""
echo "##################################################################"
echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT     #"
echo "#      SCRIPT USAGE : pre_switchover_check.sh        #"
echo "##################################################################"
echo ""
exit
fi

ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;

ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
if [ $? != 0 ]
then
echo ""
echo "##################################################################"
echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
echo "##################################################################"
echo ""
exit
fi

. /var/opt/oracle/cronjobs/set$ORACLE_SID

DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;

echo ""
echo "##################################################################"
echo "#            PERFORMING PRE-SWITCHOVER CHECKS FOR $DB            #"
echo "##################################################################"
echo ""

sqlplus  -s /nolog |&
print -p  "connect / as sysdba"
print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off "
print -p  "set sqlprompt ''"
read  -p   JUNK
print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
read  -p   DBROLE
print -p  "SELECT COUNT(FILE#) FILESTAT FROM V\$DATAFILE WHERE STATUS IN ('RECOVER', 'OFFLINE');"
read  -p   FILESTAT
print -p  "SELECT COUNT(FILE#) BKPSTAT FROM V\$BACKUP WHERE STATUS = 'ACTIVE';"
read  -p   BKPSTAT
print -p  "SELECT LOG_ARCHIVED-LOG_APPLIED LOGGAP
FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V\$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#)
FROM V\$ARCHIVED_LOG)),
(SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V\$ARCHIVED_LOG WHERE DEST_ID=2
AND APPLIED='YES'
AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\$ARCHIVED_LOG)) ;"
read  -p   LOGGAP
print -p  "EXIT"

echo "CHECKING CURRENT DATABASE ROLE..."
if [ "$DBROLE" = "PHYSICAL STANDBY"  ]
then
echo ""
echo "##################################################################"
echo "# ERROR ! ! ! NOT  A PRIMARY DATABASE, PLEASE RUN IT ON PRIMARY  #"
echo "##################################################################"
echo ""
exit
else
echo ""
echo "   OK   "
echo ""
fi


echo "CHECKING LOG GAP BETWEEN PRIMARY & STANDBY..."
if [ $LOGGAP -ne 0 ]
then
echo ""
echo "######################################################################"
echo "# ERROR ! ! !  PRIMARY & STANDBY ARE NOT IN SYNC, LOG GAP = $LOGGAP  #"
echo "######################################################################"
echo ""
exit
else
echo ""
echo "   OK   "
echo ""
fi


echo "CHECKING FILES OFFLINE OR RECOVER STATUS..."
if [ $FILESTAT -ne 0 ]
then
echo ""
echo "##################################################################"
echo "#  ERROR ! ! !  ONE OR MORE FILE/S OFFLINE OR IN RECOVER STATUS  #"
echo "##################################################################"
echo ""
exit
else
echo ""
echo "   OK   "
echo ""
fi


echo "CHECKING FILES IN BACKUP MODE..."
if [ $BKPSTAT -ne 0 ]
then
echo ""
echo "##################################################################"
echo "#      ERROR ! ! !  ONE OR MORE FILE/S ARE IN BACKUP MODE        #"
echo "##################################################################"
echo ""
exit
else
echo ""
echo "   OK   "
echo ""
fi

echo ""
echo "##################################################################"
echo "# ALL PRE-SWITCHOVER CHECKS SUCCEEDED... PROCEED WITH SWITCHOVER #"
echo "##################################################################"
echo ""

make_me_standby.sh

#!/bin/ksh

if [ "$1" = "" ]
then
echo ""
echo "##################################################################"
echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT     #"
echo "#        SCRIPT USAGE : make_me_standby.sh           #"
echo "##################################################################"
echo ""
exit
fi

ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;

ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
if [ $? != 0 ]
then
echo ""
echo "##################################################################"
echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
echo "##################################################################"
echo ""
exit
fi

. /var/opt/oracle/cronjobs/set$ORACLE_SID

DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;

sqlplus  -s /nolog |&
print -p  "connect / as sysdba"
print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"
print -p  "set sqlprompt ''"
read  -p   JUNK
print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
read  -p   DBROLE
print -p  "SELECT COUNT(FILE#) FILESTAT FROM V\$DATAFILE WHERE STATUS IN ('RECOVER', 'OFFLINE');"
read  -p   FILESTAT
print -p  "SELECT COUNT(FILE#) BKPSTAT FROM V\$BACKUP WHERE STATUS = 'ACTIVE';"
read  -p   BKPSTAT
print -p  "SELECT LOG_ARCHIVED-LOG_APPLIED LOGGAP
FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V\$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#)
FROM V\$ARCHIVED_LOG)),
(SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V\$ARCHIVED_LOG WHERE DEST_ID=2
AND APPLIED='YES'
AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\$ARCHIVED_LOG)) ;"
read  -p   LOGGAP
print -p  "EXIT"

if [ "$DBROLE" = "PHYSICAL STANDBY"  ]
then
echo ""
echo "##################################################################"
echo "# ERROR ! ! ! NOT  A PRIMARY DATABASE, PLEASE RUN IT ON PRIMARY  #"
echo "##################################################################"
echo ""
exit
fi


if [ $LOGGAP -ne 0 ]
then
echo ""
echo "######################################################################"
echo "# ERROR ! ! !  PRIMARY & STANDBY ARE NOT IN SYNC, LOG GAP = $LOGGAP  #"
echo "######################################################################"
echo ""
exit
fi


echo "CHECKING FILES OFFLINE OR RECOVER STATUS..."
if [ $FILESTAT -ne 0 ]
then
echo ""
echo "##################################################################"
echo "#  ERROR ! ! !  ONE OR MORE FILE/S OFFLINE OR IN RECOVER STATUS  #"
echo "##################################################################"
echo ""
exit
fi


if [ $BKPSTAT -ne 0 ]
then
echo ""
echo "##################################################################"
echo "#      ERROR ! ! !  ONE OR MORE FILE/S ARE IN BACKUP MODE        #"
echo "##################################################################"
echo ""
exit
fi


echo ""
echo "##################################################################"
echo "#              ALL PRE-SWITCHOVER CHECKS SUCCEEDED...            #"
echo "       SWITCHING $DB TO STANDBY ROLE, PLEASE CONFIRM ...          "
echo "##################################################################"
echo ""
echo ""
echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"
read ans
if [ "$ans" = 'Y' -o "$ans" = 'y' ]
then
sqlplus -s  /nolog < /tmp/make_me_standby.log
connect / as sysdba;
startup force;
alter database commit to switchover to standby with session shutdown;
shutdown immediate;
startup nomount;
alter database mount standby database;
select database_role from v\$database;
EOF
cat /tmp/make_me_standby.log
$ORACLE_HOME/bin/lsnrctl stop PRIMARY_$DB
else
echo "Quitting ....."
exit
fi

echo ""
echo "####################################################################"
echo "# PLEASE CONFIRM THAT THE STATUS OF DATABASE IS NOW "STANDBY"      #"
echo "#                                                                  #"
echo "# On OLD STANDBY Host please run the following script:         #"
echo "# /var/opt/oracle/dataguard/make_me_primary.sh                #"
echo "####################################################################"
echo ""
make_me_primary.sh

#!/bin/ksh

if [ "$1" = "" ]
then
echo ""
echo "##################################################################"
echo "#  PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT    #"
echo "#         SCRIPT USAGE : make_me_primary.sh          #"
echo "##################################################################"
echo ""
exit
fi

ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;

ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
if [ $? != 0 ]
then
echo ""
echo "##################################################################"
echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
echo "##################################################################"
echo ""
exit
fi

. /var/opt/oracle/cronjobs/set$ORACLE_SID


DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;


sqlplus  -s /nolog |&
print -p  "connect / as sysdba"
print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"
print -p  "set sqlprompt ''"
read  -p   JUNK
print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
read  -p   DBROLE
print -p  "exit"

if [ "$DBROLE" = "PRIMARY"  ]
then
echo ""
echo "##################################################################"
echo "#   ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY   #"
echo "##################################################################"
echo ""
exit
fi

echo ""
echo "##################################################################"
echo "#         SWITCHING $DB TO PRIMARY ROLE, PLEASE CONFIRM ...      #"
echo "##################################################################"
echo ""
echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"
read ans
if [ "$ans" = 'Y' -o "$ans" = 'y' ]
then
sqlplus -s /nolog < /tmp/make_me_primary.log
connect / as sysdba;
alter database commit to switchover to primary with session shutdown;
shutdown immediate;
startup mount;
alter system set log_archive_dest_state_2=enable scope=both;
alter database set standby database to maximize performance;
alter database open;
select database_role from v\$database;
EOF
cat /tmp/make_me_primary.log
$ORACLE_HOME/bin/lsnrctl start PRIMARY_$DB
else
echo "Quitting ....."
exit
fi

echo "####################################################################"
echo "# PLEASE CONFIRM THAT THE STATUS OF DATABASE IS NOW "PRIMARY"      #"
echo "#                                                                  #"
echo "# On NEW STANDBY Host please run the following script:             #"
echo "# /var/opt/oracle/dataguard/start_recovery.sh                 #"
echo "####################################################################"


start_recovery.sh

#!/bin/ksh

if [ "$1" = "" ]
then
echo ""
echo "##################################################################"
echo "#   PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT   #"
echo "#      SCRIPT USAGE : start_recovery.sh              #"
echo "##################################################################"
echo ""
exit
fi

ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;

ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
if [ $? != 0 ]
then
echo ""
echo "##################################################################"
echo "#           ENVIRONMENT SETTING SET FILE NOT FOUND...!!!         #"
echo "#           /var/opt/oracle/cronjobs/set$ORACLE_SID              #"
echo "##################################################################"
echo ""
exit
fi

. /var/opt/oracle/cronjobs/set$ORACLE_SID

DB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;


sqlplus  -s /nolog |&
print -p  "connect / as sysdba"
print -p  "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"
print -p  "set sqlprompt ''"
read  -p   JUNK
print -p  "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"
read  -p   DBROLE
print -p  "exit"

if [ "$DBROLE" = "PRIMARY"  ]
then
echo ""
echo "##################################################################"
echo "#   ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY   #"
echo "##################################################################"
echo ""
exit
fi

echo ""
echo "##################################################################"
echo "#     STARTING RECOVERY FOR $DB  STANDBY, PLEASE CONFIRM ...     #"
echo "##################################################################"
echo ""
echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"
read ans
if [ "$ans" = 'Y' -o "$ans" = 'y' ]
then
sqlplus -s /nolog < /tmp/start_recovery.log
connect / as sysdba;
recover managed standby database disconnect;
alter system set log_archive_dest_state_2=defer scope=both;
EOF
cat /tmp/start_recovery.log
ps -ef | grep ora_mrp0_$ORACLE_SID |grep -v grep > /dev/null
if [ $? != 0 ]
then
echo ""
echo "##################################################################"
echo "#           RECOVERY PROCESS NOT RUNNING... PLEASE CHECK         #"
echo "##################################################################"
echo ""
exit
else
echo "##################################################################"
echo "#           MRP PROCESS SUCESSFULLY STARTED                      #"
echo "##################################################################"
echo ""
echo "####################################################################"
echo "#   SWITCHOVER COMPLETE                              #"
echo ""
echo "* Perform Database Post-Switchover Checklist!                      "
echo "####################################################################"
fi
else
echo "Quitting ....."
exit
fi


DBUPGDIAG script to check integrity of database before 11g upgrade

ol TODAY NEW_VALUE _DATE
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,’fmMonth DD, YYYY’) TODAY from DUAL;
select version from v$instance;
set termout on
set echo off
set feedback off
set head off
set verify off
Prompt
PROMPT Enter location for Spooled output:
Prompt
DEFINE log_path = &1
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,’dd-Mon-yyyy_hhmi’) timecol,’.log’ spool_extension FROM
sys.dual;
column output new_value dbname
SELECT value || ‘_’ output FROM v$parameter WHERE name = ‘db_name’;
spool &log_path/db_upg_diag_&&dbname&&timestamp&&suffix
set linesize 150
set pages 100
set trim on
set trims on
col Compatible for a35
col comp_id for a12
col comp_name for a40
col org_version for a11
col prv_version for a11
col owner for a12
col object_name for a40
col object_type for a40
col Wordsize for a25
col Metadata for a8
col ‘Initial DB Creation Info’ for a35
col ‘Total Invalid JAVA objects’ for a45
col ‘Role’ for a30
col ‘User Existence’ for a27
col “JAVAVM TESTING” for a15
Prompt
Prompt
set feedback off head off
select LPAD(‘*** Start of LogFile ***’,50) from dual;
select LPAD(‘Oracle Database Upgrade Diagnostic Utility’,44)||
LPAD(TO_CHAR(SYSDATE, ‘MM-DD-YYYY HH24:MI:SS’),26) from dual;
Prompt
Prompt ===============
Prompt Database Uptime
Prompt ===============
SELECT to_char(startup_time, ‘HH24:MI DD-MON-YY’) “Startup Time”
FROM v$instance;
Prompt
Prompt =================
Prompt Database Wordsize
Prompt =================
SELECT distinct(‘This is a ‘ || (length(addr)*4) || ‘-bit database’) “WordSize”
FROM v$process;
Prompt
Prompt ================
Prompt Software Verison
Prompt ================
SELECT * FROM v$version;
Prompt
Prompt =============
Prompt Compatibility
Prompt =============
SELECT ‘Compatibility is set as ‘||value Compatible
FROM v$parameter WHERE name =’compatible’;
Prompt
Prompt ================
Prompt Component Status
Prompt ================
Prompt
SET SERVEROUTPUT ON;
DECLARE
ORG_VERSION varchar2(12);
PRV_VERSION varchar2(12);
P_VERSION VARCHAR2(10);
BEGIN
SELECT version INTO p_version
FROM registry$ WHERE cid=’CATPROC’ ;
IF SUBSTR(p_version,1,5) = ‘9.2.0’ THEN
DBMS_OUTPUT.PUT_LINE(RPAD(‘Comp ID’, 8) ||RPAD(‘Component’,35)||
RPAD(‘Status’,10) ||RPAD(‘Version’, 15));
DBMS_OUTPUT.PUT_LINE(RPAD(‘ ‘,8,’-‘) ||RPAD(‘ ‘,35,’-‘)||
RPAD(‘ ‘,10,’-‘) ||RPAD(‘ ‘,15,’-‘));
FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name,
dr.status Status,SUBSTR(dr.version,1,15) version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) ||
RPAD(SUBSTR(x.comp_name,1,35),35)||
RPAD(x.status,10) || RPAD(x.version, 15));
END LOOP;
ELSIF SUBSTR(p_version,1,5) != ‘9.2.0’ THEN
DBMS_OUTPUT.PUT_LINE(RPAD(‘Comp ID’, 8) ||RPAD(‘Component’,35)||
RPAD(‘Status’,10) ||RPAD(‘Version’, 15)||
RPAD(‘Org_Version’,15)||RPAD(‘Prv_Version’,15));
DBMS_OUTPUT.PUT_LINE(RPAD(‘ ‘,8,’-‘) ||RPAD(‘ ‘,35,’-‘)||
RPAD(‘ ‘,10,’-‘)||RPAD(‘ ‘,15,’-‘)||RPAD(‘ ‘,15,’-‘)||
RPAD(‘ ‘,15,’-‘));
FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status,
SUBSTR(dr.version,1,11) version,org_version,prv_version
FROM dba_registry dr,registry$ r
WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
ORDER BY 1)
LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) ||
RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) ||
RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15));
END LOOP;
END IF;
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt
Prompt ======================================================
Prompt List of Invalid Database Objects Owned by SYS / SYSTEM
Prompt ======================================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN ‘There are no Invalid Objects’
ELSE ‘There are ‘||count(object_name)||’ Invalid objects’
END “Number of Invalid Objects”
FROM dba_objects
WHERE status=’INVALID’
AND owner in (‘SYS’,’SYSTEM’);
Prompt
DOC
################################################################
If there are no Invalid objects below will result in zero rows.
################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status=’INVALID’
AND owner in (‘SYS’,’SYSTEM’)
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ================================
Prompt List of Invalid Database Objects
Prompt ================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN ‘There are no Invalid Objects’
ELSE ‘There are ‘||count(object_name)||’ Invalid objects’
END “Number of Invalid Objects”
FROM dba_objects
WHERE status=’INVALID’
AND owner not in (‘SYS’,’SYSTEM’);
Prompt
DOC
################################################################
If there are no Invalid objects below will result in zero rows.
################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE status=’INVALID’
AND owner not in (‘SYS’,’SYSTEM’)
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ==============================================================
Prompt Identifying whether a database was created as 32-bit or 64-bit
Prompt ==============================================================
Prompt
DOC
###########################################################################
Result referencing the string ‘B023’ ==> Database was created as 32-bit
Result referencing the string ‘B047’ ==> Database was created as 64-bit
When String results in ‘B023’ and when upgrading database to 10.2.0.3.0
(64-bit) , For known issue refer below articles
Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
Upgrading Or Patching Databases To 10.2.0.3
Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
###########################################################################
#
Prompt
SELECT SUBSTR(metadata,109,4) “Metadata”,
CASE SUBSTR(metadata,109,4)
WHEN ‘B023’ THEN ‘Database was created as 32-bit’
WHEN ‘B047’ THEN ‘Database was created as 64-bit’
ELSE ‘Metadata not Matching’
END “Initial DB Creation Info”
FROM sys.kopm$;
Prompt
Prompt ===================================================
Prompt Number of Duplicate Objects Owned by SYS and SYSTEM
Prompt ===================================================
Prompt
Prompt Counting duplicate objects ….
Prompt
SELECT count(1)
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
from dba_objects
where owner = ‘SYS’)
and owner = ‘SYSTEM’;
Prompt
Prompt =========================================
Prompt Duplicate Objects Owned by SYS and SYSTEM
Prompt =========================================
Prompt
Prompt Querying duplicate objects ….
Prompt
SELECT object_name, object_type
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
FROM dba_objects
WHERE owner = ‘SYS’)
AND owner = ‘SYSTEM’;
Prompt
DOC
################################################################################
If any objects found please follow below article.
Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Read the Exceptions carefully before taking actions.
################################################################################
#
Prompt
Prompt ================
Prompt JVM Verification
Prompt ================
Prompt
SET SERVEROUTPUT ON
DECLARE
V_CT NUMBER;
P_VERSION VARCHAR2(10);
BEGIN
— If so, get the version of the JAVAM component
EXECUTE IMMEDIATE ‘SELECT version FROM registry$ WHERE cid=”JAVAVM”
AND status 99’ INTO p_version;
SELECT count(*) INTO v_ct FROM dba_objects
WHERE object_type LIKE ‘%JAVA%’ AND owner=’SYS’;
IF SUBSTR(p_version,1,5) = ‘8.1.7’ THEN
IF v_ct>=6787 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,5) = ‘9.0.1’ THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,5) = ‘9.2.0’ THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,6) = ‘10.1.0’ THEN
IF v_ct>=13866 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
ELSIF SUBSTR(p_version,1,6) = ‘10.2.0’ THEN
IF v_ct>=14113 THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Installed properly’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – Not Installed properly’);
END IF;
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘JAVAVM – NOT Installed. Below results can be ignored’);
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt ================================================
Prompt Checking Existence of Java-Based Users and Roles
Prompt ================================================
Prompt
DOC
################################################################################
There should not be any Java Based users for database version 9.0.1 and above.
If any users found, it is faulty JVM.
################################################################################
#
Prompt
SELECT CASE count(username)
WHEN 0 THEN ‘No Java Based Users’
ELSE ‘There are ‘||count(*)||’ JAVA based users’
END “User Existence”
FROM dba_users WHERE username LIKE ‘%AURORA%’ AND username LIKE ‘%OSE%’;
Prompt
DOC
###############################################################
Healthy JVM Should contain Six Roles.
If there are more or less than six role, JVM is inconsistent.
###############################################################
#
Prompt
SELECT CASE count(role)
WHEN 0 THEN ‘No JAVA related Roles’
ELSE ‘There are ‘||count(role)||’ JAVA related roles’
END “Role”
FROM dba_roles
WHERE role LIKE ‘%JAVA%’;
Prompt
Prompt Roles
Prompt
SELECT role FROM dba_roles WHERE role LIKE ‘%JAVA%’;
set head off
Prompt
Prompt =========================================
Prompt List of Invalid Java Objects owned by SYS
Prompt =========================================
SELECT CASE count(*)
WHEN 0 THEN ‘There are no SYS owned invalid JAVA objects’
ELSE ‘There are ‘||count(*)||’ SYS owned invalid JAVA objects’
END “Total Invalid JAVA objects”
FROM dba_objects
WHERE object_type LIKE ‘%JAVA%’
AND status=’INVALID’
AND owner=’SYS’;
Prompt
DOC
#################################################################
Check the status of the main JVM interface packages DBMS_JAVA
and INITJVMAUX and make sure it is VALID.
If there are no Invalid objects below will result in zero rows.
#################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects
WHERE object_type LIKE ‘%JAVA%’
AND status=’INVALID’
AND owner=’SYS’;
set feedback off
Prompt
Prompt INFO: Below query should succeed with ‘foo’ as result.
set heading on
select dbms_java.longname(‘foo’) “JAVAVM TESTING” from dual;
set heading off
Prompt
set feedback off head off
select LPAD(‘*** End of LogFile ***’,50) from dual;
set feedback on head on
Prompt
spool off
Prompt
set heading off
set heading off
set feedback off
select ‘Upload db_upg_diag_&&dbname&&timestamp&&suffix from “&log_path” directory’
from dual;
set heading on
set feedback on
Prompt

Top 5 Queries for past week based on ADDM recommendations



/*
Top 10 SQL_ID's for the last 7 days as identified by ADDM
from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log
*/

col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" 
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7 
and  a.dbid = (select dbid from v$database) 
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 
and b.advisor_name = 'ADDM' 
and b.task_id = l.task_id 
and l.status = 'COMPLETED') 
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;

Which Database Objects Experienced the Most Number of Waits in the Past One Hour

set linesize 120
col event format a40
col object_name format a40

select * from 
(
  select dba_objects.object_name,
 dba_objects.object_type,
active_session_history.event,
 sum(active_session_history.wait_time +
  active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
    dba_objects
 where 
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
 group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
 order by 4 desc)
where rownum < 6;

Top CPU Consuming SQL During A Certain Time Period



select * from (
select
SQL_ID,
 sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum

Query all Data Pump jobs and session information




SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;

PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | Report   : Data Pump Sessions                                          |
PROMPT | Instance : &current_instance                                           |
PROMPT +------------------------------------------------------------------------+

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN instance_name     FORMAT a9         HEADING 'Instance'
COLUMN owner_name        FORMAT a15        HEADING 'Owner Name'
COLUMN job_name          FORMAT a20        HEADING 'Job Name'
COLUMN session_type      FORMAT a15        HEADING 'Session Type'
COLUMN sid               FORMAT 999999     HEADING 'SID'
COLUMN serial_id         FORMAT 99999999   HEADING 'Serial ID'
COLUMN oracle_username   FORMAT a18        HEADING 'Oracle User'
COLUMN os_username       FORMAT a18        HEADING 'O/S User'
COLUMN os_pid            FORMAT a8         HEADING 'O/S PID'

BREAK ON report ON instance_name_print ON owner_name ON job_name

SELECT
    i.instance_name    instance_name
  , dj.owner_name      owner_name 
  , dj.job_name        job_name
  , ds.type            session_type
  , s.sid              sid
  , s.serial#          serial_id
  , s.username         oracle_username
  , s.osuser           os_username
  , p.spid             os_pid
FROM
    gv$datapump_job         dj
  , gv$datapump_session     ds
  , gv$session              s
  , gv$instance             i
  , gv$process              p
WHERE
      s.inst_id  = i.inst_id
  AND s.inst_id  = p.inst_id
  AND ds.inst_id = i.inst_id
  AND dj.inst_id = i.inst_id
  AND s.saddr    = ds.saddr
  AND s.paddr    = p.addr (+)
  AND dj.job_id  = ds.job_id
ORDER BY
    i.instance_name
  , dj.owner_name
  , dj.job_name
  , ds.type;

Saturday, October 21, 2017

Rename Diskgroup having OCR , Vote File , ASM SPILE

Consider a setup having OCR ,Vote File and ASM SPFILE on a diskgroup. You have a requirement to rename this diskgroup."renamedg" utility can be used to rename of the diskgroup when the diskgroup is dismounted. Our diskgroup is containing vote disk and OCR so we need to use an intermediate diskgroup for storing OCR and Vote File temporarily while renaming the actual diskgroup.
Below assumptions are made :
 1. Name of Temporary diskgroup is TEST.
 2. OCR,Voting Disks and ASM SPFILE are originally stored in diskgroup CRS.
 3. Diskgroup CRS should be renamed to VOS.
 4. $ORACLE_HOME points to GRID Home.
 5. Cluster is up and running in all nodes of RAC.
1) Create a temporary diskgroup :
Login as SYSASM using SQLPLUS into ASM instance on one node and run below commands:
create diskgroup TEMP normal redundancy disk <disk_1> ,<disk_2> ,<disk_3>
attribute 'compatible.rdbms'='11.2.0.0', 'compatible.asm'='11.2.0.0', 'au_size'=<desired_au_size>;
Example:
SQL> create diskgroup TEMP normal redundancy disk 'ORCL:DISK4' ,'ORCL:DISK5' ,'ORCL:DATA4' attribute 'compatible.rdbms'='11.2.0.0', 'compatible.asm'='11.2.0.0', 'au_size'='4M';
Diskgroup created.
2) Move OCR and Vote file from <current diskgroup> to <TEMP> :
$ORACLE_HOME/bin/bin/ocrconfig -add +TEST
$ORACLE_HOME/bin/bin/ocrconfig -delete +CRS
$ORACLE_HOME/bin/crsctl replace votedisk +TEST
Example :
[root@rac1 ~]# $ORACLE_HOME/bin/ocrconfig -add +TEST
[root@rac1 ~]# date
Fri Mar 24 06:11:07 EDT 2017
[root@rac1 ~]# $ORACLE_HOME/bin/ocrconfig -delete +CRS
[root@rac1 ~]# date
Fri Mar 24 06:11:39 EDT 2017
[root@rac1 ~]# $ORACLE_HOME/bin/crsctl replace votedisk +TEST
Successful addition of voting disk 60eb341b8b744fe7bf85a5d513153bcc.
Successful deletion of voting disk 261993a31aa74fccbf7e8d14755cc9bc.
Successfully replaced voting disk group with +TEST.
CRS-4266: Voting file(s) successfully replaced
[root@rac1 ~]# date
Fri Mar 24 06:12:06 EDT 2017
[root@rac1 ~]# $ORACLE_HOME/bin/crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   60eb341b8b744fe7bf85a5d513153bcc (ORCL:TEMP) [TEST]
Located 1 voting disk(s).
[root@rac1 ~]# $ORACLE_HOME/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       2788
         Available space (kbytes) :     259332
         ID                       : 1056661873
         Device/File Name         :      +TEST
                                    Device/File integrity check succeeded
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
                                    Device/File not configured
         Cluster registry integrity check succeeded
         Logical corruption check succeeded
3) Change ASM SPFILE location from <Current diskgroup> to <TEST> diskgroup.That can be verified by below commands :
$ORACLE_HOME/bin/gpnptool get
$asmcmd spget
Example:
[oracle@rac1 ~]$ sqlplus / as sysasm
SQL> create pfile='/tmp/init.txt' from spfile;
File created.
SQL> create spfile='+TEST' from pfile='/tmp/init.txt' ;
File created.
[oracle@rac1 ~]$ asmcmd spget
+TEST/rac-cluster/asmparameterfile/registry.253.939449937
4) Restart CRS on all nodes to startup CRS using new SPFILE from TEMP diskgroup :
crsctl stop crs
crsctl start crs
5) Dismount the OLD Diskgroup on all cluster nodes:
Login as SYSASM using SQLPLUS into ASM instance:-
SQL>alter diskgroup crs dismount; -- on all node
Verify it with : asmcmd lsdg
Example:
SQL> alter diskgroup crs dismount;
Diskgroup altered.
SQL> !date
Fri Mar 24 06:26:34 EDT 2017
6) Rename OLD diskgroup to NEW diskgroup name :
renamedg phase=both dgname=DATA newdgname=CRS asm_diskstring='' verbose=true
Example :
[oracle@rac1 ~]$ renamedg phase=both dgname=CRS newdgname=VOS asm_diskstring='/dev/oracleasm/disks/*' verbose=true
Parsing parameters..
Parameters in effect:
         Old DG name       : CRS
         New DG name          : VOS
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : /dev/oracleasm/disks/*
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: phase=both dgname=CRS newdgname=VOS asm_diskstring=/dev/oracleasm/disks/* verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:/dev/oracleasm/disks/*
Identified disk UFS:/dev/oracleasm/disks/DISK8 with disk number:0 and timestamp (33050006 163859456)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:/dev/oracleasm/disks/*
Identified disk UFS:/dev/oracleasm/disks/DISK8 with disk number:0 and timestamp (33050006 163859456)
Checking if the diskgroup is mounted
Checking disk number:0
Checking if diskgroup is used by CSS
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for /dev/oracleasm/disks/DISK8
Modifying the header
Completed phase 2
Terminating kgfd context 0x2b39e29690a0
SQL> set line 1000
set pages 599
col path format a30
SQL> select name,state,usable_file_mb,total_mb,free_mb,required_mirror_free_mb from v$asm_diskgroup;
NAME                           STATE       USABLE_FILE_MB   TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB
------------------------------ ----------- -------------- ---------- ----------
VOS                            DISMOUNTED               0          0          0                       0
DATA                           MOUNTED               9119      12268       9119                       0
NORM1                          MOUNTED               7044      15336      14400                     312
TEST                           MOUNTED                113        509        113                       0
SQL> select name,path,group_number,header_status,total_mb,free_mb from v$asm_disk;
NAME                           PATH                           GROUP_NUMBER HEADER_STATU   TOTAL_MB    FREE_MB
------------------------------ ------------------------------ ------------ ------------
                                  ORCL:DISK8                                0 MEMBER                0          0
DISK1                          ORCL:DISK1                                2 MEMBER             3067       2274
DISK2                          ORCL:DISK2                                2 MEMBER             3067       2278
DISK3                          ORCL:DISK3                                2 MEMBER             3067       2284
DISK4                          ORCL:DISK4                                2 MEMBER             3067       2283
DISK5                          ORCL:DISK5                                3 MEMBER             5112       4800
DISK6                          ORCL:DISK6                                3 MEMBER             5112       4800
DISK7                          ORCL:DISK7                                3 MEMBER             5112       4800
TEMP                           ORCL:TEMP                                 4 MEMBER              509        113
7) Mount the renamed diskgroup on All the nodes:
Login as SYSASM using SQLPLUS into ASM instance and run below commands:
SQL>alter diskgroup VOS mount; -- on each node
Example:
SQL> alter diskgroup VOS mount;
Diskgroup altered.
SQL> select name,state,usable_file_mb,total_mb,free_mb,required_mirror_free_mb from v$asm_diskgroup;
NAME                           STATE       USABLE_FILE_MB   TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB
------------------------------ ----------- -------------- ---------- ---------- -----------------------
VOS                            MOUNTED                130        494        130                       0
DATA                           MOUNTED               9119      12268       9119                       0
NORM1                          MOUNTED               7044      15336      14400                     312
TEST                           MOUNTED                113        509        113                       0
8) Move OCR and Vote file from TEMP Diskgroup to Renamed Diskgroup :
$ORACLE_HOME/bin/ocrconfig -add +VOS
$ORACLE_HOME/bin/ocrconfig -delete +TEST
$ORACLE_HOME/bin/crsctl replace votedisk +VOS
Example :
[root@rac1 ~]# $ORACLE_HOME/bin/ocrconfig -add +VOS
[root@rac1 ~]# $ORACLE_HOME/bin/ocrconfig -delete +TEST
[root@rac1 ~]# $ORACLE_HOME/bin/crsctl replace votedisk +VOS
Successful addition of voting disk 2654f89dfbe34ffcbf823d24c6502a62.
Successful deletion of voting disk 60eb341b8b744fe7bf85a5d513153bcc.
Successfully replaced voting disk group with +VOS.
CRS-4266: Voting file(s) successfully replaced
[root@rac1 ~]# date
Fri Mar 24 06:59:13 EDT 2017
9) Change ASM SPFILE location from <TEMP diskgroup> to Renamed Diskgroup:
SQL> create spfile='+VOS' from pfile='/tmp/init.txt' ;
File created.
SQL> !asmcmd spget
+VOS/rac-cluster/asmparameterfile/registry.253.939452479
10) Restart CRS on all nodes to startup CRS using new SPFILE from TEMP diskgroup :
crsctl stop crs
crsctl start crs
11) Drop the Intermediate Diskgroup TEST :
Login as SYSASM using SQLPLUS into ASM instance on one node and run below commands::
SQL>drop diskgroup test including contents;
NOTE: Diskgroup TEMP must be mounted in order to be able to drop it.You need to mount it (if it is not already mounted at CRS startup) and then run "drop diskgroup.."
Example :
SQL> drop diskgroup test including contents;
Diskgroup dropped.
12) Need to Remove the Diskgroup Resources TEMP and <OLD Diskgroup Name> from Oracle Clusterware :
srvctl remove diskgroup -g data
srvctl remove diskgroup -g TEMP
Example :-
[root@test ~]# $ORACLE_HOME/bin/srvctl remove diskgroup -g TEST
PRCR-1001 : Resource ora.TEST.dg does not exist
[root@test ~]# $ORACLE_HOME/bin/srvctl remove diskgroup -g CRS
NOTE: Normally diskgroup TEST as resource should be removed by "drop diskgroup.." command, hence you need to run above command only if the resource is not already dropped.
In case of CRS diskgroup resource you need to explicitly remove it using srvctl command as the diskgroup was just renamed not dropped.
13) Ensure ALL Cluster resources are started successfully using below sample commands :
$ORACLE_HOME/bin/crsctl stat res -init -t
$ORACLE_HOME/bin/crsctl check cluster -all
$ORACLE_HOME/bin/crsctl stat res -t