Archive

Archive for December, 2009

ORA-00471: DBWR process terminated with error

December 22, 2009 Leave a comment

Symptoms

1. Recently, we enable direct io option (i.e.  convosync=direct) of the database filesystem (vxfs) and restart database

2. After that, create a test table with about 1 million, suddenly the database crashed

3. Try to restart the database, we got the messages below.

ORA-00471: DBWR process terminated with error

Cause

1. Can mount the database in mount, can’t open the database

2. When try to open the database, we find that high IO read/writes is monitored on system by the command iostat -cmnzx 5

Fixed

1. Fallback the database filesystem setting ( i.e. remove the convosync=direct)

2. Can start and open the database

3. The high IO read/writes are not monitored when opening the database

Advertisements
Categories: TroubleShooting

ORA-04030: out of process memory when trying to allocate

December 22, 2009 Leave a comment

Symptoms

SQL> startup
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  2031352 bytes
Variable Size             205521160 bytes
Database Buffers          310378496 bytes
Redo Buffers                6356992 bytes
Database mounted.
ORA-04030: out of process memory when trying to allocate 35692296 bytes (pga
heap,KCL disk IO request block array)

Causes

There is not memory enough on system to allocate

Fixed

Tuned down sga_target size to 250M as the following

SQL> startup mount
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  2031352 bytes
Variable Size             213909768 bytes
Database Buffers          301989888 bytes
Redo Buffers                6356992 bytes
Database mounted.

SQL> show parameter target

NAME                                 TYPE        VALUE
———————————— ———– ——————————
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
pga_aggregate_target                 big integer 200M
sga_target                           big integer 500M
SQL> alter system set sga_target=250M scope=spfile;

System altered.

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

Total System Global Area  264241152 bytes
Fixed Size                  2029360 bytes
Variable Size              96471248 bytes
Database Buffers          159383552 bytes
Redo Buffers                6356992 bytes
Database mounted.
Database opened.
SQL>

Categories: TroubleShooting

ORA-01536: space quota exceeded for tablespace ‘SGDM_TBS’

December 22, 2009 Leave a comment

Symptoms

1. Logon as user account to create a test table

SQL> create table test as select * from all_tables;
create table test as select * from all_tables
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘SGDM_TBS’

2. Check space quota on the tablespace ‘SGDM_TBS’

SQL> select * from user_ts_quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
—————————— ———- ———- ———- ———- —
SGDM_TBS                         33030144          0       4032          0 NO

Causes

The user account has no quota unlimited privilege on its default tablespace ‘SGDM_TBS’

Fixed

Logon as dba; grant quota unlimited on its tablespace

SQL>  alter user epg quota unlimited on SGDM_TBS ;

User altered.

SQL> select tablespace_name,bytes,max_bytes, username from  dba_ts_quotas ;

TABLESPACE_NAME                     BYTES  MAX_BYTES USERNAME
—————————— ———- ———- ——————————
SYSAUX                           16318464         -1 OLAPSYS
SYSAUX                           12582912         -1 WK_TEST
SYSAUX                          169213952         -1 SYSMAN
SYSAUX                             458752         -1 FLOWS_FILES
SGDM_TBS                         85590016         -1 EPG

Max_bytes=-1 means that user EPG has quota unlimited on tablespace SGDM_TBS

Categories: TroubleShooting

Cloning a RAC database in ASM

December 21, 2009 1 comment

Usually, we can create a RAC database in ASM by dbca. We can use RMAN to clone one from an existing database to a new one. Here is the steps-by-steps procedures I work out.

Cloning Database from sgptqa to vtbqa in ASM

Rename Database in ASM

December 21, 2009 Leave a comment

Recently, I deployed RAC cluster with ASM. After creating database, I found that the database name was not matching to our project objective. I needed to rename to match the requirement. I found that I couldn’t find detailed steps to do it. Finally, I worked out the detailed steps. Here is my steps below.

 An example to rename database in ASM

Add Redo Logs in ASM

December 21, 2009 Leave a comment

1. Show existing Redo Log Groups

SQL> select inst_id,group#,status,type,member from gv$logfile;

   INST_ID     GROUP# STATUS  TYPE    MEMBER

