Oracle Database Commands and Practical Examples

Friday 11 January 2019

Register Oracle Window 11g and 12c database listener on same hostname ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Hi All , This blogging only for sharing knowledge. As what troubleshooting i have faced and solution i have found i m sharing the same.

11g
11g Database : TESTUPGR
Listener Name : listener11g
Service name : testupgr
Port:1522

C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=testupgr
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>set ORACLE_HOME=C:\app\varunyadav\product\11.2.0\dbhome_1

12c
12c Database : TEST
Listener Name : listener 12c
Service Name : test
Port : 1521

C:\app\varunyadav\product\12.1.0\dbhome_1\BIN>set ORACLE_SID=test

C:\app\varunyadav\product\12.1.0\dbhome_1\BIN>set ORACLE_HOME=C:\app\varunyadav\product\12.1.0\dbhome_1

I have already installed oracle 12c stand alone database on window machine. listener already created. and able to connect db remotely as well.

Actually i was upgrading 11.2.0.1.0 to 11.2.0.4.0 . 11g software and database created successfully. But during listener configuration. services of 11g database not showing when typing command.

lsnrctl status listener11g . Instead of showing, 11g services were showing or pointing towards on 12c listener.

By default PMON process pointing towards 1521 port so 11g services showing on 12c listener.

As we have 1522 port on 11g, We have to set local_listener on 11g parameter and register the database using sql prompt.

After setting the paramater value 11g services showing on lsnrctl status listener11g and 12c listener status earlier showing 11g services got disabled.

Please find troubleshooting steps below :
#################################################################################

I have made local connection to 11g database and able to connect remotely.

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 13:30:09 2019

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

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

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.

#################################################################################

While connecting using service got error below.

C:\Windows\system32>sqlplus sys/system123@testupgr as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 13:30:39 2019

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

C:\Windows\system32>

#################################################################################

While checking listener service on 11g not showing services.

C:\Windows\system32>lsnrctl status listener11g

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2019 13:30:22

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER11G
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 10-JAN-2019 13:30:02
Uptime 0 days 0 hr. 0 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\varunyadav\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\varunyadav\diag\tnslsnr\VARUNY\listener11g\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VARUNY)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

#################################################################

On 12c it is showing 11g services , As by default PMON service point towards 1521 port.

C:\app\varunyadav\product\12.1.0\dbhome_1\BIN>lsnrctl status listener

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 10-JAN-2019 19:03:15

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date 10-JAN-2019 19:01:06
Uptime 0 days 0 hr. 2 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\varunyadav\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File C:\app\varunyadav\diag\tnslsnr\VARUNY\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2762VARUNY1198L.mind.motherson.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testupgr" has 1 instance(s).
Instance "testupgr", status READY, has 1 handler(s) for this service...
Service "testupgrXDB" has 1 instance(s).
Instance "testupgr", status READY, has 1 handler(s) for this service...
The command completed successfully

################################################################################

We need to set local listener on 11g and register database later.

C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 19:06:06 2019

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

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

SQL> sho parameter listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string
remote_listener string
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1522))' scope=both;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

###############################################################################

Stop and start the listener on 11g

C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>lsnrctl stop listener11g

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2019 19:07:41

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY1198L)(PORT=1522)))
The command completed successfully

C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>lsnrctl start listener11g

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2019 19:07:45

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is C:\app\varunyadav\product\11.2.0\dbhome_1\network\admin\listener.ora
Log messages written to c:\app\varunyadav\diag\tnslsnr\VARUNY\listener11g\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VARUNY)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias listener11g
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 10-JAN-2019 19:07:48
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\varunyadav\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\varunyadav\diag\tnslsnr\VARUNY\listener11g\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2762VARUNY1198L.mind.motherson.com)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
##########################################################################

As you can set above still services are not showing up , So we have to register database as well.

C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 19:07:54 2019

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

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

SQL> alter system register;

System altered.

SQL> sho parameter listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=2
762VARUNY1198L)(PORT=1522))
remote_listener string
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

###############################################################################

Check the listener services

C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>lsnrctl status listener11g

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2019 19:08:34

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1522)))

STATUS of the LISTENER

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

Alias listener11g

Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

Start Date 10-JAN-2019 19:07:48

Uptime 0 days 0 hr. 0 min. 48 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File C:\app\varunyadav\product\11.2.0\dbhome_1\network\admin\listener.ora

Listener Log File c:\app\varunyadav\diag\tnslsnr\VARUNY\listener11g\alert\log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2762VARUNY1198L.mind.motherson.com)(PORT=1522)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))

Services Summary...

Service "CLRExtProc" has 1 instance(s).

Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "testupgr" has 1 instance(s).

Instance "testupgr", status READY, has 1 handler(s) for this service...

Service "testupgrXDB" has 1 instance(s).

Instance "testupgr", status READY, has 1 handler(s) for this service...

The command completed successfully

C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>

#################################################################################

Able to connect 11g database service remotely using service entry

C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>sqlplus sys/system123@testupgr as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 19:08:13 2019

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

################### Thank you ####################################################

Wednesday 9 January 2019

Oracle 12c EXPDP comands and Examples

### check expdp backup status ##

select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;

##check number objects exists on schema ##

select count(*),object_type,status from dba_objects where owner='TEST' group by status,object_type;
select count(*),object_type,status from dba_objects where owner='TEST' group by status,object_type;

