Tales From A Lazy Fat DBA

ORA-01157: cannot identify/lock data file (Error)

Posted by FatDBA on September 11, 2012

Today while working i mistakenly deleted one DataFile which is of no use and was not part of any backup as well. I was happy and was good until i started recieving a weird error which is related with this old Datafile which i’ve already deleted from my Database Box and it not allowing me to start the DB instance and throwing errors:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Sep 11 09:57:30 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

ERROR at line 1:
ORA-01157: cannot identify/lock data file 11 – see DBWR trace file
ORA-01110: data file 11: ‘/u01/appwiz.dbf’
So it clearly says that it’s all because of that recent deletion of Data File 11 ‘Appwiz.dbf’ which triggered this problem.
Resolution: I’ve offline deleted that datafile (11) and tried starting the instance and finally

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

SQL> alter database open;
Database altered.

SQL> select status from v$instance;

STATUS
————
OPEN

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

RFS Process Missing – Alert Log Trace reads * Error in File sid_rfs_3724.trc *

Posted by FatDBA on September 4, 2012

Problem: If Alert Log File Reads – “Errors in file /u01/app/oracle/admin/qrcl/udump/qrcl_rfs_3724.trc” and RFS process is missing at the Standby end and you discovered difference in Archived Log Sequence.

Resolution: Export Archive logs from Primary DB side to Standby location and start recovery process on the Auxiliary end.

Exit managed recovery mode, add the logs, and then reinitiate managed recovery:

alter database recover managed standby database cancel;

[oracle@prashant1 2012_09_04]$ pwd
/u01/app/oracle/flash_recovery_area/QRCL/archivelog/2012_09_04
[oracle@prashant1 2012_09_04]$ scp * oracle@prashant2:/u01/app/oracle/flash_recovery
oracle@prashant2’s password:
o1_mf_1_293_84d32nq5_.arc
o1_mf_1_294_84d32qfx_.arc
o1_mf_1_295_84d3gr6s_.arc
o1_mf_1_296_84d3gz6b_.arc
o1_mf_1_297_84d3wt62_.arc
o1_mf_1_298_84d3x749_.arc
o1_mf_1_299_84d45g5x_.arc
o1_mf_1_300_84d52r4t_.arc
o1_mf_1_301_84d61r6n_.arc
o1_mf_1_302_84d61scr_.arc
o1_mf_1_303_84d61zfd_.arc
o1_mf_1_304_84d62c0v_.arc alter database recover managed standby database disconnect;

Start the managed recovery operation:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Recheck Log Sequence and Status/Process from v$Managed_standby and you’ll find the RFS process back again along with ARCH & MRP/MRP0.

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

ORA-00265: Cannot set archivelog (FIX)

Posted by FatDBA on August 28, 2012

Today while turning on Archivelog facility on one of my Oracle Database i recieved an error while reads “instance recovery required, cannot set ARCHIVELOG mode” and i find a very unique way to get rid of this problem.

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

Resolution Steps:
SHUTDOWN -> STARTUP -> SHUTDOWN IMMEDIATE -> STARTUP MOUNT -> alter database archivelog

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

SQL> startup
ORACLE instance started.
Total System Global Area 306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             104859096 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             109053400 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4

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

Standby (DG) – Processes Explained.

Posted by FatDBA on August 27, 2012

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

PROCESS   STATUS
——— ————
ARCH      CONNECTED
ARCH      CONNECTED
RFS         IDLE
MRP0    WAIT_FOR_LOG

RFS: Is Remote File Server. RFS process writes the redo data to either archived redo log files or standby redo log files. the RFS process writes the redo data to either archived redo log files or standby redo log files. However, if you use standby redo log files, you can enable real-time apply, which allows Data Guard to recover redo data from the current standby redo log file as it is being filled up by the RFS process.

LGWR SYNC Archival to a Remote Destination with Standby Redo Log Files

————————

LGWR ASYNC Archival with Network Server (LNSn) Processes

Real Time Feature: If the real-time apply feature is enabled, log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.

Use the ALTER DATABASE statement to enable the real-time apply feature, as follows:

For physical standby databases, issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.
For logical standby databases, issue the ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE statement.

MRP/MRP0: Stands for Managed Recovery Process and sits between Standby Database and redo-log files/Archives redo. MRP is the process which will apply the changes from standby redolog files to standby database.

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

What is Heartbeat in Oracle ?

Posted by FatDBA on August 27, 2012

