To check current environment of CDB and PDBSET LINESIZE 200 PAGESIZE 100 SERVEROUTPUT ONCOLUMN "DB DETAILS" FORMAT A100SELECT 'DB_NAME: ' ||sys_context('userenv', 'db_name') || ' \CDB:-' ||(select cdb from v$database) || ' \AUTH_ID: ' ||sys_context('userenv', 'authenticated_identity') || ' \USER: ' ||sys_context('userenv', 'current_user') || ' \CONTAINER:' ||nvl(sys_Context('userenv', 'con_Name'), 'NON-CDB') "DB DETAILS" FROM DUAL ;DB DETAILS----------------------------------------------------------------------------------------------------DB_NAME: orcl12c \CDB:-YES \AUTH_ID: oracle \USER: SYS \CONTAINER:ANUJset linesize 200 pagesize 200 col FILE_NAME for a70SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES ; CON_ID TABLESPACE_NAME FILE_NAME---------- ------------------------------ ---------------------------------------------------------------------- 3 SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf 3 SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf 3 USERS /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf 3 EXAMPLE /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf 3 APEX_1851336378250219 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf 3 APEX_5457999048253711 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf 3 APEX_5500333564645084 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf 1 SYSTEM /u01/app/oracle/oradata/orcl12c/system01.dbf 1 SYSAUX /u01/app/oracle/oradata/orcl12c/sysaux01.dbf 1 USERS /u01/app/oracle/oradata/orcl12c/users01.dbf 1 UNDOTBS2 /u01/app/oracle/oradata/orcl12c/undotbs2.dbf 1 APEX /u01/app/oracle/oradata/orcl12c/apex01.dbf 5 SYSTEM /u01/app/oracle/oradata/orcl12c/ANUJ/system01.dbf 5 SYSAUX /u01/app/oracle/oradata/orcl12c/ANUJ/sysaux01.dbf 5 USERS /u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf 4 SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf 4 SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf19 rows selected.How to check seed database path ?SQL> alter session set exclude_seed_cdb_view=false;Session altered.show parameter EXCLUDE_SEED_CDB_VIEWNAME TYPE VALUE------------------------------------ ----------- ------------------------------exclude_seed_cdb_view boolean FALSENow seed database file ..set linesize 200 pagesize 200 col FILE_NAME for a70SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES ; CON_ID TABLESPACE_NAME FILE_NAME---------- ------------------------------ ---------------------------------------------------------------------- 1 SYSTEM /u01/app/oracle/oradata/orcl12c/system01.dbf 1 SYSAUX /u01/app/oracle/oradata/orcl12c/sysaux01.dbf 1 USERS /u01/app/oracle/oradata/orcl12c/users01.dbf 1 UNDOTBS2 /u01/app/oracle/oradata/orcl12c/undotbs2.dbf 1 APEX /u01/app/oracle/oradata/orcl12c/apex01.dbf 2 SYSTEM /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf 2 SYSAUX /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf 3 SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf 3 SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf 3 USERS /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf 3 EXAMPLE /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf 3 APEX_1851336378250219 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf 3 APEX_5457999048253711 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf 3 APEX_5500333564645084 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf 5 SYSTEM /u01/app/oracle/oradata/orcl12c/ANUJ/system01.dbf 5 SYSAUX /u01/app/oracle/oradata/orcl12c/ANUJ/sysaux01.dbf 5 USERS /u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf 4 SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf 4 SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf21 rows selected. alter system set "EXCLUDE_SEED_CDB_VIEW"=FALSE ;System altered.set linesize 200 pagesize 200 col FILE_NAME for a70SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES ; CON_ID TABLESPACE_NAME FILE_NAME---------- ------------------------------ ---------------------------------------------------------------------- 2 SYSTEM /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf 2 SYSAUX /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf 4 SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf 4 SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf 3 SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf 3 SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf 3 USERS /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf 3 EXAMPLE /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf 3 APEX_1851336378250219 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf 3 APEX_5457999048253711 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf 3 APEX_5500333564645084 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf 1 SYSTEM /u01/app/oracle/oradata/orcl12c/system01.dbf 1 SYSAUX /u01/app/oracle/oradata/orcl12c/sysaux01.dbf 1 USERS /u01/app/oracle/oradata/orcl12c/users01.dbf 1 UNDOTBS2 /u01/app/oracle/oradata/orcl12c/undotbs2.dbf 1 APEX /u01/app/oracle/oradata/orcl12c/apex01.dbf18 rows selected.SET SERVEROUTPUT ONCOLUMN "RESTRICTED" FORMAT A10select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status from v$pdbs v , dba_pdbs dwhere 1=1and v.guid=d.guidorder by v.create_scn;NAME OPEN_MODE RESTRICTED STATUS------------------------------ ---------- ---------- ---------PDB$SEED READ ONLY NO NORMALORCL READ WRITE NO NORMALORDS READ WRITE NO NORMAL
=========================================
To check seed database file only
set linesize 200
col FILE_NAME for a80
select TABLESPACE_NAME, FILE_NAME from cdb_data_files where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' )
union
select TABLESPACE_NAME, FILE_NAME from cdb_temp_files where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' );
set linesize 200
col FILE_NAME for a80
select TABLESPACE_NAME, FILE_NAME from cdb_data_files where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' )
union
select TABLESPACE_NAME, FILE_NAME from cdb_temp_files where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' );
no rows selected
Check this parameter ...
SQL> show parameter EXCLUDE_SEED_CDB_VIEW
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
exclude_seed_cdb_view boolean TRUE
SQL> alter session set EXCLUDE_SEED_CDB_VIEW=false ;
Session altered.
set linesize 200
col FILE_NAME for a80
select TABLESPACE_NAME, FILE_NAME from cdb_data_files where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' )
union
select TABLESPACE_NAME, FILE_NAME from cdb_temp_files where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' );
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEMP /u01/app/oracle/oradata/orcl12c/pdbseed/pdbseed_temp012016-06-02_07-10-28-AM.dbf
SYSAUX /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
==========================================================
As seed database path from /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbfto /u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbfSo convert parameter .. FILE_NAME_CONVERT = ('/pdbseed/', '/ANUJ/')
Create Pluggable databse ..SQL> SQL>CREATE PLUGGABLE DATABASE anuj ADMIN USER vihaan IDENTIFIED BY vihaan123storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)DEFAULT TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf' SIZE 5M REUSE AUTOEXTEND ONFILE_NAME_CONVERT = ('/pdbseed/', '/ANUJ/');Pluggable database created.SET SERVEROUTPUT ONCOLUMN "RESTRICTED" FORMAT A10select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status from v$pdbs v , dba_pdbs dwhere 1=1and v.guid=d.guidorder by v.create_scn;NAME OPEN_MODE RESTRICTED STATUS------------------------------ ---------- ---------- ---------PDB$SEED READ ONLY NO NORMALORCL READ WRITE NO NORMALORDS READ WRITE NO NORMALANUJ MOUNTED n/a NEWSQL> alter pluggable database ANUJ open ;Pluggable database altered.NAME OPEN_MODE RESTRICTED STATUS------------------------------ ---------- ---------- ---------PDB$SEED READ ONLY NO NORMALORCL READ WRITE NO NORMALORDS READ WRITE NO NORMALANUJ READ WRITE NO NORMAL
set linesize 300 pagesize 300
col USERNAME for a15
col object_name for a15
col owner for a12
col kill for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,t.start_scnw,t.start_scnb
,t.start_time -- <<<<--- format mm/dd/yy
,s.username, o.object_name,o.owner,t.used_ublk ,t.used_urec ,s.event,s.sql_id,s.prev_sql_id,s.status
from gv$transaction t, gv$session s, gv$locked_object l,dba_objects o
where 1=1
and t.ses_addr = s.saddr
and t.inst_id = s.inst_id
and t.xidusn = l.xidusn
and t.xidslot = l.xidslot
and t.xidsqn = l.xidsqn
and t.inst_id = l.inst_id
and l.object_id = o.object_id
-- and username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
-- l.object_id IN ('') ----< OBJECT ID FROM DBA_OBJECTS
--and s.status='KILLED'
==
set linesize 300 pagesize 300
col kill for a15
col USERNAME for a24
col "Roll Status" for a27
col EVENT for a28
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,s.username,sql_id,t.used_urec,t.used_ublk,event
,case when bitand(flag,power(2,7)) > 0
then 'Rolling Back'
else 'Not Rolling Back'
end as "Roll Status"
from gv$session s, gv$transaction t
where 1=1
and s.saddr = t.ses_addr
and s.inst_id = t.inst_id
order by t.used_ublk desc
;
set linesize 300 pagesize 300
col kill for a15
col USERNAME for a24
col "Roll Status" for a27
col EVENT for a28
col CLIENT_INFO for a20
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,USERNAME, s.client_info, t.addr,sql_id, sum(t.used_ublk) used_ublk
,case when bitand(flag,power(2,7)) > 0
then 'Rolling Back'
else 'Not Rolling Back'
end as "Roll Status"
from gv$transaction t, gv$session s
where t.addr = s.taddr
and s.inst_id = t.inst_id
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''',USERNAME, s.client_info, t.addr,sql_id,bitand(flag,power(2,7))
;
set linesize 300 pagesize 300
col kill for a15
col SQL_TEXT for a50 wrap
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, t.used_urec records, t.used_ublk blocks,(t.used_ublk*8192/1024) kb,sql.sql_text
from gv$transaction t,gv$session s, gv$sql sql
where t.addr=s.taddr
and s.sql_id = sql.sql_id
and s.inst_id = sql.inst_id
and s.inst_id = t.inst_id
and s.sql_id='&sql_id'
-- and s.username ='USERNAME'
;
set linesize 300 pagesize 300
col SQL_TEXT for a50 wrap
select distinct s.CON_ID,s.sql_id,s.sql_text from gv$sql s, gv$undostat u where u.maxqueryid=s.sql_id and s.inst_id = u.inst_id;
set lines 170
set pages 1000
col event format a35
select inst_id,event,count(*) from gv$session_wait
group by inst_id,event order by 3
/
col inst_id for 999
col sql format a35
col username format a20
col child format 999
col secs format 9999
col machine format a12
col event format a25
col state format a10
col MINS for 99999
select distinct
w.inst_id,w.sid,s.username,substr(w.event,1,25) event,s.type,substr(s.machine,1,12) machine,substr(w.state,1,10) state,s.SQL_ID,--q.CHILD_NUMBER CHILD,
substr(q.sql_text,1,33) "SQL",round(s.LAST_CALL_ET/60) "MINS"
from gv$session_wait w,gv$session s,gv$sql q
where 1=1
and w.event like '%&event%'
and w.sid=s.sid
and w.inst_id=s.inst_id
and w.inst_id=q.inst_id
and s.SQL_HASH_VALUE=q.HASH_VALUE
and s.status='ACTIVE'
and s.username is not null
order by "MINS"
/
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 300 pagesize 300
col kill for a15
col username for a20
col sqlcommand for a20
col module for a20
col action for a20
col program for a20
col machine for a20
col client_info for a20
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
s.username,
s.machine,
NVL(s.client_info,'NA') client_info,
NVL(s.sql_id, s.prev_sql_id) sql_id,
NVL(sqlcom.command_name,'NA') sqlcommand,
s.module, s.action, s.program, t.status, t.start_date, ROUND((SYSDATE-t.start_date)*24*60*60) AS secondsopen,
SUM(t.used_urec) as "undo records used",
SUM(t.used_ublk) as "undo blocks used"
from gv$transaction t, gv$session s, gv$sqlcommand sqlcom
where 1=1
and t.addr = s.taddr
and sqlcom.command_type=s.command
and t.inst_id = s.inst_id
--and rownum <10
GROUP BY ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''',
s.username, s.machine, s.client_info, NVL(s.sql_id, s.prev_sql_id), NVL(sqlcom.command_name,'NA') , s.module, s.action, s.program, t.status, t.start_date, (sysdate-t.start_date)*24*60*60
order by "undo blocks used" desc;
select count(f.block#) "Should Increase >", count(u.block#) "Should Decrease <" from fet$ f ,uet$ u;
set linesize 500
col username for a30
col osuser for a14
col module for a20
col program for a30
col status for a12
col name for a5
col start_time for a18
col EVENT for a25
col kill for a16
with v as (
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' as kill,
--s.sid,
s.username
,s.osuser
,s.module,s.program
,s.sql_id
,s.event
--,addr,ses_addr
,tr.xid
,tr.xidusn
,tr.xidslot
,tr.xidsqn
--,ubafil,ubablk,ubasqn,ubarec
,tr.status
,tr.start_time
--,tr.start_date
,tr.used_ublk
,tr.used_urec
,tr.log_io
,tr.phy_io
,tr.cr_get
,tr.cr_change
,tr.flag
,tr.space
,tr.recursive
,tr.noundo
,tr.ptx parallel_tx
,tr.name
,tr.start_scn
--,dependent_scn
--,start_scnb,start_scnw,start_uext,start_ubafil,start_ubablk,start_ubasqn,start_ubarec
--,prv_xidusn,prv_xidslt,prv_xidsqn
--,ptx_xidusn,ptx_xidslt,ptx_xidsqn
--,dscn-b,dscn-w
--,dscn_base,dscn_wrap
--,prv_xid,ptx_xid
,row_number()over(order by used_ublk desc) blks_rn
,row_number()over(order by used_urec desc) recs_rn
,row_number()over(order by start_time desc) time_rn
from gv$transaction tr
,gv$session s
where 1=1
and tr.ses_addr = s.saddr(+)
and tr.inst_id = s.inst_id
)
select
kill
,sql_id
,event
,username
,osuser
,substr(module,1,30) as module
,program
--,xid
--,xidusn,xidslot,xidsqn
,status
,start_time
,used_ublk
,used_urec
,log_io
,phy_io
,cr_get
,cr_change
--,flag
,space,recursive,noundo,parallel_tx
,name
,start_scn
from v
where blks_rn<=10
or recs_rn<=10
or time_rn<=10
order by blks_rn
/
col username clear
col osuser clear
col module clear
col program clear
col status clear
col name clear
col start_time clear
Oracle Recommended Patches -- Oracle Database (Doc ID 756671.1)
Recommendations / Certifications
Mandatory for Oracle Exadata Real Application Clusters 12.1.0.2.0
Recommended for Oracle Exadata Real Application Clusters 12.1.0.2.0
Recommended when Oracle Clusterware 12.1.0.2.0 is used with:
Oracle Exadata Database 12.1.0.2.0
Oracle Exadata Real Application Clusters 12.1.0.2.0
System Patch Contents
The following patches are included. Disabled items are included in the system patch and cannot be downloaded independently. Some patches may not apply to your configuration. Use Configuration Manager for a detailed applicability analysis.
OPatchauto session is initiated at Sat Apr 15 08:51:47 2017
System initialization log file is /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchautodb/systemconfig2017-04-15_08-51-55AM.log.
Session log file is /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchauto/opatchauto2017-04-15_08-52-06AM.log
The id for this session is TWWV
Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/12.1.0.2/db_1
Patch applicablity verified successfully on home /u01/app/oracle/product/12.1.0.2/db_1
Verifying patch inventory on home /u01/app/oracle/product/12.1.0.2/db_1
Patch inventory verified successfully on home /u01/app/oracle/product/12.1.0.2/db_1
Preparing to bring down database service on home /u01/app/oracle/product/12.1.0.2/db_1
Successfully prepared home /u01/app/oracle/product/12.1.0.2/db_1 to bring down database service
Bringing down database service on home /u01/app/oracle/product/12.1.0.2/db_1
Database service successfully brought down on home /u01/app/oracle/product/12.1.0.2/db_1
Start applying binary patch on home /u01/app/oracle/product/12.1.0.2/db_1
Binary patch applied successfully on home /u01/app/oracle/product/12.1.0.2/db_1
Starting database service on home /u01/app/oracle/product/12.1.0.2/db_1
Database service successfully started on home /u01/app/oracle/product/12.1.0.2/db_1
Preparing home /u01/app/oracle/product/12.1.0.2/db_1 after database service restarted
No step execution required.........
Prepared home /u01/app/oracle/product/12.1.0.2/db_1 successfully after database service restarted
Trying to apply SQL patch on home /u01/app/oracle/product/12.1.0.2/db_1
[WARNING] The local database(s) on "/u01/app/oracle/product/12.1.0.2/db_1" is not running. SQL changes, if any, cannot be applied;
[WARNING] The database instance 'ora12c' from '/u01/app/oracle/product/12.1.0.2/db_1', in host'oraasm12c' is not running. SQL changes, if any, will not be applied.
To apply. the SQL changes, bring up the database instance and run the command manually from any one node (run as oracle).
Refer to the readme to get the correct steps for applying the sql changes.
SQL patch applied successfully on home /u01/app/oracle/product/12.1.0.2/db_1
Verifying patches applied on home /u01/app/oracle/product/12.1.0.2/db_1
Patch verification completed with warning on home /u01/app/oracle/product/12.1.0.2/db_1
KUP-05004: Warning: Intra source concurrency disabled because parallel select was not requested.
KUP-05007: Warning: Intra source concurrency disabled because the preprocessor option is being used.
Field Definitions for table OPATCH_XML_INV
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields
Fields in Data Source:
XML_INVENTORY CHAR (100000000)
Terminated by "UIJSVTBOEIZBEFFQBL"
Trim whitespace same as SQL Loader
KUP-04004: error while reading file /u01/app/oracle/product/12.1.0.2/db_1/QOpatch/qopiprep.bat
KUP-04017: OS message: Error 0
KUP-04017: OS message: /u01/app/oracle/product/12.1.0.2/db_1/QOpatch/qopiprep.bat: line 38: /u01/app/oracle/product/12.1.0.2/db_1/QOpatch/stout.txt: Permission denied
