Sunday, April 9, 2017

LsInventorySession failed: RawInventory gets null OracleHomeInfo


On my 2 Node RAC db when I invoke “opatch lsinventory” it is failing with below error message

oracleprod01:CUSTDB01:/u01/app/oracle/product/12.1.0.2/dbhome_1/OPatch $ ./opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.8
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.1.0.2/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0.2/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.8
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatch/opatch2017-03-22_21-10-36PM_1.log

List of Homes on this system:

  Home name= OraGI12Home1, Location= "/u01/app/12.1.0.2/grid"
  Home name= OraDb11g_home1, Location= "/u01/app/oracle/product/11.2.0.4/dbhome_1"
LsInventorySession failed: RawInventory gets null OracleHomeInfo

OPatch failed with error code 73


Solution:

oracleprod01:CUSTDB01:/u01/app $ cd oraInventory/
oracleprod01:CUSTDB01:/u01/app/oraInventory $ ls
backup  ContentsXML  logs  oraInst.loc  orainstRoot.sh
oracleprod01:CUSTDB01:/u01/app/oraInventory $ cat oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=oinstall

oracleprod01:CUSTDB01:/u01/app/oraInventory $ cd ContentsXML
oracleprod01:CUSTDB01:/u01/app/oraInventory/ContentsXML $ ls
comps.xml  inventory.xml  libs.xml

oracleprod01:CUSTDB01:/u01/app/oraInventory/ContentsXML $ cat inventory.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2013, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.4.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraGI12Home1" LOC="/u01/app/12.1.0.2/grid" TYPE="O" IDX="1" CRS="true">
   <NODE_LIST>
      <NODE NAME="oracleprod01"/>
      <NODE NAME="oracleprod02"/>
      <NODE NAME=" oracleprod03"/>
      <NODE NAME="oracleprod04"/>
   </NODE_LIST>
</HOME>
<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0.4/dbhome_1" TYPE="O" IDX="2">
   <NODE_LIST>
      <NODE NAME="oracleprod01"/>
      <NODE NAME="oracleprod02"/>
      <NODE NAME=" oracleprod03"/>
      <NODE NAME="oracleprod04"/>
   </NODE_LIST>
</HOME>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>


From the above output we can see that my 12c oracle home is missing so then I have added it then "opatch" went success

oracleprod01:NOTSET:/u01/app/oraInventory/ContentsXML $ cat /u01/app/oraInventory/ContentsXML/inventory.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2013, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.4.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraGI12Home1" LOC="/u01/app/12.1.0.2/grid" TYPE="O" IDX="1" CRS="true">
   <NODE_LIST>
      <NODE NAME="oracleprod01"/>
      <NODE NAME="oracleprod02"/>
      <NODE NAME="oracleprod03"/>
      <NODE NAME="oracleprod04"/>
   </NODE_LIST>
</HOME>
<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0.4/dbhome_1" TYPE="O" IDX="2">
   <NODE_LIST>
      <NODE NAME="oracleprod01"/>
      <NODE NAME="oracleprod02"/>
      <NODE NAME=" oracleprod03"/>
      <NODE NAME="oracleprod04"/>
   </NODE_LIST>
</HOME>
<HOME NAME="OraDb12c_home1" LOC="/u01/app/oracle/product/12.1.0.2/dbhome_1" TYPE="O" IDX="3">
   <NODE_LIST>
      <NODE NAME="oracleprod01"/>
      <NODE NAME="oracleprod02"/>
      <NODE NAME="oracleprod03"/>
      <NODE NAME="oracleprod04"/>
   </NODE_LIST>
</HOME>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>


Refer more:

OPatch Fails With "LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo" (Doc ID 728417.1)


Sunday, April 2, 2017

12c Goldengate install steps


First Download the Binaries from OTN .......



Copy the downloaded zip to GG location on the linux box

oradev01.domain.com:MYDEV:/opt/oracle/GG_SOURCE/soft $ ll
total 464472
-rw-r--r-- 1 oracle dba 475611228 Feb  3 13:22 fbo_ggs_Linux_x64_shiphome.zip

oradev01.domain.com:MYDEV:/opt/oracle/GG_SOURCE/soft $ unzip fbo_ggs_Linux_x64_shiphome.zip

oradev01.domain.com:NOTSET:/opt/oracle/GG_SOURCE/soft $ ll
total 464752
drwxr-xr-x 3 oracle dba      4096 Dec 12  2015 fbo_ggs_Linux_x64_shiphome
-rw-r--r-- 1 oracle dba 475611228 Feb  3 13:22 fbo_ggs_Linux_x64_shiphome.zip
-rw-r--r-- 1 oracle dba    282294 Jan 18  2016 OGG-12.2.0.1.1-ReleaseNotes.pdf
-rw-r--r-- 1 oracle dba      1559 Jan 18  2016 OGG-12.2.0.1-README.txt

oradev01.domain.com:NOTSET:/opt/oracle/GG_SOURCE/soft $ cd fbo_ggs_Linux_x64_shiphome

oradev01.domain.com:NOTSET:/opt/oracle/GG_SOURCE/soft/fbo_ggs_Linux_x64_shiphome $ ls
Disk1

oradev01.domain.com:NOTSET:/opt/oracle/GG_SOURCE/soft/fbo_ggs_Linux_x64_shiphome $ cd Disk1/

oradev01.domain.com:NOTSET:/opt/oracle/GG_SOURCE/soft/fbo_ggs_Linux_x64_shiphome/Disk1 $ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 11179 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 139897 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /var/tmp/OraInstall2017-02-03_03-38-02PM. Please wait ...oradev01.domain.com:NOTSET:/opt/oracle/GG_SOURCE/soft/fbo_ggs_Linux_x64_shiphome/Disk1 $
















oradev01.domain.com:MYDEV:/opt/oracle/ $ cd GG_SOURCE/home/
oradev01.domain.com:MYDEV:/opt/oracle/GG_SOURCE/home $ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (oradev01.domain.com.ux.corp.local) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (oradev01.domain.com.ux.corp.local) 2> view params mgr

PORT 7809



Saturday, April 1, 2017

ENABLE BI publisher in 13c OEM



[oracle@oradev01.domain.com bin]$ ./emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up
BI Publisher Server is Down
BI Publisher is disabled, to enable BI Publisher on this host, use the 'emctl config oms -enable_bip' command

[oracle@oradev01.domain.com bin]$ ./emctl status oms -bip_only
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
BI Publisher Server is Down
BI Publisher is disabled, to enable BI Publisher on this host, use the 'emctl config oms -enable_bip' command

Now, ENABLE BIP using below command

[oracle@oradev01.domain.com bin]$ ./emctl config oms -enable_bip
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
BI Publisher is enabled for startup on this host with the 'emctl start oms'  and 'emctl start oms -bip_only' commands.
Overall result of operations: SUCCESS

[oracle@oradev01.domain.com bin]$ ./emctl start oms -bip_only
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Starting BI Publisher Server only.
Starting BI Publisher Server ...
WebTier Successfully Started
BI Publisher Server Successfully Started
BI Publisher Server is Up

[oracle@oradev01.domain.com bin]$ ./emctl status oms -bip_only
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
BI Publisher Server is Up

[oracle@oradev01.domain.com bin]$ ./emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up
BI Publisher Server is Up



ORA-39113: Unable to determine database version during impdp network_link

SOURCE and TARGET databases are 12c databases

First copy the TNS of Source DB to the target side and test whether you are able to do tnsping the source db which we have copied

From Target DB:

Create a database link pointing to Source DB

SQL> create database link MYLINK connect to system identified by ****** using 'MYTARGET_TNS';

Database link created.

oradev01.domain.com:MYSOURCE1:/opt/oracle/scripts $ impdp nologfile=y network_link=MYTARGET parallel=8 sqlfile=abc.sql content=metadata_only directory=EXP_DIR

Import: Release 12.1.0.2.0 - Production on Fri Mar 3 16:20:14 2017

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

Username: system
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
ORA-39006: internal error
ORA-39113: Unable to determine database version
ORA-06550: line 1, column 7:
PLS-00352: Unable to access another database 'MYTARGET'
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS@MYTARGET' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

ORA-39097: Data Pump job encountered unexpected error -6550


SQL> select * from dba_db_links;

OWNER
--------------------------------------------------------------------------------
DB_LINK
--------------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
HOST
--------------------------------------------------------------------------------
CREATED
---------
SYS
MYTARGET
SYSTEM

MYTARGET_TNS
03-MAR-17


SQL> sho parameter global

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
global_names                         boolean     FALSE
global_txn_processes                 integer     1


Reason:

According to Oracle "private db links would not work in expdp/impdp"


SOLUTION:

SQL> create public database link MYTARGET_LINK connect to system identified by *****  using 'MYTARGET_TNS';

Database link created.

SQL> set pages 999
SQL>  select * from dba_db_links;

OWNER
--------------------------------------------------------------------------------
DB_LINK
--------------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
HOST
--------------------------------------------------------------------------------
CREATED
---------
SYS
MYTARGET
SYSTEM
MYTARGET_TNS
03-MAR-17

PUBLIC
MYTARGET_LINK
SYSTEM
MYTARGET_TNS
03-MAR-17


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oradev01.domain.com:MYSOURCE1:/opt/oracle/scripts $ impdp nologfile=y network_link=MYTARGET_link parallel=8 sqlfile=abc.sql content=metadata_only directory=SCRIPTS_DIR      schemas=myschema

Import: Release 12.1.0.2.0 - Production on Fri Mar 3 19:19:20 2017

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

Username: system
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01":  system/******** nologfile=y network_link=MYTARGET_link parallel=8  content=metadata_only directory=SCRIPTS_DIR schemas=myschema
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/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT


Reference:

ORA-39113 When Running A DataPump Job Through NETWORK_LINK (Doc ID 2100177.1)



My DBA Commands


Oracle Recommended Patches -- Oracle Database (Doc ID 756671.1)
Master Note for Oracle GoldenGate Core Product Patch Sets (Doc ID 1645495.1)

EXCLUDE(or)INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"
INCLUDE= role,profile,tablespace

$ ps -ef|grep oracle|grep -v ora_ |grep -v LOCAL=NO|grep -v LSNR|grep -v sshd|grep -v LOCAL=YES|grep -v bash|grep -v oemagent

$ export TMOUT=0      -----> To avoid closing of putty session from closing after sometime
$ env | more    à lists everything set to the database
$ host –d    à gives Domain name
Cat /etc/oratab   ---> shows HOME location
$ unalias ls     à to get rid of  the colors for the directories
$export ORALCE_HOME=
$ export PATH=$PATH: $ORACLE_HOME/bin

To get IP Address of host:
$ Host machine_name                     where machine_name=hostname
Ex:  Host   dev41.dxx.xxx.wxxxx.com
dev41.dxx.xxx.wxxxx.com   has address 10.0.8.138

To see the Current memory and SWAP usage
$ free

To see the current HUGE pagesize
grep  Huge /proc/meminfo

To see the OS release version
cat    /etc/redhat-release       (For only  Linux)
cat   /etc/issue                         (General )
How to know Linux machine is 32 or 64 bit?
# uname  -m
If   i386 or i686 then it is 32 bit
if   x86_64 then it is 64bit

Check Kernel:
rpm -qa --queryformat '%{installtime} (%{installtime:date}) %{n}-%{v}-%{r}\n' | sort -n | grep -e kernel-devel -e oracleasm | tail -6
Find my HP-Unix machine is 32 or 64 bit?
Method 1:
# getconf   KERNEL_BITS

Method 2:
# ps -ef |grep RT

Method 3:
# file /stand/vmunix

If 64 bit it will show something like: ELF-64 executable object file

