Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Archive for the ‘Advanced’ Category

Advance/Troubleshooting/Error-Bug Fixing

« Previous Entries
Next Entries »

A Day tried to fix the infamous ORA-00600 [kqlnrc_1] from our DB systems.

Posted by FatDBA on February 16, 2014

ORA-00600 [kqlnrc_1] most of the times is about an invalid object in the Library Cache of the database.
An Oracle Trace file is always generated during the time of this error. Below provided are few of the steps that you can try atleast before you passon the case to Oracle Support team.

Open the trace file with an editor (The following lines are from an 11.2.0.3 trace file) :

1. Find the first ORA_600 error argument
ORA-00600: internal error code, arguments: [kqlnrc_1], [0x143C6DAD0], [], [], [], [], [], [], [], [], [], []

2. Search the trace file for 0x143c6dad0 (its the first argument in lower case):

DDE: Problem Key ‘ORA 600 [kqlnrc_1]’ was flood controlled (0x2) (incident: 35561)
ORA-00600: internal error code, arguments: [kqlnrc_1], [0x143C6DAD0], [], [], [], [], [], [], [], [], [], []

LibraryHandle:  Address=0x143c6dad0 Hash=cd3b674a LockMode=S PinMode=S LoadLockMode=0 Status=INVL
ObjectName:  Name=PUBLIC.TEST
FullHashValue=5c820441b24ce2df1d9a0971cd3b674a Namespace=TABLE/PROCEDURE(01) Type=SYNONYM(05) Identifier=0 OwnerIdn=2147483644
Statistics:  InvalidationCount=2 ExecutionCount=0 LoadCount=1 ActiveLocks=1 TotalLockCount=6 TotalPinCount=6
Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=6 HandleInUse=6 HandleReferenceCount=0
Concurrency:  DependencyMutex=0x143c6db80(0, 8, 0, 0) Mutex=0x143c6dc00(233, 45, 0, 6)
Flags=REM/PIN/TIM/[00022801]
WaitersLists:
Lock=0x143c6db60[0x143c6db60,0x143c6db60]
Pin=0x143c6db40[0x143c6db40,0x143c6db40]
LoadLock=0x143c6dbb8[0x143c6dbb8,0x143c6dbb8]
Timestamp:  Current=04-25-2013 09:41:24
HandleReference:  Address=0x143c6dc90 Handle=(nil) Flags=[00]
ReferenceList:
Reference:  Address=0x145badf28 Handle=0x12d7be980 Flags=DEP[01]
Timestamp=04-25-2013 09:41:24 InvalidatedFrom=0
LibraryObject:  Address=0x10dbe40b0 HeapMask=0000-0001-0001-0000 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block:  #=’0′ name=KGLH0^cd3b674a pins=0 Change=NONE
Heap=0x12297cc10 Pointer=0x10dbe4150 Extent=0x10dbe4030 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=0.257812 Size=4.000000 LoadTime=15174108210

3. Find the object :

SELECT object_name,object_type,owner,status
FROM dba_objects
WHERE object_name=’TEST’ and owner=’PUBLIC’;

OBJECT_NAME OBJECT_TYPE OWNER STATUS
————– ————- —————- ——-
TEST SYNONYM PUBLIC VALID
4. Compile the synonym owned by public.

As sys :

SQL> alter public synonym TEST compile;

or Recreate it

SQL> create or replace public synonym xyz for xyz@javadb@testlab;

And That’s Fixed,

Hope That Helps
Prashant Dixit

Posted in Advanced | Tagged: | Leave a Comment »