The heartbeat functionality performs heartbeat checks towards HTTP-based network nodes on behalf of a plug-in. When a heartbeat fails, the plug-in is set to state INACTIVE. The heartbeat functionality will continue trying to connect to the node and, when a positive answer is received, the plug-in re-enters the ACTIVE state.

Is a part of Oracle Net 8/Net networking suite to provide connectivity.

@This is what i found while exploring alert.log file of one of my database.

Sun May 27 11:53:23 2012
ARC0: Becoming the heartbeat ARCH

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

Heartbeat Error – 16009 (PING[ARC1]) – STANDBY DB

Posted by FatDBA on August 27, 2012

Error: PING[ARC1]: Heartbeat failed to connect to standby ‘qrcl’. Error is 16009. (Alert Log Traces)

Always check Standby Archive Destination before you start troubleshooting.

SQL> show parameter archive

NAME                                 TYPE        VALUE
———————————— ———– ——————————
log_archive_dest_1                   string      location=use_db_recovery_file_
dest, valid_for=(ALL_LOGFILES,
ALL_ROLES)
log_archive_dest_2                   string      service=qrcl optional reopen=20

In this example both of my Primary and Secondary machine has same same name ‘qrcl’. To reduce conflicts I’ve renamed the standby to ‘qrcl_std’ and changed the same in all files including tnsnames.ora but somehow failed to alter the service name in parameter file for log_archive_dest_2 (For Standby) and that lead to this Heart beat error.

Also reflects when crosschecked log archive destination 2 parameter using ‘show parameter’

Resolution:

SQL> alter system set log_archive_dest_2 =’service=qrcl_std’;
System altered.

SQL> show parameter log_archive_dest;

Changed and this will fix this error. Verify it using ‘archive log list’ command on both primary and secondary machines to check log sequence number.

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

RMAN: Catalog Creation.

Posted by FatDBA on August 24, 2012

Create Tablespace to hold User Details.

SQL> create tablespace rman datafile ‘/u01/app/oracle/oradata/rmandix.dbf’ size 50m autoextend on;

Tablespace created.

create User to perform all RMAN related operations and activities.

SQL> create user rman identified by oracle90 default tablespace rman;
User created.

SQL> grant connect, resource, recovery_catalog_owner to rman;
Grant succeeded.

SQL> conn rman/oracle90
Connected.

[oracle@localhost ~]$ rman target / catalog rman/oracle90@orcl;

Recovery Manager: Release 10.2.0.1.0 – Production on Thu Aug 23 01:09:39 2012

connected to target database: ORCL (DBID=1301453781)
connected to recovery catalog database
RMAN> create catalog;
recovery catalog created
RMAN> create catalog;
ORACLE error from recovery catalog database: ORA-00955: name is already used by an existing object

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06433: error installing recovery catalog
RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> register database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of register command on default channel at 08/23/2012 01:10:46
RMAN-20002: target database already registered in recovery catalog

RMAN> upgrade catalog;

recovery catalog upgraded to version 10.02.00.00
DBMS_RCVMAN package upgraded to version 10.02.00.00
DBMS_RCVCAT package upgraded to version 10.02.00.00

Posted in Advanced | Leave a Comment »

ADDM (Automatic Database Diagnostic Monitor)

Posted by FatDBA on August 24, 2012

ADDM (Automatic Database Diagnostic Monitor) can be describe as the database’s doctor. It allows an Oracle database to diagnose itself and determine how potential problems could be resolved. ADDM runs automatically after each AWR statistics capture, making the performance diagnostic data readily available.
It Explains the problem and recommendations to fix the flaw in Database.
Example:
FINDING 1: 59% impact (944 seconds)
———————————–
The buffer cache was undersized causing significant additional read I/O.

   RECOMMENDATION 1: DB Configuration, 59% benefit (944 seconds)
      ACTION: Increase SGA target size by increasing the value of parameter
         “sga_target” by 28 M.

   SYMPTOMS THAT LED TO THE FINDING:
      Wait class “User I/O” was consuming significant database time. (83%
      impact [1336 seconds])