How to know Sun Solaris machine is 32 or 64 bit?
# isainfo -v

Find SGA size:
SELECT name,value/1024/1024 "SGA (MB)" FROM v$sga;
SELECT sum(value)/1024/1024 "TOTAL SGA (MB)" FROM v$sga;
Select POOL, Round(bytes/1024/1024,0) Free_Memory_In_MB From V$sgastat Where Name Like '%free memory%';
select * from v$sgainfo;

To find the no. of CPU’s   in a db:
SQL > Show parameter cpu        (or)
$ cat /proc/cpuinfo                             (see the processors)    (or)
$ cat /proc/cpuinfo | grep "processor"|wc -l              (LINUX)  (or)   lscpu
$ ioscan -C processor | grep processor | wc -l          (HP-UX)
$ psrinfo -v|grep "Status of processor"|wc -l           (SOLARIS)
$ lsdev -C|grep Process|wc -l                                 (AIX)

Delete OS files older than ‘X’ days :
find /path/to/files* -mtime +5 -exec rm {} \;

Possible error (if there are Huge files): /bin/rm: Argument list too long
Sol:   find /path/to/files* -mtime +5  | xargs rm

Total DATABASE info:
select dbid, name, instance_name, host_name, created, log_mode, open_mode from v$database, v$instance;

Archive log generation on on daily basis:
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from Gv$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;



To count all OBJECTS in particular Schema:
SQL>select object_type,count(*) from dba_objects where owner='CORP' group by object_type;
OBJECT_TYPE          COUNT(*)
------------------             ----------
PACKAGE BODY                4
PROCEDURE                      61
VIEW                                    3
TABLES                              24

To LIST all OBJECTS in All schemas in database:
select owner,object_type,count(*) from dba_objects where owner in (select distinct owner from dba_segments)  group by object_type,owner order by 1;


To count all OBJECTS in particular Tablespace:
select OWNER,SEGMENT_TYPE,count(*) from DBA_SEGMENTS where TABLESPACE_NAME ='Tablespace_name'  group by OWNER,SEGMENT_TYPE order by 1;

Objects created from past ‘x’ days:
SQL> select object_name, object_type from dba_objects where sysdate-created <x;
SQL > select OWNER,OBJECT_TYPE,count(*) from dba_objects where   sysdate-created <x group by OWNER,OBJECT_TYPE;

How to check the last DML  happened in a table
SELECT MAX(ORA_ROWSCN) FROM  yourtable_name;
select scn_to_timestamp(input from above query) from dual;

To check invalid objects:
select object_type,count(*) from dba_objects where status='INVALID' group by object_type;

To check USED & FREE space of ARCHIVELOG destination/FRA:
Archive log list
select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

SELECT FILE_TYPE "Type",PERCENT_SPACE_USED "% Used",PERCENT_SPACE_RECLAIMABLE "% Reclaim",
NUMBER_OF_FILES "# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;



 REDO LOG:

Redo Logs info:
set linesize 300
column REDOLOG_FILE_NAME format a50

SELECT     a.GROUP#,    a.THREAD#,  a.SEQUENCE#,  a.ARCHIVED,  a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,  (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a  JOIN  v$logfile b  ON  a.Group#=b.Group#  ORDER BY a.GROUP# ASC;


Nohup
$ Nohup   run_sql.sh   indexes &         (output creates as indexes.out)
$ nohup sqlplus / as sysdba @move_lob.sql &

To see MAXDATAFILE parameter in control file
SQL>  select  records_total  from  v$controlfile_record_section   where   type = 'DATAFILE';
SQL>  show parameter db_files;
SQL >  Alter system set db_files=700 scope=both;

SQL >  Create pfile  from spfile;             (wrong, throws an error)
SQL>  create pfile=’/var/backup/corppsdb/abc.ora’ from spfile ;          (right)

Copying CONTROL FILE
We copy control file using CP command to some location it will create but we can’t open or edit
So we can copy control file using,
SQL> Alter database backup control file to trace (or)
SQL> Alter database backup control file to ‘../../..’

SQL> sho parameter NLS_LENGTH_SEMANTICS;
SQL >   ALTER  SYSTEM  SET  NLS_LENGTH_SEMANTICS=CHAR;
or
SQL >   ALTER  SYSTEM  SET  NLS_LENGTH_SEMANTICS=BYTE;


Create MAX EXTENTS size to unlimited

Spool newrun.sql

SQL> select 'alter'||' '||object_type||' '||object_name||' '||'move storage (maxextents unlimited);' from dba_objects where object_type in ('TABLE','INDEX') and owner = 'SYSFMSC';

Set head off
set pagesize 50000

:
SQL> @newrun.sql


Total no. of USERS connected to database :
SQL> select username, osuser from v$session where username is not null;
Or
select count(*) from v$session where username is not null and username != 'SYS';

Total connections to DB:
Set linesize 280;
select count(*),INST_ID,username,osuser,status,machine from gv$session group by INST_ID,username,osuser,status,machine order by count(*),INST_ID;

To see the udump,bdump,cdump locations in 10g :
SQL> show parameter user_dump_dest;               (similarly all)

To see the udump,bdump,cdump locations in 11g:
$  env |grep diag

To see the last lines in logfile or Trace file :
SQL> tail -20 alertlog(or)tracefile                      (to display last 20 lines)

To see Tablespace and its datafiles:
SQL> select FILE_NAME, TABLESPACE_NAME, STATUS from dba_data_files where TABLESPACE_NAME='CORP_2' ;

To see all data files, Control files and redo logs including TEMP file in one query:
SQL> select name from v$datafile union select name from v$tempfile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;

While writing spool

SQL> set echo off
SQL> set verify off
SQL> set heading off
SQL> set pagesize 5000
SQL> set feedback off

SQL> select 'alter index'||' '||INDEX_NAME||' '||'storage (maxextents 2147483645);' from user_indexes where MAX_EXTENTS is NULL;
================================================================
INDEXES:

List all indexes on a table:
Select owner, table_name, index_name, column_name FROM user_ind_columns Order by owner, table_name, column_name Where table_name=’TABLE_NAME’;

To see Tables, indexes and their Tablespace :
select INDEX_NAME, TABLE_NAME, TABLESPACE_NAME from user_indexes;

Moving index to another Tablespace:
alter index <index_name> rebuild tablespace  <tablespace_name>;

How to find if index is LOCAL or GLOBAL in oracle:
select index_name, locality from all_part_indexes where table_name='tab_name’;

Find no. of objects of a user in each tablespace
select OWNER,SEGMENT_TYPE,TABLESPACE_NAME,count(*) from dba_segments where OWNER='DDS_DISTRIBUTE_MODE' group by OWNER,SEGMENT_TYPE,TABLESPACE_NAME;

To make index extent size to unlimited:
Alter index Ind_name rebuild online storage (MAXEXTENTS  Unlimited);

Script for the same :
Spool runme.sql

Select 'alter index'||' '||INDEX_NAME||' '||'rebuild online storage(MAXEXTENTS  Unlimited);' from user_indexes;
                             or
select 'alter index'||' '||INDEX_NAME||' '||'rebuild online storage(MAXEXTENTS  Unlimited);' from user_indexes where max_extents is null;

TABLE :
Count Number of COLUMNS in a Table:
select count(column_name) from user_tab_columns  where  table_name=’TABLE_NAME’;

To change table data type
alter  table  table_name  modify( name varchar2(32));                       (changed from 30 to 32)

Finding SIZE of a TABLE:
select sum(BYTES)/1024/1024/1024 GB from user_segments where SEGMENT_NAME=’tab_name’;

Find LOCK on a TABLE:
select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b where a.object_id=b.object_id

Moving Table to another Tablespace:
SQL> alter table tab_name move tablespace <new_tablespace_name>;

============================================================================
LINUX

Sqlplus commands from LINUX:  echo “sql_statement” | sqlplus –s username/passwd
Ex: echo "select count(*) from tab;"  | sqlplus -s / as sysdba
  COUNT(*)
----------
      4740

tar -czvf name-of-archive.tar.gz /path/to/directory-or-file
z: Compress the archive with gzip.       -c: Create         -f : Filename

Count specific word in Linux file:
grep -o 'CREATE SEQUENCE'  abc.txt | wc –l

Find command :  find –name abc.sql
To see LISTENER status :     $ ps -fu oracle |grep tns
To see all HOME locations :         $ env | grep HOME           (use CAPS)
                                                                $ env | grep ORACLE_HOME
                                                                $ echo  $ORACLE_HOME
Similarly for ASM :                          $  env |grep  ASM
To see all SID’s :                                $  env |grep SID
                                                                $ env |grep ORACLE_SID    (for only Oracle_sid)

To see everything :                        $ env | grep ORACLE   (use CAPS)
Ex:  > env |grep ORACLE
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0.2
ORACLE_HOSTNAME=vcd04124.ute.fedex.com
ORACLE_SID=VCD04124
ORACLE_UNQNAME=VCD0412

To know which shell are we using in database :    env | grep SHELL

NOTE :  Use coraenv when using the C Shell and oraenv when using a Bourne, Korn or Bash shell.

Running   .oraenv  à    .  ./.oraenv
Running   .profile  à     .  .profile

Find SIZE of files/directories

$ du -sh * | sort -n       (Display all sizes of files in a current path only)    
$ du  -sh          (Displays total size of the directory where you are in)
$ df  -h              (shows all the used  and free sizes on mount points)
   df  -h    /var/backup          (to see for a particular mount point)
$ stat  (file(or)directory name)                      (gives the complete status(timestamp) of that file/directory)

To know how many hours I was connected to the server till today
$ ac  -d                        (‘ac’ is an LINUX command )

To know connect time for all the users
$ ac  -p

To know the connect time for a specific user (ex: basupally)
$ ac  -d  basupally

$ w
Gives complete info of the session connected like
16:11:34 up 54 days, 20:09,  2 users,  load average: 0.29, 0.24, 0.19
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU  WHAT
cb858037 pts/0    199.81.99.89     15:22   34:58   0.01s  0.01s sshd: cb858037 [priv]
cb858037 pts/1    199.81.99.89     15:23    0.00s  0.01s  0.01s sshd: cb858037 [priv]

To know about any User
$ Finger   userid   (ex: finger cb858037)

$ chmod  777 *.log                          (for files)
$ chmod  -R 777 /var/back/omega                     (giving  rwx permissions to a directory OMEGA)

To Run DBCA:
$ Export DISPLAY=ipaddress:0  (ip address from cmd promptàipconfig)


Vi   editor Commands

1)      Replacing a word in entire file    :%s/old/new/g
2)      Replacing a word in single line only     :s/old/new/g
3)      Replacing a word in line ‘n’         :ns/old/new
4)      To go to the END of a line     ‘:$’  or  ‘G’
5)      To go to the FIRST line  ‘gg’
6)      To find CURRENT LINE number   :.=  or (ctl g   -> gives line #, file name,Total lines in a file)
7)      Count the word ABC  in entire vi file     :%s/ABC/ABC/g
8)      Count a string from outside file :  grep “abc  efg“ file_name  | wc –l
                                                        grep -c "abc efg”  file_name
                                                    more  /etc/profile | grep -i ulimit
9)      To give numbering to each line    :set nu
10)   To turn off   the numbering      :set nonu
11)    To open a file with cursor at last line     vi  +  filename
12)   To open a file with cursor at line number 25    vi  +25  filename
13)   To open a file with cursor at first occurrence of WORD  ‘abc’     vi   /abc  filename

Edit multiple files at a time :
$ vi   ipom.xml pom.xml ppom.xml tpom.xml
Opens 1st file(ipom)-->edit and save (:w) --> :n (to go to another next file) --> :n (go to next file) and so on
                                 --> :N (to go to Previous  file)




User Management

Find User privileges:
SQL> select * from dba_sys_privs where grantee='SCHEMA_NAME';

select TABLE_NAME,PRIVILEGE,GRANTOR from dba_TAB_PRIVS  where grantee='SCHEMA_NAME';

Find user roles:
SQL>   select * from dba_role_privs where grantee='SCHEMA_NAME';

To see the current Active Roles of any schema, then log in into that schema and query this:
SQL>   select * from session_roles;

Find privileges granted to a ROLE:
SELECT * FROM  role_sys_privs  where  role=ROLE_NAME’;
SELECT * FROM  role_tab_privs  where  role=ROLE_NAME’;

Roles Granted to a ROLE :
SELECT * FROM  role_role_privs  where  role=ROLE_NAME’;

Get Oracle User DDL with dbms_metadata

set long 200000 pages 0 lines 131
column meta format a121 word_wrapped
select dbms_metadata.get_ddl('USER', '&&username') meta from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', '&&username') meta from dual;
select dbms_metadata.get_granted_ddl('ROLE_GRANT', '&&username') meta from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', '&&username') meta from dual;



About   DATABASE

How to Calculate the Size of the Database (Doc ID 1360446.1)

Used space:
select sum(bytes)/1024/1024/1024 GB from dba_segments;

(Free space):
---------------
select sum(bytes)/1024/1024/1024 GB from dba_free_space;

(Total database size):
---------------------------
 select sum(bytes)/1024/1024/1024 GB from dba_data_files;
  +
(Temp size):
---------------
select SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree from  v$temp_space_header;

select tablespace_name, sum(bytes/(1024*1024)) "Total Mb" from dba_temp_files group by tablespace_name;

select a.TABLESPACE_NAME,b.file_name,sum(bytes/(1024*1024)) "Total Mb",SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree from  v$temp_space_header a, dba_temp_files b group by a.TABLESPACE_NAME,b.file_name;

(Or)

SELECT SUM (a.log_space + b.data_space + c.tempspace) "Total_DB_Size (G)"   FROM (SELECT ROUND (SUM (BYTES/1024/1024/1024), 2) data_space  FROM dba_data_files) b, (SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2) log_space  FROM v$log) a,   (SELECT NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0) tempspace  FROM dba_temp_files) c;


DROP all USERS like name:

BEGIN
  FOR i IN (
    SELECT t.username
    FROM DBA_USERS t
    WHERE t.username LIKE 'QA_%')
  LOOP
    EXECUTE IMMEDIATE 'DROP USER '|| i.username||' CASCADE';
  END LOOP;
 EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line(sqlerrm);
END;
/

Oracle server uptime:
SQL> SELECT TO_CHAR (startup_time, 'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" from SYS.v_$instance;
Or  $ uptime

Find all Child table FK’s for a parent PK:

select a.owner,a.table_name, a.constraint_name from sys.all_constraints a,(select owner,constraint_name from sys.all_constraints where owner = ‘OWNER’ and table_name = ‘TABLE_NAME’ and constraint_type in ('P','U')) b where a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name and a.r_owner = b.owner;

Find SCHEMA size:
SELECT sum(bytes)/1024/1024 MB FROM dba_segments where owner=’owner_name’;

Tablespace used and free space:
SELECT SUBSTR (df.NAME, 1, 50) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) and dfs.tablespace_name=’tablespace_name’
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes ORDER BY file_name;

Find used and free space in TABLESPACES:
select * from DBA_TABLESPACE_USAGE_METRICS;

All schema sizes in Database :
select owner, sum(bytes)/1024/1024 MB from dba_segments  group by owner order by MB desc;

Find schema and its tablespace:
SQL> select USERNAME, DEFAULT_TABLESPACE from dba_users where USERNAME='USERNAME';

Find schema and all related tablespaces:
select owner,tablespace_name from dba_tables where owner='SCHEMA' group by owner,tablespace_name;

Finding BIGFILE tablespace in database:
SQL> select name, bigfile   from v$tablespace;

Find total number of procedures in a database:
Select count(*) from dba_procedures;

To count the number of data files in a database
SQL>  select  count(file_name)  from  dba_data_files;


To find out the path to your current session’s trace file
SQL>  select value from v$diag_info where name = 'Default Trace File'; 

Find redo size generated per day
SQL>  select trunc(completion_time) rundate,count(*) logswitch ,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log group by trunc(completion_time) order by 1;

To findouts Trace file of different session:
select instance_name || ‘_ora_’ || spid || ‘.trc’ filename from v$process p, v$session s, v$instance
where p.addr = s.paddr and s.sid = 170;

Find my own SID:
select sid from v$mystat where rownum  <=1;
(OR)   select distinct  sid from v$mystat;

To check all the spfile parameters
SQL>   SHOW PARAMETER;

Startup database with pfile or spfile
SQL> CONNECT sys/password AS SYSDBA
SQL> startup pfile='<pfile location>';

Analyze all tables and indexes that are owned by the ‘ABC’ user:
execute DBMS_UTILITY.ANALYZE_SCHEMA('ABC','ESTIMATE')                 (before 8i DBMS_UTILITY is used)

To analyze single table
ANALYZE   TABLE   SCOTT.RA_INTERFACE_DISTRIBUTIONS_ALL   COMPUTE   STATISTICS;          

CREATE OR REPLACE procedure SYS.flush_buffer_and_shared_pool
as
begin
execute immediate 'alter system flush shared_pool';
execute immediate 'alter system flush buffer_cache';
dbms_output.put_line('!!!!...Shared pool and buffer cache has been flushed successfully..!!!!');
end flush_buffer_and_shared_pool;
/

Created this procedure to do both @same time
Running the above procedure as follows:
SQL > Exec SYS.flush_buffer_and_shared_pool          (from sys user)

Gather stats on the entire database...
execute  dbms_stats.gather_database_stats;
exec dbms_stats.delete_database_stats;

Gather stats for a single schema...
execute dbms_stats.gather_schema_stats('SCOTT');
exec dbms_stats.delete_schema_stats('SCOTT');
(or)
exec dbms_stats.gather_schema_stats(ownname=>'USER_NAME',OPTIONS=>'GATHER AUTO');

Gather stats for a schema table...
exec dbms_stats.gather_table_stats('<owner>', '<table_name>');
exec dbms_stats.delete_table_stats('SCOTT', 'EMPLOYEES');
exec dbms_stats.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

exec dbms_stats.gather_schema_stats( -
     ownname          => 'SCOTT', -
     options          => 'GATHER AUTO'   )

Gather stats on multiple tables (generates script)
set linesize 200
select
'exec DBMS_STATS.GATHER_TABLE_STATS (ownname => '
||chr(39)
||owner||
chr(39)||
', tabname => '
||chr(39)
||table_name||
chr(39)||
', cascade => true, estimate_percent => 100, method_opt =>'
||chr(39)
||'FOR ALL COLUMNS SIZE AUTO'
||chr(39)||
', granularity => '
||chr(39)||
'ALL'
||chr(39)||
', degree => 4);' as "script to Gather Table Stats"
from dba_tables where owner='SCHEMA' and table_name in ('TABLE1, 'TABLE2, 'TABLE3, 'TABLE4);



Find TABLE modifications done after gathering stats
Select TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP from DBA_TAB_MODIFICATIONS where TABLE_OWNER='SCHEMA_NAME’;

To restrict the out of a query use rownum parameter
Ex: select * from user_tables  where rownum <5 ;         (Displays only 4 rows)
      select * from user_tables  where rownum < 9;          (Displays only 8 rows)

Convert SCN to Timestamp:
SQL>   select scn_to_timestamp(SCN_number) from dual;

Conn / as sysdba
(if u want to run anything as DEMO user and if you don’t know the passwd then do this)

SQL> alter session set current_schema=DEMO;
SQL> select sys_context('USERENV','SESSION_USER') current_user,sys_context('USERENV','SESSION_SCHEMA') current_schema from dual;

CURRENT_USER
--------------------------------------------------------------------------------
CURRENT_SCHEMA
--------------------------------------------------------------------------------
SYS
DEMO


List autoextensible datafiles
SQL>   Select  file_name from dba_data_files  where  autoextensible = 'YES'

Datapump job status:

SELECT owner_name, job_name, operation, job_mode,state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;

SELECT SID, SERIAL#, CONTEXT,OPNAME,TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2)  "%_COMPLETE",MESSAGE, to_char(START_TIME, 'dd-mm-yy hh24:mi:ss') START_TIME FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' and  SOFAR <> TOTALWORK;

SELECT NAME, STATUS, TIMEOUT, ERROR_NUMBER, ERROR_MSG FROM DBA_RESUMABLE;


Temporary Tablespace :

Check all TEMP tablespace space in database:
SELECT  A.tablespace_name tablespace,    D.mb_total,   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM    v$sort_segment A, (SELECT    B.name,    C.block_size,    SUM (C.bytes) / 1024 / 1024 mb_total FROM    v$tablespace B,    v$tempfile C WHERE    B.ts#= C.ts#  GROUP BY    B.name,    C.block_size) D WHERE    A.tablespace_name = D.name  GROUP by    A.tablespace_name,    D.mb_total
/

Checking Default Temporary tablespace:
select  property_value  from  database_properties where  property_name = 'DEFAULT_TEMP_TABLESPACE';

To see Temporary segment usage:    v$tempseg_usage

To check if anyone doing sort operations:
SELECT COUNT(*) FROM v$sort_usage WHERE tablespace = 'TEMP'

LIST all Temp files;
select FILE_NAME, BYTES/1024/1024/1024 GB from dba_temp_files;


DIFFERENCES:

DBA_ views: These views are built on Data dictionary they're not available if the database is not mounted and opened.

V$ views : These views tend to run against the instance, and therefore may be available if the database is not mounted, or is not mounted and opened, depending on the nature of the view.


***DATAPUMP PARALLEL option is only available in Enterprise Edition of Oracle Database ***

-- locate Data Pump master tables:

SELECT o.status, o.object_id, o.object_type,        o.owner||'.'||object_name "OWNER.OBJECT"
  FROM dba_objects o, dba_datapump_jobs j   WHERE o.owner=j.owner_name AND o.object_name=j.job_name     AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

EXCLUDE=SCHEMA:"IN\(\'SYS\',\'SYSMAN\',\'SYSTEM\',\'XDB\',\'PUBLIC\',\'OUTLN\',\'ORDSYS\',\'OLAPSYS\',\'DBSNMP\',\'APEX_030200\',\'CTXSYS\',\'EXFSYS\',\'FLOWS_FILES\',\'MDSYS\',\'ORDDATA\',\'OUTLN\'\)"


RMAN:

Full bkup:

run {
2> backup as backupset tag 'QA1_FULL' database FORMAT '/usr/mware/full_%u_%s_%p' include current controlfile PLUS ARCHIVELOG;
3> }


Rman TARGET SYS/pwd NOCATALOG debug trace=rman.trc log=’/pullpath/rman.log’

$ORACLE_HOME/bin/rman target sys/****@VCO_DB auxiliary sys/*****@VSOURCE<< _END_ >> standby_6.log
RUN
{
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;
}
exit
_END_

The following query shows the status RMAN jobs.

select OPERATION, START_TIME, END_TIME, OBJECT_TYPE, STATUS from v$RMAN_STATUS where rownum <10 order by START_TIME;

RMAN>  Delete archivelog all completed before  'SYSDATE-7';

COLD Backup:

RMAN >  run {
backup full tag full_cold_backup
format ‘/var/backup/corppsdb/rman/db_t%t_s%s_p%p’
database;
}

Rman running job status :

set line 280 pages 999
SELECT SID, SERIAL#,SOFAR,TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2)  "%_COMPLETE",OPNAME,to_char(START_TIME, 'dd-mm-yy hh24:mi:ss') START_TIME FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%'  AND OPNAME NOT LIKE '%aggregate%'   AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

Tape backup status:
select name, start_time, end_time, INPUT_TYPE,status, time_taken_display ELAPSED, compression_ratio COMPR,
input_bytes_display INB, output_bytes_display OUTB, input_bytes_per_sec_display INBPERSEC, output_bytes_per_sec_display OUTBPERSEC  from V$RMAN_BACKUP_JOB_DETAILS, V$database where output_device_type = 'SBT_TAPE' order by session_stamp desc;

To see any Dictionary view use DICT view:
Example
SQL> select table_name from dict where table_name like '%BACKUP%';



DATAGUARD (STANDBY) :

select DB_UNIQUE_NAME, DATABASE_ROLE,STATUS,OPEN_MODE from v$database,v$instance;

select * from v$recovery_progress;

Check standby redo logs:
select * from  v$logfile where type='STANDBY';
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Use the following query to confirm that Data Guard is in active mode:
 SELECT 'Using Active Data Guard' ADG FROM V$MANAGED_STANDBY M, V$DATABASE D WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE='READ ONLY';
ADG
-----------------------
Using Active Data Guard

no rows selected    ---->  then Active Data Guard is not enabled


To determine if Redo Apply has recovered all redo that has been received from the
primary, query the V$DATAGUARD_STATS view.

SQL> SELECT * FROM V$DATAGUARD_STATS WHERE NAME=’apply lag’;

To see if the MRP is running or not

SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
select process, thread#, sequence#, status from v$managed_standby where process='MRP0';

To start Redo Apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
alter database recover managed standby database using current logfile disconnect;

To stop recovery of a standby database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Check ALL DEST_ messages on PRIMARY and STANDBY:

SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

On primary:
SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)  ORDER BY 1;

On Standby:
set line 280
SELECT name "Database name", STATUS "MRP process",ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name,(SELECT STATUS  FROM V$MANAGED_STANDBY where process like '%MRP%') process,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

For phy standby (MOUNT state)
set line 280
SELECT name "Database name", status, ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name, (select status from v$instance) status,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'
/
SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a, (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

Check Archive gap on Standby:
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Check the max log sequence on Primary DB and on Standby DB

SELECT Max(sequence#) FROM   v$log_history;
verify whether the archive log file is corrupted on either standby or primary site by

alter system dump logfile '<full path/archive log file name>' validate;
   -- If you get the SQL prompt back without error, then the archive log file is not corrupted.

# check the last log applied on STANDBY

SELECT thread#,   Max(sequence#) "Last Standby Seq Applied" FROM   v$archived_log   WHERE  applied = 'YES'  GROUP  BY thread#   ORDER  BY 1; 

On Primary check ERROR MESSAGE to standby:
SELECT gvi.thread#, timestamp, message FROM gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal') and rownum <11 ORDER BY timestamp, thread#;
Find archive log for a seq#:
select name,SEQUENCE#,status, first_time, next_time, first_change#, next_change# from v$archived_log where 318457507487 between first_change# and next_change#;
Recovery speed:
set linesize 400
col Values for a65
col Recover_start for a21
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recover_start",to_char(item)||' = '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi') "Values" from v$recovery_progress where start_time=(select max(start_time) from v$recovery_progress);

Check any error for arch dest
SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=number


ASM:

Export ORACLE_SID=+ASM

To see DISK GROUP free and Used space:
select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;
select name, state from v$asm_diskgroup

To MOUN/DISMOUNT diskgroup:
alter diskgroup DATA mount;

To see DISK free and Used space in a DISK GROUP:
select disk_number "Disk #", free_mb from v$asm_disk where group_number = x order by 2;

To view ASM operation(after add/drop/resize):
SQL> select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;

ASM disk path and header_status:
select path, header_status, mode_status from v$asm_disk;

ASM volumes:
SELECT volume_name, volume_device, STATE from V$ASM_VOLUME;

ACFS:
ASMCMD [+] > volinfo -G datac1 ACFSVOL01
ACFS start
/u01/app/12.1.0.2/grid/bin/srvctl start filesystem -d /dev/asm/acfsvol01-50

select FS_NAME,STATE,NUM_VOL,TOTAL_SIZE from V$ASM_FILESYSTEM;

select * from V$ASM_ACFSSNAPSHOTS;


Database TUNING:

Find table having STALE stats or not:

select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where TABLE_NAME=’TABLE_NAME’;

Begin DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'EMP',
degree => 2,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /

There are several values for the options parameter that we need to know about:

gather – re-analyzes the whole schema.
gather empty – Only analyze tables that have no existing statistics.
gather stale – Only re-analyze tables with more than 10% modifications (inserts, updates, deletes).
gather auto – This will re-analyze objects which currently have no statistics and objects with stale statistics.   Using gather auto is like combining gather stale and gather empty.

Find what SQL’s is Currently Running:

select session.sid, session.username, optimizer_mode, hash_value, address, cpu_time, elapsed_time,      sql_text from v$sqlarea sqlarea, v$session session  where session.sql_hash_value = sqlarea.hash_value  and session.sql_address    = sqlarea.address    and session.username is not null ;

Monitor all current sql executions in database:

SET HEAD ON PAGES 50000 ECHO OFF VERIFY OFF FEED ON LINESIZE  400 TRIMSPOOL ON AUTOTRACE OFF
COLUMN percent         FOR 999.99
COLUMN message         FOR A90
COLUMN "Start  Time"   FOR A23
COLUMN "Actual Time"   FOR A23
COLUMN username        FOR A10
COLUMN sql_id          FOR A15

SELECT a.sid, a.username,s.status,   a.sql_id,s.OSUSER,s.program,s.state,s.SECONDS_IN_WAIT ,
        TO_CHAR(start_time,'DD-Mon-YYYY HH24:MI:SS')  "Start  Time",
        TO_CHAR(SYSDATE   ,'DD-Mon-YYYY HH24:MI:SS')  "Actual Time",
        message,   (sofar/DECODE(NVL(totalwork,1),0,1,totalwork))* 100 percent
FROM   v$session_longops a, v$session s WHERE a.sid=s.sid and DECODE(sofar/DECODE(NVL(totalwork,1),0,1,totalwork),0,1,sofar/DECODE(NVL(totalwork,1),0,1,totalwork)) * 100 <> 100;

Last time run and duration:

select LAST_LOAD_TIME, LAST_ACTIVE_TIME,ELAPSED_TIME/60000000 MINUTES from v$sql where sql_id='9nqk9rywerty'


List all STALE objects in databases:

SET SERVEROUTPUT ON

DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/

Query to check blocking sessions:  (use GV$ for RAC db)

SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid     AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1     AND l2.id2 = l2.id2 ;


Script to Check Maximum Processes (check if database is hitting ORA-00020: maximum number of processes)

select resource_name, current_utilization/limit_value*100,current_utilization, max_utilization,limit_value from v$resource_limit where resource_name in ('processes','sessions');

Find out the locks in the table

select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id;


Find LOCK on object:

select owner||'.'||object_name obj,oracle_username||' ('||s.status||')' oruser,os_user_name osuser,
machine computer,l.process unix,
''''||s.sid||','||s.serial#||'''' ss,r.name rs,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from v$locked_object l, dba_objects o, v$session s, v$transaction t, v$rollname r
where l.object_id = o.object_id and s.sid=l.session_id and s.taddr=t.addr
and t.xidusn=r.usn and o.object_name=’OBJECT_NAME’
order by osuser, ss, obj;


SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ  WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;


FIND how much undo rollbacked:
select start_time,used_urec,used_ublk from v$transaction;

SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec  FROM v$session s, v$transaction t
 WHERE s.taddr = t.addr  ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Find last DDL & DML time on a object:

 select  (select last_ddl_time from dba_objects where object_name='T' and owner='SYS') "DDL Time",
  decode(maxscn,0,'N/A',scn_to_timestamp(maxscn)) "DML Time"
  from   (select nvl(max(ora_rowscn),0) maxscn from cvm_schema.CI_CUST_HIER_SUMMARY);


Scheduler job status:
select    owner, job_name, job_class, enabled, next_run_date, repeat_interval from dba_scheduler_jobs where job_name=

Check PLSQL  DDL:
select text from dba_source where name ='Procedure_name’;

SQL text for particular SID:

select a.sid,a.program,b.sql_text from v$session a, v$sqltext b
where a.sql_hash_value = b.hash_value and a.sid=144 order by a.sid,hash_value,piece;


Total cursors open, by session
select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' order by 1;

Kill particular  OSUSER sessions
select 'Alter system kill session '''||sid||','||serial#||''';' from v$session where osuser='username' ;
or

begin
  for sessions in ( select sid,serial# from   v$session  where  username = 'QA_SR')
  loop
    execute immediate 'alter system kill session '''||sessions.sid||','||sessions.serial#||'''';
  end loop;
end;
/

Getting BIND variable values:
select * from V$SQL_BIND_CAPTURE where sql_id='8f6sssp4mzf';

Finding bind variable values :
select sn.BEGIN_INTERVAL_TIME,sn.END_INTERVAL_TIME,sb.NAME,sb.VALUE_STRING from DBA_HIST_SQLBIND sb,DBA_HIST_SNAPSHOT sn  where sb.sql_id='8f6sdfqdpf' and sb.WAS_CAPTURED='YES' and sn.snap_id=sb.snap_id  order by sb.snap_id,sb.NAME;

Query to show sql_ids related to SQL Profiles:

select distinct  p.name sql_profile_name,s.sql_id from dba_sql_profiles p, DBA_HIST_SQLSTAT s where
p.name=s.sql_profile;
select name, created, status from dba_sql_profiles order by 2;

RMAN wait:

SELECT s.SID, s.serial#,USERNAME AS "User", PROGRAM, MODULE,  ACTION, LOGON_TIME "Logon", l.*
FROM V$SESSION s, V$ENQUEUE_LOCK l  WHERE l.SID = s.SID  AND l.TYPE = 'CF' AND l.ID1 = 0 AND l.ID2 = 2;

AWR:

-- This causes the repository to refresh every 15 minutes & and retain all data for 2 weeks.
Exec dbms_workload_repository.modify_snapshot_settings  (retention=>20160, interval=> 15);

Create Manual snapshotEXEC dbms_workload_repository.create_snapshot;

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

RAC:

$GRID_HOME/log/<hostname>/alert<<hostname>>.log
$GRID_HOME/log/<hostname>/cssd/ocssd.log
ASM alert log location:
. oraenv
+ASM
Adrci
Show alert
$ORACLE_BASE/diag/asm/+asm/+ASM{instance number}/trace/ alert_+ASM {instance number}.log
OS logs: /var/log/messages
rpm –qa | grep oracleasm
./crsctl stop crs  (OR)  ./crsctl stop crs –f
crsctl check crs
crsctl check cluster –all   (lists all on all the nodes)
crsctl query css votedisk
crs_stat –t    (or) crsctl status resource –t

Check autostart of CRS and HAS:
./crsctl config has(or)crs

Enable autostart of CRS and HAS:
./crsctl enable has(or)crs

crsctl status resource -t

Service:

srvctl status service -d database_name -s service_name

Oracle RESTART:
crsctl start has – to manually start the Oracle Restart stack when running disabled or after manually stopping it
crsctl stop has [-f] – to manually stop the Oracle Restart stack. The -f option
crsctl enable has – to enable the stack for automatic startup at server reboot
crsctl disable has – to disable the stack for automatic startup at server reboot
crsctl config has – to display the configuration of Oracle Restart
crsctl check has –


Auto Scroll Stop Scroll