Archive for August, 2012

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 »

CSSCAN & CSALTER: Character Set Change Utility.

Posted by FatDBA on August 21, 2012

—————————–
CSSCAN & CSALTER
—————————–
The character set migration utility schema is installed by running the “$ORACLE_HOME/rdbms/admin/csminst.sql” script in SQL*Plus as the SYS user. Once the schema is present, the character set scanner should work normally.

Username: sys as sysdba

Password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

(1)Full database, (2)User, (3)Table, (4)Column: 1 > 2

Current database character set is JA16SJIS.

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 > 32

Enter user name to scan: > larry tonchar
CSS-00124: user larry tonchar not found

Enter user name to scan: > larry

Enumerating tables to scan…

. process 2 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAHAAAAMJAAA]
. process 1 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAHAAAAAJAAA]
. process 3 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAIxAAA]
. process 3 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAAWJAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAATpAAA]
. process 4 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAASpAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAAMBAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAKZAAA]
. process 5 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAJxAAA]
. process 4 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAEAAAAWpAAA]
. process 6 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAIJAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_01[AAANXxAAEAAAAK5AAA]
. process 7 scanning LARRY.SYS_EXPORT_SCHEMA_01[AAANSoAAEAAAAGJAAA]
. process 6 scanning LARRY.SYS_EXPORT_SCHEMA_01[AAANSoAAEAAAAHxAAA]
. process 8 scanning LARRY.PLAN_TABLE[AAANieAAEAAAAXBAAA]
. process 2 scanning LARRY.SYS_EXPORT_FULL_02[AAANgbAAHAAAAPJAAA]
. process 4 scanning LARRY.DEPT1[AAANQuAAEAAAAIBAAA]
. process 9 scanning LARRY.ETR[AAAM/uAAEAAAAGBAAA]
. process 7 scanning LARRY.SALARY[AAAM75AAEAAAAHpAAA]
. process 4 scanning LARRY.EMP1[AAAM9RAAEAAAAH5AAA]
. process 5 scanning LARRY.PERSON[AAAM/xAAEAAAAGhAAA]

Creating Database Scan Summary Report…

Creating Individual Exception Report…

Scanner terminated successfully.
[oracle@localhost admin]$ sqlplus / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

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

SQL> startup restrict
ORACLE instance started.

Total System Global Area 306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             113247704 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

SQL> @csalter.plb
4 rows created.

Function created.

Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)? Y
old 6: if (UPPER(‘&conf’) <> ‘Y’) then
new 6: if (UPPER(‘Y’) <> ‘Y’) then
Checking data validility…
Exceptional data found in scanner result

PL/SQL procedure successfully completed.

Checking or Converting phrase finished successfully
Database (national) character set will be altered
CSALTER finished successfully.

PL/SQL procedure successfully completed.

4 rows deleted.

Function dropped.

Procedure dropped.

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

Posted in Advanced | Tagged: character set | Leave a Comment »

RMAN Error during Backup – “ORA-19809: limit exceeded for recovery files”

Posted by FatDBA on August 21, 2012

ORA-19804: cannot reclaim string bytes disk space from string limit
Reason: Oracle cannot reclaim disk space of specified bytes from the DB_RECOVERY_FILE_DEST_SIZE limit.

alter system set db_recovery_file_dest_size=<size> scope=both
If receives below error while altering DB Recovery Dest Size ex:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

Try:
– create spfile from pfile;
– shutdown immediate
– startup

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

« Previous Entries

Tales From A Lazy Fat DBA

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

Likes

Archive for August, 2012

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”

CSSCAN & CSALTER: Character Set Change Utility.

RMAN Error during Backup – “ORA-19809: limit exceeded for recovery files”

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

Likes

Archive for August, 2012

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: