Tales From A Lazy Fat DBA

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

Archive for August, 2012

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:

  1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
  1. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
  1. Complete the following statements:
  1. STARTUP MOUNT;
  2. ALTER SYSTEM ENABLE RESTRICTED SESSION;
  3. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
  4. ALTER SYSTEM SET AQ_TM_PROCESSES=0;
  5. ALTER DATABASE OPEN;
  6. 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—-

Advertisement

Posted in Advanced | Tagged: | 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: | 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

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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: , | Leave a Comment »

ADDR & AWR Detailed Study

Posted by FatDBA on August 6, 2012

This post gives an overview and understanding of  AWR and ADDM in Oracle 10g

AWR Architecture

Oracle 10g – awrArchitecture

AWR Features :

Automatic Workload Repository (AWR) collects processes and maintains performance statistics for problem detection & self tuning purposes.

The AWR is used to collect performance statistics including:

  • Wait events used to identify performance problems.
  • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
  • Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
  • Object usage statistics.
  • Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:

  • Automatic Database Diagnostic Monitor
  • SQL Tuning Advisor
  • Undo Advisor
  • Segment Advisor

AWR Space Requirements

Space requirement for AWR depends on following:

Number of active sessions in system at given time.

  • Snapshot interval – Frequency at which snapshot are captured.
  • Historical data retention period.

Estimate:

Snapshot interval           : 1 hr

No of sessions               : 10

Retention period : 7 days

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

Space for AWR              : 200 – 300 MB

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

Initialization parameters & Grants

STATISTICS_LEVEL –> TYPICAL or ALL

Note: BASIC – turns off in-memory collection of many system statistics.

  • User running AWR packages needs DBA role.

Note:

  • For STATISTICS_LEVEL, default setting is TYPICAL which ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.
  • When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.
  • Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

AWR Automated Snapshots

Oracle Database 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics.

This job is created, and enabled automatically, when you create a new Oracle database under Oracle Database 10g.

Note: GATHER_STATS_JOB is Oracle defined automatic optimizer statistics collection job run by SYS. For more information refer DBA_SCHEDULER_JOBS, DBA_SCHEDULER_JOB_RUN_DETAILS

Checking current Configuration in database

  • Use following query to check the current settings of “GATHER_STATS_JOB” in any database:

SELECT a.job_name, a.enabled, c.window_name, c.schedule_name,

c.start_date, c.repeat_interval

FROM dba_scheduler_jobs a,

dba_scheduler_wingroup_members b,

dba_scheduler_windows c

WHERE job_name=’GATHER_STATS_JOB’

And a.schedule_name=b.window_group_name

And b.window_name=c.window_name;

  • To check the AWR snap interval & Retention time – use following query:

SELECT * FROM DBA_HIST_WR_CONTROL

Sample Result

DBID                            SNAP_INTERVAL                      RETENTION                  TOPNSQL

————————————————————————————————————————–

1195050809                  +00000 01:00:00.0                     +00007 00:00:00.0         DEFAULT

Generate AWR Report

Oracle provides two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql).

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

Note:  awrrpt.sql & awrrpti.sql are exactly same except that awrrpti report allows specific instance to report on. awrrpti.sql asks to enter dbid for particular instance of interest.

Execution of script

  • SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

DB Id                         DB Name          Inst Num           Instance

—————————————————————————————-

1195050809                 O01ABC0             1                   O01ABC0

  • Specify the Report Type : (html / txt)
  • Specify the number of days of snapshots to choose: This will show the snap id along with the time data.

Instance            DB Name           Snap Id            Snap Started                 Level

———————————————————————————————————-

O01ABC0          O01ABC0           5652                01 Apr 2008 00:00          1

5653               01 Apr 2008 01:00          1

5654                         01 Apr 2008 02:00          1

5655               01 Apr 2008 03:00          1

  • Specify the Begin and End Snapshot Ids: Choose from the list displayed due to previous point.
  • Specify the Report Name: If leave blank then it will take default file name.
  • FTP the result in the local drive and view the results

Changing AWR Configuration

  • Oracle Default Settings

è  Snap Time               : 1 hr

è  Snap Retention        : 7 days

  • Snapshot

Snapshots & Baselines are sets of historical data for a specific time period that are used for performance comparison.

è  Change snapshot default settings by using following package:

BEGIN

DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings (

retention => 43200,        — Minutes (= 30 Days). Current value retained if NULL.

interval  => 30);              – Minutes. Current value retained if NULL.

END;

/

è Extra snapshots can be taken and existing snapshots can be removed using:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN

DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (

low_snap_id  => 22,

high_snap_id => 32);

END;

/

  • Baseline

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past.

è Create Baseline:

BEGIN

DBMS_WORKLOAD_REPOSITORY.create_baseline (

start_snap_id => 210,

end_snap_id   => 220,

baseline_name => ‘batch baseline’);

