Thinking Out Loud

September 9, 2016

Bad Coding and 12c Upgrade Will Break

Filed under: 12c,oracle — mdinh @ 4:14 am

With the introduction of CDB, many views have added column CON_ID.

DB1:(SYS@DB1):PRIMARY> select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select * from dba_blockers));
select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select * from dba_blockers))
                                                                              *
ERROR at line 1:
ORA-00913: too many values


DB1:(SYS@DB1):PRIMARY> desc dba_blockers;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 HOLDING_SESSION                                                NUMBER
 CON_ID                                                         NUMBER

DB1:(SYS@DB1):PRIMARY> select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select HOLDING_SESSION from dba_blockers));

INST        SID    SERIAL# PROGRAM                        MACHINE
---- ---------- ---------- ------------------------------ ------------------------------
   1         30      14048 sqlplus@arrow.localdomain (TNS arrow.localdomain
   1         32      25425 sqlplus@arrow.localdomain (TNS arrow.localdomain

DB1:(SYS@DB1):PRIMARY>

August 30, 2016

Simple Cold Backup using tar

Filed under: oracle — mdinh @ 1:50 pm

At times, I find it much simpler to shutdown my test database and create cold backup which will allow me to revert from any errors.

$ echo $ORACLE_SID
DB01

$ tar -cvzf /media/sf_OracleSoftware/$ORACLE_SID_$USER_`hostname -s`_backup.tar.gz .
./
./obj_source.out
./onlinelog/
./onlinelog/o1_mf_1_cw3syboq_.log
./onlinelog/o1_mf_2_cw3sybrt_.log
./onlinelog/o1_mf_3_cw3syc03_.log
./controlfile/
./controlfile/o1_mf_cw3sybl0_.ctl
./datafile/
./datafile/o1_mf_temp_cw3syrhz_.tmp
./datafile/o1_mf_users_cw3syrk7_.dbf
./datafile/o1_mf_sysaux_cw3syl2w_.dbf
./datafile/o1_mf_demo_cw4tz56f_.dbf
./datafile/o1_mf_undotbs1_cw3syoc6_.dbf
./datafile/o1_mf_system_cw3syd5k_.dbf

$ ll /media/sf_OracleSoftware/DB01_oracle_arrow_backup.tar.gz
-rwxrwx---. 1 root vboxsf 382676286 Aug 28 13:09 /media/sf_OracleSoftware/DB01_oracle_arrow_backup.tar.gz

++++++++++

[06:45]oracle@arrow:DB01:/oradata
$ mkdir DB01/
[06:45]oracle@arrow:DB01:/oradata
$ cd DB01/
[06:45]oracle@arrow:DB01:/oradata/DB01
$ ll
total 0
[06:45]oracle@arrow:DB01:/oradata/DB01
$ tar -xvf /media/sf_OracleSoftware/$ORACLE_SID_$USER_`hostname -s`_backup.tar.gz .
./
./obj_source.out
./onlinelog/
./onlinelog/o1_mf_1_cw3syboq_.log
./onlinelog/o1_mf_2_cw3sybrt_.log
./onlinelog/o1_mf_3_cw3syc03_.log
./controlfile/
./controlfile/o1_mf_cw3sybl0_.ctl
./datafile/
./datafile/o1_mf_temp_cw3syrhz_.tmp
./datafile/o1_mf_users_cw3syrk7_.dbf
./datafile/o1_mf_sysaux_cw3syl2w_.dbf
./datafile/o1_mf_demo_cw4tz56f_.dbf
./datafile/o1_mf_undotbs1_cw3syoc6_.dbf
./datafile/o1_mf_system_cw3syd5k_.dbf
[06:46]oracle@arrow:DB01:/oradata/DB01
$ ll
total 28
drwxr-x---. 2 oracle oinstall  4096 Aug 27 12:19 controlfile
drwxr-x---. 2 oracle oinstall  4096 Aug 27 12:19 datafile
-rw-r--r--. 1 oracle oinstall 14506 Aug 27 22:23 obj_source.out
drwxr-x---. 2 oracle oinstall  4096 Aug 27 12:19 onlinelog
[06:46]oracle@arrow:DB01:/oradata/DB01
$

August 28, 2016

ORACLE_MAINTAINED Set From “_ORACLE_SCRIPT” parameter

Filed under: 12c,migration,oracle — mdinh @ 2:26 pm

A huge thank you to Pete Finnigan for his blog post.

Hacking Oracle 12c COMMON Users

Not only is the implementation of ORACLE_MAINTAINED not ideal, it’s a possible security risk.

My tip to you is to always exit the session before performing any object creation.

From my pain as you can see, looks like “_ORACLE_SCRIPT was not reset when new users where created.

Also ORACLE_MAINTAINED Objects Don’t Export

Learned quite a few new thing this weekend and can now lay to rest.

test:(SYS@test):PRIMARY> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

test:(SYS@test):PRIMARY> create user newdemo identified by demo;

User created.

test:(SYS@test):PRIMARY> select username,oracle_maintained from dba_users order by 2;

USERNAME             O
-------------------- -
TEST1                N
MDINH                N
TESTING              N
DEMO                 N
NEWDEMO              N
GSMUSER              Y
AUDSYS               Y
ANONYMOUS            Y
DBSNMP               Y
XDB                  Y
APPQOSSYS            Y
GSMADMIN_INTERNAL    Y
SYSBACKUP            Y
OUTLN                Y
SYSDG                Y
SYSKM                Y
SYSTEM               Y
XS$NULL              Y
OJVMSYS              Y
ORACLE_OCM           Y
DIP                  Y
SYS                  Y
GSMCATUSER           Y

23 rows selected.

test:(SYS@test):PRIMARY> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

test:(SYS@test):PRIMARY> create user ndemo identified by demo;

User created.

test:(SYS@test):PRIMARY> select username,oracle_maintained from dba_users order by 2;

USERNAME             O
-------------------- -
DEMO                 N
MDINH                N
TEST1                N
NEWDEMO              N
TESTING              N
GSMUSER              Y
SYSKM                Y
XS$NULL              Y
OJVMSYS              Y
APPQOSSYS            Y
ORACLE_OCM           Y
XDB                  Y
DBSNMP               Y
SYSDG                Y
DIP                  Y
OUTLN                Y
ANONYMOUS            Y
SYSBACKUP            Y
AUDSYS               Y
NDEMO                Y
GSMADMIN_INTERNAL    Y
GSMCATUSER           Y
SYS                  Y
SYSTEM               Y

24 rows selected.

test:(SYS@test):PRIMARY>

ORACLE_MAINTAINED Objects Don’t Export

Filed under: 12c,migration,oracle — mdinh @ 5:03 am

What a disaster this is with 12c full database export and ORACLE_MAINTAINED objects.

Somehow, oracle thinks MDINH is an ORACLE_MAINTAINED user and user MDINH does not get exported.

———

Why Can an Object Not Be Exported? Expdp of SYSTEM User’s Table Returns ORA-39166 or ORA-31655 (Doc ID 2114233.1)

There are certain system generated schemas that are not exportable using exp or expdp because they contain Oracle-managed data and metadata. SYS, MDSYS, and ORDSYS are some examples.

———

From Blog about  (ORACLE_MAINTAINED Column) DEMO was ORACLE_MAINTAINED user as well.

For testing purposes, I dropped and recreated the user to test export which removed ORACLE_MAINTAINED flag.

Still, this does not explained how MDINH came to be ORACLE_MAINTAINED user.

Has anyone experience this and has a solution or work around?

test:(SYS@test):PRIMARY> select username,oracle_maintained from dba_users order by 1;

USERNAME             O
-------------------- -
ANONYMOUS            Y
APPQOSSYS            Y
AUDSYS               Y
DBSNMP               Y
DEMO                 N
DIP                  Y
GSMADMIN_INTERNAL    Y
GSMCATUSER           Y
GSMUSER              Y
MDINH                Y
OJVMSYS              Y
ORACLE_OCM           Y
OUTLN                Y
SYS                  Y
SYSBACKUP            Y
SYSDG                Y
SYSKM                Y
SYSTEM               Y
TEST1                N
TESTING              N
XDB                  Y
XS$NULL              Y

22 rows selected.

test:(SYS@test):PRIMARY>

++++++++++

$ expdp parfile=expdp_full.par

Export: Release 12.1.0.2.0 - Production on Sat Aug 27 21:38:15 2016

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

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
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** parfile=expdp_full.par
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
     Estimated 1 TABLE_DATA objects in 2 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
     Estimated 16 TABLE_DATA objects in 0 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
     Estimated 9 TABLE_DATA objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
     Estimated 0 TABLE_DATA objects in 1 seconds
Total estimation using BLOCKS method: 2.125 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
     Completed 1 MARKER objects in 0 seconds
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
     Completed 1 MARKER objects in 0 seconds
Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 4 TABLESPACE objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
     Completed 1 USER objects in 0 seconds

*** There are 4 users but only 3 are exported.
Processing object type DATABASE_EXPORT/SCHEMA/USER
     Completed 3 USER objects in 0 seconds

Processing object type DATABASE_EXPORT/RADM_FPTM
     Completed 1 RADM_FPTM objects in 0 seconds
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Completed 6 PROC_SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
     Completed 2 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
     Completed 4 ROLE_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
     Completed 6 DEFAULT_ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
     Completed 7 ON_USER_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
     Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
     Completed 1 TRUSTED_DB_LINK objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Completed 1 SYNONYM objects in 5 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
     Completed 2 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 3 PROCACT_SYSTEM objects in 1 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     Completed 23 PROCOBJ objects in 5 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 3 PROCACT_SYSTEM objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
     Completed 11 PROCACT_SCHEMA objects in 0 seconds
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
     Completed 1 TABLE objects in 5 seconds
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
     Completed 1 MARKER objects in 2 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
     Completed 16 TABLE objects in 16 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
     Completed 9 TABLE objects in 26 seconds
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
     Completed 1 MARKER objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
     Completed  MARKER objects in  seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
     Completed  MARKER objects in  seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
     Completed  MARKER objects in  seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
     Completed  MARKER objects in  seconds
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
     Completed 1 MARKER objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
     Completed 3 PROCACT_SCHEMA objects in 2 seconds
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
     Completed 2 AUDIT_POLICY_ENABLE objects in 1 seconds
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
     Completed 1 MARKER objects in 0 seconds
ORA-31693: Table data object "SYS"."KU$_USER_MAPPING_VIEW" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
. . exported "SYS"."AUD$"                                22.57 KB       0 rows in 0 seconds
. . exported "SYS"."DAM_CLEANUP_EVENTS$"                 7.187 KB       0 rows in 0 seconds
. . exported "SYS"."DAM_CLEANUP_JOBS$"                   7.171 KB       0 rows in 0 seconds
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.531 KB      14 rows in 0 seconds
. . exported "SYS"."TSDP_ASSOCIATION$"                   5.898 KB       0 rows in 0 seconds
. . exported "SYS"."TSDP_CONDITION$"                     5.890 KB       0 rows in 0 seconds
. . exported "SYS"."TSDP_FEATURE_POLICY$"                5.906 KB       0 rows in 0 seconds
. . exported "SYS"."TSDP_PARAMETER$"                     5.953 KB       1 rows in 0 seconds
. . exported "SYS"."TSDP_POLICY$"                        5.921 KB       1 rows in 0 seconds
. . exported "SYS"."TSDP_PROTECTION$"                    6.320 KB       0 rows in 0 seconds
. . exported "SYS"."TSDP_SENSITIVE_DATA$"                8.437 KB       0 rows in 0 seconds
. . exported "SYS"."TSDP_SENSITIVE_TYPE$"                6.320 KB       0 rows in 0 seconds
. . exported "SYS"."TSDP_SOURCE$"                        6.312 KB       0 rows in 0 seconds
. . exported "SYS"."TSDP_SUBPOL$"                        6.328 KB       1 rows in 0 seconds
. . exported "SYSTEM"."REDO_DB"                          25.59 KB       1 rows in 0 seconds
. . exported "SYSTEM"."REDO_LOG"                         26.34 KB       0 rows in 0 seconds
ORA-31693: Table data object "SYS"."FGA_LOG$FOR_EXPORT" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"               8.390 KB       0 rows in 1 seconds
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"           9.523 KB      12 rows in 0 seconds
ORA-31693: Table data object "SYS"."AUDTAB$TBS$FOR_EXPORT" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."DBA_SENSITIVE_DATA" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."DBA_TSDP_POLICY_PROTECTION" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_ACE_EXP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_HOST_EXP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_WALLET_EXP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
     Completed 0 DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 0 seconds
     Completed 16 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 0 seconds
     Completed 2 DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 1 seconds
     Completed 0 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 0 seconds
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /oradata/dpump/expdp_full.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 8 error(s) at Sat Aug 27 21:39:50 2016 elapsed 0 00:01:34

++++++++++

$ impdp parfile=impdp_full.par

Import: Release 12.1.0.2.0 - Production on Sat Aug 27 21:43:12 2016

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

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
Startup took 0 seconds
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_full.par
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
     Completed 1 MARKER objects in 0 seconds
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
     Completed 1 MARKER objects in 1 seconds
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
     Completed 4 TABLESPACE objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
     Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
     Completed 3 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/RADM_FPTM
     Completed 1 RADM_FPTM objects in 0 seconds
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Completed 6 PROC_SYSTEM_GRANT objects in 2 seconds

Looks like MDINH was not exported.

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role 'MDINH' does not exist
Failing sql is:
GRANT CREATE SYNONYM TO "MDINH"
     Completed 2 SYSTEM_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'MDINH' does not exist
Failing sql is:
 GRANT "RESOURCE" TO "MDINH"
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'MDINH' does not exist
Failing sql is:
 GRANT "CONNECT" TO "MDINH"
     Completed 4 ROLE_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
ORA-39083: Object type DEFAULT_ROLE:"MDINH" failed to create with error:
ORA-01918: user 'MDINH' does not exist
Failing sql is:
 ALTER USER "MDINH" DEFAULT ROLE ALL
     Completed 6 DEFAULT_ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
ORA-39083: Object type ON_USER_GRANT failed to create with error:
ORA-31625: Schema MDINH is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
 GRANT INHERIT PRIVILEGES ON USER "MDINH" TO "PUBLIC"
     Completed 7 ON_USER_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
     Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
     Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
ORA-31684: Object type SYNONYM:"PUBLIC"."MORE_RECS_TBL" already exists
     Completed 1 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
ORA-39083: Object type SYNONYM:"MDINH"."TCUSTMER" failed to create with error:
ORA-01917: user or role '' does not exist
Failing sql is:
CREATE EDITIONABLE SYNONYM "MDINH"."TCUSTMER" FOR "DEMO"."TCUSTMER"
ORA-39083: Object type SYNONYM:"MDINH"."TCUSTORD" failed to create with error:
ORA-01917: user or role '' does not exist
Failing sql is:
CREATE EDITIONABLE SYNONYM "MDINH"."TCUSTORD" FOR "DEMO"."TCUSTMER"
     Completed 2 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
>>> Cannot set an SCN larger than the current SCN. If a Streams Capture configuration was imported then the Apply that processes the captured messages needs to be dropped and recreated. See My Oracle Support article number 1380295.1.
     Completed 3 PROCACT_SYSTEM objects in 1 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     Completed 23 PROCOBJ objects in 1 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 3 PROCACT_SYSTEM objects in 25 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema MDINH is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'CI2', inst_scn=>'806486');COMMIT; END;
     Completed 11 PROCACT_SCHEMA objects in 1 seconds
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
     Completed 1 TABLE objects in 0 seconds
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
     Completed 1 MARKER objects in 0 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
     Completed 16 TABLE objects in 1 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYS"."AMGT$DP$AUD$"                        22.57 KB       0 rows in 0 seconds
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$"         7.187 KB       0 rows in 0 seconds
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$"           7.171 KB       0 rows in 0 seconds
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$"           6.531 KB      14 rows in 0 seconds
. . imported "SYS"."DP$TSDP_ASSOCIATION$"                5.898 KB       0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_CONDITION$"                  5.890 KB       0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_FEATURE_POLICY$"             5.906 KB       0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_PARAMETER$"                  5.953 KB       1 rows in 0 seconds
. . imported "SYS"."DP$TSDP_POLICY$"                     5.921 KB       1 rows in 0 seconds
. . imported "SYS"."DP$TSDP_PROTECTION$"                 6.320 KB       0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_SENSITIVE_DATA$"             8.437 KB       0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$"             6.320 KB       0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_SOURCE$"                     6.312 KB       0 rows in 0 seconds
. . imported "SYS"."DP$TSDP_SUBPOL$"                     6.328 KB       1 rows in 0 seconds
. . imported "SYSTEM"."REDO_DB_TMP"                      25.59 KB       1 rows in 0 seconds
. . imported "SYSTEM"."REDO_LOG_TMP"                     26.34 KB       0 rows in 0 seconds
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
ORA-39342: Internal error - failed to import internal objects tagged with SCHEDULER due to ORA-00910: specified length too long for its datatype.
ORA-39083: Object type TABLE:"SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" failed to create with error:
ORA-00910: specified length too long for its datatype
Failing sql is:
CREATE TABLE "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" ("OWNER" VARCHAR2(128 BYTE) NOT NULL ENABLE, "PROGRAM_NAME" VARCHAR2(128 BYTE) NOT NULL ENABLE, "ARGUMENT_NAME" VARCHAR2(128 BYTE), "ARGUMENT_POSITION" NUMBER NOT NULL ENABLE, "ARGUMENT_TYPE" VARCHAR2(257 BYTE), "METADATA_ATTRIBUTE" VARCHAR2(19 BYTE), "DEFAULT_VALUE" VARCHAR2(32767 BYTE), "DEFA
ORA-39083: Object type TABLE:"SYSTEM"."SCHEDULER_JOB_ARGS_TMP" failed to create with error:
ORA-00910: specified length too long for its datatype
Failing sql is:
CREATE TABLE "SYSTEM"."SCHEDULER_JOB_ARGS_TMP" ("OWNER" VARCHAR2(128 BYTE), "JOB_NAME" VARCHAR2(128 BYTE), "ARGUMENT_NAME" VARCHAR2(128 BYTE), "ARGUMENT_POSITION" NUMBER, "ARGUMENT_TYPE" VARCHAR2(257 BYTE), "VALUE" VARCHAR2(32767 BYTE), "ANYDATA_VALUE" "SYS"."ANYDATA" , "OUT_ARGUMENT" VARCHAR2(5 BYTE)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     Completed 9 TABLE objects in 0 seconds
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
     Completed 1 MARKER objects in 1 seconds
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
ORA-39342: Internal error - failed to import internal objects tagged with AUDIT_TRAILS due to ORA-01403: no data found
ORA-01403: no data found.
     Completed 1 MARKER objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
     Completed 3 PROCACT_SCHEMA objects in 0 seconds
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
     Completed 2 AUDIT_POLICY_ENABLE objects in 1 seconds
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
     Completed 1 MARKER objects in 0 seconds
     Completed 16 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 1 seconds
Job "SYS"."SYS_IMPORT_FULL_01" completed with 16 error(s) at Sat Aug 27 21:43:55 2016 elapsed 0 00:00:41

August 27, 2016

To CDB or NOT To CDB (ORACLE_MAINTAINED Column)

Filed under: 12c,migration,oracle — mdinh @ 2:26 pm

I recently discovered about column ORACLE_MAINTAINED added to DBA views and got excited over nothing.

ORACLE_MAINTAINED from DBA_ROLES
Denotes whether the role was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql).
A role for which this column has the value Y must not be changed in any way except by running an Oracle-supplied script.

STOP HERE:

Please read post ORACLE_MAINTAINED Set From “_ORACLE_SCRIPT” parameter to the real reasons (I must have messed up somewhere).

Updated: the statements below are not correct.

It looks like ORACLE_MAINTAINED is only valid for CDB and seems as if Oracle is really wanting all databases to go to CDB.

For Non-CDB, ORACLE_MAINTAINED value is ‘Y’ even when not.

test:(MDINH@test):PRIMARY> select name,cdb from v$database;

NAME                           CDB
------------------------------ ---
test                            NO

1 row selected.

test:(MDINH@test):PRIMARY> select username,oracle_maintained from dba_users order by 1;

USERNAME             O
-------------------- -
ANONYMOUS            Y
APPQOSSYS            Y
AUDSYS               Y
DBSNMP               Y
DEMO                 Y
DIP                  Y
GSMADMIN_INTERNAL    Y
GSMCATUSER           Y
GSMUSER              Y
MDINH                Y
OJVMSYS              Y
ORACLE_OCM           Y
OUTLN                Y
SYS                  Y
SYSBACKUP            Y
SYSDG                Y
SYSKM                Y
SYSTEM               Y
XDB                  Y
XS$NULL              Y

20 rows selected.

test:(MDINH@test):PRIMARY> select role,oracle_maintained from dba_roles order by 1;

ROLE                           O
------------------------------ -
ADM_PARALLEL_EXECUTE_TASK      Y
AQ_ADMINISTRATOR_ROLE          Y
AQ_USER_ROLE                   Y
AUDIT_ADMIN                    Y
AUDIT_VIEWER                   Y
AUTHENTICATEDUSER              Y
CAPTURE_ADMIN                  Y
CDB_DBA                        Y
CONNECT                        Y
DATAPUMP_EXP_FULL_DATABASE     Y
DATAPUMP_IMP_FULL_DATABASE     Y
DBA                            Y
DBFS_ROLE                      Y
DELETE_CATALOG_ROLE            Y
EJBCLIENT                      Y
EM_EXPRESS_ALL                 Y
EM_EXPRESS_BASIC               Y
EXECUTE_CATALOG_ROLE           Y
EXP_FULL_DATABASE              Y
GATHER_SYSTEM_STATISTICS       Y
GDS_CATALOG_SELECT             Y
GLOBAL_AQ_USER_ROLE            Y
GSMADMIN_ROLE                  Y
GSMUSER_ROLE                   Y
GSM_POOLADMIN_ROLE             Y
HS_ADMIN_EXECUTE_ROLE          Y
HS_ADMIN_ROLE                  Y
HS_ADMIN_SELECT_ROLE           Y
IMP_FULL_DATABASE              Y
JAVADEBUGPRIV                  Y
JAVAIDPRIV                     Y
JAVASYSPRIV                    Y
JAVAUSERPRIV                   Y
JAVA_ADMIN                     Y
JAVA_DEPLOY                    Y
JMXSERVER                      Y
LOGSTDBY_ADMINISTRATOR         Y
OEM_ADVISOR                    Y
OEM_MONITOR                    Y
OPTIMIZER_PROCESSING_RATE      Y
PDB_DBA                        Y
PROVISIONER                    Y
RECOVERY_CATALOG_OWNER         Y
RECOVERY_CATALOG_USER          Y
RESOURCE                       Y
SCHEDULER_ADMIN                Y
SELECT_CATALOG_ROLE            Y
TEST                           Y
XDBADMIN                       Y
XDB_SET_INVOKER                Y
XDB_WEBSERVICES                Y
XDB_WEBSERVICES_OVER_HTTP      Y
XDB_WEBSERVICES_WITH_PUBLIC    Y
XS_CACHE_ADMIN                 Y
XS_NAMESPACE_ADMIN             Y
XS_RESOURCE                    Y
XS_SESSION_ADMIN               Y

57 rows selected.

test:(MDINH@test):PRIMARY>

++++++++++

SYS@tmnt> select username,oracle_maintained from dba_users order by 1;

USERNAME             O
-------------------- -
ANONYMOUS            Y
APPQOSSYS            Y
AUDSYS               Y
C##GGS_ADMIN         N
C##TESTING           N
DBSNMP               Y
DIP                  Y
GSMADMIN_INTERNAL    Y
GSMCATUSER           Y
GSMUSER              Y
ORACLE_OCM           Y
OUTLN                Y
SYS                  Y
SYSBACKUP            Y
SYSDG                Y
SYSKM                Y
SYSTEM               Y
XDB                  Y
XS$NULL              Y

19 rows selected.

SYS@tmnt> create role test;
create role test
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SYS@tmnt> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
         4 DONNIE                         MOUNTED
SYS@tmnt> alter session set container=april;

Session altered.

SYS@tmnt> create role test;

Role created.

SYS@tmnt> select role,oracle_maintained from dba_roles order by 1;

ROLE                           O
------------------------------ -
ADM_PARALLEL_EXECUTE_TASK      Y
AQ_ADMINISTRATOR_ROLE          Y
AQ_USER_ROLE                   Y
AUDIT_ADMIN                    Y
AUDIT_VIEWER                   Y
AUTHENTICATEDUSER              Y
CAPTURE_ADMIN                  Y
CDB_DBA                        Y
CONNECT                        Y
DATAPUMP_EXP_FULL_DATABASE     Y
DATAPUMP_IMP_FULL_DATABASE     Y
DBA                            Y
DBFS_ROLE                      Y
DELETE_CATALOG_ROLE            Y
EM_EXPRESS_ALL                 Y
EM_EXPRESS_BASIC               Y
EXECUTE_CATALOG_ROLE           Y
EXP_FULL_DATABASE              Y
GATHER_SYSTEM_STATISTICS       Y
GDS_CATALOG_SELECT             Y
GLOBAL_AQ_USER_ROLE            Y
GSMADMIN_ROLE                  Y
GSMUSER_ROLE                   Y
GSM_POOLADMIN_ROLE             Y
HS_ADMIN_EXECUTE_ROLE          Y
HS_ADMIN_ROLE                  Y
HS_ADMIN_SELECT_ROLE           Y
IMP_FULL_DATABASE              Y
LOGSTDBY_ADMINISTRATOR         Y
OEM_ADVISOR                    Y
OEM_MONITOR                    Y
OPTIMIZER_PROCESSING_RATE      Y
PDB_DBA                        Y
PROVISIONER                    Y
RECOVERY_CATALOG_OWNER         Y
RECOVERY_CATALOG_USER          Y
RESOURCE                       Y
SCHEDULER_ADMIN                Y
SELECT_CATALOG_ROLE            Y
TEST                           N
XDBADMIN                       Y
XDB_SET_INVOKER                Y
XDB_WEBSERVICES                Y
XDB_WEBSERVICES_OVER_HTTP      Y
XDB_WEBSERVICES_WITH_PUBLIC    Y
XS_CACHE_ADMIN                 Y
XS_NAMESPACE_ADMIN             Y
XS_RESOURCE                    Y
XS_SESSION_ADMIN               Y

49 rows selected.

SYS@tmnt>

August 24, 2016

Columns Affected by Extended Data Type

Filed under: 12c,migration,oracle — mdinh @ 1:02 pm

I am not going to post how to convert to extended data type since there are many blogs on that already.

Just a reminder, there’s no going back; hence have backup and possibly minimize changes during testing to be able to restore (which is ideal and may not be feasible).

Before reverting to MAX_STRING_SIZE=STANDARD, columns affected by extended data type need to be identified.

From Oracle documentation, MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL.

STANDARD means that the length limits for Oracle Database releases prior to Oracle Database 12c apply
(for example, 4000 bytes for VARCHAR2 and NVARCHAR2, and 2000 bytes for RAW).

EXTENDED means that the 32767 byte limit introduced in Oracle Database 12c applies.

Test case:

[05:41]oracle@arrow:test:/media/sf_working/sql
$ sysdba @max_string_size.sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 24 05:41:22 2016

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


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


OWNER                TABLE_NAME                               COLUMN_NAME               DATA_TYPE  DATA_LENGTH CHAR_LENGTH C
-------------------- ---------------------------------------- ------------------------- ---------- ----------- ----------- -
MDINH                T                                        NAME                      VARCHAR2          5000        5000 B
MDINH                T2                                       T2                        RAW               2555           0
MDINH                T3                                       ID                        VARCHAR2         24000        6000 C
SYS                  DBA_ADDM_FINDINGS                        FINDING_NAME              VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_FINDINGS                        IMPACT_TYPE               VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_FINDINGS                        MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_FINDINGS                        MORE_INFO                 VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_TASKS                           ERROR_MESSAGE             VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_TASKS                           STATUS_MESSAGE            VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_TASK_DIRECTIVES                 DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_ACTIONS                      MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_DEF_PARAMETERS               DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_EXECUTIONS                   ERROR_MESSAGE             VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_EXECUTIONS                   STATUS_MESSAGE            VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_EXEC_PARAMETERS              DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     FINDING_NAME              VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     IMPACT_TYPE               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     MORE_INFO                 VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDING_NAMES                FINDING_NAME              VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_PARAMETERS                   DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_RATIONALE                    IMPACT_TYPE               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_RATIONALE                    MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_RECOMMENDATIONS              BENEFIT_TYPE              VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_TASKS                        ERROR_MESSAGE             VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_TASKS                        STATUS_MESSAGE            VARCHAR2         32767       32767 B
SYS                  DBA_REGISTRY                             OTHER_SCHEMAS             VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_CHAIN_RULES                ACTION                    VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_CHAIN_RULES                CONDITION                 VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_JOBS                       PROGRAM_NAME              VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOBS                       PROGRAM_OWNER             VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOBS                       RAISE_EVENTS              VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_JOBS                       SCHEDULE_NAME             VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOBS                       SCHEDULE_OWNER            VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOB_RUN_DETAILS            ERRORS                    VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_JOB_RUN_DETAILS            OUTPUT                    VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_WINDOWS                    SCHEDULE_NAME             VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_WINDOWS                    SCHEDULE_OWNER            VARCHAR2         16000       16000 B
SYS                  DBA_VIEWS                                TEXT_VC                   VARCHAR2         32767       32767 B
SYS                  INT$DBA_VIEWS                            TEXT_VC                   VARCHAR2         32767       32767 B

40 rows selected.

test:(SYS@test):PRIMARY> show parameter max_string

NAME                                 TYPE                           VALUE
------------------------------------ ------------------------------ ------------------------------
max_string_size                      string                         EXTENDED

test:(SYS@test):PRIMARY> desc mdinh.t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             VARCHAR2(1000)
 NAME                                                           VARCHAR2(5000)

test:(SYS@test):PRIMARY> desc mdinh.t2
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 T2                                                             RAW(2555)

test:(SYS@test):PRIMARY> desc mdinh.t3
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             VARCHAR2(6000 CHAR)

test:(SYS@test):PRIMARY> @nls.sql

PARAMETER                      SESSION                        DATABASE                       INSTANCE
------------------------------ ------------------------------ ------------------------------ ------------------------------
NLS_COMP                       BINARY                         BINARY                         BINARY
NLS_SORT                       BINARY                         BINARY
NLS_CALENDAR                   GREGORIAN                      GREGORIAN
NLS_CURRENCY                   $                              $
NLS_LANGUAGE                   AMERICAN                       AMERICAN                       AMERICAN
NLS_TERRITORY                  AMERICA                        AMERICA                        AMERICA
NLS_DATE_FORMAT                YYYY-MM-DD HH24:MI:SS          DD-MON-RR
NLS_TIME_FORMAT                HH.MI.SSXFF AM                 HH.MI.SSXFF AM
NLS_CHARACTERSET                                              AL32UTF8
NLS_ISO_CURRENCY               AMERICA                        AMERICA
NLS_DATE_LANGUAGE              AMERICAN                       AMERICAN
NLS_DUAL_CURRENCY              $                              $
NLS_RDBMS_VERSION                                             12.1.0.2.0
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR             HH.MI.SSXFF AM TZR
NLS_NCHAR_CONV_EXCP            FALSE                          FALSE                          FALSE
NLS_LENGTH_SEMANTICS           CHAR                           BYTE                           BYTE
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM       DD-MON-RR HH.MI.SSXFF AM
NLS_NCHAR_CHARACTERSET                                        AL16UTF16
NLS_NUMERIC_CHARACTERS         .,                             .,
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR   DD-MON-RR HH.MI.SSXFF AM TZR

20 rows selected.

test:(SYS@test):PRIMARY>

Now it’s evident as to why there is no going back since SYS objects seem to be modified too.

That’s the easy part. Next is to create the database with identical components installed and hopefully full export/import will work.

Some useful information if you are thinking about migrating to extended data type.
12c Indexing Extended Data Types Part I (A Big Hurt)

12c Indexing Extended Data Types Part II (15 Steps)

August 19, 2016

rlwrap – there’s a rpm for that

Filed under: linux — mdinh @ 4:09 am

Recently, there has been discussion about using rlwrap for Goldengate on Twitter feed.

Truthfully, I did not know there was RPM for this and I did not even know it was already installed.

It just worked, when I had set it up.

A very nice cheat sheet for using yum.

Yum Command Cheat Sheet for Red Hat Enterprise Linux

I started to investigate how was rlwrap installed on the system.

OS Version.

$ cat /etc/issue
Oracle Linux Server release 6.6
Kernel \r on an \m

$ cat /etc/oracle-release
Oracle Linux Server release 6.6

Where’s rlwrap?

$ which rlwrap
/usr/bin/rlwrap

What’s rlwrap RPM?

$ yum provides rlwrap
Loaded plugins: refresh-packagekit, security
rlwrap-0.42-1.el6.x86_64 : Wrapper for GNU readline
Repo        : epel
Matched from:

rlwrap-0.42-1.el6.x86_64 : Wrapper for GNU readline
Repo        : installed
Matched from:
Other       : Provides-match: rlwrap

What’s rlwrap dependencies?

$ yum deplist rlwrap
Loaded plugins: refresh-packagekit, security
Finding dependencies:
package: rlwrap.x86_64 0.42-1.el6
...
Output omitted from brevity

Configure alias using rlwrap.

$ alias sqlplus
alias sqlplus='rlwrap sqlplus'

$ alias ggsci
alias ggsci='rlwrap ./ggsci'

Using rpm commands.

$ rpm -q --whatprovides rlwrap
rlwrap-0.42-1.el6.x86_64

$ rpm -q --requires rlwrap
/usr/bin/env
libc.so.6()(64bit)
libc.so.6(GLIBC_2.11)(64bit)
libc.so.6(GLIBC_2.2.5)(64bit)
libc.so.6(GLIBC_2.3)(64bit)
libc.so.6(GLIBC_2.3.4)(64bit)
libc.so.6(GLIBC_2.4)(64bit)
libreadline.so.6()(64bit)
libtinfo.so.5()(64bit)
libutil.so.1()(64bit)
libutil.so.1(GLIBC_2.2.5)(64bit)
perl >= 0:5.006
perl(AutoLoader)
perl(Carp)
perl(Config)
perl(Data::Dumper)
perl(Exporter)
perl(Getopt::Std)
perl(POSIX)
perl(RlwrapFilter)
perl(constant)
perl(lib)
perl(strict)
perl(vars)
rpmlib(CompressedFileNames) <= 3.0.4-1
rpmlib(FileDigests) <= 4.6.0-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1
rpmlib(VersionedDependencies) <= 3.0.3-1
rtld(GNU_HASH)
rpmlib(PayloadIsXz) <= 5.2-1

August 18, 2016

Configuring Multiple local_listener

Filed under: 11g,oracle — mdinh @ 5:16 am

I was working on configuring multiple local listeners and having difficulties setting local_listener using full address list.

How would you set up local_listeners?

Demo:

There are 4 listeners, 1-4.

$ lsnrctl status listener1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:34:31

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:08
Uptime                    0 days 0 hr. 0 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1551)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener2

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:34:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1552)))
STATUS of the LISTENER
------------------------
Alias                     listener2
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:10
Uptime                    0 days 0 hr. 0 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1552)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1552)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener3

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:34:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1553)))
STATUS of the LISTENER
------------------------
Alias                     listener3
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:13
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1553)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1553)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener4

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:34:36

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1554)))
STATUS of the LISTENER
------------------------
Alias                     listener4
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:15
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener4/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1554)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1554)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Modify database LOCAL_LISTENER parameter.

alter system set LOCAL_LISTENER="(ADDRESS_LIST=
)";
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1551))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1552))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1553))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1554))
  6  )";
alter system set LOCAL_LISTENER="(ADDRESS_LIST=
                                *
ERROR at line 1:
ORA-00972: identifier is too long


alter system set LOCAL_LISTENER="(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1551))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1552))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1553))
  5  )";
alter system set LOCAL_LISTENER="(ADDRESS_LIST=
                                *
ERROR at line 1:
ORA-00972: identifier is too long


alter system set LOCAL_LISTENER="(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1551))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1552))
  4  )";

System altered.

ARROW:(SYS@leo):PRIMARY> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS_LIST=
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1551))
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1552))
                                                 )
ARROW:(SYS@leo):PRIMARY>

Using single quote works.

ARROW:(SYS@leo):PRIMARY>
alter system set LOCAL_LISTENER='(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1551))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1552))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1553))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1554)))'
  6  ;

System altered.

ARROW:(SYS@leo):PRIMARY> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS_LIST=
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1551))
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1552))
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1553))
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1554)))
ARROW:(SYS@leo):PRIMARY>

Interesting.

$ oerr ora 972
00972, 00000, "identifier is too long"
// *Cause:  An identifier with more than 30 characters was specified.
// *Action:  Specify at most 30 characters.

Configure tnsnames for listeners

$ cat tnsnames.ora
leo =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1531))
    )
    (CONNECT_DATA =
      (SID = leo)
    )
  )

LISTENER_1551_1554 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1551))
      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1552))
      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1553))
      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1554))
    )
  )

$ tnsping LISTENER_1551_1554

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:48:43

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1551)) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1552)) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1553)) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1554))))
OK (0 msec)

Modify database LOCAL_LISTENER parameter using tnsnames and register listener.

ARROW:(SYS@leo):PRIMARY> alter system set LOCAL_LISTENER="LISTENER_1551_1554";

System altered.

ARROW:(SYS@leo):PRIMARY> alter system register;

System altered.

ARROW:(SYS@leo):PRIMARY> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_1551_1554
ARROW:(SYS@leo):PRIMARY>

Service is now ready.

$ lsnrctl status listener1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:51:26

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:08
Uptime                    0 days 0 hr. 17 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1551)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
Service "leo_a" has 1 instance(s).
  Instance "leo01", status READY, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener2

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:51:29

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1552)))
STATUS of the LISTENER
------------------------
Alias                     listener2
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:10
Uptime                    0 days 0 hr. 17 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1552)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1552)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
Service "leo_a" has 1 instance(s).
  Instance "leo01", status READY, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener3

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:51:31

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1553)))
STATUS of the LISTENER
------------------------
Alias                     listener3
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:13
Uptime                    0 days 0 hr. 17 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1553)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1553)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
Service "leo_a" has 1 instance(s).
  Instance "leo01", status READY, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener4

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:51:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1554)))
STATUS of the LISTENER
------------------------
Alias                     listener4
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:15
Uptime                    0 days 0 hr. 17 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener4/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1554)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1554)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
Service "leo_a" has 1 instance(s).
  Instance "leo01", status READY, has 1 handler(s) for this service...
The command completed successfully

August 17, 2016

Quick note on using nc (netcat)

Filed under: linux — mdinh @ 12:43 pm

Due to security hardening, telnet is not available.

$ rpm -q --whatprovides nc
nc-1.84-24.el6.x86_64

$ yum list nc
Loaded plugins: refresh-packagekit, security
epel/metalink                                                                                                           |  11 kB     00:00
epel                                                                                                                    | 4.3 kB     00:00
epel/primary_db                                                                                                         | 5.9 MB     00:03
public_ol6_UEKR3_latest                                                                                                 | 1.2 kB     00:00
public_ol6_latest                                                                                                       | 1.4 kB     00:00
Installed Packages
nc.x86_64                                                    1.84-24.el6                                                     @public_ol6_latest

$ nc -v -z -w 3 stackoverflow.com 80; echo $?
Connection to stackoverflow.com 80 port [tcp/http] succeeded!
0

August 6, 2016

Note to self for blocking locks

Filed under: oracle — mdinh @ 10:20 am

Session 1 starts UPDATE and nothing else.

++++++++++
Session 1:
02:53:39 ARROW:(MDINH@leo):PRIMARY> update t set object_id=100;

1 row updated.

02:53:45 ARROW:(MDINH@leo):PRIMARY>
++++++++++
Session 2:
02:53:50 ARROW:(SYSTEM@leo):PRIMARY> update mdinh.t set object_id=2;
++++++++++
Session 3:
02:53:58 ARROW:(DEMO@leo):PRIMARY> update mdinh.t set object_id=200;
++++++++++

Monitor blocking locks

$ sysdba @b.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 6 02:55:03 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options


STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           22,1947                        57 SQL*Net message from INACTIVE    1 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           27,487       0sst1nnb8vw49     45 enq: TX - row lock c ACTIVE      1 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *SYSTEM         35,795       dh603ks5ggumy     45 enq: TX - row lock c ACTIVE      1 arrow.localdomain    oracle     sqlplus@arrow.l


STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           6827jhnufmcfx update t set object_id=100
WAITING    *DEMO           0sst1nnb8vw49 update mdinh.t set object_id=200
WAITING    *SYSTEM         dh603ks5ggumy update mdinh.t set object_id=2

ARROW:(SYS@leo):PRIMARY> select sql_id, prev_sql_id from v$session where sid=22;

SQL_ID        PREV_SQL_ID
------------- -------------
              6827jhnufmcfx

ARROW:(SYS@leo):PRIMARY>

Blocking session is INACTIVE and the UPDATE SQL is available.

.

Session 1 execute SELECT following UPDATE.

++++++++++
Session 1:
02:56:16 ARROW:(MDINH@leo):PRIMARY> select sysdate from dual;

SYSDATE
-------------------
2016-08-06 02:56:23

02:56:23 ARROW:(MDINH@leo):PRIMARY>
++++++++++

Monitor blocking locks

ARROW:(SYS@leo):PRIMARY> @b

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           22,1947      7h35uxf5uhmm1     61 SQL*Net message from INACTIVE    0 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           27,487       0sst1nnb8vw49     45 enq: TX - row lock c ACTIVE      3 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *SYSTEM         35,795       dh603ks5ggumy     45 enq: TX - row lock c ACTIVE      3 arrow.localdomain    oracle     sqlplus@arrow.l


STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           7h35uxf5uhmm1 select sysdate from dual
WAITING    *DEMO           0sst1nnb8vw49 update mdinh.t set object_id=200
WAITING    *SYSTEM         dh603ks5ggumy update mdinh.t set object_id=2

ARROW:(SYS@leo):PRIMARY> select sql_id, prev_sql_id from v$session where sid=22;

SQL_ID        PREV_SQL_ID
------------- -------------
7h35uxf5uhmm1 7h35uxf5uhmm1

ARROW:(SYS@leo):PRIMARY>

Note: SQL_ID=PREV_SQL_ID and SQL is not the blocking SQL.

ARROW:(SYS@leo):PRIMARY> !cat b.sql
col username for a15 trunc
col state for a10 trunc
col osuser for a10 trunc
col program for a15 trunc
col sid_serial for a12 trunc
col event for a20 trunc
col machine for a20 trunc
col sid for 999999
col wait_min for 999
col sql_text for a100 trunc
col seq# for 99999
col min for 999
col sql_text for a80 trunc
set lines 200 pages 10000 tab off trimspool off
SELECT
decode(level,1,'BLOCKING','WAITING') state,
LPAD('*',(level-1)*1,' ') || NVL(s.username,'(oracle)') AS username,
s.sid||','||s.serial# sid_serial,
sql_id,
s.seq#,
s.event,
s.status,
round(s.last_call_et/60) min,
s.machine,
s.osuser,
s.program
FROM   v$session s
WHERE  level > 1
OR     EXISTS (SELECT null FROM v$session WHERE blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL
order by 1,2
;
---
with s as (
SELECT
decode(level,1,'BLOCKING','WAITING') state,
LPAD('*',(level-1)*1,' ') || NVL(s.username,'(oracle)') AS username,
decode(status,'INACTIVE',prev_sql_id,sql_id) sql_id
FROM   v$session s
WHERE  level > 1
OR     EXISTS (SELECT null FROM v$session WHERE blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL
)
SELECT s.state, s.username, s.sql_id, sql_text
FROM v$sqlarea a, s
WHERE a.sql_id=s.sql_id
order by 1,2
;

ARROW:(SYS@leo):PRIMARY>

Nice Script from Jeffrey M. Hunter

http://www.idevelopment.info/data/Oracle/DBA_scripts/Locks/locks_blocking.sql

$ sysdba @locks_blocking.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 6 03:12:55 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options


+------------------------------------------------------------------------+
| Report   : Blocking Locks                                              |
| Instance : leo                                                         |
+------------------------------------------------------------------------+


+------------------------------------------------------------------------+
| BLOCKING LOCKS (Summary)                                               |
+------------------------------------------------------------------------+

Number of blocking lock incidents: 2

Incident 1
---------------------------------------------------------------------------------------------------------
                        WAITING                                  BLOCKING
                        ---------------------------------------- ----------------------------------------
Instance Name         : leo                                      leo
Oracle SID            : 27                                       22
Serial#               : 487                                      1947
Oracle User           : DEMO                                     MDINH
O/S User              : oracle                                   oracle
Machine               : arrow.localdomain                        arrow.localdomain
O/S PID               : 20525                                    20521
Terminal              : pts/3                                    pts/2
Lock Time             : 19 minutes                               19 minutes
Status                : ACTIVE                                   INACTIVE
Program               : sqlplus@arrow.localdomain (TNS V1-V3)    sqlplus@arrow.localdomain (TNS V1-V3)
Waiter Lock Type      : Transaction
Waiter Mode Request   : Exclusive
Waiting SQL           : update mdinh.t set object_id=200

Incident 2
---------------------------------------------------------------------------------------------------------
                        WAITING                                  BLOCKING
                        ---------------------------------------- ----------------------------------------
Instance Name         : leo                                      leo
Oracle SID            : 35                                       22
Serial#               : 795                                      1947
Oracle User           : SYSTEM                                   MDINH
O/S User              : oracle                                   oracle
Machine               : arrow.localdomain                        arrow.localdomain
O/S PID               : 20706                                    20521
Terminal              : pts/7                                    pts/2
Lock Time             : 19 minutes                               19 minutes
Status                : ACTIVE                                   INACTIVE
Program               : sqlplus@arrow.localdomain (TNS V1-V3)    sqlplus@arrow.localdomain (TNS V1-V3)
Waiter Lock Type      : Transaction
Waiter Mode Request   : Exclusive
Waiting SQL           : update mdinh.t set object_id=2


+------------------------------------------------------------------------+
| LOCKED OBJECTS                                                         |
+------------------------------------------------------------------------+

Instance  SID / Serial#   Status    Locking Oracle User  Object Owner    Object Name               Object Type     Locked Mode
--------- --------------- --------- -------------------- --------------- ------------------------- --------------- -------------------------
leo       22 / 1947       INACTIVE  MDINH                MDINH           T                         TABLE           Row-Exclusive (SX)
leo       27 / 487        ACTIVE    DEMO                 MDINH           T                         TABLE           Row-Exclusive (SX)
leo       35 / 795        ACTIVE    SYSTEM               MDINH           T                         TABLE           Row-Exclusive (SX)

ARROW:(SYS@leo):PRIMARY>
Next Page »

Create a free website or blog at WordPress.com.