AWR/ADDM/ASH Coming Blank. Tuning Advisors not working :( … (What to do ??)

Posted by FatDBA on January 23, 2014

You have a new database designed and created for the team to test changes before they go and apply in Production. One fine day you feel a need to check performance of database and get some auto suggestions from oracle engine itself using ADDM and AWR or ASH … But what if they are coming blank and Tuning Advisors throwing errors saying ….

DECLARE
*
ERROR at line 1:
ORA-13717: Tuning Package License is needed for using this feature.
ORA-06512: at “SYS.PRVT_SMGUTIL”, line 52
ORA-06512: at “SYS.PRVT_SMGUTIL”, line 37
ORA-06512: at “SYS.DBMS_MANAGEMENT_PACKS”, line 26
ORA-06512: at “SYS.DBMS_SQLTUNE”, line 651
ORA-06512: at line 4

WHAT TO DO ?

Reason: This case comes in to existence if you are using STANDARD edition of Oracle Database.
Here is a small fix for the problem —-
Change the parameter (control_management_pack) to ‘DIAGNOSTIC+TUNING’ in order to enable MMON to collect the health statistics timely to AWR related tables (Sits in SYSAUX)

SQL> show parameter control_management_pack
NAME                                 TYPE                             VALUE
———————————— ——————————– ——————————
control_management_pack_access       string                           NONE

SQL> ALTER system SET CONTROL_MANAGEMENT_PACK_ACCESS=‘DIAGNOSTIC+TUNING’;
System altered.

SQL>  show parameter control_management_pack
NAME                                 TYPE                             VALUE
———————————— ——————————– ——————————
control_management_pack_access       string                           DIAGNOSTIC+TUNING

Posted in Advanced | 2 Comments »

RMAN Error: ORA-01152: file 1 was not restored from a sufficiently old backup & ORA-01110:

Posted by FatDBA on January 15, 2014

RMAN Scenario:
DB: Oracle 11g r2
OS: Linux – RHEL 5
Error: “ORA-01152: file 1 was not restored from a sufficiently old backup” when tried to open the database.

STATUS
==============
SQL> @db

NAME      ROLE       LOG_MODE     OPEN_MODE      DG_BROKE FORC_LGNG   CUR_SCN        CKPT_CHG        CTRLFIL_SEQ CTRLFL_CNG      ARCH_CNG       PROT_MOD
——— ———- ———— ————– ——– ———– ————– ————— ———– ————— ————– ——————–
SAIRAM     PRIMARY    ARCHIVELOG   MOUNTED        DISABLED YES         0              32913920280       795938526 32913922263     32913811789    MAXIMUM PERFORMANCE

SQL> select open_mode from v$database;
OPEN_MODE
————–
MOUNTED

This is what’s happening with the database when tried to open it.
Throwing error which reads System datafile is restored from an old backup.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/dbmount212/sairam/datafiles/system01.dbf’

When tried to recover the database it’s asking for sequence number 75, which is not available (Conformed, as asked sequence is not available in ARCHIVE destination).

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 32913922263 generated at 01/14/2014 10:57:39 needed for thread 1
ORA-00289: suggestion : /dbmount212/oracle/sairam/archivelog/1_75_833707787.arc
ORA-00280: change 32913922263 for thread 1 is in sequence #75

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log ‘/dbmount212/oracle/sairam/archivelog/1_75_833707787.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log ‘/dbmount212/oracle/sairam/archivelog/1_75_833707787.arc’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/dbmount212/sairam/system01.dbf’

There is a possibility that the sequence might be struck inside of any redo log file and has not been archived.
Let’s query required sequence number (75) using one of the dynamic view v$log

SQL> select * from v$log where SEQUENCE#=’75’;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE#
———- ———- ———- ———- ———- ———- — —————- ————-
FIRST_TIM NEXT_CHANGE# NEXT_TIME
——— ———— ———
9          1         75  524288000        512          1 NO  CURRENT             3.2914E+10
14-JAN-14   2.8147E+14

Yes,the log is inside group number 9. Let’s check the location of the related log file using v$logfile;

SQL> SELECT * FROM V$LOGFILE where group#=’9′;

GROUP# STATUS  TYPE
———- ——- ——-
MEMBER
—————————————————————————————————-
IS_
—
9         ONLINE
/var/log/oracle/redo/onlinelog/redo07.log
NO

Let’s try to recover the database again.

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 32913922872 generated at 01/14/2014 11:17:33 needed for thread 1
ORA-00289: suggestion : /dbmount212/oracle/sairam/archivelog/1_75_833707787.arc
ORA-00280: change 32913922872 for thread 1 is in sequence #75Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/var/log/oracle/redo/onlinelog/redo09.log
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 69 is unknown – rename to correct file
ORA-01110: data file 69: ‘/dbmnt12/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00069’
ORA-01157: cannot identify/lock data file 69 – see DBWR trace file
ORA-01111: name for data file 69 is unknown – rename to correct file
ORA-01110: data file 69: ‘/dbmnt12/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00069’

Alright, it’s asking for one datafile (69) which is unknown to the controlfile. Yes, this is right. As we have created a test datafile before that controlfile backup.
As it’s of no importance, we’d dropped the datafile.

SQL> alter database datafile 69 offline drop;
Database altered.

Let’s try again to recover the database using the log file ‘/var/log/oracle/redo/onlinelog/redo09.log’.

SQL> recover database using backup controlfile ;
ORA-00279: change 32913922872 generated at 01/14/2014 11:17:33 needed for thread 1
ORA-00289: suggestion : /dbmount212/oracle/sairam/archivelog/1_75_833707787.arc
ORA-00280: change 32913922872 for thread 1 is in sequence #75

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/var/log/oracle/redo/onlinelog/redo09.log
Log applied.
Media recovery complete.

Okay, when provided exact filename of the logfile, it has accepted it and applied and came up, hence media recovery is completed.
Now we can go and try to open the database.

SQL> alter database open resetlogs;
Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /dbmount212/oracle/sairam/archivelog/
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

And we are up!
Below provided is the list of incarnation of the database and the last line with a new incarnation created on today’s date.

SQL> select * from V$DATABASE_INCARNATION;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
———— —————– ——— ———————– ——— ——- ———— —————— ————————–
1            602821 05-MAY-10                       1 12-MAR-08 PARENT     718177612                  0 NO
2         148027929 19-MAY-10                  602821 05-MAY-10 PARENT     719411741                  1 NO
3        9199856900 28-AUG-11               148027929 29-MAY-10 PARENT     760357356                  2 NO
4        1.9412E+10 11-AUG-12              9199856900 28-AUG-11 PARENT     791021290                  3 NO
5        3.2910E+10 09-DEC-13              1.9412E+10 1-AUG-12 PARENT     833707787                  4 NO
6        3.2914E+10 15-JAN-14              3.2910E+10 09-DEC-13 CURRENT    836892925                  5 YES

6 rows selected.

Hope That Helps
Prashant Dixit

Posted in Advanced | Tagged: , | 4 Comments »

Oracle Golden Gate: Tuning GG processes.

Posted by FatDBA on January 8, 2014

While doing a load tests on one of our databases for Golden Gate found replicate process doing an FTS while updating a big batch of rows in a table.

Problem : Replicate process was going for a  Full table Scan while Updating 804000 rows.

Source Database = 11.2.0.3

Below is update statement which was issued to update 804000 rows.
This completed in 15 seconds on SOURCE side DB.

sql_id :ghsutzgq0m8j9

PLAN_TABLE_OUTPUT
——————————————————————————————-
SQL_ID ghsutzgq0m8j9
——————–
UPDATE dummy11.OS_HISTORYSTEP_BLD SET ENTRY_ID=5555, STEP_ID=100000,
ACTION_ID=20000, OWNER=’JIGNESHKANKRECHA’,
START_DATE=TO_TIMESTAMP(‘5/28/2012 9:19:11.910000
PM’,’fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM’),
FINISH_DATE=TO_TIMESTAMP(‘5/28/2012 9:19:11.910000
PM’,’fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM’),
DUE_DATE=TO_TIMESTAMP(‘5/28/2012 9:19:11.910000 PM’,’fmMMfm/fmDDfm/YYYY
fmHH12fm:MI:SS.FF AM’), STATUS=’QC Validation failed’, CALLER=’KALIYA’
where ID  BETWEEN 1220000 AND 5999999

Plan hash value: 3578452229

—————————————————————————————–
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————–
|   0 | UPDATE STATEMENT   |                    |       |       |   376 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |    47 |  4277 |   376   (1)| 00:00:05 |
—————————————————————————————–

——————————————————————
Target Database = 10.2.0.4
Below statement Replicate executed, Because i had BATCHSQL replicate made BATCH of few rows and send for update that is the reason we see COUNT STOPKEY in execution plan.

PLAN_TABLE_OUTPUT
—————————————————————————————
SQL_ID b4t7hv6p53165
——————–
UPDATE “dummy11″.”OS_HISTORYSTEP_BLD” SET “ENTRY_ID” = :a1,”STEP_ID” =
:a2,”ACTION_ID” = :a3,”OWNER” = :a4,”START_DATE” = :a5,”FINISH_DATE” =
:a6,”DUE_DATE” = :a7,”STATUS” = :a8,”CALLER” = :a9 WHERE “ID” = :b0 AND ROWNUM = 1

Plan hash value: 185372276

——————————————————————————————
| Id  | Operation           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————
|   0 | UPDATE STATEMENT    |                    |       |       |   304 (100)|          |
|   1 |  UPDATE             | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   COUNT STOPKEY     |                    |       |       |            |          |
|   3 |    TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |     1 |    91 |   304   (1)| 00:00:04 |
——————————————————————————————

–Excerpt from Report file.
BATCHSQL BATCHESPERQUEUE 100, BATCHTRANSOPS 10000, OPSPERBATCH 10000, OPSPERQUEUE 100000

MAP resolved (entry dummy11.OS_HISTORYSTEP_BLD):
MAP “dummy11″.”OS_HISTORYSTEP_BLD”, TARGET dummy11.OS_HISTORYSTEP_BLD, KEYCOLS(ID);

xxxxxxx 12:00:30  WARNING OGG-00869  No unique key is defined for table ‘OS_HISTORYSTEP_BLD’. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
Using following columns in default map by name:
ID, ENTRY_ID, STEP_ID, ACTION_ID, OWNER, START_DATE, FINISH_DATE,
DUE_DATE, STATUS, CALLER
Using the following key columns for target table dummy11.OS_HISTORYSTEP_BLD: ID.

——–some statistic of replicat.
xxxxxxx 12:18:22  INFO    OGG-01408  Restoring current schema for DDL operation to [GGADMIN].
104578 records processed as of xxxxxxxxxxxxxxxx 12:21:05 (rate 84,delta 236)
457309 records processed as of xxxxxxxxxxxxxxxx 13:35:07 (rate 80,delta 79)
913119 records processed as of xxxxxxxxxxxxxxxx 13:39:47 (rate 153,delta 1628)
943118 records processed as of xxxxxxxxxxxxxxxx 13:41:25 (rate 155,delta 305)
953118 records processed as of xxxxxxxxxxxxxxxx 13:42:43 (rate 155,delta 127)
963118 records processed as of xxxxxxxxxxxxxxxx 13:44:25 (rate 154,delta 98)
973118 records processed as of xxxxxxxxxxxxxxxx 13:46:29 (rate 153,delta 80)
983118 records processed as of xxxxxxxxxxxxxxxx 13:48:57 (rate 151,delta 67)
993118 records processed as of xxxxxxxxxxxxxxxx 13:51:47 (rate 148,delta 58)
1003118 records processed as of xxxxxxxxxxxxxxxx 13:54:59 (rate 146,delta 51)
1013118 records processed as of xxxxxxxxxxxxxxxx 13:58:35 (rate 143,delta 46)

** xxxxxxx- Intentional

SOLUTION:
1) kill session of replicate which was running update statement, This will abend replicate.
2) Add Index on ID column which was in KEYCOLS Of MAP statement.
3) start replicate .

And we are back to the normal.

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:40

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:42

GGSCI (catlmsxt205) 6> INFO ALL
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:33:06

GGSCI (catlmsxt205) 7> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      00:00:03

Execution plan of Update after Index.

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————————————————————-
SQL_ID  b4t7hv6p53165, child number 0
————————————-
UPDATE “dummy11″.”OS_HISTORYSTEP_BLD” SET “ENTRY_ID” = :a1,”STEP_ID” =
:a2,”ACTION_ID” = :a3,”OWNER” = :a4,”START_DATE” = :a5,”FINISH_DATE” =
:a6,”DUE_DATE” = :a7,”STATUS” = :a8,”CALLER” = :a9 WHERE “ID” = :b0 AND ROWNUM =
1

Plan hash value: 2252287618

—————————————————————————————–
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————–
|   0 | UPDATE STATEMENT   |                    |       |       |     5 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|*  2 |   COUNT STOPKEY    |                    |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| OS_HIST_BLDIDX     |     1 |    91 |     3   (0)| 00:00:01 |
—————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – filter(ROWNUM=1)
3 – access(“ID”=TO_NUMBER(:B0))

Posted in Advanced | Tagged: | Leave a Comment »

RMAN-06900/RMAN-06901 and ORA-19921: maximum number of 64 rows exceeded

