Tales From A Lazy Fat DBA

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

Something Strikingly odd with Oracle: DB with Two ‘Current’ log-files!!

Posted by FatDBA on January 8, 2014

Something real uncanny happened with one of our databases recently: There were 2 log-files with CURRENT status: Strange right!!!

SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024 as SIZE_MB,MEMBERS,STATUS from v$log;
GROUP#  SEQUENCE#      SIZE_MB    MEMBERS STATUS
———- ———- ———- ———-   —————
1       15651       50          2 INACTIVE
3       15653       50          2 CURRENT
2       15652       50          2 CURRENT
4       15650       50          2 INACTIVE

Found the ‘Notorious’ Generic error ORA-600 in alert log :
ORA-00600: internal error code, arguments: [3705], [1], [3], [2], [8], [], [], []

We had to perform point-in-time recovery up to 15652 sequence and we were all good.

Hope That Helps
Prashant Dixit

Posted in Uncategorized | Tagged: | Leave a Comment »

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 »

‘Weird’ Error with Oracle’s “Log Errors” potentiality: — PL/SQL: ORA-00972: identifier is too long

Posted by FatDBA on November 20, 2013

Going to discuss one of the uncanny situation faced by me couple of minutes ago on one of my Test Machine. (Yes!, work on Vacations as well 🙂 )

One of the coolest features introduced in Oracle 10gR2 is the ability to log errors in large DML commands such as
“INSERT INTO table_name SELECT ….”.

One silly scenario I just came across while implementing the following.
I wanted to create an error log on a table who’s name is 30 characters long

PRASHANT.DIXIT_DEV07> BEGIN
2        DBMS_ERRLOG.CREATE_ERROR_LOG(‘MYREALLY_LONG_TABLE_NAME_HERE’);
3  END;
4  /

PL/SQL procedure successfully completed.

This command created an error log table named “ERR$_MYREALLY_LONG_TABLE_NAME” (note that the “_HERE” has been removed).
I then tried to compile a stored procedure that had a command like the following

“INSERT INTO MYREALLY_LONG_TABLE_NAME_HERE (cola, colb)
SELECT a, b FROM ….
LOG ERRORS
REJECT LIMIT UNLIMITED”

No luck, I get a “PL/SQL: ORA-00972: identifier is too long” error when trying to compile.

Looking through metalink and Google didn’t help much, so I then tried supplying the name of the error log table to the DBMS_ERRLOG.CREATE_ERROR_LOG procedure, once I did that I had to modify the “INSERT INTO” command to tell it where to log the errors.

Note the “INTO [schema.]table” section below.

LOG ERRORS [INTO [schema.]table]
[ (simple_expression) ]
[ REJECT LIMIT  {integer|UNLIMITED} ]

In a nutshell, I guess Oracle is smart enough on the creation of the table to truncate the error log table at 30 characters but it’s not smart enough yet when actually using the “LOG ERRORS” feature.
I haven’t checked the same in 11g and until then I’m going consider using the “INTO” clause as a best practice when using the “LOG ERRORS” feature.

Hope That Helps
Prashant Dixit

Posted in Uncategorized | Leave a 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 »

Viagra For SQL’s. DBMS_STATS (NO_INVALIDATE argument/parameter) ?

Posted by FatDBA on August 20, 2013

While trying to get any resolution for one of our old problem (Database Hangs while Gathering Stats – Schema Wise), one of the oracle support analyst recommends us to gather stats next time with no_invalidate parameter. We actually never tested/tried this option with DBMS_STATS procedure before.

Let me put some light over this argument/parameter and how it can act as a Viagra for your SQL’s. Fast, Instant …

DBMS_STATS provides us an argument to control invalidation of SQL plans available and are sitting in your Library Cache and are used by your CBO. This is quite decisive when used to control SQL executions and query optimizations and performance.

It has three arguments types.
dbms_stats.set_param (no_invalidate false):  
This option will orders that a change in statistics always invalidates the dependent sql cursors/plans immediately upon a change to the statistics. Very much similar to ‘ALTER SYSTEM FLUSH SHARED POOL’.
dbms_stats.set_param (no_invalidate true):  In this case, a change in statistics will never nullify or voids current SQL execution plans in Library Cache.
dbms_stats.set_param (no_invalidate dbms_stats.auto_invalidate):  This is the default (Since Oracle 10g). This default type says that Oracle will not invalidate the old plan immediately after new statistics are generated.

Now a question:
After how many seconds, minutes, hours or probably days, old plans gets invalidate in case of no_invalidate.auto_invalidate which is a Default ??
Ans: Many claims that a hidden parameter _optimizer_invalidation_period dictates the maximum time before invalidation. Which is default of 18000 seconds (5 hours)

SQL> select     rpad(i.ksppinm, 35) || ‘ = ‘ || v.ksppstvl parameter,
i.ksppdesc description,
v.ksppstdf dflt
from    x$ksppi         i,
x$ksppcv        v
where   v.indx = i.indx
and     v.inst_id = i.inst_id
and     i.ksppinm like ‘_optimizer_invalidation_period’
order by 1;

PARAMETER                                                                                DESCRIPTION                                                                             DFLT
—————————————————————–    ——————————————————————————————-    ———–
_optimizer_invalidation_period      = 18000            time window for invalidation of cursors of analyzed objects         TRUE

Thanks
Prashant Dixit
“Sharing is Good”

Posted in Uncategorized | 1 Comment »

« Previous Entries
Next Entries »