Tuesday, 22 August 2017

Oracle Process information ..

col kill       for a15

col evnt   for a30
col usr    for a10
col osuser for a14
col mach   for a8
col prog   for a20
col sid    for 999
select ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,osuser,substr(a.username,1,10) usr,substr(a.program,1,20) prog,to_char(logon_time,'dd/mm hh24:mm:ss') logon,sql_id,prev_sql_id,substr(event,1,30) evnt,pga_alloc_mem pga
,decode(a.command,0,'UNKNOWN',
1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP CLUSTER',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT OBJECT',
18,'REVOKE OBJECT',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK',
27,'NO-OP',
28,'RENAME',
29,'COMMENT',
30,'AUDIT OBJECT',
31,'NOAUDIT OBJECT',
32,'CREATE DATABASE LINK',
33,'DROP DATABASE LINK',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEG',
37,'ALTER ROLLBACK SEG',
38,'DROP ROLLBACK SEG',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE MATERIALIZED VIEW LOG',
72,'ALTER MATERIALIZED VIEW LOG',
73,'DROP MATERIALIZED VIEW LOG',
74,'CREATE MATERIALIZED VIEW',
75,'ALTER MATERIALIZED VIEW',
76,'DROP MATERIALIZED VIEW',
77,'CREATE TYPE',
78,'DROP TYPE',
79,'ALTER ROLE',
80,'ALTER TYPE',
81,'CREATE TYPE BODY',
82,'ALTER TYPE BODY',
83,'DROP TYPE BODY',
84,'DROP LIBRARY',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
88,'ALTER VIEW',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
100,'LOGON',
101,'LOGOFF',
102,'LOGOFF BY CLEANUP',
103,'SESSION REC',
104,'SYSTEM AUDIT',
105,'SYSTEM NOAUDIT',
106,'AUDIT DEFAULT',
107,'NOAUDIT DEFAULT',
108,'SYSTEM GRANT',
109,'SYSTEM REVOKE',
110,'CREATE PUBLIC SYNONYM',
111,'DROP PUBLIC SYNONYM',
112,'CREATE PUBLIC DATABASE LINK',
113,'DROP PUBLIC DATABASE LINK',
114,'GRANT ROLE',
115,'REVOKE ROLE',
116,'EXECUTE PROCEDURE',
117,'USER COMMENT',
118,'ENABLE TRIGGER',
119,'DISABLE TRIGGER',
120,'ENABLE ALL TRIGGERS',
121,'DISABLE ALL TRIGGERS',
122,'NETWORK ERROR',
123,'EXECUTE TYPE',
128,'FLASHBACK',
129,'CREATE SESSION',
130,'ALTER MINING MODEL',
131,'SELECT MINING MODEL',
133,'CREATE MINING MODEL',
134,'ALTER PUBLIC SYNONYM',
135,'DIRECTORY EXECUTE',
136,'SQL*LOADER DIRECT PATH LOAD',
137,'DATAPUMP DIRECT PATH UNLOAD',
157,'CREATE DIRECTORY',
158,'DROP DIRECTORY',
159,'CREATE LIBRARY',
160,'CREATE JAVA',
161,'ALTER JAVA',
162,'DROP JAVA',
163,'CREATE OPERATOR',
164,'CREATE INDEXTYPE',
165,'DROP INDEXTYPE',
166,'ALTER INDEXTYPE',
167,'DROP OPERATOR',
168,'ASSOCIATE STATISTICS',
169,'DISASSOCIATE STATISTICS',
170,'CALL METHOD',
171,'CREATE SUMMARY',
172,'ALTER SUMMARY',
173,'DROP SUMMARY',
174,'CREATE DIMENSION',
175,'ALTER DIMENSION',
176,'DROP DIMENSION',
177,'CREATE CONTEXT',
178,'DROP CONTEXT',
179,'ALTER OUTLINE',
180,'CREATE OUTLINE',
181,'DROP OUTLINE',
182,'UPDATE INDEXES',
183,'ALTER OPERATOR',
192,'ALTER SYNONYM',
197,'PURGE USER_RECYCLEBIN',
198,'PURGE DBA_RECYCLEBIN',
199,'PURGE TABLESPACE',
200,'PURGE TABLE',
201,'PURGE INDEX',
202,'UNDROP OBJECT',
204,'FLASHBACK DATABASE',
205,'FLASHBACK TABLE',
206,'CREATE RESTORE POINT',
207,'DROP RESTORE POINT',
208,'PROXY AUTHENTICATION ONLY',
209,'DECLARE REWRITE EQUIVALENCE',
210,'ALTER REWRITE EQUIVALENCE',
211,'DROP REWRITE EQUIVALENCE',
212,'CREATE EDITION',
213,'ALTER EDITION',
214,'DROP EDITION',
215,'DROP ASSEMBLY',
216,'CREATE ASSEMBLY',
217,'ALTER ASSEMBLY',
218,'CREATE FLASHBACK ARCHIVE',
219,'ALTER FLASHBACK ARCHIVE',
220,'DROP FLASHBACK ARCHIVE',
225,'ALTER DATABASE LINK',
305,'ALTER PUBLIC DATABASE LINK') command
from gv$session a,gv$process b 
where a.paddr=b.addr 
and a.inst_id=b.inst_id
and b.username is not null 
order by 9 desc ;


KILL            OSUSER         USR        PROG                 LOGON          SQL_ID        PREV_SQL_ID   EVNT                                  PGA COMMAND
--------------- -------------- ---------- -------------------- -------------- ------------- ------------- ------------------------------ ---------- --------
'370,58214,@2'  oracle                    oracle@*******.int.s 25/07 13:07:02               6hnhqahphpk8n rdbms ipc message               165162756 UNKNOWN
'370,2076,@1'   oracle                    oracle@*******.int.s 25/07 13:07:51               6hnhqahphpk8n rdbms ipc message               163327748 UNKNOWN
'981,14255,@1'  oracle                    oracle@*******.int.s 25/07 13:07:53                             wait for unread message on bro   64040708 UNKNOWN

===




SET PAGESIZE 500 linesize 500

COLUMN instance_name     FORMAT a9         HEADING 'Instance'
COLUMN sid               FORMAT 99999      HEADING 'SID'
COLUMN serial_id         FORMAT 99999999   HEADING 'Serial ID'
COLUMN session_status    FORMAT a9         HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a14        HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a12        HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999    HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a26        HEADING 'Session Program' TRUNC
COLUMN session_terminal  FORMAT a10        HEADING 'Terminal'        JUSTIFY right
COLUMN session_machine   FORMAT a22        HEADING 'Machine'         JUSTIFY right
col kill for a17
prompt
prompt +----------------------------------------------------+
prompt | User Sessions (All)                                |
prompt +----------------------------------------------------+

BREAK ON instance_name SKIP PAGE

SELECT
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill
   -- i.instance_name      instance_name
 -- , s.sid                sid
 -- , s.serial#            serial_id
   , sql_id
   ,PREV_SQL_ID
  , lpad(s.status,9)     session_status
  , lpad(s.username,14)  oracle_username
  , lpad(s.osuser,12)    os_username
  , lpad(p.spid,7)       os_pid
 -- , s.program            session_program
  , lpad(s.terminal,10)  session_terminal
  , lpad(s.machine,19)   session_machine
  , s.event
,FINAL_BLOCKING_SESSION_STATUS
,FINAL_BLOCKING_SESSION
,p1
,p2
 p3
FROM   gv$session  s
    INNER JOIN gv$process  p ON (s.paddr = p.addr AND s.inst_id = p.inst_id)
    INNER  JOIN gv$instance i ON (p.inst_id = i.inst_id)
    AND s.TYPE != 'BACKGROUND'
and s.status='ACTIVE'
ORDER BY
    i.instance_name
  , s.sid
/



define 1='XXXX' ---- Change user name !!!!
col u_username head USERNAME for a23
col u_sid head SID for a14 
col u_audsid head AUDSID for 9999999999
col u_osuser head OSUSER for a16
col u_machine head MACHINE for a25 truncate
col u_program head PROGRAM for a20

select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  kill ,s.username||CASE WHEN s.sid = SYS_CONTEXT('userenv','sid') THEN ' (me)' WHEN s.type = 'BACKGROUND' THEN ' (bg)' END u_username,  
       p.spid, 
       s.sql_id, 
       s.audsid u_audsid,
       s.osuser u_osuser, 
       substr(s.machine,instr(s.machine,'\')) u_machine,
       CASE WHEN s.type = 'BACKGROUND' OR REGEXP_LIKE(s.program, '.*\([PJ]\d+\)') THEN
          REGEXP_REPLACE(SUBSTR(s.program,INSTR(s.program,'(')), '\d', 'n')
       ELSE
          '('||REGEXP_REPLACE(REGEXP_REPLACE(s.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
       END u_program,
       --substr(s.program,instr(s.program,'(')) u_program, 
       -- s.sql_address, 
       s.last_call_et lastcall, 
       s.status, 
       --, s.logon_time
	   s.event 
from 
    gv$session s,
    gv$process p
where 1=1
  and   s.paddr=p.addr
  and   s.inst_id=p.inst_id
--and s.type!='BACKGROUND'
and (lower(s.username) like lower('&1') or (nvl(s.username,'%') = '%' and s.type = 'USER'))
and s.status='ACTIVE'
order by s.inst_id,s.last_call_et desc
/







set linesize 300
COLUMN Block                            HEADING "Block"                      FORMAT a10
COLUMN blocking_instance                HEADING "Blocking|I#"                FORMAT 999999999
COLUMN blocking_instance                HEADING "Blocking|I#"                FORMAT 999999999
COLUMN blocking_session_status          HEADING "Blocking|SID Status"        FORMAT a10
COLUMN final_blocking_instance          HEADING "Final|Blocking|I#"          FORMAT 999999999
COLUMN final_blocking_session           HEADING "Final|Blocking|SID"         FORMAT 999999999
COLUMN final_blocking_session_status    HEADING "Final|Blocking|SID Status"  FORMAT a10
COLUMN inst_id                          HEADING "I#"                         FORMAT 99
COLUMN username                         HEADING "DBUser"                     FORMAT a15
COLUMN osuser                           HEADING "OSUser"                     FORMAT a15
COLUMN status                           HEADING "Status"                     FORMAT a10
COLUMN state                            HEADING "State"                      FORMAT a10
COLUMN logon_time                       HEADING "LogonTime"                  FORMAT a18
COLUMN service_name                     HEADING "ServiceName"                FORMAT a20
col EVENT for a30
col kill for a15
select 
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.sql_id,PREV_SQL_ID,event,'Blocked By' Block
     , s.blocking_instance
     , s.blocking_session
     , s.blocking_session_status
     , s.final_blocking_instance
     , s.final_blocking_session
     , s.final_blocking_session_status
                         FROM gv$session s
                        where 1=1
                  -- and s.sid = :SID
                       -- AND   s.inst_id = :INST_ID
                      --  and s.blocking_session is NOT NULL
                        order by 1;


set linesize 1000 pagesize 500
col kill for a17

col PLSQL_ENTRY_OBJECT       for a20                                                                                                        
col PLSQL_ENTRY_SUBPROGRAM for a20 
col PLSQL_ENTRY_SUBPROGRAM for a20 
col  PLSQL_ENTRY_OBJECT for a20 

col CLIENT_IDENTIFIER for a20

select 
--s.ECID ,s.inst_id, s.SID,s.SERIAL#,con_id,p.spid,s.status,s.machine, s.ACTION, s.MODULE, s.TERMINAL,s.sql_id,s.last_call_et,s.event, s.client_info,s.PLSQL_SUBPROGRAM_ID,s.PROGRAM,s.client_identifier
'''' || s.SID || ',' || s.serial# ||',@'|| s.inst_id||'''' kill,s.con_id,
--s.ECID ,
p.spid,s.status,s.machine, s.ACTION, s.MODULE, s.TERMINAL,s.sql_id,s.last_call_et,s.event, s.client_info,s.PLSQL_SUBPROGRAM_ID,s.PROGRAM,s.client_identifier
, ( SELECT max( substr( sql_text , 1, 40 )) FROM gv$sql sq WHERE sq.sql_id = s.sql_id ) AS sql_text
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = PLSQL_SUBPROGRAM_ID) AS plsql_entry_subprogram
--, 'alter system kill session ' || '''' || s.SID || ',' || s.serial# ||',@'|| s.inst_id||''''|| ' immediate;' kill_session
from gv$session s ,gv$process p
where 1=1
-- and s.process='5'  --forms OS process ID
--s.program like '%xxx%'
and p.addr=s.paddr
and p.inst_id = s.inst_id
and s.status!='INACTIVE'
;


  
  
set linesize 500 pagesize 400
col osuser 		for a16
col program 	for a10 trunc
col Locked 		for a6
col status 		for a1 trunc print
col "hh:mm:ss" 	for a8
col SQL_ID 		for a15
col seq# 		for 99990 --Sequence number that uniquely identifies the wait. Incremented for each wait
col module 		for a25
col ACTION 		for a20
col kill 		for a19
col event heading 'Current/LastEvent' for a25 trunc
col state head 'State (sec)' for a14
col username    for a25
col MACHINE for a37
select ''''||sid ||','|| serial#||',@'||inst_id ||''''  kill
,STATUS
,MACHINE 
--,con_id --12c
, username
, ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser
, substr(program,instr(program,'/',-1)+1,decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-1),instr(program,'@')-1)) program, decode(lockwait,NULL,' ','L') locked, status,
to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss" , sql_id,prev_sql_id, seq# , event,
decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6),'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state
, substr(module,1,25) module,final_blocking_session_status,sql_exec_start
-- substr(action,1,20) action
from GV$SESSION
where 1=1
--and USERNAME like 'DBFS%' 
--and USERNAME not in ('SYS','SYSRAC','DBSNMP' )
--and  type = 'USER'
--and STATUS!='INACTIVE'
--and audsid != 0 -- to exclude internal processess
--and SQL_ID='cabcn90dg7ttu'
--and event not like 'SQL*Net message from%'
--and osuser='_USER'
--and con_id =5
--and sid=4605
--and program like 'oracle%'
--and SERVICE_NAME in ('p_p')
 and PADDR = (select ADDR from V$PROCESS where SPID = 22289)
order by inst_id,  last_call_et desc, sid
/




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

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN session_status    FORMAT a9         HEADING 'Status'
COLUMN oracle_username   FORMAT a18        HEADING 'Oracle User'
COLUMN os_username       FORMAT a18        HEADING 'O/S User'
COLUMN os_pid            FORMAT a8         HEADING 'O/S PID'
COLUMN session_terminal  FORMAT a10        HEADING 'Terminal'         TRUNC
COLUMN session_machine   FORMAT a30        HEADING 'Machine'          TRUNC
COLUMN session_program   FORMAT a40        HEADING 'Session Program'  TRUNC
col kill for a23 
col EVENT for a20 TRUNC
BREAK ON instance_name SKIP PAGE