———- ———- ——- ——- ————————————————–

         1          2         ONLINE  +DAT1/sgcsprod/onlinelog/group_2.286.700253009

         1          2         ONLINE  +REC1/sgcsprod/onlinelog/group_2.278.700253011

         1          1         ONLINE  +DAT1/sgcsprod/onlinelog/group_1.285.700253007

         1          1         ONLINE  +REC1/sgcsprod/onlinelog/group_1.277.700253009

         1          3         ONLINE  +DAT1/sgcsprod/onlinelog/group_3.289.700253235

         1          3         ONLINE  +REC1/sgcsprod/onlinelog/group_3.279.700253235

         1          4         ONLINE  +DAT1/sgcsprod/onlinelog/group_4.290.700253237

         1          4         ONLINE  +REC1/sgcsprod/onlinelog/group_4.280.700253239

         2          2         ONLINE  +DAT1/sgcsprod/onlinelog/group_2.286.700253009

         2          2         ONLINE  +REC1/sgcsprod/onlinelog/group_2.278.700253011

         2          1         ONLINE  +DAT1/sgcsprod/onlinelog/group_1.285.700253007

         2          1         ONLINE  +REC1/sgcsprod/onlinelog/group_1.277.700253009

         2          3         ONLINE  +DAT1/sgcsprod/onlinelog/group_3.289.700253235

         2          3         ONLINE  +REC1/sgcsprod/onlinelog/group_3.279.700253235

         2          4         ONLINE  +DAT1/sgcsprod/onlinelog/group_4.290.700253237

         2          4         ONLINE  +REC1/sgcsprod/onlinelog/group_4.280.700253239

 16 rows selected.

SQL> select inst_id,group#,thread#,sequence#,bytes,members,status from gv$log;

    INST_ID     GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS STATUS

———- ———- ———- ———- ———- ———- —————-

         1          1          1         99  104857600          2 INACTIVE

         1          2          1        100  104857600          2 CURRENT 

         1          3          2        113  104857600          2 CURRENT <–

         1          4          2        112  104857600          2 INACTIVE

         2          1          1         99  104857600          2 INACTIVE

         2          2          1        100  104857600          2 CURRENT

         2          3          2        113  104857600          2 CURRENT

         2          4          2        112  104857600          2 INACTIVE

– Redo group 1and 2 are assigned to instance 1, Redo group 3 and 4 are assigned to instance 2

– Redo group 2 is active on instance 1 and instance 1 acts as recovery instance once instance 2 is down, therefore Redo group 3 is active on instance 1

– Redo group 3 is active on instance 2 and instance  acts as recovery instance once instance 2 is down, therefore Redo group 1 is active on instance 2

2. Add log groups for thread 1 and thread 2 with two members, one is created on ‘+DAT1’ Diskgroup, anoth 

alter database add logfile thread 1 group 5 (‘+DAT1′,’+REC1’) size 100M;

alter database add logfile thread 1 group 6 (‘+DAT1′,’+REC1’) size 100M;

alter database add logfile thread 2 group 7 (‘+DAT1′,’+REC1’) size 100M;

alter database add logfile thread 2 group 8 (‘+DAT1′,’+REC1’) size 100M;

 

exec dbms_mview.refresh returns ORA-12018: following error encountered during code generation

December 21, 2009 3 comments

symptoms

1. We can create the materialized view with the following statement

CREATE MATERIALIZED VIEW VI_CHANNEL_MV AS SELECT channel_key, channel_id, status, name_eng, name_chi, display_channel_name_eng, display_channel_name_chi, language FROM vi.vi_channel_t@vi_db_link;

2. We can do complete refresh the mview with complete option as the following

SQL> exec dbms_mview.refresh(‘VI_CHANNEL_MV’,’C’);

PL/SQL procedure successfully completed.

3.  But, can’t refresh the mview with dbms_view like as the following

call dbms_mview.refresh(‘VI_CHANNEL_MV’);

ERROR at line 1:

ORA-12018: following error encountered during code generation for

“EPG”.”VI_CHANNEL_MV”

ORA-00942: table or view does not exist

ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2545

ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2751

ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2720

ORA-06512: at line 1

 Causes

1. Do session trace for refresh the mview by the command  alter session set events ‘10046 trace name context forever, level 12’;

2. Get the following detailed for the error ORA-00942

=====================
PARSE ERROR #9:len=52 dep=2 uid=99 oct=3 lid=99 tim=1533135953690 err=942 
SELECT * FROM “VI”.”MLOG$_VI_CHANNEL_T”@”VI_DB_LINK” <– this shows it can’t access the mview log table
CLOSE #9:c=0,e=0,dep=2,type=0,tim=1533135953690
WAIT #9: nam=’SQL*Net message to dblink’ ela= 0 driver id=675562835 #bytes=1 p3=0 obj#=633 tim=1569931221721
WAIT #9: nam=’SQL*Net message from dblink’ ela= 625 driver id=675562835 #bytes=1 p3=0 obj#=633 tim=1569931222368

Fixed

Logon the source database, grant select privilege to remote login user or role corresponding to that user

SQL> grant select on MLOG$_VI_CHANNEL_T  to SGDM_ROLE;

Logon the destination database, refresh the materialized view successfully

SQL> exec dbms_mview.refresh(‘VI_CHANNEL_MV’);

PL/SQL procedure successfully completed.

Categories: TroubleShooting