## check valid invalid objects of schema ##

select object_type,count(*) from dba_objects where status='INVALID' and owner='TEST' group by object_type;

select object_type,count(*) from dba_objects where status='VALID' and owner='TEST' group by object_type;

select distinct tablespace_name from dba_segments where owner='TEST';
select distinct tablespace_name from dba_segments where owner='TEST';

### check job name during impdp expdp ###
###############################################################
select message, time_remaining/3600 hrs from v$session_longops where sofar <> totalwork;

###############################################################

select t.username,t.START_TIME,t.LAST_UPDATE_TIME,t.TIME_REMAINING,t.ELAPSED_SECONDS,
t.opname,t.target_desc,t.sofar,t.totalwork,t.message from V$SESSION_LONGOPS t where/* t.USERNAME = 'user_name' and*/ t.TARGET_DESC = 'EXPORT';

#########Recompile invalid objects###########

@?/rdbms/admin/utlrp.sql
set time on
set timi on
exec dbms_stats.gather_schema_stats('INFODBA',cascade => TRUE,estimate_percent => 30);
exec dbms_stats.gather_schema_stats('MLIVE',cascade => TRUE,estimate_percent => 10);

EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'INFODBA');
EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'INFODBA2');

########Drop Users##########

Drop Single User
drop user INFODBA cascade;

Drop multiple user
drop user INFODBA1, INFODBA cascade;

######## schema DDL Metadata ################

set heading off
set echo off
set feedback off
set long 999999
spool EISP_PISDATA_PISVIEW_PISLOAD_PERMISSIONS.txt
set long 100000
set pages 200
select dbms_metadata.get_ddl( 'USER','INFODBA' ) from dual;
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'INFODBA' ) from dual;
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'INFODBA' ) from dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'INFODBA' ) from dual;
spool off
set heading on
set feedback on

###################################### expdp -impdp 12c ######################

CREATE OR REPLACE DIRECTORY expdp AS '/u01/app/oracle/oradata/';

GRANT READ, WRITE ON DIRECTORY expdp TO username; (if expdp is done through schema)

---> Full EXPDP full=y

expdp \"sys/infodba@dmenv as sysdba\" directory=expdp dumpfile=expdp:expdp_dmenv%d.dmp logfile=expdp:expdp_full_dmenv%d.log full=y

NOTE : Before full impdp check existing tablespace on particular system and datafile .

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u04/MasterDB/oradata/system01.dbf
/u04/MasterDB/oradata/sysaux01.dbf
/u04/MasterDB/oradata/undotbs01.dbf
/u04/MasterDB/oradata/idata01.dbf
/u04/MasterDB/oradata/ilog01.dbf
/u04/MasterDB/oradata/indx01.dbf
/u04/MasterDB/oradata/idata02.dbf

7 rows selected.

SQL>
select name,open_mode,database_role,db_unique_name from v$database;
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
SQL> @tablespace

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
xxx READ WRITE PRIMARY xxx

Tablespace Allocated Current Used Available %Used (vs)
Name Size(GB) Size(GB) Size(GB) Size(GB) (Allocated)
------------------------------ ------------ ------------ ------------ ------------ ------------
IDATA 64.00 16.10 4.61 59.39 7.20
ILOG 32.00 2.00 .01 31.99 .02
INDX 32.00 .05 .00 32.00 .00
SYSAUX 32.00 4.00 1.15 30.85 3.60
SYSTEM 32.00 4.00 .41 31.59 1.28
UNDOTBS1 32.00 4.00 .05 31.95 .16

6 rows selected.

SQL>

impdp \"sys/infodba@dmenv as sysdba\" directory=expdp dumpfile=expdp:expdp_dmenv%d.dmp logfile=expdp:expdp_full_dmenv%d.log full=y

### expdp schema ###

remap_schema=Source_schema:Destination_schema

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp00.dmp logfile=expdptesr.log schemas=infodba1 parallel=2 content=all exclude=statistics parallel=2

impdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp.dmp logfile=impdp_remap.log schemas=infodba:infodba1 parallel=2 transform=oid:n

Different schema

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp00.dmp logfile=expdptesr.log schemas=infodba1 parallel=2 content=all exclude=statistics parallel=2

impdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp.dmp logfile=impdp_remap.log remap_schema=infodba:infodba1 parallel=2 transform=oid:n

#### expdp using different directories ######

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01.dmp,expdp1:expdp02.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics

impdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01.dmp,expdp1:expdp02.dmp logfile=expdp1:impdpdp.log remap_schema=infodba1:infodba parallel=2

#### EXPDP FILESIZE #####

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01.dmp,expdp1:expdp02.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics filesize=200M

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics

SENARIO :

1. schema size is 1058.875 MB ,Total estimation using BLOCKS method: 495.1 MB , we have given filesize=100 MB but total estimation size of schema is 495 MB .

We have run expdp ang got error below:

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01.dmp,expdp1:expdp02.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics filesize=100M

ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes

2. If dumpfile name already exists and with same name we are doing expdp on it we will get error.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-31641: unable to create dump file "C:\varun\expdp\expdp01.dmp"

ORA-27038: created file already exists

OSD-04010: <create> option specified, file already exists

########## expdp filesize using %U ###############

expdp \"sys/system@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics filesize=50M

Dump file set for SYS.SYS_EXPORT_S