SELECT
'''' || s.SID || ',' || s.serial# ||',@'|| s.inst_id||'''' kill
--,s.con_id,
,'kill -9 '||spid PKill
  , s.status
  , s.sql_id
 ,prev_sql_id
 ,event
  , s.username          oracle_username
  , s.osuser            os_username
  , p.spid              os_pid
  , s.terminal          session_terminal
  , s.machine           session_machine
  , s.program           session_program
FROM          gv$session  s
  INNER JOIN gv$process  p ON (s.paddr = p.addr AND s.inst_id = p.inst_id)
WHERE 1=1
    -- and   s.status = 'ACTIVE'
  AND s.username IS NOT null
ORDER BY  1
  
  

set linesize 300
col PROGRAM  for a20
col MACHINE for a20
col SCHEMANAME for a18
col kill for a17
select ''''||sid ||','|| serial#||',@'||inst_id ||''''  kill ,sid,serial#,username,sql_id,schemaname,osuser,process,machine,port,program,type,paddr
from gv$session
where PADDR = (select ADDR from V$PROCESS where SPID = &spid)

Oracle Session Wait Info ..



Oracle Session Wait Info

col username format a20
col event format a30
col wait_class format a15
col kill for a15
col STATE for a12
SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
s.con_id ,
       NVL(s.username, '(oracle)') AS username,
       sw.event,
       sw.wait_class,
       sw.wait_time,
       sw.seconds_in_wait,
       sw.state,
	   s.sql_id ,
	   PREV_SQL_ID,
    s.p1,
    s.p2,
    s.p3
	--   FINAL_BLOCKING_SESSION_STATUS
FROM   gv$session_wait sw,  gv$session s
WHERE  s.sid     = sw.sid
AND    s.inst_id = sw.inst_id
-- and event='enq: TX - row lock contention'
and s.state='WAITING'
--and USERNAME!='SYS'
and sql_id is not null 
--and s.sql_id='15hgdm2v0vaj0'
ORDER BY sw.seconds_in_wait DESC;





set pagesize 100 linesize 500
col username       format a12
col sid            format 9999
col state          format a25
col event          format a50
col wait_time      format 99999999
col command        format a8
col kill           format a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.username, se.event, se.state,
-- sql_id, 
-- se.wait_time
se.seconds_in_wait
,decode(s.command,0,'UNKNOWN',
1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP CLUSTER',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT OBJECT',
18,'REVOKE OBJECT',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK',
27,'NO-OP',
28,'RENAME',
29,'COMMENT',
30,'AUDIT OBJECT',
31,'NOAUDIT OBJECT',
32,'CREATE DATABASE LINK',
33,'DROP DATABASE LINK',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEG',
37,'ALTER ROLLBACK SEG',
38,'DROP ROLLBACK SEG',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE MATERIALIZED VIEW LOG',
72,'ALTER MATERIALIZED VIEW LOG',
73,'DROP MATERIALIZED VIEW LOG',
74,'CREATE MATERIALIZED VIEW',
75,'ALTER MATERIALIZED VIEW',
76,'DROP MATERIALIZED VIEW',
77,'CREATE TYPE',
78,'DROP TYPE',
79,'ALTER ROLE',
80,'ALTER TYPE',
81,'CREATE TYPE BODY',
82,'ALTER TYPE BODY',
83,'DROP TYPE BODY',
84,'DROP LIBRARY',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
88,'ALTER VIEW',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
100,'LOGON',
101,'LOGOFF',
102,'LOGOFF BY CLEANUP',
103,'SESSION REC',
104,'SYSTEM AUDIT',
105,'SYSTEM NOAUDIT',
106,'AUDIT DEFAULT',
107,'NOAUDIT DEFAULT',
108,'SYSTEM GRANT',
109,'SYSTEM REVOKE',
110,'CREATE PUBLIC SYNONYM',
111,'DROP PUBLIC SYNONYM',
112,'CREATE PUBLIC DATABASE LINK',
113,'DROP PUBLIC DATABASE LINK',
114,'GRANT ROLE',
115,'REVOKE ROLE',
116,'EXECUTE PROCEDURE',
117,'USER COMMENT',
118,'ENABLE TRIGGER',
119,'DISABLE TRIGGER',
120,'ENABLE ALL TRIGGERS',
121,'DISABLE ALL TRIGGERS',
122,'NETWORK ERROR',
123,'EXECUTE TYPE',
128,'FLASHBACK',
129,'CREATE SESSION',
130,'ALTER MINING MODEL',
131,'SELECT MINING MODEL',
133,'CREATE MINING MODEL',
134,'ALTER PUBLIC SYNONYM',
135,'DIRECTORY EXECUTE',
136,'SQL*LOADER DIRECT PATH LOAD',
137,'DATAPUMP DIRECT PATH UNLOAD',
157,'CREATE DIRECTORY',
158,'DROP DIRECTORY',
159,'CREATE LIBRARY',
160,'CREATE JAVA',
161,'ALTER JAVA',
162,'DROP JAVA',
163,'CREATE OPERATOR',
164,'CREATE INDEXTYPE',
165,'DROP INDEXTYPE',
166,'ALTER INDEXTYPE',
167,'DROP OPERATOR',
168,'ASSOCIATE STATISTICS',
169,'DISASSOCIATE STATISTICS',
170,'CALL METHOD',
171,'CREATE SUMMARY',
172,'ALTER SUMMARY',
173,'DROP SUMMARY',
174,'CREATE DIMENSION',
175,'ALTER DIMENSION',
176,'DROP DIMENSION',
177,'CREATE CONTEXT',
178,'DROP CONTEXT',
179,'ALTER OUTLINE',
180,'CREATE OUTLINE',
181,'DROP OUTLINE',
182,'UPDATE INDEXES',
183,'ALTER OPERATOR',
192,'ALTER SYNONYM',
197,'PURGE USER_RECYCLEBIN',
198,'PURGE DBA_RECYCLEBIN',
199,'PURGE TABLESPACE',
200,'PURGE TABLE',
201,'PURGE INDEX',
202,'UNDROP OBJECT',
204,'FLASHBACK DATABASE',
205,'FLASHBACK TABLE',
206,'CREATE RESTORE POINT',
207,'DROP RESTORE POINT',
208,'PROXY AUTHENTICATION ONLY',
209,'DECLARE REWRITE EQUIVALENCE',
210,'ALTER REWRITE EQUIVALENCE',
211,'DROP REWRITE EQUIVALENCE',
212,'CREATE EDITION',
213,'ALTER EDITION',
214,'DROP EDITION',
215,'DROP ASSEMBLY',
216,'CREATE ASSEMBLY',
217,'ALTER ASSEMBLY',
218,'CREATE FLASHBACK ARCHIVE',
219,'ALTER FLASHBACK ARCHIVE',
220,'DROP FLASHBACK ARCHIVE',
225,'ALTER DATABASE LINK',
305,'ALTER PUBLIC DATABASE LINK') command,
s.blocking_session,
s.blocking_session_status,
s.status,
'select * from table(dbms_xplan.display_cursor('||''''||s.sql_id ||''''||',null,''ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS''));'  for_plan 
from gv$session s, gv$session_wait se
where 1=1  
and s.sid=se.sid
and s.inst_id=se.inst_id
-- and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
and SQL_ID is not null 
order by se.wait_time;


KILL            USERNAME     EVENT                                              STATE                     SECONDS_IN_WAIT COMMAND  BLOCKING_SESSION BLOCKING_SE STATUS   FOR_PLAN
--------------- ------------ -------------------------------------------------- ------------------------- --------------- -------- ---------------- ----------- -------- --------------------------------------------------------------------------------------------------------------------
'985,59968,@1'  SYS          PX Deq: Execution Msg                              WAITED SHORT TIME                       0 SELECT                    NOT IN WAIT ACTIVE   select * from table(dbms_xplan.display_cursor('fza8yc9wrjrb1',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
'1714,14669,@2' SYS          PX Deq: Execution Msg                              WAITED SHORT TIME                       0 SELECT                    NOT IN WAIT ACTIVE   select * from table(dbms_xplan.display_cursor('fza8yc9wrjrb1',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
'859,23018,@1'  SYS          PX Deq: Execute Reply                              WAITING                                 0 SELECT                    UNKNOWN     ACTIVE   select * from table(dbms_xplan.display_cursor('fza8yc9wrjrb1',null,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));



select RANK,
       WAIT_EVENT,
       lpad(TO_CHAR(PCTTOT, '990D99'), 6) || '% waits with avg.du =' ||
       TO_CHAR(AVERAGE_WAIT_MS, '9999990D99') || ' ms' as EVENT_VALUES
  from (select DENSE_RANK() OVER(order by sum(time_waited) desc) as RANK,
               event as WAIT_EVENT,
               round(RATIO_TO_REPORT(sum(time_waited)) OVER() * 100, 2) AS PCTTOT,
               round(avg(average_wait) * 10, 2) as AVERAGE_WAIT_MS
          from (select se.SID,
                       se.INST_ID,
                       se.EVENT,
                       se.TIME_WAITED,
                       se.AVERAGE_WAIT
                  from gv$session_event se
                 where se.WAIT_CLASS not in ('Idle')
                union
                select ss.SID,
                       ss.INST_ID,
                       sn.NAME    as EVENT,
                       ss.VALUE   as TIME_WAITED,
                       0          as AVERAGE_WAIT
                  from gv$sesstat ss, v$statname sn
                 where ss."STATISTIC#" = sn."STATISTIC#"
                   and sn.NAME in ('CPU used when call started'))
         where (sid, inst_id) in
               (select sid, inst_id
                  from gv$session
                 where gv$session.SERVICE_NAME not in ('SYS$BACKGROUND'))
         group by event
         order by PCTTOT desc) we
 where RANK <= 10
/   RANK WAIT_EVENT                                                       EVENT_VALUES
---------- ---------------------------------------------------------------- -----------------------------------------------------------
         1 CPU used when call started                                         29.9% waits with avg.du =       0.00 ms
         2 db file sequential read                                            21.8% waits with avg.du =       0.58 ms
         3 gc buffer busy acquire                                             21.4% waits with avg.du =       0.80 ms
         4 read by other session                                               9.4% waits with avg.du =       0.33 ms
         5 SQL*Net break/reset to client                                       8.6% waits with avg.du =       0.05 ms
         6 gc cr disk read                                                     8.0% waits with avg.du =       0.23 ms
         7 acknowledge over PGA limit                                          0.3% waits with avg.du =       8.35 ms
         8 SQL*Net message to client                                           0.1% waits with avg.du =       0.00 ms
         9 events in waitclass Other                                           0.0% waits with avg.du =       0.00 ms
        10 gc cr block 2-way                                                   0.0% waits with avg.du =       0.22 ms

===========

define v_event_filter='library cache lock'  ----<<<<
set echo off heading on feedback on
set linesize 200 trimspool on
set pagesize 60
set tab off

col blocking_sql_id format a12 head 'BLOCKING|SQL_ID'
col session_id format 999999 head 'SID'
col event format a40 head 'EVENT'
col session_state format a12 head 'SESSION|STATE'
col time_waited format 999,999.99 head 'TIME|WAITED|SECONDS'
col sample_time format a25 head 'SAMPLE TIME'
with blocked as (
	select distinct con_id, h.inst_id, h.sample_time, h.sample_id, h.session_id, h.session_serial#, h.blocking_session, h.blocking_session_serial#, h.sql_id, h.event, h.session_state
	from gv$active_session_history h
	where h.blocking_session is not null
	--and h.event like '&v_event_filter'
	and sample_time > sysdate - interval '5' minute
),
blockers as (
	select distinct
		 max(b.sample_id) over (partition by b.inst_id, b.session_id, b.session_serial#) sample_id
		, max(b.sample_time) over (partition by b.inst_id, b.session_id, b.session_serial#) sample_time
		, count(b.sample_time) over (partition by b.inst_id, b.session_id, b.session_serial#) event_count
		, b.sql_id
		, b.inst_id
		, b.session_id
		, b.blocking_session
		, b.event
		, b.session_serial#
		, b.session_state
		,b.con_id
		-- NULL for top level blockers
		--, b.time_waited
	from blocked b
	left outer join gv$active_session_history bl
		on bl.sample_id = b.sample_id
		and bl.inst_id = b.inst_id
		and bl.con_id = b.con_id
		and bl.sql_id = b.sql_id
		and bl.blocking_session = b.session_id
		and bl.blocking_session_serial# = b.blocking_session_serial#
)
select
	--sample_id
	con_id,
	sample_time
	, sql_id
	, session_id
	, blocking_session
	, inst_id
	, event_count
	, session_state
	, event
from blockers
order by sample_time, session_id
/

                                                                                                  SESSION
    CON_ID SAMPLE TIME               SQL_ID            SID BLOCKING_SESSION    INST_ID EVENT_COUNT STATE        EVENT
---------- ------------------------- ------------- ------- ---------------- ---------- ----------- ------------ ----------------------------------------
         5 16-MAY-24 06.44.51.752 AM 1m8z8cvabtqyf    3241             1523          1           1 WAITING      library cache lock 


col sid format 99999
col username format a15
col event format a30
col p1text format a15
col p1 format 999999999999999
col p2text format a12
col p2 format 99999999999
col wait_time format 999999 head 'WAIT|TIME'
col seconds_in_wait format 999999 head 'SECONDS|IN|WAIT'
col state format a20
col seq format 999999 head 'SEQ'
col blocking_session format a8 head 'BLKING|INSTANCE|SESSION'
col wait_time_micro format 999,999,999 head 'WAIT TIME|MICROSECONDS'
col wait_time_seconds format 999999.99 head 'WAIT TIME|SECONDS'
col kill for a17

set linesize 250 pagesize 60
select 	''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
s.con_id,
	s.username username,
	e.event event,
	s.sid,
	s.sql_id,
	e.p1text,
	e.p1,
	e.p2text,
	e.p2,
	e.seq# seq,
	e.state,
	e.wait_time,
	-- time of current or most recent wait in microseconds
	--e.wait_time_micro,
	e.wait_time_micro / 1000000 wait_time_seconds,
	--e.seconds_in_wait, -- ???
	decode(s.blocking_session, null,'',s.blocking_instance || ':' || s.blocking_session) blocking_session
from gv$session s, gv$session_wait e
where s.username is not null
	and s.sid = e.sid
and s.inst_id = e.inst_id
	-- skip sqlnet idle session messages
	--and s.module like 'Gathering Stats :EMT%'
and s.event not in ('SQL*Net message from client','SQL*Net more data from client','REPL Capture/Apply: messages','rdbms ipc message')
order by s.username, upper(e.event)
/



  break on day
set pagesize 1000 linesize 500
 col day for a11
 col metric_name for a18
 select
   to_char(begin_time,'DD-Mon') Day,
    A.METRIC_NAME,
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'00',maxval,0)),'999999999999999') "00",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'01',maxval,0)),'999999999999999') "01",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'02',maxval,0)),'999999999999999') "02",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'03',maxval,0)),'999999999999999') "03",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'04',maxval,0)),'999999999999999') "04",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'05',maxval,0)),'999999999999999') "05",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'06',maxval,0)),'999999999999999') "06",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'07',maxval,0)),'999999999999999') "07",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'08',maxval,0)),'999999999999999') "08",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'09',maxval,0)),'999999999999999') "09",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'10',maxval,0)),'999999999999999') "10",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'11',maxval,0)),'999999999999999') "11",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'12',maxval,0)),'999999999999999') "12",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'13',maxval,0)),'999999999999999') "13",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'14',maxval,0)),'999999999999999') "14",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'15',maxval,0)),'999999999999999') "15",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'16',maxval,0)),'999999999999999') "16",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'17',maxval,0)),'999999999999999') "17",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'18',maxval,0)),'999999999999999') "18",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'19',maxval,0)),'999999999999999') "19",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'20',maxval,0)),'999999999999999') "20",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'21',maxval,0)),'999999999999999') "21",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'22',maxval,0)),'999999999999999') "22",
   to_char(sum(decode(substr(to_char(BEGIN_TIME,'HH24'),1,2),'23',maxval,0)),'999999999999999') "23"
    from     dba_hist_sysmetric_summary A
    where   
    A.METRIC_NAME in 
    ('Host CPU Utilization (%)',
     'Average Active Sessions',
     'Session Count',
     'SQL Service Response Time',
     'User Transaction Per Sec',
     'Temp Space Used',
     'Total PGA Allocated'
    ) 
 and BEGIN_TIME > trunc(sysdate) - 7
 group by A.METRIC_NAME, to_char(begin_time,'DD-Mon')
 order by to_char(begin_time,'DD-Mon'),a.metric_name ;

Sunday, 30 July 2017

Restore and Recovery of Pluggable Database (PDB) after dropping Pluggable Database (PDB)

Restore and Recovery of Pluggable Database (PDB) after dropping Pluggable Database (PDB) ...

SQL> startup ;

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 8801008 bytes

Variable Size 624952592 bytes

Database Buffers 436207616 bytes

Redo Buffers 3780608 bytes

Database mounted.

Database opened.

--- Create new pluggable database

create pluggable database anuj1 admin user anuj identified by vihaan123;

set linesize 200

col NAME for a30

COLUMN "RESTRICTED" FORMAT A10

select

d.PDB_ID,

v.create_scn,

v.name,

v.open_mode,

nvl(v.restricted, 'n/a') "RESTRICTED",

d.status

from v$pdbs v , dba_pdbs d

where 1=1

and v.guid=d.guid

order by v.create_scn;

PDB_ID CREATE_SCN NAME OPEN_MODE RESTRICTED STATUS

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

2 1409189 PDB$SEED READ ONLY NO NORMAL

3 1524245 ANUJ MOUNTED n/a NORMAL

4 1674153 ANUJT MOUNTED n/a NORMAL

5 1701092 ANUJ1 MOUNTED n/a NEW

SQL> alter pluggable database anuj1 open ;

Pluggable database altered.

PDB_ID CREATE_SCN NAME OPEN_MODE RESTRICTED STATUS

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

2 1409189 PDB$SEED READ ONLY NO NORMAL

3 1524245 ANUJ MOUNTED n/a NORMAL

4 1674153 ANUJT MOUNTED n/a NORMAL

5 1701092 ANUJ1 READ WRITE NO NORMAL

set head off verify off echo off pages 1500 linesize 120 feedback off

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

select

'PDB_ID..................................................: '||PDB_ID ,

'PDB_NAME................................................: '||PDB_NAME ,

'DBID....................................................: '||DBID ,

'CON_UID.................................................: '||CON_UID,

'GUID....................................................: '||GUID ,

'STATUS..................................................: '||STATUS,

'CREATION_SCN............................................: '||CREATION_SCN,

'VSN.....................................................: '||VSN,

'LOGGING ................................................: '||LOGGING ,

'FORCE_LOGGING ..........................................: '||FORCE_LOGGING ,

'FORCE_NOLOGGING ........................................: '||FORCE_NOLOGGING,

'APPLICATION_ROOT........................................: '||APPLICATION_ROOT,

'APPLICATION_PDB.........................................: '||APPLICATION_PDB,

'APPLICATION_SEED........................................: '||APPLICATION_SEED,

'APPLICATION_ROOT_CON_ID.................................: '||APPLICATION_ROOT_CON_ID,

'IS_PROXY_PDB............................................: '||IS_PROXY_PDB,

'CON_ID..................................................: '||CON_ID,

'UPGRADE_PRIORITY........................................: '||UPGRADE_PRIORITY,

'APPLICATION_CLONE.......................................: '||APPLICATION_CLONE,

'FOREIGN_CDB_DBID........................................: '||FOREIGN_CDB_DBID,

'UNPLUG_SCN..............................................: '||UNPLUG_SCN,

'FOREIGN_PDB_ID..........................................: '||FOREIGN_PDB_ID,

'CREATION_TIME...........................................: '||CREATION_TIME,

'REFRESH_MODE............................................: '||REFRESH_MODE,

'REFRESH_INTERVAL........................................: '||REFRESH_INTERVAL

from CDB_PDBS

where 1=1

and PDB_ID=5

;

PDB_ID..................................................: 5

PDB_NAME................................................: ANUJ1

DBID....................................................: 3927710339

CON_UID.................................................: 3927710339

GUID....................................................: 557155DB36F60EC6E055000000000001

STATUS..................................................: NORMAL

CREATION_SCN............................................: 1701092

VSN.....................................................: 203424000

LOGGING ................................................: LOGGING

FORCE_LOGGING ..........................................: NO

FORCE_NOLOGGING ........................................: NO

APPLICATION_ROOT........................................: NO

APPLICATION_PDB.........................................: NO

APPLICATION_SEED........................................: NO

APPLICATION_ROOT_CON_ID.................................:

IS_PROXY_PDB............................................: NO

CON_ID..................................................: 5

UPGRADE_PRIORITY........................................:

APPLICATION_CLONE.......................................: NO

FOREIGN_CDB_DBID........................................: 1477822556

UNPLUG_SCN..............................................: 1699940

FOREIGN_PDB_ID..........................................: 2

CREATION_TIME...........................................: 29-07-2017 09:10:48

REFRESH_MODE............................................: NONE

REFRESH_INTERVAL........................................:

Creating a test table !!!

sqlplus system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

connect system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

-bash-4.2$ sqlplus system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:18:22 2017

Last Successful login time: Thu Jul 27 2017 20:12:59 +01:00

Connected to:

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

SQL> connect system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

Connected.

-bash-4.2$ sqlplus anuj/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:20:03 2017

Connected to:

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

SQL> show user

USER is "ANUJ"

SQL> def

DEFINE _DATE = "29-JUL-17" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "anuj1" (CHAR)

DEFINE _USER = "ANUJ" (CHAR)

DEFINE _PRIVILEGE = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)

DEFINE _EDITOR = "vi" (CHAR)

DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)

DEFINE _O_RELEASE = "1202000100" (CHAR)

-bash-4.2$ sqlplus anuj/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:28:05 2017

Last Successful login time: Sat Jul 29 2017 09:20:03 +01:00

Connected to:

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

SQL> create table anuj( tdate date);

Table created.

SQL> insert into anuj as select sysdate from dual;

insert into anuj as select sysdate from dual

ERROR at line 1:

ORA-00926: missing VALUES keyword

SQL> insert into anuj select sysdate from dual ;

insert into anuj select sysdate from dual

ERROR at line 1:

ORA-01950: no privileges on tablespace 'SYSTEM'

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

-bash-4.2$ sqlplus system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:36:23 2017

Last Successful login time: Sat Jul 29 2017 09:27:17 +01:00

Connected to:

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

SQL> GRANT UNLIMITED TABLESPACE TO anuj;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