[oracle@localhost admin]$ ls -ltr *add*
-rw-r—– 1 oracle oinstall 13103 Oct 22 2002 owaddemo.sql
-rw-r—– 1 oracle oinstall 3168 Oct 15 2003 addmrpt.sql
-rw-r—– 1 oracle oinstall 4748 Jan 5 2005 addmrpti.sql
-rw-r—– 1 oracle oinstall 1249 Jun 28 2005 prvthadd.plb
-rw-r—– 1 oracle oinstall 55186 Jun 28 2005 prvtbadd.plb
[oracle@localhost admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Aug 24 01:49:54 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> @addmrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
———– ———— ——– ————
1301453781 ORCL 1 orcl
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
———— ——– ———— ———— ————————
* 1301453781        1 ORCL         orcl         localhost.localdomain

Using 1301453781 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
———— ———— ——— —————— —–
orcl         ORCL               226 22 Aug 2012 03:42      1
                                         236 23 Aug 2012 05:30      1
                                           237 23 Aug 2012 06:30      1
                                         238 23 Aug 2012 07:30      1

239 24 Aug 2012 01:45 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
Begin Snapshot Id specified:

Enter value for end_snap:

Alternate Ways to Generate Report:

===============================

DBA_ADVISOR_FINDINGS – To get the findings in the Database.
DBA_ADVISOR_RECOMMENDATIONS – To get the Recommendations on the findings.

SQL> SELECT * FROM DBA_ADVISOR_dEFINITIONS;

ADVISOR_ID ADVISOR_NAME                     PROPERTY
———- —————————— ———-
         1 ADDM                                    1
         2 SQL Access Advisor                     15
         3 Undo Advisor                            1
         4 SQL Tuning Advisor                      7
         5 Segment Advisor                         3
         6 SQL Workload Manager                    0
         7 Tune MView                             31

7 rows selected.

Posted in Advanced | Leave a Comment »

Explain Plan.

Posted by FatDBA on August 23, 2012

Explain Plan is a great way to tune your queries.
As a bonus for using Explain Plan, you will learn more about how the DBMS works “behind the scenes”, enabling you to write efficient queries the first time around.

Explain Plan executes your query and records the “plan” that Oracle devises to execute your query. By examining this plan, you can find out if Oracle is picking the right indexes and joining your tables in the most efficient manner. There are a few different ways to utilize Explain Plan.

The first thing you will need to do is make sure you have a table called plan_table available in your schema.

If this table is not there run this script provided by oracle to create that table
ORACLE_HOME/rdbms/admin/utlxplan.sql .. for UNIX plat formas and
ORACLE_HOME\rdbms\admin\utlxplan.sql .. for WINDOWS platforms

SQL> select * from plan_table;

no rows selected

SQL> explain plan for select * from etr where team=’cis’;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3490786915

—————————————————————————-
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |
—————————————————————————-
|   0 | SELECT STATEMENT |        |     2 |    30 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| ETR |     2 |    30 |     3   (0)| 00:00:01 |
—————————————————————————-

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“TEAM”=’cis’)

13 rows selected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3490786915

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“TEAM”=’cis’)

13 rows selected.

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

RMAN (Catalog) Error: ORA-06508 – “RMAN.DBMS_RCVMAN”

Posted by FatDBA on August 22, 2012

Error Recieved while trying to access Catalog:

[oracle@localhost admin]$ rman catalog rman/oracle90@orcl

Recovery Manager: Release 10.2.0.1.0 – Production on Wed Aug 22 00:56:29 2012

connected to recovery catalog database
ORACLE error from recovery catalog database: ORA-04067: not executed, package body “RMAN.DBMS_RCVMAN” does not exist
ORA-06508: PL/SQL: could not find program unit being called: “RMAN.DBMS_RCVMAN”

Resolution: Run ‘Upgrade Catalog’ command to fix the problem with Recovery Manager.

RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 10.02.00.00
DBMS_RCVMAN package upgraded to version 10.02.00.00
DBMS_RCVCAT package upgraded to version 10.02.00.00

RMAN> exit
Recovery Manager complete.
[oracle@localhost admin]$ rman catalog rman/oracle90@orcl

Recovery Manager: Release 10.2.0.1.0 – Production on Wed Aug 22 00:59:20 2012

connected to recovery catalog database

RMAN>

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

« Previous Entries

Next Entries »

Tales From A Lazy Fat DBA

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

Likes

ORA-01157: cannot identify/lock data file (Error)

RFS Process Missing – Alert Log Trace reads * Error in File sid_rfs_3724.trc *

ORA-00265: Cannot set archivelog (FIX)

Standby (DG) – Processes Explained.

What is Heartbeat in Oracle ?

Heartbeat Error – 16009 (PING[ARC1]) – STANDBY DB

RMAN: Catalog Creation.

ADDM (Automatic Database Diagnostic Monitor)

Explain Plan.

RMAN (Catalog) Error: ORA-06508 – “RMAN.DBMS_RCVMAN”

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

Likes

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: