Oracle Errors & their solutions.

by rajeshkumarUncategorized
Post Views: 21

Error

SQL> SELECT name FROM v$database;
SELECT name FROM v$database
                 *
ERROR at line 1:
ORA-01507: database not mounted

SQL> select * from global_name
  2  ;
select * from global_name
              *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

SQL> print dbname;
SP2-0552: Bind variable "DBNAME" not declared.

https://www.oratable.com/find-your-database-name-in-oracle/

Solution

Error

QL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-15000: command disallowed by current instance type

Error

SQL> CREATE OR REPLACE DIRECTORY datapump_dir AS '/tmp/rajesh';
CREATE OR REPLACE DIRECTORY datapump_dir AS '/tmp/rajesh'
*
ERROR at line 1:
ORA-01109: database not open

Solution

As the error states - the database is not open - it was previously shut down, and someone left it in the middle of the startup process. They may either be intentional, or unintentional (i.e., it was supposed to be open, but failed to do so).

Assuming that's nothing wrong with the database itself, you could open it with a simple statement:(Since the question is asked specifically in the context of SQLPlus, kindly remember to put a statement terminator(Semicolon) at the end mandatorily, otherwise, it will result in an error.)

ALTER DATABASE OPEN;

Error

QL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-15000: command disallowed by current instance type
\

Error

SQL> SELECT SERVICE_NAME, INSTANCE_NAME, STATUS FROM GV$SERVICENAMES WHERE INSTANCE_NAME='bbdc2';
SELECT SERVICE_NAME, INSTANCE_NAME, STATUS FROM GV$SERVICENAMES WHERE INSTANCE_NAME='bbdc2'
                                                *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

Solution

To resolve this error, you need to first open the database before querying the dynamic performance view. You can do this by connecting to the database using SQL*Plus as a user with SYSDBA privileges, and then executing the ALTER DATABASE OPEN command:

sql
Copy code
sqlplus / as sysdba
ALTER DATABASE OPEN;
Once the database is open, you can then execute the original query to get the service names:

SOME COMMANDS

To check what database we have in this cluster.
$ srvctl config database
$ srvctl status database -d bbdc
$ srvctl status database -d bbdc -n node-name1

Error

[oracle@tcb-oracle1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 3 14:58:58 2023
Version 19.16.0.0.0

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

ERROR:
ORA-12547: TNS:lost contact


Enter user-name: ^C

Error

Version 19.16.0.0.0
[oracle@tcb-oracle1 ~]$ impdp \"/ as sysdba\" DIRECTORY=MY_DIR DUMPFILE=2022Sep27_exp_meta_a4m.dmp LOGFILE=importamlpoc.log

Import: Release 19.0.0.0.0 - Production on Thu May 4 00:56:07 2023
Version 19.16.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39061: import mode FULL conflicts with export mode TRANSPORTABLE

Error


[oracle@tcb-oracle1 rajesh]$ impdp \"/ as sysdba\" DUMPFILE=2022Sep27_exp_meta_a4m.dmp DIRECTORY=MY_DIR  LOGFILE=importamlpoc.log TRANSPORTABLE=ALWAYS

Import: Release 19.0.0.0.0 - Production on Thu May 4 01:38:29 2023
Version 19.16.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39207: Value  ALWAYS is invalid for parameter TRANSPORTABLE.

Error

1 ORA-39112: Dependent object type REF_CONSTRAINT:"BAOBAB"."FK_CFG_DD_TELEBANK_CFG_INACTIV" skipped, base object type TABLE:"BAOBAB"."CFG_DD_TELEBANK" creation failed
      1 ORA-39112: Dependent object type REF_CONSTRAINT:"BAOBAB"."FK_CFG_DD_TELEBANK_CFG_PORT_IN" skipped, base object type TABLE:"BAOBAB"."CFG_DD_TELEBANK" creation failed
      1 ORA-39112: Dependent object type REF_CONSTRAINT:"BAOBAB"."FK_CFG_DD_TELEBANK_CFG_UNIT" skipped, base object type TABLE:"BAOBAB"."CFG_DD_TELEBANK" creation failed
      1 ORA-39112: Dependent object type REF_CONSTRAINT:"BAOBAB"."FK_CFG_DD_TELEBANK_CNS_CHANNEL" skipped, base object type TABLE:"BAOBAB"."CFG_DD_TELEBANK" creation failed
      1 ORA-39112: Dependent object type REF_CONSTRAINT:"BAOBAB"."FK_CFG_DD_TELEBANK_TER_COMMON" skipped, base object type TABLE:"BAOBAB"."CFG_DD_TELEBANK" creation failed
      1 ORA-39112: Dependent object type REF_CONSTRAINT:"BAOBAB"."FK_CFG_DD_VOICE_CFG_INACTIVITY" skipped, base object type TABLE:"BAOBAB"."CFG_DD_VOICE" creation failed
      1 ORA-39112: Dependent object type REF_CONSTRAINT:"BAOBAB"."FK_CFG_DD_VOICE_CFG_PORT" skipped, base object type TABLE:"BAOBAB"."CFG_DD_VOICE" creation failed

Error

oracle@tcb-oracle1 rajesh]$ grep "ORA-" importamlpoc.log | sort | uniq -c | cut -f1 -d "." | sort | uniq -c
      1    1098 ORA-01917: user or role 'BAO_READROLE' does not exist
      1      12 ORA-01917: user or role 'BAO_PADSSROLE' does not exist
      1      16 ORA-01917: user or role 'SRV_TWO_PDMS' does not exist
      1       1 ORA-01435: user does not exist
      1       1 ORA-01917: user or role 'NOTI_TEST' does not exist
      1       1 ORA-31625: Schema TWO_DS1 is needed to import this object, but is unaccessible
    254       1 ORA-39082: Object type TRIGGER:"BAOBAB"
      1       1 ORA-39083: Object type INDEX failed to create with error:
    108       1 ORA-39151: Table "BAOBAB"
      1      25 ORA-01917: user or role 'LOYALTWO' does not exist
      1       2 ORA-01917: user or role 'SRV_IAM_FIMI' does not exist
      1       2 ORA-01917: user or role 'SRV_IAM_TWO' does not exist
      1      30 ORA-01917: user or role 'TWA' does not exist
      1     318 ORA-01917: user or role 'BAO_SYSROLE' does not exist
      1     328 ORA-01917: user or role 'BAO_KEYROLE' does not exist
      1      37 ORA-01917: user or role 'SRV_GRAFANA_MONITOR' does not exist
      1      38 ORA-01917: user or role 'BAOLOGIN1' does not exist
      1      38 ORA-01917: user or role 'BAOLOGIN2' does not exist
      1    4353 ORA-01917: user or role 'BAO_WRITEROLE' does not exist
      1      45 ORA-01917: user or role 'BAO_CMDROLE' does not exist
      1      45 ORA-01917: user or role 'BAOLOGIN' does not exist
      1       5 ORA-01917: user or role 'SRV_TWO_CMREPORT' does not exist
      1    6540 ORA-39083: Object type OBJECT_GRANT failed to create with error:
      1      72 ORA-01917: user or role 'DWHTWO' does not exist
      1      75 ORA-01917: user or role 'TWO_DS1' does not exist
Latest posts by rajeshkumar (see all)
0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x