Posted by FatDBA on November 22, 2013

Received one of the odd and obscure RMAN/ORA errors of its kind While trying to connect with RMAN in Red Hat Enterprise Linux 5 and have wasted almost several minutes of my precious time, until i realized that there is one old RMAN session alive and was connected from the another terminal and was one day old before getting this error.

Okay so here is the case, while trying to connect with the target database in RMAN we have started recieving several RMAN and ORA errors …

[oracle@prashant1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Nov 22 21:30:38 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: QRCL (DBID=859215136)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 64 rows exceeded

RMAN> host;

Let’s explore more aboput the ora error using my favorite utility OERR

[oracle@prashant1 ~]$ oerr ORA 19921
19921, 00000, “maximum number of %s rows exceeded”
// *Cause:  The maximum number of rows in the V$RMAN_STATUS or V$RMAN_OUTPUT
//          table has been exceeded.
// *Action: Close some of existing and unused RMAN connections and sessions.

Let’s check how many and how old the rman sessions exists in my database.

[oracle@prashant1 ~]$ ps -ef|grep rman
oracle   25970 25861  0 Nov21 pts/1    00:00:50 rman target /
oracle   29999 29863  0 21:30 pts/4    00:00:00 rman target /
oracle   30113 30088  0 21:33 pts/4    00:00:00 grep rman

[oracle@prashant1 ~]$ date
Fri Nov 22 21:34:05 IST 2013

Alright, so there is one of the session with OS ID – 25970 running from November 21 from Terminal (tty 1) –
Let’s kill the session and try to connect with the Recovery Manager once again.

[oracle@prashant1 ~]$ kill -9 25970

[oracle@prashant1 ~]$ ps -ef|grep rman
oracle   29999 29863  0 21:30 pts/4    00:00:00 rman target /
oracle   30115 30088  0 21:34 pts/4    00:00:00 grep rman

Okay, so it’s gone and no more under the process list.

[oracle@prashant1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Nov 22 21:34:15 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: QRCL (DBID=859215136)
RMAN>

Okay and we have not getting list of those errors what we previously encountered and issue is F.I.X.E.D

Hope That Helps
Prashant Dixit

Posted in Advanced | Tagged: , | 1 Comment »

Standby/Dataguard:– RFS Process not working.

Posted by FatDBA on November 12, 2013

Not feeling well today!! 😦 , but as  It’s been a long time since i wrote my last article on Oracle database issues … here i am back again with one of the problem that we faced in our production database of our erstwhile customer, some time ago.
It was a typical Physical Standby environment with Maximum Performance model set. This is a story happened on one fine day when we started receiving some issues after a small network outage which blocked the redo stream to transfer logs from Production to standby server. ** * The bad part was  – the issue had happened during peak hours where we had confined amount of time to rectify the case.

Issue: RFS Process not working
Problem Description:
The filesystem containing the archive destination in DR server was not accessible. As a result the log shipping got stopped. We deferred the log shipping in the production server. After the filesystem was back, we enabled the log shipping but the RFS process in DR server was not running…The problem occurred when the log 24717 was being shipped… When we queried

SQL> Select status, sequence# from v$managed_standby;

The status for MRP showed that it was waiting for gap…We then manually shipped the log file and then applied… When we enabled the shipping we found that the RFS process was still not started…..There were no error in the alert log of DR…We found a trace file in the production server with the following message..

tkcrrsarc: (WARN) Failed to find ARCH for message (message:0xa)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0xa)

So we thought that the issue is with the archiver process….

Solution:
Check whether the archiver process is available for shipping log files. You can identify this by querying V$ARCHIVE_PROCESSES view.
SQL> Select * from v$archive_processes;

The output have the following columns:
Process: Indicates the process number.
Status: This should be ACTIVE
Log Sequence: Log sequence number of the log that is being shipped by the archiver. If it is not shipping any log then it should be 0.
State: This should be IDLE if the archiver is not shipping any log. If it is shipping any log then its state is BUSY.

In our case we had two archiver process running.

The status of both the arch process is ACTIVE.
The log sequence of First arch process is 0 and its state is IDLE. Hence it is healthy. However the log sequence of 2nd arch process is 24717 and its state is BUSY.

This was interesting because the problem occurred when the arch process was transferring the log 24717. This log was then manually shipped and applied. But the process still shows that it was shipping the 24717 log…
So we thought of increasing the arch processes. We increased the arch process from two to four.
SQL> alter system set log_archive_max_processes=4 scope=both;

We queried the v$archive_processes, the 3rd and 4th arch process was ready to ship the log files 24718 and 24719 logs respectively with their corresponding state as IDLE…
We enabled the log shipping and the RFS process in DR was started and the log shipping went smoothly.

However the 2nd arch process still was showing the same log sequence (24717) and state (BUSY)…. We then killed that archiver process…. And we were all good!

Hope That Helped
Prashant Dixit

Posted in Advanced | Tagged: , | 5 Comments »

PRVF-7531 – Oracle 11.2 Prerequisite Checks fail (MS Windows Error)

Posted by FatDBA on October 8, 2013

Today i got a call from one of my good friend who was trying to install Oracle 11g on Windows 7 platform (And was very furious about — Why oracle website don’t have version 10g available anymore, after the release of latest 12c … 🙂 … ) and was getting errors during pre checks during OUI calls.

Exact Error:
On  Step 7 of 10,

All Prerequisite Checks fail.

Physical Memory                        Failed
Available Physical Memory         Failed
Swap Size                                  Failed
Free Space                                 Failed
Architecture                                Failed
Environment variable PATH        Failed

Clicking on the more details link, shows:
– PRVF-7531 : Physical memory check cannot be performed on node “machine-name”
– Cause: Could not perform check of physical memory on the node indicated.
– Action: Ensure ability to access the node specified and view memory information.

 

image001

 

image002
With many errors related to Physical/Swap Size, initially i thought that i could be due to insufficient memory on the system, But he has 4GB of RAM available.
Next i asked if he is trying to install the software on any Admin controlled machine, and my guess was right – He was.

So, all of these errors left us bewildered and with very less details available. Ultimately, MOS came to rescue.
Found one of the Metalink note for his issue — Doc ID 1086063.1

Cause
The Workstation or Server services have not been started.

Solution
Make sure both the “WORKSTATION” and “SERVER” services are started.
Rerun the setup program.
Now all the Prerequisite Checks Succeed.

– The Workstation or Server services need to be started for Oracle to communicate with the machine, to make sure all the prerequisite checks are performed.
– It has been reported, the TCP/IP NetBIOS Helper service should also be started, to pass the Pre-Req checks.

Found SERVER service disabled on his machine and is the reason which is obstructing the pre checks to complete.

Posted in Advanced | Tagged: | 6 Comments »

ORA-00904: “POLTYP”: invalid identifier — During EXPORT

Posted by FatDBA on October 6, 2013

Error:
EXP-00008: ORACLE error 904 encountered
ORA-00904: “POLTYP”: invalid identifier

When:  During EXPORT operations.
This Scenario: During export of rman recovery catalog (Production).

$ exp rman/xxxxxx@rxxxxxx FILE=catalogexport.dmp OWNER=rman

Export: Release 11.2.0.3.0 – Production on Sun Oct 6 10:50:22 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user RMAN
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user RMAN
About to export RMAN’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export RMAN’s tables via Conventional Path …
EXP-00008: ORACLE error 904 encountered
ORA-00904: “POLTYP”: invalid identifier
EXP-00000: Export terminated unsuccessfully

This error occurs if you try the old export command from an 11g client against a database on version 10g or lower. The export command runs a query against a table called EXU9RLS in the SYS schema. On 11g this table was expanded with the column POLTYP and the export command (exp) expects to find this column. This should not be much of a problem since Data Pump export can be used.

SQL> desc EXU9RLS
Name                                                                                                  Null?    Type
—————————————————————————————————– ——– ——————————————————————–
OBJOWN                                                                                                NOT NULL VARCHAR2(30)
OBJNAM                                                                                                NOT NULL VARCHAR2(30)
POLGRP                                                                                                NOT NULL VARCHAR2(30)
POLICY                                                                                                NOT NULL VARCHAR2(30)
POLOWN                                                                                                NOT NULL VARCHAR2(30)
POLSCH                                                                                                         VARCHAR2(30)
POLFUN                                                                                                NOT NULL VARCHAR2(30)
STMT                                                                                                           VARCHAR2(34)
CHKOPT                                                                                                NOT NULL NUMBER
ENABLED                                                                                               NOT NULL NUMBER
SPOLICY                                                                                                        NUMBER
 POLTYP                                                                                                         VARCHAR2(33)

Resolution:
Since there was no code added in export to extract the RLS policy type, the fix for Bug 7568350 introduces a new column POLTYP in EXU9RLS view that is associated with the RLS policy type.

Fixed: Patch 7568350
Link:
https://support.oracle.com/epmos/faces/ui/patch/PatchDetail.jspx?_afrLoop=15303088597334&patchId=7568350&_afrWindowMode=0&_adf.ctrl-state=x42u0k77b_206

Doc ID: Doc ID 784038.1

Posted in Advanced | Tagged: | Leave a Comment »

VMWare: Reason: “Cannot open the disk, Reason: Failed to lock the file”.

Posted by FatDBA on August 25, 2013

Today while starting one of my Test machine/instance which is a part of an VMWare (This Team includes a total of 3 virtual machines) has started giving an error message which reads:

“Cannot open the disk: ‘H:\Machine ………….’ or one of the snapshot disks it depends on.Reason: Failed to lock the file”.

errrrrrro
And this error restricts us to open the same machine and kept on throwing the same again and again.

Error:
After reading errors it seems to be related with the locks that machines acquire in VMWare Team environment in order to access resources.

Resolution:
While we start our virtual machines there are many different folders related to locks created which contains .lck (Lock Files) and are used by the machine during the operation i.e M30076.lck, Mx456.lck et.
These locks are dynamic in nature and auto refreshes after some time. So, i removed/renamed all of the lock files (.lck) existed under all of these folder and tried the machine again and as what i’ve expected, it started working back again.

* Note: Performing these steps on PROD systems could be dangerous and can cause serious performance/hang issues. Performing such steps always requires recommendations from the VMWARE support.

Thanks
Prashant Dixit
“Sharing is Good”

Posted in Advanced | Tagged: , , | Leave a Comment »

Using DBCA (DB Create) in Silent Mode.

Posted by FatDBA on August 23, 2013

DBCA is one of the most important and easiest utility to create new databases. Most of us have used it during our careers in GUI mode, but we have an option to launch it in non-graphical mode (Non-Interactive Mode) as well. Below provided steps can be performed to create database in silent/non-interactive mode. So you can launch DBCA in text mode from your favorite ssh client/terminal like putty, secureCRT, SSH Tectia Client etc. and can save yourself from that ‘old’ manual DB create methods (Using catprocs, catalog sql’s and creating new dump directories).

To create database: This requires a response file which includes all necessary details like GGDBNAME (Global Database Name), SID, Listener Name, SYS/SYSTEM/DBSNMP/SYSMAN Passwords etc.
Browser the response file (ORACLE_HOME/assistants/dbca) and make all mandatory changes.

Step 1: Location of response file
[oracle@prashant dbca]$ pwd
/u01/app/oracle/product/11.2.0/db_1/assistants/dbca    — Default Location of Response File.

[oracle@prashant dbca]$ ls
dbca.rsp  doc  jlib  logs  templates

Create a copy of original file.
[oracle@prashant dbca]$ cp dbca.rsp  dbcacreatedb.rsp

[oracle@prashant dbca]$ ls
dbcacreatedb.rsp  dbca.rsp  doc  jlib  logs  templates

Step 2:
Edit all mandatory changes in response file.
GDBNAME = “silent”
SID = “silent”
SYSPASSWORD = “oracle90”
SYSTEMPASSWORD = “oracle90”
SYSMANPASSWORD = “oracle90”
DBSNMPPASSWORD = “oracle90”
CHARACTERSET   = “US7ASCII” (Default)
NATIONAL CHARACTERSET = “UTF8” (Default)

Although there are various sections inside the response file “.rsp” file like createdatabase, deleteDatabase, createTemplateFromDB or createCloneTemplate. In this post we have talk only about ‘createDatabase‘ and is the area of interest (Used to create new database).

All Examples :
—————————–
1. dbca -progress_only -responseFile <response file>
Display a progress bar depicting progress of database creation
process.

2. dbca -silent -responseFile <response file>
Creates database silently. No user interface is displayed.

3. dbca -silent -createDatabase -cloneTemplate
-responseFile <response file>    
Creates database silently with clone template. The template in
responsefile is a clone template.

4. dbca -silent -deleteDatabase -responseFile <response file>
Deletes database silently.

Step: 3

So, we are going to install the database using ‘STEP 3’ without using -cloneTemplate. This will create database silently and will show progress in percentages.
[oracle@prashant dbca]$ dbca -silent -createDatabase -responseFile dbcacreatedb.rsp
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/silent/silent.log” for further details.

It seems that Database Creation is successful completed. Let’s check creation  log file.
File: “/u01/app/oracle/cfgtoollogs/dbca/silent/silent.log”

Okay, while checking log file i found some new entries after creating database:

Database creation complete. For details check the log-files at:
/u01/app/oracle/cfgtoollogs/dbca/silent.
Database Information:
Global Database Name:silent
System Identifier(SID):silent

[oracle@prashant dbca]$ cd /u01/app/oracle/cfgtoollogs/dbca/silent
[oracle@prashant silent]$ ls -ltr
total 104
-rw-r—– 1 oracle oinstall  1372 Aug 23 11:47 rmanRestoreDatafiles.sql
-rw-r—– 1 oracle oinstall   330 Aug 23 11:49 CloneRmanRestore.log
-rw-r—– 1 oracle oinstall  1135 Aug 23 11:50 cloneDBCreation.log
-rw-r—– 1 oracle oinstall     8 Aug 23 11:50 postScripts.log
-rw-r—– 1 oracle oinstall     6 Aug 23 11:50 lockAccount.log
-rw-r—– 1 oracle oinstall   349 Aug 23 11:51 postDBCreation.log
-rw-r—– 1 oracle oinstall   458 Aug 23 11:51 OraDb11g_home1_silent_creation_checkpoint.xml
-rw-r—– 1 oracle oinstall 66392 Aug 23 11:51 trace.log
-rw-r—– 1 oracle oinstall   654 Aug 23 11:51 silent.log

* There are some interesting files generated during DB creation like CloneRmanRestore.log, cloneDBCreation.log, postDBCreation.log and rmanRestoreDatafiles.sql.

Let’s check our new database ‘Silent’.
[oracle@prashant silent]$ ps -ef|grep pmon
oracle   12671     1  0 Aug20 ?        00:00:03 ora_pmon_sairam
oracle   20771     1  0 11:50 ?        00:00:00 ora_pmon_silent
oracle   22315 18308  0 13:06 pts/2    00:00:00 grep pmon

Okay we have a PMON for databse (Silent).
[oracle@prashant silent]$ . oraenv
ORACLE_SID = [sairam] ? silent
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@prashant silent]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 23 13:07:04 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
SILENT    READ WRITE

 

Thanks
Prashant Dixit
“Sharing is Good”

Posted in Advanced, Basics | Tagged: , , | Leave a Comment »

« Previous Entries
Next Entries »