-bash-4.2$ sqlplus anuj/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:37:08 2017

Last Successful login time: Sat Jul 29 2017 09:28:05 +01:00

Connected to:

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

SQL> insert into anuj select sysdate from dual ;

1 row created.

SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

SQL> select * from anuj;

TDATE

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

29-07-2017 09:37:13

SQL>

-bash-4.2$ sqlplus system/sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.77)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=anuj1)))

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 09:56:41 2017

Last Successful login time: Sat Jul 29 2017 09:36:24 +01:00

Connected to:

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

SQL> set linesize 200 pagesize 200

col FILE_NAME for a70

SQL> SQL> col PDB_NAME for a20

select a.CON_ID,PDB_NAME,b.status pdbstatus,TABLESPACE_NAME,FILE_NAME ,trunc(BYTES/1024/1024/1024,2) Gb, trunc(MAXBYTES/1024/1024/1024,2) Max from cdb_data_files a,dba_pdbs b

where b.PDB_ID=a.CON_ID

SQL> 2 3 order by 1;

CON_ID PDB_NAME PDBSTATUS TABLESPACE_NAME FILE_NAME GB MAX

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

5 ANUJ1 NORMAL UNDOTBS1 /u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/dat .09 31.99

afile/o1_mf_undotbs1_dqrjrhhw_.dbf

5 ANUJ1 NORMAL SYSAUX /u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/dat .27 31.99

afile/o1_mf_sysaux_dqrjrhhv_.dbf

5 ANUJ1 NORMAL SYSTEM /u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/dat .24 31.99

afile/o1_mf_system_dqrjrhhl_.dbf

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

Taking a full backup before dropping ( This is a must )

RMAN> RUN

2> {

3> configure controlfile autobackup on;

4> set command id to 'ORCLBackupFull';

5> ALLOCATE CHANNEL c1 DEVICE TYPE disk;

6> backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/u01/app/o

7> sql 'alter system archive log current';

8> backup tag ORCL_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIV

backup tag ORCL_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%

9> 10> release channel c1;

11> }

using target database control file instead of recovery catalog

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

executing command: SET COMMAND ID

allocated channel: c1

channel c1: SID=60 device type=DISK

Starting backup at 29-JUL-17

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_114_1_FULL tag=ORCL_FULL comment=NONE

channel c1: backup set complete, elapsed time: 00:11:11

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00010 name=/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_sysaux_dq898gpw_.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_system_dq898gom_.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/orcl/ORCL/54F57A42B6BF1AD0E055000000000001/datafile/o1_mf_undotbs1_dq898gpx_.dbf

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_115_1_FULL tag=ORCL_FULL comment=NONE

channel c1: backup set complete, elapsed time: 00:03:42

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00022 name=/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anujto1_mf_sysaux_dq898gpw_.dbf

input datafile file number=00021 name=/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anujto1_mf_system_dq898gom_.dbf

input datafile file number=00023 name=/u01/app/oracle/oradata/orcl/ORCL/pdb/anuj1/anujto1_mf_undotbs1_dq898gpx_.dbf

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_116_1_FULL tag=ORCL_FULL comment=NONE

channel c1: backup set complete, elapsed time: 00:07:04

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf

channel c1: starting piece 1 at 29-JUL-17

Message from syslogd@localhost at Jul 29 10:24:23 ...

kernel:BUG: soft lockup - CPU#0 stuck for 21s! [kswapd0:25]

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_117_1_FULL tag=ORCL_FULL comment=NONE

channel c1: backup set complete, elapsed time: 00:04:43

channel c1: starting compressed full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00025 name=/u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrjrhhv_.dbf

input datafile file number=00024 name=/u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_dqrjrhhl_.dbf

input datafile file number=00026 name=/u01/app/oracle/oradata/orcl/ORCL/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrjrhhw_.dbf

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_118_1_FULL tag=ORCL_FULL comment=NONE

channel c1: backup set complete, elapsed time: 00:04:54

Finished backup at 29-JUL-17

Starting Control File and SPFILE Autobackup at 29-JUL-17

piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/c-1477822556-20170729-01 comment=NONE

Finished Control File and SPFILE Autobackup at 29-JUL-17

sql statement: alter system archive log current

Starting backup at 29-JUL-17

current log archived

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=5 RECID=5 STAMP=950474250

input archived log thread=1 sequence=6 RECID=4 STAMP=950474214

input archived log thread=1 sequence=7 RECID=3 STAMP=950474204

input archived log thread=1 sequence=8 RECID=6 STAMP=950610821

input archived log thread=1 sequence=9 RECID=7 STAMP=950610823

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_120_1_ARCHIVE tag=ORCL_ARCHIVE comment=NONE

channel c1: backup set complete, elapsed time: 00:02:31

channel c1: deleting archived log(s)

archived log file name=/u01/app/oracle/Archive/1_5_950041842.dbf RECID=5 STAMP=950474250

archived log file name=/u01/app/oracle/Archive/1_6_950041842.dbf RECID=4 STAMP=950474214

archived log file name=/u01/app/oracle/Archive/1_7_950041842.dbf RECID=3 STAMP=950474204

archived log file name=/u01/app/oracle/Archive/1_8_950041842.dbf RECID=6 STAMP=950610821

archived log file name=/u01/app/oracle/Archive/1_9_950041842.dbf RECID=7 STAMP=950610823

Finished backup at 29-JUL-17

Starting backup at 29-JUL-17

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current control file in backup set

channel c1: starting piece 1 at 29-JUL-17

channel c1: finished piece 1 at 29-JUL-17

piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_121_1_CONTROL tag=ORCL_CONTROL comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 29-JUL-17

Starting Control File and SPFILE Autobackup at 29-JUL-17

piece handle=/u01/app/oracle/product/12.2.0/db_1/dbs/c-1477822556-20170729-02 comment=NONE

Finished Control File and SPFILE Autobackup at 29-JUL-17

released channel: c1

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

-bash-4.2$

-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 10:40:11 2017

SQL> alter pluggable database anuj1 close immediate ;

Pluggable database altered.

SQL> drop pluggable database anuj1 INCLUDING DATAFILES ;

Pluggable database dropped.

RMAN>

RMAN> restore pluggable database anuj1 ;

Starting restore at 29-JUL-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=68 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/29/2017 10:45:51

RMAN-06813: could not translate pluggable database ANUJ1

-bash-4.2$ export ORACLE_SID=dbcdbaux

-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 10:50:28 2017

Connected to an idle instance.

SQL> startup nomount ;

ORACLE instance started.

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Duplicate database to 'dbcdbaux' noopen backup location '/u01/app/oracle/RmanBackup/'; DUPLICATE database to 'dbcdbaux' PLUGGABLE DATABASE anuj1 noopen backup location '/u01/app/oracle/RmanBackup/' ;

RMAN> DUPLICATE database to 'dbcdbaux' PLUGGABLE DATABASE anuj1 noopen backup location '/u01/app/oracle/RmanBackup/' ;

RMAN> exit

Recovery Manager complete.

-bash-4.2$ export ORACLE_SID=dbcdbaux

-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 10:50:28 2017

Connected to an idle instance.

SQL> startup nomount ;

ORACLE instance started.

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

-bash-4.2$ rman auxiliary sys/vihaanssss1!

Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jul 29 10:53:41 2017

connected to auxiliary database: DBCDBAUX (not mounted)

RMAN> DUPLICATE database to 'dbcdbaux' PLUGGABLE DATABASE anuj1 noopen backup location '/u01/app/oracle/RmanBackup/' ;

Starting Duplicate Db at 29-JUL-17

contents of Memory Script:

{

sql clone "alter system set db_name =

''ORCL'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''DBCDBAUX'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile from '/u01/app/oracle/RmanBackup/ORCL_20170729_121_1_CONTROL';

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''DBCDBAUX'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

Starting restore at 29-JUL-17

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=35 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08

output file name=/u01/app/oracle/oradata/dbcdbaux/CONTROL01.CTL

Finished restore at 29-JUL-17

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''DBCDBAUX'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

shutdown clone immediate;

}

executing Memory Script

sql statement: alter system set db_name = ''DBCDBAUX'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 07/29/2017 10:57:45

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file version 12.2.0.0.0 incompatible with ORACLE version 12.1.0.2.0

ORA-00202: control file: '/u01/app/oracle/oradata/dbcdbaux/CONTROL01.CTL'

RMAN> exit

Recovery Manager complete.

-bash-4.2$ ls -ltr /u01/app/oracle/oradata/dbcdbaux/CONTROL01.CTL

-rw-r----- 1 oracle oinstall 18726912 Jul 29 10:56 /u01/app/oracle/oradata/dbcdbaux/CONTROL01.CTL

SQL> startup nomount pfile=initdbcdbaux.ora ;

ORACLE instance started.

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

-bash-4.2$ rman auxiliary sys/vihaanssss1!

Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jul 29 11:12:21 2017

connected to auxiliary database: DBCDBAUX (not mounted)

RMAN> DUPLICATE database to 'dbcdbaux' PLUGGABLE DATABASE anuj1 noopen backup location '/u01/app/oracle/RmanBackup/' ;

Starting Duplicate Db at 29-JUL-17

contents of Memory Script:

{

sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''ORCL'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''DBCDBAUX'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile from '/u01/app/oracle/RmanBackup/ORCL_20170729_121_1_CONTROL';

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''DBCDBAUX'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

Starting restore at 29-JUL-17

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07

output file name=/u01/app/oracle/oradata/dbcdbaux/CONTROL01.CTL

Finished restore at 29-JUL-17

database mounted

released channel: ORA_AUX_DISK_1

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

Skipping pluggable database ANUJ

Skipping pluggable database ANUJT

Automatically adding tablespace SYSTEM

Automatically adding tablespace SYSAUX

Automatically adding tablespace PDB$SEED:SYSTEM

Automatically adding tablespace PDB$SEED:SYSAUX

Automatically adding tablespace UNDOTBS1

Skipping tablespace USERS

Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects

contents of Memory Script:

{

set until scn 1707599;

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 4 to new;

set newname for clone datafile 5 to new;

set newname for clone datafile 6 to new;

set newname for clone datafile 8 to new;

set newname for clone datafile 24 to new;

set newname for clone datafile 25 to new;

set newname for clone datafile 26 to new;

restore

clone database

skip forever tablespace "USERS",

"ANUJ":"UNDOTBS1",

"ANUJ":"SYSTEM",

"ANUJ":"SYSAUX",

"ANUJT":"UNDOTBS1",

"ANUJT":"SYSTEM",

"ANUJT":"SYSAUX" ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 29-JUL-17

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170729_114_1_FULL

Message from syslogd@localhost at Jul 29 11:20:02 ...

kernel:BUG: soft lockup - CPU#0 stuck for 25s! [oracle_23746_db:23746]

Message from syslogd@localhost at Jul 29 11:21:17 ...

kernel:BUG: soft lockup - CPU#0 stuck for 27s! [oracle_23746_db:23746]

Message from syslogd@localhost at Jul 29 11:21:18 ...

kernel:BUG: soft lockup - CPU#0 stuck for 26s! [oracle_23746_db:23746]

Message from syslogd@localhost at Jul 29 11:21:19 ...

kernel:BUG: soft lockup - CPU#0 stuck for 22s! [gnome-shell:2743]

Message from syslogd@localhost at Jul 29 11:36:00 ...

kernel:BUG: soft lockup - CPU#0 stuck for 24s! [oracle_23746_db:23746]

Message from syslogd@localhost at Jul 29 11:37:21 ...

kernel:BUG: soft lockup - CPU#0 stuck for 22s! [oracle:24765]

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_11

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:46:38

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/dbcd

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/dbcd

channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/dbcd

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORC

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_117_1_FULL tag=ORCL_FULL

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:17:42

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00024 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00025 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00026 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170729_118_1_FULL

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_118_1_FULL tag=ORCL_FULL

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:16

Finished restore at 29-JUL-17

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=10 STAMP=950617525 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_system_dqrr1838_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=11 STAMP=950617525 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_dqrr1tn8_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=12 STAMP=950617526 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_dqrr27rc_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=13 STAMP=950617526 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_system_dqrtv31c_.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=14 STAMP=950617526 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_sysaux_dqrttzy9_.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=15 STAMP=950617527 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_undotbs1_dqrtvt7c_.dbf

datafile 24 switched to datafile copy

input datafile copy RECID=16 STAMP=950617527 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_dqrvw0pb_.dbf

datafile 25 switched to datafile copy

input datafile copy RECID=17 STAMP=950617527 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrvw0d6_.dbf

datafile 26 switched to datafile copy

input datafile copy RECID=18 STAMP=950617527 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrvw0rr_.dbf

contents of Memory Script:

{

set until scn 1707599;

recover

clone database

skip forever tablespace "USERS",

"ANUJ":"UNDOTBS1",

"ANUJ":"SYSTEM",

"ANUJ":"SYSAUX",

"ANUJT":"UNDOTBS1",

"ANUJT":"SYSTEM",

"ANUJT":"SYSAUX" delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 29-JUL-17

using channel ORA_AUX_DISK_1

Executing: alter database datafile 11 offline drop

Executing: alter database datafile 9 offline drop

Executing: alter database datafile 10 offline drop

Executing: alter database datafile 23 offline drop

Executing: alter database datafile 21 offline drop

Executing: alter database datafile 22 offline drop

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=8

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=9

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_20170729_120_1_ARCHIVE

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_20170729_120_1_ARCHIVE tag=ORCL_ARCHIVE

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25

archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_950041842.dbf thread=1 sequence=8

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_950041842.dbf RECID=2 STAMP=950617658

archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_950041842.dbf thread=1 sequence=9

Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 7 needs more recovery to be consistent

ORA-01110: data file 7: '/u01/app/oracle/oradata/orcl/users01.dbf'

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_950041842.dbf RECID=1 STAMP=950617585

media recovery complete, elapsed time: 00:00:52

Finished recover at 29-JUL-17

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''DBCDBAUX'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

}

executing Memory Script

sql statement: alter system set db_name = ''DBCDBAUX'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance started

Total System Global Area 297795584 bytes

Fixed Size 8791960 bytes

Variable Size 234883176 bytes

Database Buffers 50331648 bytes

Redo Buffers 3788800 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBCDBAUX" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 SIZE 200 M ,

GROUP 2 SIZE 200 M ,

GROUP 3 SIZE 200 M

DATAFILE

'/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_system_dqrr1838_.dbf',

'/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_system_dqrtv31c_.dbf',

'/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_dqrvw0pb_.dbf'

CHARACTER SET US7ASCII

contents of Memory Script:

{

set newname for clone tempfile 1 to new;

set newname for clone tempfile 2 to new;

set newname for clone tempfile 5 to new;

switch clone tempfile all;

catalog clone datafilecopy "/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_dqrr1tn8_.dbf",

"/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_dqrr27rc_.dbf",

"/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_sysaux_dqrttzy9_.dbf",

"/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_undotbs1_dqrtvt7c_.dbf",

"/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrvw0d6_.dbf",

"/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrvw0rr_.dbf";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 2 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 5 to /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_dqrr1tn8_.dbf RECID=1 STAMP=950618295

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_dqrr27rc_.dbf RECID=2 STAMP=950618295

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_sysaux_dqrttzy9_.dbf RECID=3 STAMP=950618295

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_undotbs1_dqrtvt7c_.dbf RECID=4 STAMP=950618295

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrvw0d6_.dbf RECID=5 STAMP=950618295

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrvw0rr_.dbf RECID=6 STAMP=950618295

datafile 3 switched to datafile copy

input datafile copy RECID=1 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_dqrr1tn8_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=2 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_dqrr27rc_.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=3 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_sysaux_dqrttzy9_.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=4 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_undotbs1_dqrtvt7c_.dbf

datafile 25 switched to datafile copy

input datafile copy RECID=5 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrvw0d6_.dbf

datafile 26 switched to datafile copy

input datafile copy RECID=6 STAMP=950618295 file name=/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrvw0rr_.dbf

Leaving database unopened, as requested

Cannot remove created server parameter file

Finished Duplicate Db at 29-JUL-17

RMAN>

RMAN> report schema;

RMAN-06139: warning: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name DBCDBAUX

List of Permanent Datafiles

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

File Size(MB) Tablespace RB segs Datafile Name

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

1 810 SYSTEM *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_system_dqrr1838_.dbf

3 470 SYSAUX *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_sysaux_dqrr1tn8_.dbf

4 50 UNDOTBS1 *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_undotbs1_dqrr27rc_.dbf

5 250 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_system_dqrtv31c_.dbf

6 280 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_sysaux_dqrttzy9_.dbf

8 100 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_undotbs1_dqrtvt7c_.dbf

24 250 _###_UNKNOWN_PDB_#_5:SYSTEM *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_dqrvw0pb_.dbf

25 290 _###_UNKNOWN_PDB_#_5:SYSAUX *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_sysaux_dqrvw0d6_.dbf

26 100 _###_UNKNOWN_PDB_#_5:UNDOTBS1 *** /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_undotbs1_dqrvw0rr_.dbf

List of Temporary Files

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

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1 20 TEMP 32767 /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/datafile/o1_mf_temp_%u_.tmp

2 64 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/54EE57451A455CA3E055000000000001/datafile/o1_mf_temp_%u_.tmp

5 64 _###_UNKNOWN_PDB_#_5:TEMP 32767 /u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_temp_%u_.tmp

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 ANUJ MOUNTED

4 ANUJT MOUNTED

5 ANUJ1 MOUNTED

SQL> ALTER PLUGGABLE DATABASE anuj1 OPEN RESETLOGS ;

ALTER PLUGGABLE DATABASE anuj1 OPEN RESETLOGS

ERROR at line 1:

ORA-39862: RESETLOGS option only valid after a Pluggable Database incomplete

recovery

SQL> alter pluggable database anuj1 unplug into '/tmp/anuj1.xml';

Pluggable database altered.

SQL> SQL> def

DEFINE _DATE = "29-JUL-17" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "dbcdbaux" (CHAR)

DEFINE _USER = "SYS" (CHAR)

DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)

DEFINE _EDITOR = "vi" (CHAR)

DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)

DEFINE _O_RELEASE = "1202000100" (CHAR)

SQL>

SQL> SET SERVEROUTPUT ON

DECLARE

l_result BOOLEAN;

BEGIN

l_result := DBMS_PDB.check_plug_compatibility(

pdb_descr_file => '/tmp/anuj1.xml',

pdb_name => 'anuj1');

IF l_result THEN

DBMS_OUTPUT.PUT_LINE('compatible ....');

ELSE

DBMS_OUTPUT.PUT_LINE('incompatible :( ');

END IF;

END;

SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14

15 /

DECLARE

ERROR at line 1:

ORA-65012: Pluggable database ANUJ1 already exists.

ORA-06512: at "SYS.DBMS_PDB", line 24

ORA-06512: at line 4

SQL> SQL> SQL> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !cat /tmp/anuj1.xml

<?xml version="1.0" encoding="UTF-8"?>

<PDB>

<vsns>

</vsns>

<name>SYSTEM</name>

<file>

<path>/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/o1_mf_system_dqrvw0pb_.dbf</path>

-bash-4.2$ . oraenv

ORACLE_SID = [dbcdbaux] ? orcl

The Oracle base remains unchanged with value /u01/app/oracle

-bash-4.2$ !sql

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 14:18:11 2017

Connected to an idle instance.

SQL> startup ;

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 8801008 bytes

Variable Size 624952592 bytes

Database Buffers 436207616 bytes

Redo Buffers 3780608 bytes

Database mounted.

Database opened.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

SQL> SQL> SET SERVEROUTPUT ON

DECLARE

l_result BOOLEAN;

BEGIN

l_result := DBMS_PDB.check_plug_compatibility(

pdb_descr_file => '/tmp/anuj1.xml',

pdb_name => 'anuj1');

IF l_result THEN

DBMS_OUTPUT.PUT_LINE('compatible ....');

ELSE

DBMS_OUTPUT.PUT_LINE('incompatible :( ');

END IF;

END;

SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14

compatible ....

PL/SQL procedure successfully completed.

drwxr-x--- 5 oracle oinstall 94 Jul 29 09:10 ORCL/

drwxr-x--- 2 oracle oinstall 6 Jul 22 18:46 orclpdb/

drwxr-x--- 2 oracle oinstall 150 Jul 22 20:55 pdbseed/

-bash-4.2$ cd orclpdb/

-bash-4.2$ ls -ld */

ls: cannot access */: No such file or directory

-bash-4.2$ ls -ld */

ls: cannot access */: No such file or directory

-bash-4.2$ ls -ltr

total 0

-bash-4.2$ pwd

/u01/app/oracle/oradata/orcl/orclpdb

-bash-4.2$ mkdir anuj1

-bash-4.2$

-bash-4.2$ cd anuj1/

-bash-4.2$ pwd

/u01/app/oracle/oradata/orcl/orclpdb/anuj1

-bash-4.2$ !sql

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 29 14:48:53 2017

Connected to:

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

SQL> CREATE PLUGGABLE DATABASE anuj1 USING '/tmp/anuj1.xml' FILE_NAME_CONVERT=('/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/','/u01/app/oracle/oradata/orcl/orclpdb/anuj1/');

CREATE PLUGGABLE DATABASE anuj1 USING '/tmp/anuj1.xml' FILE_NAME_CONVERT=('/u01/app/oracle/oradata/dbcdbaux/DBCDBAUX/557155DB36F60EC6E055000000000001/datafile/','/u01/app/oracle/oradata/orcl/orclpdb/anuj1/')

ERROR at line 1:

ORA-01276: Cannot add file

/u01/app/oracle/oradata/orcl/orclpdb/anuj1/o1_mf_system_dqrvw0pb_.dbf. File

has an Oracle Managed Files file name.

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE anuj1 OPEN READ WRITE;

set linesize 200

col NAME for a30

col "RESTRICTED" FORMAT A10

select d.PDB_ID,v.name,v.open_mode,nvl(v.restricted, 'n/a') "RESTRICTED",d.status from v$pdbs v , dba_pdbs d

where 1=1

and v.guid=d.guid

order by v.create_scn;

SQL> SQL> SQL> SQL> 2 3 4

PDB_ID NAME OPEN_MODE RESTRICTED STATUS

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

2 PDB$SEED READ ONLY NO NORMAL

3 ANUJ MOUNTED n/a NORMAL

4 ANUJT MOUNTED n/a NORMAL

6 ANUJ1 READ WRITE NO NORMAL