Skip to content
Advertisements

Posts from the ‘Administration’ Category

Trigger for open all pdb

create trigger open_all_pdbs
after startup on database
begin
execute immediate ‘alter pluggable database all open’;
end open_all_pdbs;
/

Advertisements

Drop database link using sys

Step 1:

select * from dba_db_links

Step 2:  create a procedure

Create or replace procedure Drop_DbLink(schemaName varchar2, dbLink varchar2 ) is
plsql varchar2(1000);
cur number;
uid number;
rc number;
begin
select
u.user_id into uid
from dba_users u
where u.username = schemaName;
plsql := ‘drop database link “‘||dbLink||'”‘;
cur := SYS.DBMS_SYS_SQL.open_cursor;
SYS.DBMS_SYS_SQL.parse_as_user(
c => cur,
statement => plsql,
language_flag => DBMS_SQL.native,
userID => uid
);
rc := SYS.DBMS_SYS_SQL.execute(cur);

SYS.DBMS_SYS_SQL.close_cursor(cur);
end;
/

 

Step 3: Drop database link using sys

 

exec Drop_DbLink(‘owner’,’dblinkname’);

ezyconnect error ORA-12504

[premiadb:oracle:oradb]$ sqlplus apps@”ebsuat.xxxx.com:1586/ebsuat”

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 11 11:04:08 2017

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

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Cause :

Syntax issue

Solution :

[premiadb:oracle:oradb]$ sqlplus apps@\”ebsuat.xxxx.com:1586/ebsuat\”

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 11 11:04:30 2017

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

Enter password:

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

SQL>

 

Enable DDL logging

Enable DDL Logging:

ENABLE_DDL_LOGGING enables or disables the writing of a subset of data definition language (DDL) statements to a DDL log.

SQL> show parameter enable_ddl_logging

NAME TYPE VALUE
———————————— ———– ——————————
enable_ddl_logging boolean FALSE

SQL> alter system set enable_ddl_logging=TRUE scope=both;

System altered.

SQL> show parameter enable_ddl_logging

NAME TYPE VALUE
———————————— ———– ——————————
enable_ddl_logging boolean TRUE

SQL> select value from v$diag_info where name=’ADR Home’;

VALUE
——————————————————————————–
/premia/app/12c/oracle/diag/rdbms/oradb/oradb

SQL>

SQL> select value from v$diag_info where name=’ADR Home’;

VALUE
——————————————————————————–
/premia/app/12c/oracle/diag/rdbms/oradb/oradb

SQL> conn test/test123
Connected.
SQL> create table invoicenum(invoice number);

Table created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

[premiadb:oracle:oradb]$ pwd
/premia/app/12c/oracle/diag/rdbms/oradb/oradb/log/ddl

[premiadb:oracle:oradb]$ cat log.xml

<msg time=’2017-02-21T15:25:47.163+03:00′ org_id=’oracle’ comp_id=’rdbms’
msg_id=’opiexe:4383:2946163730′ type=’UNKNOWN’ group=’diag_adl’
level=’16’ host_id=’premiadb’ host_addr=’10.1.4.66′>
<txt>create table invoicenum(invoice number)
</txt>
</msg>

Reference :

https://docs.oracle.com/database/121/REFRN/GUID-6FBA6147-D545-4E7D-94F0-A97EC1C721AE.htm#REFRN10302

 

Listener Exception : Could not create listener: TNS-04414: File error

When I tried to add listner using netca for my test database, I am getting below issue

“Listener Exception : Could not create listener: TNS-04414: File error’

It could be possible ” Listener file having some wrong entries, so you need to check” or renaming current listener and tried to add (Here Please be note, your current listener configuration will be removed).

ORA-23515: materialized views and/or their indices exist in the tablespace

When I am trying to drop the tablespace, I am getting below error

ORA-23515: materialized views and/or their indices exist in the tablespace

Solution :

Step 1: Find the materialized views and/or their indices

SQL> set head off
SQL> set newpage none
SQL> set pagesize 9999
SQL> spool drop_materialized_view.sql

SQL>select ‘drop materialized view ‘||owner||’.’||name||’ PRESERVE TABLE;’ from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name =’XXXXXXX’);

SQL>spool off

Step 2:  Drop the materialized views and/or their indices

SQL>@drop_materialized_view.sql

Step 3: Drop the tablespace

SQL>drop tablespace XXXXXXX including contents and datafiles;

Tablespace dropped.

desc dba_views does not exist in database open stage

While you issued this dba_*  dictionary views on database mount

SQL> alter database mount;

Database altered.

SQL> desc dba_views;
ERROR:
ORA-04043: object dba_views does not exist

After database opened

SQL> alter database open;

Database altered.

SQL> desc dba_views;
ERROR:
ORA-04043: object dba_views does not exist

Don’t issue this command on database mount stage

Restart the database normal

or

SQL> alter system flush shared_pool;

System altered.

SQL> desc dba_views;
Name Null? Type
—————————————– ——– —————————-

OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
EDITIONING_VIEW VARCHAR2(1)
READ_ONLY VARCHAR2(1)

Exception in thread “main” java.lang.InternalError: Can’t connect to X11 window server using ‘xx.x.x.xxx:0.0″

[root@ebsdev ~]# suappltrng
[appltrng@ebsdev ~]$ /u01/EBSTRNG/apps/apps_st/appl/ad/12.0.0/bin/admsi.pl
Invoking Oracle Patch Application Assistant….
Please set the DISPLAY variable and re-run this script

[appltrng@ebsdev ~]$ export DISPLAY=xx.x.x.xxx:0.0
[appltrng@ebsdev ~]$ /u01/EBSTRNG/apps/apps_st/appl/ad/12.0.0/bin/admsi.pl
Invoking Oracle Patch Application Assistant….
Exception in thread “main” java.lang.InternalError: Can’t connect to X11 window server using ‘xx.x.x.x:0.0′ as the value of the DISPLAY variable.
at sun.awt.X11GraphicsEnvironment.initDisplay(Native Method)
at sun.awt.X11GraphicsEnvironment.access$100(X11GraphicsEnvironment.java :52)
at sun.awt.X11GraphicsEnvironment$1.run(X11GraphicsEnvironment.java:155)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.X11GraphicsEnvironment.<clinit>(X11GraphicsEnvironment.java:1 31)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:169)
at java.awt.GraphicsEnvironment.getLocalGraphicsEnvironment(GraphicsEnvi ronment.java:68)
at sun.awt.X11.XToolkit.<clinit>(XToolkit.java:89)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:169)
at java.awt.Toolkit$2.run(Toolkit.java:834)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.Toolkit.getDefaultToolkit(Toolkit.java:826)
at com.jgoodies.looks.LookUtils.isLowResolution(LookUtils.java:436)
at com.jgoodies.looks.LookUtils.<clinit>(LookUtils.java:180)
at com.jgoodies.looks.plastic.PlasticLookAndFeel.<clinit>(PlasticLookAnd Feel.java:122)
at oracle.apps.ad.msi.LaunchUI._useOracleLookAndFeel(LaunchUI.java:84)
at oracle.apps.ad.msi.LaunchUI.<clinit>(LaunchUI.java:30)
Could not find the main class: oracle.apps.ad.msi.LaunchUI. Program will exit.

Solution :

[root@ebsdev ~]# xhost +
access control disabled, clients can connect from any host
[root@ebsdev ~]# su - appltrng
[appltrng@ebsdev ~]$ export DISPLAY=:0

[appltrng@ebsdev ~]$ /u01/EBSTRNG/apps/apps_st/appl/ad/12.0.0/bin/admsi.pl

ORA-19504 ORA-12547 ORA-15001 ORA-15055

If you’re face below issues while migrate single instance to Grid standalone instance, this below OTN forums discussion topics will helps you

 

ORA-19504: failed to create file "+ASM_DATA_DG"
ORA-12547: TNS:lost contact
ORA-15001: diskgroup "ASM_DATA_DG" does not exist or is not mounted
ORA-15055: unable to connect to ASM instance
ORA-12547: TNS:lost contact

https://forums.oracle.com/forums/thread.jspa?threadID=2431846&tstart=0

Checkpoint Tuning and Troubleshooting Guide

The below note id will helpful for you if you want to know how do i do checkpoint tuning.

Checkpoint Tuning and Troubleshooting Guide [ID 147468.1]