ORA-04043: object x$bh does not exist


SQL> conn rajat/rajat;
Connected.
SQL> desc v$bh;
Name Null? Type


FILE# NUMBER
BLOCK# NUMBER
CLASS# NUMBER
STATUS VARCHAR2(10)
XNC NUMBER
FORCED_READS NUMBER
FORCED_WRITES NUMBER
LOCK_ELEMENT_ADDR RAW(8)
LOCK_ELEMENT_NAME NUMBER
LOCK_ELEMENT_CLASS NUMBER
DIRTY VARCHAR2(1)
TEMP VARCHAR2(1)
PING VARCHAR2(1)
STALE VARCHAR2(1)
DIRECT VARCHAR2(1)
NEW CHAR(1)
OBJD NUMBER
TS# NUMBER

SQL> desc x$bh;
ERROR:
ORA-04043: object x$bh does not exist

solution: It is there in the SYS schema. It is just no longer exposed. If you want to expose it, you can do it like this…

  1. WARNING _ THIS IS NOT SUPPORTED BY ORACLE – you are completely on your own here.
  2. Login as SYS
  3. Execute the following
  • create view x_$bh as select * from x$bh;
  • create public synonym x$bh for x_$bh;
  • grant select on x_$bh to <some dba-only role>;

SQL> conn / as sysdba
Connected.
SQL> create view x_$bh as select * from x$bh;

View created.

SQL> create public synonym x$bh for x_$bh;

Synonym created.

SQL> grant select on x_$bh to rajat;

Grant succeeded.

SQL> conn rajat/rajat;
Connected.
SQL> desc x$bh;
Name Null? Type
—————————————– ——– —————————-
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
HLADDR RAW(8)
BLSIZ NUMBER
NXT_HASH RAW(8)
PRV_HASH RAW(8)
NXT_REPL RAW(8)
PRV_REPL RAW(8)
FLAG NUMBER
FLAG2 NUMBER
LOBID NUMBER
RFLAG NUMBER
SFLAG NUMBER
LRU_FLAG NUMBER
TS# NUMBER
FILE# NUMBER
DBARFIL NUMBER
DBABLK NUMBER
CLASS NUMBER
STATE NUMBER
MODE_HELD NUMBER
CHANGES NUMBER
CSTATE NUMBER
LE_ADDR RAW(8)
DIRTY_QUEUE NUMBER
SET_DS RAW(8)
OBJ NUMBER
BA RAW(8)
CR_SCN_BAS NUMBER
CR_SCN_WRP NUMBER
CR_XID_USN NUMBER
CR_XID_SLT NUMBER
CR_XID_SQN NUMBER
CR_UBA_FIL NUMBER
CR_UBA_BLK NUMBER
CR_UBA_SEQ NUMBER
CR_UBA_REC NUMBER
CR_SFL NUMBER
CR_CLS_BAS NUMBER
CR_CLS_WRP NUMBER
LRBA_SEQ NUMBER
LRBA_BNO NUMBER
HSCN_BAS NUMBER
HSCN_WRP NUMBER
HSUB_SCN NUMBER
US_NXT RAW(8)
US_PRV RAW(8)
WA_NXT RAW(8)
WA_PRV RAW(8)
OQ_NXT RAW(8)
OQ_PRV RAW(8)
AQ_NXT RAW(8)
AQ_PRV RAW(8)
OBJ_FLAG NUMBER
TCH NUMBER
TIM NUMBER
CR_RFCNT NUMBER
SHR_RFCNT NUMBER

Happy reading and don’t be shy to comment :)

ORA-01919: role ‘PLUSTRACE’ does not exist


The autotrace provides instantaneous feedback including the returned rows, execution plan, and statistics. The user doesn’t need to be concerned about trace file locations and formatting since the output is displayed instantly on the screen. This is very important data that can be used to tune the SQL statement.

Problem: 

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> GRANT PLUSTRACE TO scott;
GRANT PLUSTRACE TO scott
*
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not exist

solution:

Run plustrce.sql through SYS schema if it did not run before
<pre>
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
</pre>

Grant PLUSTRACE to <USER WHO WANTS TO RUN AUTOTRACE>

Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table ,UTLXPLAN.sql have to be in user’s schema.

SQL> @/u01/app/oracle/product/11.1.0/db/sqlplus/admin/plustrce.sql
This script in turn will execute below sql statements:
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not exist
SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off

For any specific SQL query you can see SQL execution performance statistics. In SQL*Plus you can issue the “set autotrace” command and then issue your SQL statement:

SQL> conn scott/scott
Connected.
SQL> set autot TRACE STAT
SQL> select * from t1;

14 rows selected.
Statistics
———————————————————-
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

ORA-02095: specified initialization parameter cannot be modified


Yesterday i was trying to relocate my control files from default location (/u01/app/oracle/oradata/orabreakfix/control01.ctl ) to new location /s01/oradata1/orabreakfix/control01.ctl.

So the command i ran is

alter system set control_files=’/s01/oradata1/orabreakfix/control01.ctl, /s01/oradata1/orabreakfix/control02.ctl,/s01/oradata1/orabreakfix/control03.ctl’;

But something unexpected happened

ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified.

It took me a while to think the work around, which is related my previous post  https://rajat1205sharma.wordpress.com/2015/11/30/how-to-identify-a-static-or-dynamic-parameter-in-oracle/

SQL> select NAME,VALUE,DISPLAY_VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE,ISMODIFIEd from v$parameter where NAME like ‘control%’;

NAME

VALUE

DISPLAY_VALUE

ISDEFAULT ISSES_MODIFIABL ISSYS_MODIFIABLE ISINSTANCE_MODI ISMODIFIED

control_files
/u01/app/oracle/oradata/orabreakfix/control01.ctl, /u01/app/oracle/oradata/orabreakfix/control02.ctl, /u01/app/oracle/oradata/orabreakfix/control03.ctl
/u01/app/oracle/oradata/orabreakfix/control01.ctl, /u01/app/oracle/oradata/orabreakfix/control02.ctl, /u01/app/oracle/oradata/orabreakfix/control03.ctl
FALSE     FALSE     FALSE    FALSE     FALSE

SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter system set control_files=’/s01/oradata1/orabreakfix/control01.ctl, /s01/oradata1/orabreakfix/control02.ctl,/s01/oradata1/orabreakfix/control03.ctl’ scope=spfile;

System altered.

similarly when i tried to run below query i got the same problem

Database mounted.
SQL> alter system set log_archive_format=’log%t_%s_%r.arc’;
alter system set log_archive_format=’log%t_%s_%r.arc’
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

now i know what i need to check, so i checked

SQL> Select name,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name=’log_archive_format’;

NAME

VALUE ISDEFAULT ISSES_MODIFIABL ISSYS_MODIFIABLE


log_archive_format
%t_%s_%r.dbf TRUE FALSE FALSE

so i ran the below query and it worked and  all i need to doafter that is to restart my database to get this parameter changed.

SQL> alter system set log_archive_format=’log%t_%s_%r.arc’ scope=spfile;

System altered.

SQL> show parameter LOG_ARCHIVE_FORMAT;

NAME TYPE VALUE


log_archive_format string log%t_%s_%r.arc

I hope it would be helpful.

 

 

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


Today, i was trying to put my test db in archive log mode(currently in no archive log mode). I was impatient so i just ran shut abort to shut down the database. But when  i tried to put db in archive log mode it threw error-00265

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> shut abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size 2160112 bytes
Variable Size 469764624 bytes
Database Buffers 1124073472 bytes
Redo Buffers 7413760 bytes
Database mounted.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> alter database open;

Database altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size 2160112 bytes
Variable Size 469764624 bytes
Database Buffers 1124073472 bytes
Redo Buffers 7413760 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

Cause: The database either crashed or was shutdown with the ABORT option. Media recovery cannot be enabled because the online logs may not be sufficient to recover the current datafiles.
Action: Open the database and then enter the SHUTDOWN command with the NORMAL or IMMEDIATE option.

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected


SQL*Plus: Release 12.1.0.1.0 Production on Sat Mar 12 01:59:44 2016

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2505338880 bytes
Fixed Size 2291472 bytes
Variable Size 1493174512 bytes
Database Buffers 989855744 bytes
Redo Buffers 20017152 bytes
Database mounted.
Database opened.
SQL> alter database close;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed

Solution:

The database close operation is implicit in a database shutdown. The nature of the operation depends on whether the database shutdown is normal or abnormal.
At this stage, the only option for the instance is to shut down, ending the life of this instance.

You can open your db only once. If you would do a close of your db, its not like going backwards. You have to go with the shutdown and then do the startup again.

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2505338880 bytes
Fixed Size 2291472 bytes
Variable Size 1493174512 bytes
Database Buffers 989855744 bytes
Redo Buffers 20017152 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

 

Fix-ORA-09817: Write to audit file failed


Problem:

oracle@borvik:~> . oraenv

ORACLE_SID = [ord3067b1] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db is /u01/app/oracle
oracle@borvik:~> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 – Production on Wed Mar 9 10:51:01 2016

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

ERROR:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
ORA-01075: you are currently logged on

Work around: 

1.) Check the free space of mount point /u01

oracle@borvik:/u01/app/oracle/product/11.1.0/db/dbs> df -Ph /u01/
Filesystem      Size             Used                              Avail                         Use%                          Mounted on
/dev/mapper/rootvg-u01lv1  20G  20G           0                               100%                             /u01

2.) Check the audit file location in init.ora file.

oracle@borvik: /u01/app/oracle/product/11.1.0/db/dbs: cat initord306b1.ora | grep adump

*.audit_file_dest=’/u01/app/oracle/product/11.1.0/oradump/ord3067b1/adump’
*.core_dump_dest=’/u01/app/oracle/product/11.1.0/oradump/ord3067b1/cdump’

3) Removed any old unwanted audit files.

4) Remove any old trace files.

5) Check the free space on /u01 mount point.

6) Now try to connect oracle database as SYS user.

7) You should be able to connect to the database.

 

 

 

ORA-00130: invalid listener address


ORA-00130: invalid listener address…
The actual error was:
SQL>STARTUP;

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address ‘(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))
I had finally managed to create my 11gR2 instance when I got this. So what happened?

1. Started OEL 5.5 64 bit VM
2. Logged in as user oracle
3. Opened terminal and ran . oraenv
4. sqlplus / as sysdba
5. Issued the startup command.

ORA-00130 from the docs:
Cause: The listener address specification is not valid.
Action: Make sure that all fields in the listener address (protocol, port, host, key, …) are correct.

So it has something to do with how I configured everything. What else did I change on the system?

After installing the database, I ran netca to set up the listener. I manually edited the file to change localhost.localdomain to just localhost. I also added an entry in the tnsnames.ora file to point to the listener.

From a system perspective, I decided I didn’t like “new-host” showing up on my router configuration page. So I changed the hostname to oracle. I used the network GUI tool, not the CLI (still hazy on how exactly that is done).

I’m back to my sql prompt.
SQL>STARTUP;

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address ‘(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))
Come on!

I then asked The Google Machine for help. That led me to this thread on OTN. About 6 or 7 replies in, Hans Forbrich offers up some very helpful troubleshooting tips.

1. Check that your environment variables are set. Check.
2. Make sure the listener is started. Check.

There was a caveat for #2 though. If your listener blew up on start, check these other things. That’s when I saw cat /etc/hosts and his output for that. I quickly checked mine and saw this:
[oracle@oracle bin]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
Ah…where’s oracle? I tried pinging oracle, no response. I open up the file and add it in:
[oracle@oracle bin]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost oracle
::1 localhost6.localdomain6 localhost6
I ping oracle again, response!

Start and restart the listener. tnsping TESTING OK!
SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 616566616 bytes
Database Buffers 444596224 bytes
Redo Buffers 5554176 bytes
Database mounted.
Database opened.