Tales From A Lazy Fat DBA

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.

[oracle@localhost admin]$ csscan
Character Set Scanner v2.1 : Release 10.2.0.0.0 – Production on Tue Aug 21 09:43:59 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.

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

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 21 09:47: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> 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 »

Character Set Change.

Posted by FatDBA on August 17, 2012

SQL> SELECT * FROM nls_database_parameters WHERE parameter = ‘NLS_CHARACTERSET’;

PARAMETER VALUE
—————————— —————————————-
NLS_CHARACTERSET WE8ISO8859P1

SQL> SELECT * FROM nls_database_parameters WHERE parameter = ‘NLS_CHARACTERSET’;
PARAMETER VALUE
—————————— —————————————-
NLS_CHARACTERSET WE8ISO8859P1

Modify profile and add environmental variable – NLS_LANG
[root@localhost ~]# vi /etc/profile
NLS_LANG=’AMERICAN_AMERICA.we8iso8859p1′; export NLS_LANG

[oracle@localhost ~]$ echo $NLS_LANG
AMERICAN_AMERICA.we8iso8859p1

To change the database character set, perform the following steps:

Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.

Complete the following statements:

STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET new_character_set;

SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;

STARTUP;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
System altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> ALTER DATABASE CHARACTER SET WE8ISO8895P1;
ALTER DATABASE CHARACTER SET WE8ISO8895P1
*
ERROR at line 1:
ORA-12715: invalid character set specified

If getting below provided error while change perform following steps to fix:
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

Resolution:
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE JA16SJIS;
Database altered.

SQL> SELECT * FROM nls_database_parameters WHERE parameter = ‘NLS_CHARACTERSET’;

PARAMETER
——————————
VALUE
——————————————————————————–
NLS_CHARACTERSET
JA16SJIS
—– DONE—-

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

Nofilenamecheck in RMAN

Posted by FatDBA on August 17, 2012

If you want the duplicate filenames to be the same as the target filenames, and if the databases are in different hosts, then you must specify NOFILENAMECHECK.

If duplicating a database on the same host as the target database, do not specify the NOFILENAMECHECKoption. Otherwise, RMAN may signal this error:

RMAN-10035: exception raised in RPC: ORA-19504: failed to create file 
            "/oracle/dbs/tbs_01.f" 
ORA-27086: skgfglk: unable to lock file - already in use 
SVR4 Error: 11: Resource temporarily unavailable 
Additional information: 8 
RMAN-10031: ORA-19624 occurred during call to 
DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE

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

Manual Creation of Database.

Posted by FatDBA on August 6, 2012

Below provided are Steps to create a Database manually 9Without using any GUI tool – DBCA)

Manual Creation Of Database
================================

Create Directories in ORACLE_BASE to install physical files.

[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/adump
[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/bdump
[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/cdump
[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/udump

[oracle@localhost root]$ mkdir -p /u01/app/oracle/oradata/doom

Create parameter file for new database
[oracle@prashant2 dbs]$ cp initarcl.ora initdoom.ora

Note: Use Find and Replace command in VI in order to replace all old SID terms from the param file.
ex: :%s/OLD/NEW/g
Capital ZZ to save all changes made.

create Password File using ORAPWD Utility.
[oracle@prashant2 dbs]$ orapwd file=orapwdoom password=oracle90 entries=19

Export newly created SID
[oracle@prashant2 dbs]$ export ORACLE_SID=doom

[oracle@prashant2 dbs]$ echo $ORACLE_SID
doom

Create a script/text file to create Database (Contains Steps to create all data-files, control files, System files, redo log files etc).
Something like:

create database doom
datafile ‘/u01/app/oracle/admin/doom/system.dbf’ size 250M
sysaux datafile ‘/u01/app/oracle/admin/doom/sysaux.dbf’ size 100m
undo tablespace undotbs1 datafile ‘/u01/app/oracle/admin/doom/undo.dbf’ size 100m
default temporary tablespace temp tempfile ‘/u01/app/oracle/admin/doom/tmp.dbf’ size 100m
logfile group 1 ‘/u01/app/oracle/admin/doom/log1.ora’ size 50m,group 2 ‘/u01/app/oracle/admin/doom/log2.ora’ size 50m;
Startup Database in ‘NOMOUNT’ Mode.
As we already created Parameter file for the Database but yet to create controlfiles so it’s not possibel to start DB in MOUNT mode.
Starting DB will allow us to run scripts from any locations.

[oracle@prashant2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Aug 6 15:24:59 2012

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 306184192 bytes
Fixed Size                  1219112 bytes
Variable Size              96470488 bytes
Database Buffers          205520896 bytes
Redo Buffers                2973696 bytes
SQL> @dbcreate.sql

Database created.

SQL>

N.E.X.T is to load two sql scripts (files) which helps database to create all default tablespaces, views. rows, comments, indexes, packages, procedures, synonyms, libraries, grants and commits columns and other objects and create entries in data dictionary.

catalog.sql – create data dictionary views.
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql

Grant succeeded.

Commit complete.

View created.

catproc.sql – run all sql scripts for the procedural option
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql

@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql
DOC>######################################################################
DOC>######################################################################
DOC>The following PL/SQL block will cause an ORA-20000 error and
DOC>terminate the current SQLPLUS session if the user is not SYS.
DOC>Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

Once Created start Database and check Status of the Instance using:

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
——— ———-
DOOM READ WRITE

1 row selected.

SQL>

Thanks for Reading.
Prash’ant’ Dixit

Posted in Basics | Tagged: Installation, Manual | Leave a Comment »

« Previous Entries

Next Entries »

Tales From A Lazy Fat DBA

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

Likes

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”

Character Set Change.

Nofilenamecheck in RMAN

Manual Creation of Database.

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: