Who's accessing a particular table

While trying to alter any table or index, you may see error similar to below

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

You can find out who's accessing the object and may kill that session to proceed with altering the object.

SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id FROM dba_objects where object_name='EMPLOYEE' and object_type='TABLE' and owner='SCOTT') ;

and the below statement will generate the commands to kill those sesssions.

select 'alter system kill session '||''''||a.SESSION_ID||','||b.serial#||''';' FROM GV$LOCKED_OBJECT a, gv$session b
where a.session_id=b.sid AND OBJECT_ID=(select object_id FROM dba_objects where object_name='EMPLOYEE' and object_type='TABLE' and owner='SCOTT') ;

and the below statement will generate the command to kill sessions on another instance as well.

select 'alter system kill session '||''''||a.SESSION_ID||','||b.serial#||',@'||b.inst_id||''';' FROM GV$LOCKED_OBJECT a, gv$session b
where a.session_id=b.sid AND OBJECT_ID=(select object_id FROM dba_objects where object_name='EMPLOYEE' and object_type='TABLE' and owner='SCOTT')
/

select
o.object_type,
o.object_name,
DECODE(v.locked_mode,
1, 'no lock',
2, 'row share (SS)',
3, 'row exclusive (SX)',
4, 'shared table (S)',
5, 'shared row exclusive (SSX)',
6, 'exclusive (X)') lock_mode,
v.oracle_username,
v.os_user_name,
v.session_id
from
all_objects o,
gv$locked_object v
where
o.object_id = v.object_id;


SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id

Query the session_longops

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done, to_char(sysdate + TIME_REMAINING/3600/24,'MM-DD-YYYY, HH24:MI:SS') end_at from v$session_longops
where totalwork > sofar AND username like 'SCOTT%'
/

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

The ‘ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired’ error can probably be avoided on a running system by setting the session ddl_lock_timeout e.g. ‘alter session set ddl_lock_timeout=5;’ will tell Oracle to retry for 5 seconds.

How to get explain plan and predicate information while running the SQL Statement

SQL> set autotrace traceonly explain
SQL> select sysdate from dual ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

SQL>

Find files for a date and time and then copy to a directory

-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:23 mys1id5_ora_19242.trc
-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:23 mys1id5_ora_19281.trc
-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:23 mys1id5_ora_19317.trc
-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:23 mys1id5_ora_19325.trc
-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:23 mys1id5_ora_19333.trc
-rw-r--r-- 1 mys1idb oragrid 0 Sep 19 21:24 mys1id5_ora_19386.trc

for i in `ls -latR | grep "Sep 19 21" | awk '{print $9}'`; do cp -pr $i /tmp/trace; done

Trick to generate SQL from an Excel File

="INSERT INTO Table (ID, Name) VALUES (" & C2 & ", '" & D2 & "')"

How to resize redo logs for RAC with ASM

Find out the current redo log size

select b.group#,b.thread#,a.member, b.bytes/1024/1024 from v$logfile a, v$log b where a.GROUP#=b.GROUP# order by 1 ;
    GROUP#    THREAD# MEMBER                                                       B.BYTES/1024/1024
---------- ---------- ------------------------------------------------------------ -----------------
         1          1 +RECOVERY/myracdb/onlinelog/group_1.9390.825874743                       1024
         1          1 +RECOVERY/myracdb/onlinelog/group_1.11031.825874749                      1024
         2          1 +RECOVERY/myracdb/onlinelog/group_2.323.825874755                        1024
         2          1 +RECOVERY/myracdb/onlinelog/group_2.11085.825874761                      1024
         3          2 +RECOVERY/myracdb/onlinelog/group_3.324.825874767                        1024
         3          2 +RECOVERY/myracdb/onlinelog/group_3.10010.825874773                      1024
         4          2 +RECOVERY/myracdb/onlinelog/group_4.8913.825874779                       1024
         4          2 +RECOVERY/myracdb/onlinelog/group_4.5641.825874785                       1024

Now create some temporary groups

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 ( '+RECOVERY', '+RECOVERY') SIZE 1024M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 12 ( '+RECOVERY', '+RECOVERY') SIZE 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 13 ( '+RECOVERY', '+RECOVERY') SIZE 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 14 ( '+RECOVERY', '+RECOVERY') SIZE 1024M;

Now you have to keep switch them until you can drop the group 1 thru 4, you can put that in a script and
keep running until you have dropped group 1 thru 4

alter system switch logfile ;
alter system switch logfile ;
alter database drop logfile group 1 ;
alter database drop logfile group 2 ;
alter database drop logfile group 3 ;
alter database drop logfile group 4 ;

Now repeat the process by creating the Group 1 thur 4 with desired size (say 2G each)

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ( '+RECOVERY', '+RECOVERY') SIZE 2048M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ( '+RECOVERY', '+RECOVERY') SIZE 2048M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 ( '+RECOVERY', '+RECOVERY') SIZE 2048M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ( '+RECOVERY', '+RECOVERY') SIZE 2048M;

Now you can drop the temporary groups 11 thru 14

alter system switch logfile ;
alter system switch logfile ;
alter database drop logfile group 11 ;
alter database drop logfile group 12 ;
alter database drop logfile group 13 ;
alter database drop logfile group 14 ;

Redo Threads
Each online redo log has a thread number and a sequence number. The thread number is mainly relevant in RAC databases where
there can be multiple threads; one for each instance. The thread number is not necessarily the same as the instance number.
For single instance databases there is only one redo log thread at any time.

Redo Log Groups
A redo thread consists of two or more redo log groups.

Each redo log group contains one or more physical redo log files known as members. Multiple members are configured to provide
protection against media failure (mirroring). All members within a redo log group should be identical at any time.

Each redo log group has a status. Possible status values include UNUSED, CURRENT, ACTIVE and INACTIVE. Initially redo log
groups are UNUSED. Only one redo log group can be CURRENT at any time. Following a log switch, redo log group continues to be
ACTIVE until a checkpoint has completed. Thereafter the redo log group becomes INACTIVE until it is reused by the LGWR background process.

Log Switches
Log switches occur when the online redo log becomes full. Alternatively log switches can be triggered externally by commands such as:

ALTER SYSTEM SWITCH LOGFILE;

When a log switch occurs, the sequence number is incremented and redo continues to be written to the next
file in the sequence. If archive logging is enabled, then following a low switch the completed online redo log will be copied to the archive
log destination(s) either by the ARCH background process or the LNSn background process depending on the configuration.

ETL Vs ELT - Explanation

Very Nice explanation of the terms ETL and ELT...

http://blog.performancearchitects.com/wp/2013/06/13/etl-vs-elt-whats-the-difference/

- credit goes to the original creator of the content.

Invisible Indexes and its impact on Foreign Keys

Invisible Indexes on Foreign Keys can still be used by Oracle to prevent locking and performance
related issues when delete/update operations are performed on the parent records.

for more information read a very nice article by Richard Foote.
http://richardfoote.wordpress.com/category/invisible-indexes/

ORA-02297: cannot disable constraint ( ........ ) - dependencies exist

SQL> alter table scott.employee disable constraint employee_pk ;
ORA-02297: cannot disable constraint (SCOTT.EMPLOYEE_PK) - dependencies exist

Problem
Disable constraint command fails as the table is parent table and it has foreign
key that are dependent on this constraint.

Fix
There are two things we can do here.
1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

Following query will check dependent table and the dependent constraint name.
After that disable child first and then parent constraint.

SELECT p.table_name "Parent Table", c.table_name "Child Table",
p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
FROM user_constraints p
JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R' AND p.table_name = UPPER('&table_name')
/

The following query will generate a script to drop the child constraints

select 'alter table '||c.table_name||' disable constraint '||c.constraint_name||' ;'
FROM user_constraints p
JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R' AND p.table_name = UPPER('&table_name')
/

2)Disable the constraint with cascade option.

SQL> alter table transaction disable constraint EMPLOYEE_PK cascade;

Track Progress of Database Restore

Find out the name of the restore point
1* select name,time from v$restore_point
SQL> /

NAME TIME
------------------------------ ----------------------------------------
JULY_01_2013 01-JUL-13 08.30.29.000000000 AM

SQL>

Startup the database in mount state
SQL> Shutdown immediate
SQL> startup mount

SQL> flashback database to restore point JULY_01_2013 ;


NOW, Track the Progress of the restore using

SQL> select sid,message from v$session_longops where sofar <> totalwork ;

SID
----------
MESSAGE
--------------------------------------------------------------------------------
1173
Flashback Database: Flashback Data Applied : 43160 out of 52292 Megabytes done


SQL>

Using QUERY with Data Pump Export - expdp

You can use QUERY within expdp to do a selective export of a table
Here's the exact way you need to format your query

QUERY=SCHEMA.table_name:single_quote double_quote QUERY_CLAUSE double_quote single_quote

Below EXAMPLE will export the entire schema SCOTT but from table EMP only rows having EMPNO >= 7900 would be exported.

mylx1 $ expdp scott/tiger directory=data_pump_dir dumpfile=abc.dmp QUERY=scott.emp:'"where EMPNO >= 7900"'

Export: Release 11.2.0.3.0 - Production on Wed Jun 5 08:39:56 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=data_pump_dir dumpfile=abc.dmp query=scott.emp:"where empno>=7900"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.109 KB       3 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/usr/mydb1/expdp/abc.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:40:53

$

addnode gave PRCF-2023 : The following contents are not transferred as they are non-readable.

While adding a node to an existing four node cluster, got the following error.

Copying to remote nodes (Monday, May 20, 2013 8:37:18 AM EDT)
.........................................................................
WARNING:Error while copying directory /usr/local/opt/oracle/product/11.2.0.3 with exclude file list '/tmp/OraInstall2013-05-20_08-36-45AM/installExcludeFile.lst' 
to nodes 'myrac6d5'. [PRKC-PRCF-2015 : One or more commands were not executed successfully on one or more nodes : <null>]
----------------------------------------------------------------------------------
nmsis6d5:
PRCF-2023 : The following contents are not transferred as they are non-readable.
Directories:
-
Files:
1) /usr/local/opt/oracle/product/11.2.0.3/root.sh_11203

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

Refer to '/usr/local/opt/oracle/product/oraInventory/logs/addNodeActions2013-05-20_08-36-45AM.log' for details. 
You may fix the errors on the required remote nodes. Refer to the install guide for error recovery.

The issue in this case was the file permission on the file root.sh_11203

Solution
========
Fix the file permission so that its readable by the user owning the ORACLE_HOME and re-run the add_node command.

SQL Scripts to find TEMP tablespace usage

Here are various scripts which helps in determining who's using the TEMP tablespace.

Script 1

select a.inst_id,b.Total_MB, b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,        
round(used_blocks*8/1024) Current_Used_MB, round(max_used_blocks*8/1024)             
Max_used_MB from gv$sort_segment a,  
(select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b; 

Script 2

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM gv$session a, gv$tempseg_usage b, gv$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks

Script 3

select s.sid, s.osuser, s. process, s.sql_id, tmp.segtype, ((tmp.blocks*8)/1024)MB, tmp.tablespace
from gv$tempseg_usage tmp, gv$session s
where tmp.session_num=s.serial# and segtype in ('HASH','SORT')
order by blocks desc;

Script 4

select sql_id,sum(blocks) from gv$tempseg_usage group by sql_id order by 2 desc;

Useful Oracle Notes in reference to TEMP Tablespace

How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)

TROUBLESHOOTING GUIDE (TSG) : ORA-1652: unable to extend temp segment [ID 1267351.1]

Generate AWR report for a particular SQL

Once you have identified that a particular SQL is causing issues, you can generate the AWR report for a particular SQL between two specific snap_id's for further analysis. SQL "awrsqrpt.sql" is located under $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 66397
Begin Snapshot Id specified: 66397

Enter value for end_snap: 66398
End   Snapshot Id specified: 66398

Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 8p6wy9fr8m0sn
SQL ID specified:  8p6wy9fr8m0sn

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_2_66397_66398.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrsqlrpt_2_66397_66398.html

awrsqlrpt_2_66397_66398.html => This report gives lot of good information about that particular SQL including the Plan Statistics and EXECUTION PLAN

srvctl remove commands

SRVCTL REMOVE DATABASE - Removes a database configuration

Syntax and Options

srvctl remove database -d db_unique_name [-f]
-d db_unique_name
-f 
Force remove the database.

Example

srvctl remove database -d racdb

SRVCTL REMOVE INSTANCE - Removes a database instance configuration

Syntax and Options

srvctl remove instance -d db_unique_name -i inst_name [-f]
-d db_unique_name
-i inst_name
-f 
Force remove the database.

Example

srvctl remove instance -d racdb -i racdb01 -f

SRVCTL REMOVE NODEAPPS - Removes the node application configuration from the specified node. You must have
full administrative privileges to run this command. On Linux and UNIX systems, you must be logged in as root
and on Windows systems, you must be logged in as a user with Administrator privileges.

Syntax and Options

srvctl remove nodeapps -n node_name_list [-f]

Options Description 
-n node_name_list
 Node name or a comma-delimited list of node names.
-f
 Force remove.

Example

srvctl remove nodeapps -n "mynode1,mynode2,mynode3"

srvctl remove listener - Removes the listener from the specified node.

Syntax and Options

srvctl remove listener -n node_name [-l listener_name]

Options Description 
-n node_name
 Name of the node.
-l listener_name
 Name of the listener that you want to remove.

You will see something like below from $GRID_HOME/bin/crsstat output

HA Resource                                   Target     State
-----------                                   ------     -----
ora.LISTENER.lsnr                             ONLINE     ONLINE on myrac1d1
ora.LISTENER_SCAN1.lsnr                       ONLINE     ONLINE on myrac1d4
ora.myrac1d1.ASM1.asm                         ONLINE     ONLINE on myrac1d1
ora.myrac1d1.LISTENER_MYRAC1D1.lsnr           ONLINE     ONLINE on myrac1d1
ora.nmsis1d1.ons                              ONLINE     ONLINE on myrac1d1

Examples
The following command removes the listener LISTENER_MYRAC1D1 from the myrac1d1 node:

srvctl remove listener -n myrac1d1 -l LISTENER_MYRAC1D1

ORA-12012: error on auto execute of job : ORA-01878: specified field not found in datetime or interval

You may see the below errors in your alert log file after the daylight time changes if the job happens to run around
the time when the daylight time changes, which is 2 AM.

ORA-12012: error on auto execute of job 219820676
ORA-01878: specified field not found in datetime or interval

You can run the below queries to find out when the job was scheduled to run and who owns the job

select job ,to_char(LAST_DATE,'YYYYMMDD HH24:MI:SS'),to_char( NEXT_DATE ,'YYYYMMDD HH24:MI:SS') from dba_jobs where NEXT_DATE < sysdate;

select job, what, log_user, priv_user from dba_jobs where job=<job_number> ;

Connect to the database with the priv_user from the above query for that particular job and change the next_date manually by running the following

NOTE: If possible move the time of the job to away from 2 AM time, that is when the time change happens twice every year. I did moved the job to 04:00 AM so you are good for the future as well.

exec DBMS_JOB.NEXT_DATE(219820676,to_date('20130409 04:00:00','YYYYMMDD HH24:MI:SS')) ;

Create Restore Point and Recover the database using Restore Point

To create a flashback restore point, you must be using FRA and flashback must be turned on.
Check to see if flashback is turned on with the following:

Enabling Flashback Database

Step 1 . Set the parameters

ALTER SYSTEM SET  db_recovery_file_dest_size       = 750G   scope = spfile ;
ALTER SYSTEM SET  db_flashback_retention_target    = 240    scope = spfile ;

{ Default is 1440 minutes i.e. 24 hours. Start with 4 hour retention and this can be changed 
dynamically once we have the stats about how much flash back space we need per hour }

Step 2 . Shutdown the database

$ srvctl stop database –d myracdb

Step 3 . Startup mount the database (one node) and turn on Flash Back

SQL> STARTUP MOUNT;  --- > start only one instance manually
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

Step 4 . Make Sure Flashback is Turned ON and shutdown the instance.

SQL> SELECT FLASHBACK_ON FROM v$database ;
SQL> SELECT * FROM v$bgprocess where name='RVWR';
SQL> SHUTDOWN IMMEDIATE ;

Step 5 . Start up RAC instances

$ srvctl start database –d myracdb

Recovery Writer process (RVWR)
The Recovery Writer process is responsible for writing to the flashback logs in the flash recovery area.

These logs are required to restore the database to a previous point in time by using the "flashback" option 
for Oracle databases (Oracle 10g and later).

Create Restore Point

SQL> CREATE RESTORE POINT before_migration GUARANTEE FLASHBACK DATABASE;

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE,
     STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ;

Recover Dataabse with Restore Point

SQL> startup mount
SQL> flashback database to restore point before_migration;
SQL> SHUTDOWN IMMEDIATE

Set the Oracle environment back to the orignal version, run the following:

SQL> startup mount
SQL> alter database open resetlogs;
SQL> drop restore point before_migration;

PRVG-11050 : No matching interfaces "bond0" for subnet "90.xxx.127.0" on nodes "myrac1,myrac2,myrac3,myrac4"

I got the below errors while running the pre-checks before upgrading from 11gR1 (11.1.0.7) to 11gR2 (11.2.0.3)

PRVG-11050 : No matching interfaces "bond0" for subnet "90.xxx.127.0" on nodes "myrac1,myrac2,myrac3,myrac4"

Check: Node connectivity for interface "bond0"
Result: Node connectivity failed for interface "bond0"

Check the following on one of the nodes.

myrac1:/usr/local/opt/oracle/ $ oifcfg iflist -p -n
bond0 90.xxx.126.0 UNKNOWN 255.255.254.0
bond1 172.29.70.0 PRIVATE 255.255.255.0

myrac1:/usr/local/opt/oracle/ $ oifcfg getif
bond0 90.xxx.127.0 global public
bond1 172.29.70.0 global cluster_interconnect

You would notice that values for bond0 after running oifcfg getif
is different when you run oifcfg iflist -p -n

To solve the issue, work with your Network Admin to find out the real values for the bond0 and update.
In our case it should have been 90.xxx.126.0

Login as ROOT

# oifcfg delif -global bond0/90.xxx.127.0

# oifcfg setif -global bond0/90.xxx.126.0:public

Running the above should solve the issue.

RMAN - unregister database from recovery catalog

SQL> select db_key, db_name, reset_time, dbinc_status from RMANCAT.DBINC where db_name = 'MYRACDB' ;

DB_KEY DB_NAME RESET_TIME DBINC_ST
---------- -------- ----------- --------
91068668 MYRACDB 17-nov-2010 PARENT
91068668 MYRACDB 12-mar-2008 PARENT
91068668 MYRACDB 02-may-2011 CURRENT
91068668 MYRACDB 28-apr-2011 ORPHAN
91068668 MYRACDB 27-apr-2011 ORPHAN
91068668 MYRACDB 27-apr-2011 ORPHAN
91068668 MYRACDB 28-apr-2011 ORPHAN

7 rows selected.

SQL> select db_key, db_id from RMANCAT.DB where DB_KEY=91068668;

DB_KEY DB_ID
---------- ----------
91068668 232532794

Now Login as RMAN

${ORACLE_HOME}/bin/rman catalog rmancat/cat@rmandb.world.com

RMAN> set dbid=232532794;

RMAN> unregister database ;

database name is "MYRACDB" and DBID is 232532794

Do you really want to unregister the database (enter YES or NO)? yes
database unregistered from the recovery catalog

RMAN>

Database runInstaller "Nodes Selection" Window Does not Show RAC Nodes

Oracle Clusterware (CRS or GI) is up and running as confirmed by $CRS_HOME/bin/crsctl check crs on all nodes, and $CRS_HOME/bin/olsnodes -n show all the nodes, but database runInstaller does not show all cluster nodes.

There might be an issue with the Inventory for Clusterware home.

Look at inventory.xml file under the oraInventory/ContentsXML directory.
It should show CRS="true" against the correct CRS or GI home and only one entry should have CRS="true" even if there are multiple (older) CRS or GI homes listed.

Do not update the inventory.xml manually. Use the below commands to fix the issue.

$GRID_HOME/oui/bin/runInstaller -silent -ignoreSysPrereqs -updateNodeList ORACLE_HOME="/opt/app/oragrid/oracle/product/11.2.0.3" LOCAL_NODE="myracd1" CLUSTER_NODES="{myracd1,myracd2}" CRS=true

Change the LOCAL_NODE to point to the node from where you are running the command. This needs to be run from every node where you want the inventory.xml updated.

If another CRS_HOME also has CRS="true" as in example below.

then use the below command to set it to false.

$GRID_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME="/opt/app/t5cim1d/oracle/product/crs" CRS=false

Oracle Notes 1327486.1 and 1053393.1 has more details.

Test Box

Font 1
Font 2
Font 3
Font 4
Font 5

abc
abc
abc
abc

put
your
content
here

select job ,to_char(LAST_DATE,'YYYYMMDD HH24:MI:SS'),to_char( NEXT_DATE ,'YYYYMMDD HH24:MI:SS') from dba_jobs where NEXT_DATE < sysdate; select job, what, log_user, priv_user from dba_jobs where job= ;

select job ,to_char(LAST_DATE,'YYYYMMDD HH24:MI:SS'),to_char( NEXT_DATE ,'YYYYMMDD HH24:MI:SS') from dba_jobs where NEXT_DATE < sysdate; select job, what, log_user, priv_user from dba_jobs where job= ; select job ,to_char(LAST_DATE,'YYYYMMDD HH24:MI:SS'),to_char( NEXT_DATE ,'YYYYMMDD HH24:MI:SS') from dba_jobs where NEXT_DATE < sysdate; select job, what, log_user, priv_user from dba_jobs where job= ;

11gR1 to 11gR2 Upgrade: cluvfy tool found some mandatory patches are not installed

The cluvfy tool found some mandatory patches are not installed.
These patches need to be installed before the upgrade can proceed.
The pre-upgrade checks failed, aborting the upgrade

The above error is mis-leading sometimes. Look under the log file at
$GRID_HOME/cfgtoollogs/crsconfig and re-run the cluvfy commands listed there manually by removing the "-_patch_only"

/bin/su oragrid -c ' /opt/app/oragrid/oracle/product/11.2.0.3/bin/cluvfy stage -pre crsinst -n myrac1,myrac2 -upgrade -src_crshome /opt/app/oracle/product/crs -dest_crshome /opt/app/oragrid/oracle/product/11.2.0.3 -dest_version 11.2.0.3.0 '

if the above comes back without issues then the problem is the environment variables ORA_CRS_HOME. If that is set at the session from where you are running rootupgrade.sh, then you will see the above error.

unset ORA_CRS_HOME and re-run the rootupgrade.sh and it should finish without errors.

Oracle Note 1498538.1 has more details about it as well.

Oracle DB 11gR2 Global AWR Report Generation

Oracle DB 11gR2 AWR Global Report Generation

Before 11gR2, the awrrpt.sql under $ORACLE_HOME/rdbms/admin only generates awr report for local instance.
You have to collect awr report for each of RAC instances.

In 11gR2 there are two new scripts awrgrpt.sql AND awrgdrpt.sql for RAC

awrgrpt.sql -- AWR Global Report (RAC) (global report)
awrgdrpt.sql -- AWR Global Diff Report (RAC)

Some other important scripts under $ORACLE_HOME/rdbms/admin

spawrrac.sql -- Server Performance RAC report
awrsqrpt.sql -- Standard SQL statement Report
awrddrpt.sql -- Period diff on current instance
awrrpti.sql -- Workload Repository Report Instance (RAC)

PRCD-1231 : Failed to upgrade configuration of database and PRKC-1136

PROBLEM: After upgrading the database from 11.1.0.7 to 11.2.0.3 (using MANUAL Method), unable to update the CRS with new version of the database

rklx1:11gr2_upgrade/ $ srvctl upgrade database -d racdb -o /usr/local/opt/oracle/product/11.2.0.3
PRCD-1231 : Failed to upgrade configuration of database racdb to version 11.2.0.3.0 in new Oracle home /usr/local/opt/oracle/product/11.2.0.3
PRKC-1136 : Unable to find version for database with name racdb

rklx1:11gr2_upgrade/ $ srvctl remove database -d racdb
PRCD-1120 : The resource for database racdb could not be found.
PRCR-1001 : Resource ora.racdb.db does not exist

SOLUTION:

Login as ROOT to node 1

#$GRID_HOME/bin/./crs_unregister ora.racdb.racdbt4.inst
#$GRID_HOME/bin/./crs_unregister ora.racdb.racdbt3.inst
#$GRID_HOME/bin/./crs_unregister ora.racdb.racdbt2.inst
#$GRID_HOME/bin/./crs_unregister ora.racdb.racdbt1.inst
#$GRID_HOME/bin/./crs_unregister ora.racdb.db

Then Login as Oracle User and Add the database and the instance

srvctl add database -d racdb -o $ORACLE_HOME
srvctl add instance -d racdb -i racdbt1 -n rklx1
srvctl add instance -d racdb -i racdbt2 -n rklx2
srvctl add instance -d racdb -i racdbt3 -n rklx3
srvctl add instance -d racdb -i racdbt4 -n rklx4

and then start the database

srvctl start database -d racdb

PRVG-11055 : Interfaces configured with subnet number "90.xxx.xxx.0" have multiple subnets masks

Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "172.29.70.0".
PRVG-11055 : Interfaces configured with subnet number "90.xxx.xxx.0" have multiple subnets masks
PRVG-11056 : subnet masks "255.255.254.0" are configured with subnet number "90.xxx.xxx.0" on nodes "rklx4,rklx3,rklx2,rklx1"
PRVG-11056 : subnet masks "255.255.255.0" are configured with subnet number "90.xxx.xxx.0" on nodes "rklx4,rklx3,rklx2,rklx1"
Subnet mask consistency check failed.

Result: Node connectivity check failed

SOLUTION
========

# $ORA_CRS_HOME/bin/oifcfg iflist -p -n

bond0 172.xx.xx.0 PRIVATE 255.255.255.0
bond1 90.xxx.xxx.0 UNKNOWN 255.255.254.0



# $ORA_CRS_HOME/bin/crs_stat -p ora.rklx1.vip =====> run this on all nodes of the cluster

You need to modify the subnet mask by running the following

srvctl modify nodeapps -n rklx1 -A 90.xxx.xxx.166/255.255.254.0/bond1

srvctl modify nodeapps -n rklx2 -A 90.xxx.xxx.61/255.255.254.0/bond1

srvctl modify nodeapps -n rklx3 -A 90.xxx.xxx.114/255.255.254.0/bond1

srvctl modify nodeapps -n rklx4 -A 90.xxx.xxx.133/255.255.254.0/bond1

How to remove Disks from Disk Group

=> Find out the group number and name :

SQL> select group_number, name from v$asm_diskgroup ;

GROUP_NUMBER NAME
------------ ------------------------------
1 DATA
2 RECOVERY
3 GRID

=> Find out the name of the disk belonging to GROUP_NUMBER=3 which is GRID Disk Group.

SQL> select DISK_NUMBER, name, failgroup, group_number from v$asm_disk where group_number=3 order by name ;

DISK_NUMBER NAME FAILGROUP GROUP_NUMBER
----------- -------------- ---------------------- ------------
0 ASM2_VMAX00639 ASM2_VMAX00639 3
1 ASM2_VMAX0063A ASM2_VMAX0063A 3

=> so from above, there are two disks belonging to GRID diskgroup, now we'll remove one of the disks from the diskgroup
=> Drop the Disk from diskgroup named GRID

SQL> alter DISKGROUP GRID drop disk ASM2_VMAX00639 ;


=>You can check the re-balance progress using below SQL

SQL> select * from v$asm_operation;

How to Display Directory Structure Linux/Unix

The below command displays the directory tree structure

ls -R | grep ":$" | sed -e 's/:$//' -e 's/[^-][^\/]*\//--/g' -e 's/^/ /' -e 's/-/|/'

More information at http://www.centerkey.com/tree/