2009.04.27: Oracle in Russia, RAC days.

Thread 1 cannot allocate new log, sequence...

I sometimes expecting this message in alert.log file:
Thread 1 cannot allocate new log, sequence nnnnn

It can be skipped because it is not harmful but I decided to explore.

SQL> alter system set log_checkpoints_to_alert=true;

SQL> alter system checkpoint;

let's get some outcome at first.

"Error: ORA-16541: site is not enabled" after ENABLE CONFIGURATION command in Data Guard Brocker

If you see something like that:

DGMGRL> connect /
Connected.
DGMGRL> DISABLE CONFIGURATION;
Disabled.
DGMGRL> edit database "dbXX" set property 'LocalListenerAddress'='(ADDRESS=(PROTOCOL=tcp)(HOST=XXX.XX.au)(PORT=1521))';
Error: ORA-16541: site is not enabled

Configuration details cannot be determined by DGMGRL
DGMGRL> ENABLE CONFIGURATION;
Error: ORA-16541: site is not enabled

Configuration details cannot be determined by DGMGRL
DGMGRL> exit

It may well happen after switchover operation.

ORA-16570: operation requires restart of database ""

After successfull switchover I got  the message
ORA-16570: operation requires restart of database "".

DGMGRL> SWITCHOVER TO dbXXsb;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "dbXX" on database "dbXX"
Shutting down instance "dbXX"...
ORA-01031: insufficient privileges

You are no longer connected to ORACLE
Please connect again.
Unable to shut down instance "dbXX"
You must shut down instance "dbXX" manually
Operation requires shutdown of instance "dbXX" on database "dbXXsb"
You must shut down instance "dbXX" manually
Operation requires startup of instance "dbXX" on database "dbXX"
You must start instance "dbXX" manually
Operation requires startup of instance "dbXX" on database "db07sb"
You must start instance "dbXX" manually
Switchover succeeded, new primary is "dbXXsb"
DGMGRL> SHOW CONFIGURATION;
not logged on
DGMGRL> connect /
Connected.
Error:
ORA-16570: operation requires restart of database ""
ORA-06512: at "SYS.X$DBMS_DRS", line 180
ORA-06512: at line 1

DGMGRL>
DGMGRL> exit


shutdown immediate;

startup mount;
DG Brocker starts all others.

Warning: ORA-16610: command 'Broker automatic health check' in progress

Continuous getting  "Warning: ORA-16610: command 'Broker automatic health check' in progress" message after successful switchover operation.

DGMGRL> show configuration;

Configuration
Name: dbXX.rtcomm.ru
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
dbXX - Physical standby database
dbXXsb - Primary database

Current status for "dbXX.rtcomm.ru":
Warning: ORA-16610: command 'Broker automatic health check' in progress


the only way was to restart DG Brocker
ALTER SYSTEM SET DG_BROKER_START=FALSE;
ALTER SYSTEM SET DG_BROKER_START=TRUE;

The message disappeared.

Parallelism analyzing

set :

set timi on
set autot on

to chose the difference betwee:

select count(*) from all_users au;

 and

select /*+ parallel (au,10) */ count(*) from all_users au;

some useful information:

select * from V$PX_SESSION;

SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';

SELECT * FROM V$PX_PROCESS;

SELECT * FROM V$PX_PROCESS_SYSSTAT;

parameter to reduse/increase parallelism in Oracle is PARALLEL_MAX_SERVERS.

Direct path load operations incur waits for "control file sequential read" event

Usual select generates a lot of wait events. In Enterprise Manager it looks like:

System I/O    control file sequential read    file#    0    block#    18    blocks    1    0
System I/O    control file sequential read    file#    0    block#    16    blocks    1    0
System I/O    control file sequential read    file#    3    block#    1    blocks    1    0
System I/O    control file sequential read    file#    2    block#    1    blocks    1    0
System I/O    control file sequential read    file#    1    block#    1    blocks    1    0
System I/O    control file sequential read    file#    0    block#    1    blocks    1    0
System I/O    control file sequential read    file#    0    block#    18    blocks    1    10
System I/O    control file sequential read    file#    0    block#    16    blocks    1    0
System I/O    control file sequential read    file#    3    block#    1    blocks    1    0
System I/O    control file sequential read    file#    2    block#    1    blocks    1    0 

Following Metalink:

It takes a long time to query v$log_history in rman session

In rman session hangs for a long time while executing:
SELECT recid, stamp, thread#, sequence#, first_change# low_scn,
       first_time low_time, next_change# next_scn, resetlogs_change#,
       resetlogs_time
    FROM v$log_history
    WHERE recid BETWEEN :b1 AND :b2
      AND resetlogs_time IS NOT NULL
      AND stamp >= :b3
    ORDER BY recid

It occurred at full resync time. It suppose to be another appearance of BUG:5620640 - LONG WAIT ON OPEN DB RESETLOGS WHEN USING FLASHBACK DB GUARANTEED RECOVERY POINT

Oracle's Block Browser and Editor tool

Featured Database Articles

September 23, 2009
Oracle's Block Browser and Editor tool

By Steve Callan
In a prior article on Oracle’s Block Browser and Editor tool (BBED), the installation of the tool was covered. In this article, we’ll look at starting a session using a simple database and see how to view data. The database used in this example is minimal in terms of structure. Use dbca to create a general purpose database and pick a sample schema such as SCOTT to practice on. This simplifies what you have to find.

The schema contains four tables (EMP, DEPT, BONUS, and SALGRADE) and two indexes, all contained in the USERS tablespace and further, only one database file to contend with (users01). We don’t care about the other two segment names (PK_EMP and PK_DEPT) because they represent indexes, and really, why would you want to use bbed on an index? Remember, use of bbed is a last resort measure, and there are so many other ways of fixing or rescuing indexes that are much safer.

As an example, let’s get the

How to set trace for others sessions, for your own session and at instance level

a very interesting article which I found at src

This short paper is not a polished white paper (so please do not expect lots of explanatory text) but is intended to be a good example to show the many ways to set Oracle trace in your own session or in another session. let's get on with it:

Tools to analyse trace files

Up to and including Oracle 10g the tool that is generally used to analyse trace files is called tkprof. This tool formats the trace files that have been generated into a more readable format. Understanding the trace file format seems daunting on first inspection. A good source for details on the trace file format is a metalink note 39817.1. In 10g there is a new tool for formatting trace files called trcsess. This tool has been designed to deal with the new trace facilities that allow trace to be identified based on client identifier or by a combination of service name / module / action. This allows trace to be completed even if connection pooling and multi-threading is used. An individual client in these circumstances could share many different sessions.

Find out where the trace file will be written to

If the user you are using is not a DBA or to be more specific has not been granted access to the data dictionary view V$PARAMETER then you will need to use this technique to find out where your trace files are written to:

    SQL> set serveroutput on size 1000000 for wra
    SQL> declare
      2  paramname varchar2(256);
      3  integerval binary_integer;

Oracle Open World: OOW 2010: We Go Green and healthy ... oh ... sort of ...

By Mike Dietrich:

OOW 2010: We Go Green and healthy ... oh ... sort of ...

The healthy food provided in the breaks ... :-(

src

library cache pin & library cache lock

Once there was an error on one of our production database. I saw some process awaiting for "library cache lock". One process was in "library cache pin' state. This situation was expected during table partition movement operation.

As I understood it was a consequences of invalid package which became invalid due to "alter table XXX move partition". Session 388 was getting library cache pin when he or some system process had been tried to recompile invalid object during partition movement. The others were waiting for 388 process to get a lock. After partition had been moved successfully package was recompiled and the queue disappeared.

b-tree index structure (Non-Unique)

First name kind of field based index. This is Non-Unique index.

How b-tree database indexes work and how to tell if they are efficient (100' level)


src

Dropping and creating tables in read only tablespaces?!

Dropping and creating tables in read only tablespaces?!

July 11th, 2010, Tanel Poder


You probably already know that it’s possible to drop tables in Oracle read only tablespaces… (You did know that already, right? ;-) Here’s a little example:

create tablespace ronly datafile '/u03/oradata/LIN112/ronly.01.dbf' size 10m;
Tablespace created.
create table test tablespace ronly as select * from all_users;
Table created.
alter tablespace ronly READ ONLY;
Tablespace altered.
drop table test;
Table dropped.

How to prevent scheduled jobs to run at instance startup?

The solution is very simple:

1. startup in restricted mode
startup restrict;


2. disable all windows
select 'exec DBMS_SCHEDULER.DISABLE('''||WINDOW_NAME||''');' from ALL_SCHEDULER_WINDOWS where ENABLED='TRUE';


3. close all windows
select 'exec DBMS_SCHEDULER.CLOSE_WINDOW('''||WINDOW_NAME||''');' from ALL_SCHEDULER_WINDOWS where ACTIVE='TRUE';


4. stop running & disable jobs

CASE WHEN state !='RUNNING' THEN 'begin'  || CHR(10)  || 'DBMS_SCHEDULER.STOP_JOB ('''||OWNER||'.'||JOB_NAME||''', TRUE); '  || CHR(10)  || 'EXCEPTION WHEN OTHERS THEN NULL;'  || CHR(10)  || 'END; ' || CHR(10)  || '/' || CHR(10)  || 'exec DBMS_SCHEDULER.DISABLE ('''||OWNER||'.'||JOB_NAME||''', TRUE );'
ELSE
'exec DBMS_SCHEDULER.DISABLE ('''||OWNER||'.'||JOB_NAME||''', true);' 
end DDD
FROM DBA_SCHEDULER_JOBS
where OWNER not in ('SYS','EXFSYS','ORACLE_OCM');"| sqlplus -S '/ as sysdba'

5. shutdown immediate

6. startup

ORA-14287: cannot REBUILD a partition of a Composite Range partitioned index

When table is partitioned and it has subpartitions I received ORA-14287 error when I was trying to move partition to tablespace with nologging storage option:


SQL> ALTER TABLE TABLE_NAME
    MOVE PARTITION PARTITION_NAME
    TABLESPACE TABLESPACE_NAME;
    MOVE PARTITION PARTITION_NAME
                   *
ERROR at line 2:
ORA-14257: cannot move partition other than a Range or Hash partition


It means that it is impossible move logical structure to somewhere. It is better to move subpartition first and modify default attributes for partition next.

alter table TABLE_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME;

DBMS_LOGMNR something useful. Who generates redo?

На наших системах некоторые данные подлежат периодической перегенерации и располагаются в partitioned таблицах на nologging разделах. Иногда разработчики промахиваются и создают таблицу в разделе с опцией NOLOGGING. Это приводит к генерации большого количества архивных redo логов. Определить кто производит столь непосильную работу поможет следующий скрипт:

##########################
#
# DBMS_LOGMNR
#

STORE SET /tmp/_sqlplus-env.sql
SET AUTOP OFF
set long 200000
set lines 180
set trimspool on
set heading off
set feedback off
set pages 0
set verify off
set echo off
set timing offn
SET LINESIZE 5000
SET TERMOUT OFF
SET TRIMS ON
WHENEVER SQLERROR EXIT SQL.SQLCODE;
WHENEVER OSERROR EXIT FAILURE;
set serveroutput on;
spool /tmp/_tmp2run.sql

Some things that I've found to recover database without redo

SQL> startup mount
SQL> recover database until
SQL> alter database open resetlogs;

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [25501152], [0],
[25521272], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [25501151], [0],
[25521272], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [25501149], [0],
[25521272], [12583040], [], [], [], [], [], []
Process ID: 10846
Session ID: 1 Serial number: 5

_allow_resetlogs_corruption=true