C:\VARUN\EXPDP\EXPDP0001.DMP

C:\VARUN\EXPDP1\EXPDP0101.DMP

C:\VARUN\EXPDP2\EXPDP0201.DMP

C:\VARUN\EXPDP\EXPDP0002.DMP

C:\VARUN\EXPDP1\EXPDP0102.DMP

C:\VARUN\EXPDP2\EXPDP0202.DMP

C:\VARUN\EXPDP\EXPDP0003.DMP

C:\VARUN\EXPDP1\EXPDP0103.DMP

#################### Senerio ################################

Accidently infodba already exists and we are trying to remap_schema=infodba:infodba1,

NOTE: But by mistakenly we have done remap_schema=infodba1:infodba, so infodba already exists , so we get the error ORA-31684: Object type USER:"INFODBA" already exists

C:\Users\varunyadav>impdp \"sys/system@prod12c as sysdba\"

directory=expdp

dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp

logfile=expdp:expdp.log

remap_schema=infodba1:infodba

Import: Release 12.1.0.2.0 - Production on Fri Dec 1 13:32:55 2017

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01": "sys/********@prod12c AS SYSDBA" directory=expdp dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:expdp.log remap_schema=infodba1:infodba

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"INFODBA" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39151: Table "INFODBA"."EIM_UID_GENERATOR_ROOT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."PM_PROCESS_LIST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."ACLS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_ROOT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_BOOT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_CV_OBJECTS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_INDEXES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_M_LOCK" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_R_LOCK" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_F_LOCK" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_B_LOCK" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."AUDITLOG" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."USERDEFINEDLOG" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_LOCK_LOGGING" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."POM_RECIPE_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."TIE_CONCURRENCY" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."SCRATCH_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."SUBSCRIPTION_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."BOM_ACCT_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."BOM_RECIPE_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."ACCT_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."MMV_SPATIAL_CELL_INDEX" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."MMV_EQUIVALENT_THREADS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."TRANSACTION_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."ROOT_OBJS_IN_TRANS_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."EXPORT_TO_SITE_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "INFODBA"."PRINCIPAL_OBJ_IN_TRANS_TABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

######### IMPDP schema using FILESIZE ############

remap_schema=Source_schema:Destination_schema

impdp \"sys/system@smrprodb as sysdba\" dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:impdp.log remap_schema=infodba1:infodba3,infodba2:infodba4 parallel=1 filesize =200m

####### Impdp remap schema (multiple schema) #####

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_dmenv12dec2017.dmp logfile=impdp_dmenv12dec2017.log remap_schema=infodba:infodbatest parallel=2

###### Remap multiple tablespace ##########

SQL> create tablespace ILOG3 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\ilog3.DBF' size 2g autoextend on;

Tablespace created.

SQL> create tablespace ILOG4 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\ilog4.DBF' size 2g autoextend on;

Tablespace created.

SQL> create tablespace INDX3 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\indx3.DBF' size 2g autoextend on;

Tablespace created.

SQL> create tablespace INDX4 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\indx4.DBF' size 2g autoextend on;

Tablespace created.

SQL> create tablespace IDATA3 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\idata3.DBF' size 2g autoextend on;

Tablespace created.

SQL> create tablespace IDATA4 datafile 'C:\APP\VARUNYADAV\ORADATA\SMRPRODB\idata4.DBF' size 2g autoextend on;

Tablespace created.

#####################################

REMAP_TABLESPACE=src1:dst1 REMAP_TABLESPACE=src2:dst2

######### EXPDP table flashback #########################

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log full=y FLASHBACK_SCN=2106577

########################################################## EXPDP table Flashback SCN | Flashback Time ###################################################

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log full=y FLASHBACK_SCN=2106577

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba flashback_time="to_timestamp('13-12-2017 14:21:00', 'DD-MM-YYYY HH24:MI:SS')"

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME="TO_TIMESTAMP('2017/12/13 14:00:00','YYYY/MM/DDHH24:MI:SS')"

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME='13-12-2017 2:35:00 PM'

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME='13-12-2017 2:35:00 PM'expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba

FLASHBACK_TIME="2017/12/1315:34:01"

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME="2017/12/1315:34:01"

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn%.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME="2017/12/1315:34:01"

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn_1.dmp logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME="2017/12/1315:34:01"

####################################### table_exists_action=append|replace | truncate ########################################

673 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=impdp_dmenv_table.log tables=infodba.PITEM,infodba.PEPMTASK table_exists_action=append

674 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=impdp_dmenv_table.log tables=infodba.PITEM,infodba.PEPMTASK table_exists_action=replace

675 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=impdp_dmenv_table.log remap_schema=infodba:infodbatest tables=PITEM,PEPMTASK

676 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=impdp_dmenv_table.log remap_schema=infodba:infodbatest tables=infodba.PITEM,infodba.PEPMTASK

677 expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=dmenv_table1.log tables=infodba.PITEM,infodba.PFORM

678 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log remap_schema=infodba:infodbatest tables=infodba.PITEM,infodba.PFORM table_exists_action=append

679 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log remap_schema=infodba:infodbatest tables=infodba.PITEM,infodba.PFORM table_exists_action=replace

680 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log remap_schema=infodba:infodbatest tables=infodba.PITEM,infodba.PFORM table_exists_action=truncate

681 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log tables=PITEM:PITEM2,PFORM:PFORM2

682 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log schemas=infodba tables=PITEM:PITEM2,PFORM:PFORM2

683 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log schemas=infodba tables=PITEM:PITEM2,PFORM:PFORM2

684 impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp logfile=impdp_dmenv_table1.log tables=infodba.PITEM:PITEM2,infodba.PFORM:PFORM2

########################### IMPDP TABLES #############################################################

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=dmenv_table.log tables=infodba.PITEM,infodba.PEPMTASK

impdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table.dmp logfile=impdp_dmenv_table.log tables=infodba.PITEM,infodba.PEPMTASK

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log tables=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp directory=expdpdmenv dumpfile=expdp_table%u.dmplogfile=impdp_table1.log remap_table=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=PITEM:infodbatest.pitem2,PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=infodbatest.pitem2,PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2

impdp "sys/infodba@dmenv AS SYSDBA" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=truncate

impdp "sys/infodba@dmenv AS SYSDBA" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=replace

impdp "sys/infodba@dmenv AS SYSDBA" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=append

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=append

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=replace

impdp \"sys/infodba@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=truncateimpdp help=y

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log tables=infodba.PITEM:infodbatest.pitem2 parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log tables=infodba.PITEM,infodba.pam_tree remap_schema=infodba:infodbatest parallel=2

impdp \"sys/infodba@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=PITEM:pitem2,pam_tree:pam_tree2

Expdp Estimate only

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=expdp_full.dmp ESTIMATE_ONLY=yes logfile=full_log_estimate.log

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=yes logfile=full_log_estimate.log

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=y logfile=full_log_estimate.log

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=y logfile=full_log_estimate.log full=y

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=y logfile=full_log_estimate.log full=y

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=BLOCKS logfile=full_log_estimate.log full=y

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=STATISTICS logfile=full_log_estimate.log full=y

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=expdp_statistivcs.dmp ESTIMATE=STATISTICS logfile=full_log_estimate.log full=y

expdp \"sys/infodba@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=y logfile=full_log_estimate.log schemas=infodba

Tuesday 8 January 2019

Oracle 12c ADCRI commands and examples

Using sqlplus prompt check the location diagnostic file location.

set lines 200

col NAME for a50

col VALUE for a50

SELECT name, value FROM v$diag_info;

oracle@sgdcplm02:~> adrci

oracle@sgdcplm01:~> adrci -help

adrci> show incident

ADR Home = /u02/app/oracle/diag/rdbms/smrprodb/SMRPRODB:

*************************************************************************

INCIDENT_ID PROBLEM_KEY CREATE_TIME

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

57201 ORA 4031 2018-03-22 15:27:57.652000 +01:00

57202 ORA 4031 2018-03-22 15:28:01.143000 +01:00

57185 ORA 4031 2018-03-22 15:28:33.741000 +01:00

57186 ORA 4031 2018-03-22 15:28:35.431000 +01:00

57065 ORA 4031 2018-03-22 15:32:11.646000 +01:00

56998 ORA 4031 2018-04-19 21:35:53.990000 +02:00

56999 ORA 4031 2018-04-19 21:35:57.807000 +02:00

57000 ORA 4031 2018-04-19 21:35:59.521000 +02:00

59207 ORA 4031 2018-04-19 21:36:01.251000 +02:00

59208 ORA 4031 2018-04-19 21:36:02.932000 +02:00

56841 ORA 4031 2018-04-30 13:44:40.166000 +02:00

56842 ORA 4031 2018-04-30 13:44:44.128000 +02:00

56945 ORA 4031 2018-05-01 13:29:45.663000 +02:00

56946 ORA 4031 2018-05-01 13:29:49.617000 +02:00

56969 ORA 4031 2018-05-02 08:56:38.026000 +02:00

56970 ORA 4031 2018-05-02 08:56:50.693000 +02:00

56971 ORA 4031 2018-05-02 08:56:52.178000 +02:00

56972 ORA 4031 2018-05-02 08:56:55.567000 +02:00

56973 ORA 4031 2018-05-02 08:56:57.119000 +02:00

56889 ORA 4031 2018-05-02 14:25:25.394000 +02:00

57097 ORA 4031 2018-05-02 16:35:22.001000 +02:00

57098 ORA 4031 2018-05-02 16:35:26.076000 +02:00

57169 ORA 4031 2018-05-02 16:38:08.116000 +02:00

57209 ORA 4031 2018-05-02 18:09:05.799000 +02:00

57210 ORA 4031 2018-05-02 18:09:09.130000 +02:00

56985 ORA 4031 2018-05-04 11:12:07.000000 +02:00

56986 ORA 4031 2018-05-04 11:12:11.576000 +02:00

56905 ORA 4031 2018-05-04 11:57:12.841000 +02:00

56906 ORA 4031 2018-05-04 11:57:14.210000 +02:00

56907 ORA 4031 2018-05-04 11:57:16.004000 +02:00

55609 ORA 4031 2018-05-04 12:13:50.193000 +02:00

55610 ORA 4031 2018-05-04 12:13:54.372000 +02:00

55611 ORA 4031 2018-05-04 12:13:55.907000 +02:00

55612 ORA 4031 2018-05-04 12:13:57.455000 +02:00

55613 ORA 4031 2018-05-04 12:14:39.442000 +02:00

56865 ORA 4031 2018-05-04 13:18:53.301000 +02:00

56866 ORA 4031 2018-05-04 13:18:54.845000 +02:00

56867 ORA 4031 2018-05-04 13:18:55.972000 +02:00

56937 ORA 4031 2018-05-04 13:33:44.904000 +02:00

56938 ORA 4031 2018-05-04 13:33:46.180000 +02:00

56962 ORA 4031 2018-05-05 11:12:41.345000 +02:00

56963 ORA 4031 2018-05-05 11:12:43.702000 +02:00

56964 ORA 4031 2018-05-05 11:12:45.192000 +02:00

56965 ORA 4031 2018-05-05 11:12:47.690000 +02:00

56966 ORA 4031 2018-05-05 11:12:49.226000 +02:00

55615 ORA 4031 2018-05-05 13:06:14.143000 +02:00

55616 ORA 4031 2018-05-05 13:06:21.174000 +02:00

59254 ORA 4031 2018-05-05 13:06:22.670000 +02:00

59255 ORA 4031 2018-05-05 13:06:26.469000 +02:00

59256 ORA 4031 2018-05-05 13:06:29.736000 +02:00

ADR Home = /u02/app/oracle/diag/clients/user_oracle/host_1505528871_82:

*************************************************************************

0 rows fetched

ADR Home = /u02/app/oracle/diag/tnslsnr/sgdcplm02/smrpro_listener:

*************************************************************************

0 rows fetched

ADR Home = /u02/app/oracle/diag/tnslsnr/sgdcplm02/listener:

*************************************************************************

First 0 rows fetched (*** more available ***)

adrci> show problem

adrci> show control

DIA-48448: This command does not support multiple ADR homes

adrci> show incident -mode detail -p "incident_id=59256"

ADR Home = /u02/app/oracle/diag/rdbms/smrprodb/SMRPRODB:

*************************************************************************

**********************************************************

INCIDENT INFO RECORD 1

**********************************************************

INCIDENT_ID 59256

STATUS ready

CREATE_TIME 2018-05-05 13:06:29.736000 +02:00

PROBLEM_ID 1

CLOSE_TIME <NULL>

FLOOD_CONTROLLED none

ERROR_FACILITY ORA

ERROR_NUMBER 4031

ERROR_ARG1 52520

ERROR_ARG2 shared pool

ERROR_ARG3 SELECT /*+ LEADING( tt ) DYN...

ERROR_ARG4 KGLH0^46c0ec77

ERROR_ARG5 kkslcr:unsafepos

ERROR_ARG6 <NULL>

ERROR_ARG7 <NULL>

ERROR_ARG8 <NULL>

ERROR_ARG9 <NULL>

ERROR_ARG10 <NULL>

ERROR_ARG11 <NULL>

ERROR_ARG12 <NULL>

SIGNALLING_COMPONENT KGH

SIGNALLING_SUBCOMPONENT <NULL>

SUSPECT_COMPONENT <NULL>

SUSPECT_SUBCOMPONENT <NULL>

ECID <NULL>

IMPACTS 0

PROBLEM_KEY ORA 4031

FIRST_INCIDENT 57201

FIRSTINC_TIME 2018-03-22 15:27:57.652000 +01:00

LAST_INCIDENT 74413

LASTINC_TIME 2018-09-15 08:40:23.636000 +02:00

IMPACT1 34668547

IMPACT2 34668546

IMPACT3 0

IMPACT4 0

KEY_NAME SID

KEY_VALUE 481.39600

KEY_NAME ProcId

KEY_VALUE 51.103

KEY_NAME PQ

KEY_VALUE (0, 1525518389)

KEY_NAME Service

KEY_VALUE SMRPRODB

KEY_NAME Module

KEY_VALUE tcserver@sgdcpl03 (TNS V1-V3)

KEY_NAME Client ProcId

KEY_VALUE oracle@sgdcplm02 (TNS V1-V3).76855_140555836445280

OWNER_ID 1

INCIDENT_FILE /u02/app/oracle/diag/rdbms/smrprodb/SMRPRODB/incident/incdir_59256/SMRPRODB_ora_76855_i59256.trc

OWNER_ID 1

INCIDENT_FILE /u02/app/oracle/diag/rdbms/smrprodb/SMRPRODB/trace/SMRPRODB_ora_76855.trc

ADR Home = /u02/app/oracle/diag/clients/user_oracle/host_1505528871_82:

*************************************************************************

0 rows fetched

</ADR_HOME>

<ADR_HOME name="/u02/app/oracle/diag/clients/user_oracle/host_1505528871_82">

ADR Home = /u02/app/oracle/diag/tnslsnr/sgdcplm02/smrpro_listener:

*************************************************************************

0 rows fetched

</ADR_HOME>

<ADR_HOME name="/u02/app/oracle/diag/tnslsnr/sgdcplm02/smrpro_listener">

ADR Home = /u02/app/oracle/diag/tnslsnr/sgdcplm02/listener:

*************************************************************************

0 rows fetched

adrci> show trace /u02/app/oracle/diag/rdbms/smrprodb/SMRPRODB/trace/SMRPRODB_ora_76855.trc

DIA-48908: No trace files are found

adrci> show trace /u02/app/oracle/diag/rdbms/smrprodb/SMRPRODB/incident/incdir_59256/SMRPRODB_ora_76855_i59256.trc

Output the results to file: /tmp/utsout_101863_1397_4.ado

adrci>

£££££££££££££££££££££££££££££££££££££££££££££

ADCRI Example

[oracle@srv6 ~]$ adrci

ADRCI: Release 12.1.0.2.0 - Production on Wed Sep 15 16:41:10 2021

ADR base = "/u01/app/oracle"

adrci> show problem;

DIA-48318: ADR Relation [PROBLEM] of version=3 cannot be supported

ADR Home = /u01/app/oracle/diag/rdbms/test19c/TEST19C:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/rdbms/orcl12c/orcl12c:

*************************************************************************

PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME

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

3 ORA 600 [kewrose_1] 73025 2021-09-15 16:20:05.145000 +05:30

4 ORA 603 78481 2021-09-15 16:30:05.901000 +05:30

6 ORA 600 [ORA-00600: internal error code, arguments: [4193], 78643 2021-09-15 16:31:37.232000 +05:30

2 ORA 600 [600] 78818 2021-09-15 16:35:07.316000 +05:30

1 ORA 600 [4193] 78644 2021-09-15 16:36:00.519000 +05:30

5 ORA 600 [ORA-00600: internal error code, arguments: [4193], 78645 2021-09-15 16:36:09.369000 +05:30

ADR Home = /u01/app/oracle/diag/clients/user_oracle/host_1213252236_82:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/clients/user_oracle/host_1213252236_110:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/tnslsnr/srv6/listener:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/tnslsnr/srv6/listener12c:

*************************************************************************

0 rows fetched

adrci> show incident

DIA-48458: "show incident" failed due to the following errors

DIA-48318: ADR Relation [INCIDENT] of version=5 cannot be supported

ADR Home = /u01/app/oracle/diag/rdbms/test19c/TEST19C:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/rdbms/orcl12c/orcl12c:

*************************************************************************

INCIDENT_ID PROBLEM_KEY CREATE_TIME

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

61785 ORA 600 [4193] 2021-09-15 12:08:23.344000 +05:30

61753 ORA 600 [4193] 2021-09-15 12:08:34.605000 +05:30

61754 ORA 600 [kewrose_1] 2021-09-15 12:08:42.202000 +05:30

61873 ORA 600 [4193] 2021-09-15 12:08:49.612000 +05:30

61874 ORA 600 [600] 2021-09-15 12:08:57.767000 +05:30

61977 ORA 600 [4193] 2021-09-15 12:09:04.404000 +05:30

61985 ORA 600 [4193] 2021-09-15 12:09:12.321000 +05:30

61994 ORA 600 [ORA-00600: internal error code, arguments: [4193], 2021-09-15 12:09:29.156000 +05:30

67204 ORA 603 2021-09-15 12:09:34.996000 +05:30

67268 ORA 600 [4193] 2021-09-15 13:21:49.255000 +05:30

67364 ORA 600 [4193] 2021-09-15 13:21:56.384000 +05:30

67365 ORA 600 [kewrose_1] 2021-09-15 13:22:02.472000 +05:30

67636 ORA 600 [4193] 2021-09-15 13:22:07.732000 +05:30

67637 ORA 600 [600] 2021-09-15 13:22:17.857000 +05:30

67684 ORA 600 [4193] 2021-09-15 13:22:19.020000 +05:30

67685 ORA 600 [600] 2021-09-15 13:22:32.617000 +05:30

67620 ORA 600 [4193] 2021-09-15 13:25:03.933000 +05:30

67621 ORA 600 [600] 2021-09-15 13:25:09.363000 +05:30

67541 ORA 600 [600] 2021-09-15 13:30:04.647000 +05:30

67542 ORA 603 2021-09-15 13:30:10.228000 +05:30

67544 ORA 600 [kewrose_1] 2021-09-15 13:32:52.639000 +05:30

67558 ORA 600 [600] 2021-09-15 13:45:01.578000 +05:30

67573 ORA 600 [kewrose_1] 2021-09-15 13:51:49.295000 +05:30

67575 ORA 600 [kewrose_1] 2021-09-15 13:51:53.110000 +05:30

67577 ORA 600 [kewrose_1] 2021-09-15 13:51:55.935000 +05:30

72826 ORA 600 [4193] 2021-09-15 14:24:39.977000 +05:30

72827 ORA 600 [600] 2021-09-15 14:24:45.460000 +05:30

67579 ORA 600 [4193] 2021-09-15 14:30:02.207000 +05:30

72828 ORA 600 [600] 2021-09-15 14:30:08.348000 +05:30

67596 ORA 600 [4193] 2021-09-15 14:30:16.874000 +05:30

67597 ORA 600 [kewrose_1] 2021-09-15 14:30:24.285000 +05:30

72834 ORA 600 [4193] 2021-09-15 14:35:02.161000 +05:30

72835 ORA 600 [600] 2021-09-15 14:35:07.577000 +05:30

72829 ORA 600 [4193] 2021-09-15 14:36:49.925000 +05:30

67645 ORA 600 [600] 2021-09-15 15:39:56.800000 +05:30

67653 ORA 600 [600] 2021-09-15 15:40:00.270000 +05:30

67647 ORA 600 [kewrose_1] 2021-09-15 15:40:34.146000 +05:30

72864 ORA 603 2021-09-15 16:15:05.119000 +05:30

72920 ORA 600 [4193] 2021-09-15 16:19:50.912000 +05:30

73024 ORA 600 [4193] 2021-09-15 16:19:58.426000 +05:30

73025 ORA 600 [kewrose_1] 2021-09-15 16:20:05.145000 +05:30

73320 ORA 600 [4193] 2021-09-15 16:20:13.965000 +05:30

73321 ORA 600 [ORA-00600: internal error code, arguments: [4193], 2021-09-15 16:20:24.693000 +05:30

72921 ORA 600 [4193] 2021-09-15 16:20:31.496000 +05:30

72922 ORA 600 [ORA-00600: internal error code, arguments: [4193], 2021-09-15 16:20:40.305000 +05:30

72923 ORA 600 [4193] 2021-09-15 16:20:56.744000 +05:30

72924 ORA 600 [ORA-00600: internal error code, arguments: [4193], 2021-09-15 16:21:04.091000 +05:30

73217 ORA 600 [600] 2021-09-15 16:24:40.940000 +05:30

78481 ORA 603 2021-09-15 16:30:05.901000 +05:30

78537 ORA 600 [4193] 2021-09-15 16:31:16.588000 +05:30

ADR Home = /u01/app/oracle/diag/clients/user_oracle/host_1213252236_82:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/clients/user_oracle/host_1213252236_110:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/tnslsnr/srv6/listener:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/tnslsnr/srv6/listener12c:

*************************************************************************

First 0 rows fetched (*** more available ***)

adrci> show incident -mode detail -p "incident_id= 78537"

DIA-48458: "show incident" failed due to the following errors

DIA-48318: ADR Relation [INCIDENT] of version=5 cannot be supported

ADR Home = /u01/app/oracle/diag/rdbms/test19c/TEST19C:

*************************************************************************

0 rows fetched

<INCIDENT_INFO mode="detail">

<ADR_HOME name="/u01/app/oracle/diag/rdbms/test19c/TEST19C">

ADR Home = /u01/app/oracle/diag/rdbms/orcl12c/orcl12c:

*************************************************************************

**********************************************************

INCIDENT INFO RECORD 1

**********************************************************

INCIDENT_ID 78537

STATUS ready

CREATE_TIME 2021-09-15 16:31:16.588000 +05:30

PROBLEM_ID 1

CLOSE_TIME <NULL>

FLOOD_CONTROLLED none

ERROR_FACILITY ORA

ERROR_NUMBER 600

ERROR_ARG1 4193

ERROR_ARG2 376

ERROR_ARG3 380

ERROR_ARG4 <NULL>

ERROR_ARG5 <NULL>

ERROR_ARG6 <NULL>

ERROR_ARG7 <NULL>

ERROR_ARG8 <NULL>

ERROR_ARG9 <NULL>

ERROR_ARG10 <NULL>

ERROR_ARG11 <NULL>

ERROR_ARG12 <NULL>

SIGNALLING_COMPONENT <NULL>

SIGNALLING_SUBCOMPONENT <NULL>

SUSPECT_COMPONENT <NULL>

SUSPECT_SUBCOMPONENT <NULL>

ECID <NULL>

IMPACTS 0

PROBLEM_KEY ORA 600 [4193]

FIRST_INCIDENT 61785

FIRSTINC_TIME 2021-09-15 12:08:23.344000 +05:30

LAST_INCIDENT 78646

LASTINC_TIME 2021-09-15 16:41:59.817000 +05:30

IMPACT1 34668555

IMPACT2 34668546

IMPACT3 0

IMPACT4 0

KEY_NAME Service

KEY_VALUE SYS$USERS

KEY_NAME PdbName

KEY_VALUE CDB$ROOT

KEY_NAME Client ProcId

KEY_VALUE oracle@srv6.example.com (TNS V1-V3).15950_140061077414528

KEY_NAME Module

KEY_VALUE sqlplus@srv6.example.com (TNS V1-V3)

KEY_NAME ProcId

KEY_VALUE 6.3

KEY_NAME PQ

KEY_VALUE (16777217, 1631703675)

KEY_NAME SID

KEY_VALUE 1.39345

OWNER_ID 1

INCIDENT_FILE /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/incident/incdir_78537/orcl12c_ora_15950_i78537.trc

OWNER_ID 1

INCIDENT_FILE /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_15950.trc

ADR Home = /u01/app/oracle/diag/clients/user_oracle/host_1213252236_82:

*************************************************************************

0 rows fetched

</ADR_HOME>

<ADR_HOME name="/u01/app/oracle/diag/clients/user_oracle/host_1213252236_82">

ADR Home = /u01/app/oracle/diag/clients/user_oracle/host_1213252236_110:

*************************************************************************

0 rows fetched

</ADR_HOME>

<ADR_HOME name="/u01/app/oracle/diag/clients/user_oracle/host_1213252236_110">

ADR Home = /u01/app/oracle/diag/tnslsnr/srv6/listener:

*************************************************************************

0 rows fetched

</ADR_HOME>

<ADR_HOME name="/u01/app/oracle/diag/tnslsnr/srv6/listener">

ADR Home = /u01/app/oracle/diag/tnslsnr/srv6/listener12c:

*************************************************************************

0 rows fetched

adrci> exit

[oracle@srv6 ~]$

Purge example

oracle@PLM01:~> adrci

ADRCI: Release 12.1.0.2.0 - Production on Mon Oct 11 07:38:30 2021

ADR base = "/u01/app/oracle"

adrci> show homes

ADR Homes:

diag/rdbms/smrprodb_dr/SMRPRODB_DR

diag/rdbms/smrtestenv/SMRTESTENV

diag/rdbms/smrpatch7/SMRPATCH7

diag/rdbms/smrminddev/SMRMINDDEV

diag/rdbms/smrrac/smrrac2

diag/rdbms/smrprod/SMRPROD2

diag/clients/user_oracle/host_1450352090_82

diag/clients/user_grid/host_1450352090_82

diag/clients/user_root/host_1450352090_82

diag/tnslsnr/PLM01/listener

diag/tnslsnr/PLM01/listener_standalone

adrci> set homes diag/rdbms/smrprod/SMRPROD2

minutes =7200 = 5 days

adrci> purge -age 7200 -type TRACE

adrci> purge -age 7200 -type ALERT

###########################

oracle@srv1:~> adrci

ADRCI: Release 12.1.0.2.0 - Production on Fri Oct 8 13:13:08 2021

ADR base = "/u01/app/oracle"

adrci> show homes

ADR Homes:

diag/rdbms/rac/rac1

diag/clients/user_oracle/host_775046424_82

adrci> set homes diag/rdbms/rac/rac1

adrci> purge -age 30240 -type TRACE

adrci> purge -age 7200 -type TRACE

adrci> purge -age 3600 -type TRACE

adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/rac/rac1:

*************************************************************************

INCIDENT_ID PROBLEM_KEY CREATE_TIME

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

29297 ORA 7445 [kghfre] 2021-07-23 19:38:24.079000 +05:30

67698 ORA 7445 [kghfre] 2021-09-06 12:29:34.844000 +05:30

67699 ORA 7445 [kspgip] 2021-09-06 12:29:35.390000 +05:30

129604 ORA 603 2021-10-07 16:38:05.466000 +05:30

129605 ORA 603 2021-10-07 16:38:16.733000 +05:30

129606 ORA 603 2021-10-07 16:38:27.237000 +05:30

6 rows fetched

adrci> purge -i 29297

adrci> purge -i 67698

adrci> show alert -p "message_text like '%ORA-%'"

ADR Home = /u01/app/oracle/diag/rdbms/rac/rac1:

*************************************************************************

Output the results to file: /tmp/alert_19058_1400_rac1_1.ado

Additional information: 256

Additional information: 2

adrci>

Alert log in linux

adrci> show alert -p "message_text like '%ORA-%'"

ADR Home = /u01/app/oracle/diag/rdbms/rac/rac1:

*************************************************************************

Output the results to file: /tmp/alert_19058_1400_rac1_1.ado

2021-09-27 18:21:41.381000 +05:30

Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_psp0_4298.trc:

ORA-27300: OS system dependent operation:fork failed with status: 11

ORA-27301: OS failure message: Resource temporarily unavailable

ORA-27302: failure occurred at: skgpspawn5

Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_4415.trc:

ORA-00443: background process "CJQ0" did not start

ORA-450 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:59059:2} */...

2021-10-07 16:35:58.207000 +05:30

Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_psp0_4856.trc:

ORA-27300: OS system dependent operation:fork failed with status: 11

ORA-27301: OS failure message: Resource temporarily unavailable

ORA-27302: failure occurred at: skgpspawn5

2021-10-07 16:35:59.208000 +05:30

Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_5067.trc:

ORA-00443: background process "GTX0" did not start

Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_5067.trc:

ORA-00450: background process 'GTX0' did not start

ORA-00443: background process "GTX0" did not start

Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_5067.trc:

ORA-00450: background process 'GTX0' did not start

ORA-00443: background process "GTX0" did not start

2021-10-07 16:36:00.424000 +05:30

ORA-1092 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:63819:2} */...

opiodr aborting process unknown ospid (5067) as a result of ORA-1092

ORA-1092 : opitsk aborting process

2021-10-07 16:36:22.942000 +05:30

Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_psp0_5952.trc:

ORA-27300: OS system dependent operation:fork failed with status: 11

ORA-27301: OS failure message: Resource temporarily unavailable

ORA-27302: failure occurred at: skgpspawn3

2021-10-07 16:36:27.142000 +05:30

Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_psp0_5952.trc:

ORA-27300: OS system dependent operation:fork failed with status: 11

ORA-27301: OS failure message: Resource temporarily unavailable

ORA-27302: failure occurred at: skgpspawn5

Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_psp0_5952.trc:

ORA-27300: OS system dependent operation:fork failed with status: 11

ORA-27301: OS failure message: Resource temporarily unavailable

ORA-27302: failure occurred at: skgpspawn5

Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_psp0_5952.trc:

ORA-27300: OS system dependent operation:fork failed with status: 11

ORA-27301: OS failure message: Resource temporarily unavailable

"/tmp/alert_19058_1400_rac1_1.ado" 97L, 5774C

window ADRCI Alert :

ADR base = "C:\app\varunyadav"

adrci>

adrci> show alert

Choose the home from which to view the alert log:

1: diag\rdbms\orcl\orcl

2: diag\tnslsnr\2762VARUNY0000L\listener

Q: to quit

Please select option: 1

Output the results to file: C:\Users\VARUNY~1\AppData\Local\Temp\alert_16440_20588_orcl_1.ado

Please select option: 2

Output the results to file: C:\Users\VARUNY~1\AppData\Local\Temp\alert_16440_20588_listener_2.ado

Please select option: q

adrci>