END;

/

è The pair of snapshots associated with a baseline is retained until the baseline is explicitly deleted:

BEGIN

DBMS_WORKLOAD_REPOSITORY.drop_baseline (

baseline_name => ‘batch baseline’,

cascade       => FALSE); — Deletes associated snapshots if TRUE.

END;

/

Workload Repository Views

View Name Description
V$ACTIVE_SESSION_HISTORY Displays the active session history (ASH) sampled every second.
V$METRIC Displays metric information.
V$METRICNAME Displays the metrics associated with each metric group.
V$METRIC_HISTORY Displays historical metrics.
V$METRICGROUP Displays all metrics groups
DBA_HIST_ACTIVE_SESS_HISTORY Displays the history contents of the active session history.
DBA_HIST_BASELINE Displays baseline information.
DBA_HIST_DATABASE_INSTANCE Displays database environment information.
DBA_HIST_SNAPSHOT Displays snapshot information.
DBA_HIST_SQL_PLAN Displays SQL execution plans.
DBA_HIST_WR_CONTROL Displays AWR settings.

Automatic Performance Diagnostics

The automatic database diagnostic monitor (ADDM) analyzes the AWR data on regular basis, then locates the root cause of performance problems, provides recommendation for correcting any problems, identifies non problem areas of the system.

  • ADDM analysis is performed every time AWR snapshot is taken and the results are saved in database.
  • The goal of ADDM analysis is to reduce a single throughput metric called DB time. DB time is the cumulative time spent by database server in processing user requests. (DB time includes CPU time and time of non-idle user sessions).
  • ADDM does not target individual user response times.

Types of Problems

Following are the types of problems ADDM report addresses:

è     CPU bottlenecks

è     Undersized memory structures

è     I/O capacity issues

è     High load SQL statements

è     RAC specific issues

è     Database configuration issues

è     Also provides recommendations on hardware changes, database configuration & schema changes.

ADDM Results

ADDM report has following sections:

è     Problem: Finding that describe the root cause of database performance issue.

è     Symptom: Finding that contain information that often lead to one or more problem finding.

è     Information: Findings that are used to report non-problem area of the system.

è     Recommendations: Composed of action & rationales.

Initialization parameters & Grants

  • User running the ADDM scripts needs “ADVISOR” privileges
  • STATISTICS_LEVEL should be set to TYPICAL or ALL
  • DBIO_EXPECTED – describes the expected performance of the I/O subsystem. The value of DBIO_EXPECTED is the average time that takes to read a single database block in microsecond. Default is 10millisecond.

Note:

To modify DBIO_EXPECTED value to 8000 microseconds, execute following as SYS user.

EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER (

‘ADDM’, ‘DBIO_EXPECTED’, 8000);

Running ADDM

We can generate ADDM report in two ways:

  • By using oracle script $ORACLE_HOME/rdbms/admin/addmrpt.sql
  • DBMS_ADVISOR package.

Running ADDM using addmrpt.sql

  • SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

DB Id    DB Name      Inst Num Instance

———– ———— ——– ————

1195050809 O01ABC0             1 O01ABC0

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id     Inst Num DB Name      Instance     Host

———— ——– ———— ———— ————

* 1195050809        1 O01ABC0      O01ABC0      test01

Using 1195050809 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

Automatic Workload Repository (AWR) in Oracle Database 10g

Automatic Workload Repository (AWR) in Oracle Database 10g

Snap

Instance DB Name Snap Id Snap Started Level

———————————————————————————————————————

O01ABC0 O01ABC0 6020 16 Apr 2008 08:00 1

6021 16 Apr 2008 09:00 1

6022 16 Apr 2008 10:00 1

  • Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 6020

Begin Snapshot Id specified: 6020

Enter value for end_snap: 6022

Running ADDM using DBMS_ADVISOR APIs

Steps:

  • Create advisor task. (DBMS_ADVISOR.CREATE_TASK)
  • Setting required parameters to run specific type of task, such as START_SNAPSHOT, END_SNAPSHOT parameters. (DBMS_ADVISOR.TASK_PARAMETER)
  • Execute task. (DBMS_ADVISOR.EXECUTE_TASK)
  • View results ((DBMS_ADVISOR.GET_TASK_REPORT)

Automatic Workload Repository (AWR) in Oracle Database 10g

ADDM Views

View Name Description
DBA_ADVISOR_TASKS Provides information about existing task, like – task id, name, etc.
DBA_ADVISOR_LOG Contain the current task information such as status, progress, error messages, and execution times.
DBA_ADVISOR_RECOMMENDATIONS Complete result of diagnostic task.
DBA_ADVISOR_FINDINGS Display all symptoms and specific recommendations.

Posted in Advanced | Tagged: | Leave a Comment »

 
%d bloggers like this: