Missing Tempfiles

Missing Tempfiles

Tempfiles are usually not backed up.

Since Oracle does not record checkpoint information in tempfiles, Oracle can start up a database with a missing tempfile.

Likewise, it is possible to remove (or in this case, not recreate) all tempfiles from a temporary tablespace and keep it empty.

But when a user attempts to sort to the TEMPORARY tablespace, an error is generated.

The solution is to add a new tempfile

--------

Redo log switch report from alert.log

Redo log file switch Report from alert.log using perl script

Redo log switch History

Redo log switch History

Find out  date  & time, SCN and other details about log switch

How Much Redo log is generated

Redo log Daily and Hourly volume calculated 

Find out how many MB of redo log is generated

Redo log Switch

Redo log Switch Rate by Date and Hour     

Find out how many log switches are taking place per hour

Redo Log Files and Sizing

 

Redo Log Files and Sizing         

set heading off;

select '******************************************************' from dual;

select '****           Redo Log Files and Sizing          ****' from dual;

select '******************************************************' from dual;

timing start 'Redo Sizing';

set heading on;

col "File Name" for a60;

col "Size in MB" format 999,999,999,999,990

select a.group#, thread#, substr(a.member,1,80) as "File Name",b.bytes/1024/1024 as "Size in MB" from v$logfile a,v$log b where a.group#=b.group#;

timing stop 'Redo Sizing';

Hope this helps. Regards Rupam

Find Sessions Generating Lots of Redo or Archive logs

Find Sessions Generating Lots of Redo or Archive logs

1. Login to a user with dba privilege

2. Execute the query

sql> SELECT s.sid, s.serial#, s.username, s.program,  i.block_changes

         FROM v$session s, v$sess_io i

       WHERE s.sid = i.sid 

       ORDER BY 5 asc, 1, 2, 3, 4;

3. Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

Hope this helps. Regards Rupam

Redo Log File - add and drop

Steps to add and drop redo log in database using ASM

1. Check redo log file destination

sql> show parameter db_create

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest                        string      +ORADATA001

db_create_online_log_dest_1          string      +ORADATA001

db_create_online_log_dest_2          string      +ORAFLASH001

db_create_online_log_dest_3          string

db_create_online_log_dest_4          string

db_create_online_log_dest_5          string

CPU Patch

CPU Patch Install  Process  

1. Follow the instructions  to apply the  patch using OPatch but do not start the instances or the listeners

Apply the Patch using Opatch

2. Apply the CPU sql script to each database from one node/instance only

   

    set your environment to the instance name
    cd  directory specified in README.txt of patch

    > cd $ORACLE_HOME/rdbms/admin

       sqlplus / as sysdba

       SQL>@catbundle.sql cpu apply

       SQL>@utlrp

       SQL>exit

3. After all databases have been updated, start the remaining instances(in RAC).

4. Start the listeners

5. Check connectibity

Hope this help. Regards Rupam

change adrci destination

How to change adrci diagnostics destination in 11g.

Steps :

1. sqlplus as sysdba

2. check current diagonostic destination

Sample Interview Questions

1. for standby – which one is used archive log or redo log?

2. steps to apply patchset  

5. steps for disaster recovery

5. Asm – to add space  is required  shutdown?

6. How to add datafiles to database ?

6. why stats is important?

7. steps to clone a database

8. one morning got the performance issues. What do you do ?

9. change management process

10.education and certification

Hope this help. Regards Rupam

tablespace

Space Management in Oracle

-- create system managed tablespace using OFA

     CREATE SMALLFILE TABLESPACE USERS

     LOGGING

     DATAFILE SIZE 2M

     AUTOEXTEND ON

     MAXSIZE UNLIMITED

     EXTENT MANAGEMENT LOCAL

     SEGMENT SPACE MANAGEMENT AUTO;

# to add datafile

alter tablespace USERS add datafile size 10M autoextend on;

   

Register the database with CRS

CRS  SRVCTL

Register database with CRS to enable automatic startup/shutdown of database and services when server is bounced

To register the database with CRS

Peepli live

Review: Peepli live dekhe.

Gather statistics of Partition Tables Part 2

--  collect global statistics on Partition Tables

Option # 1

set serveroutput on

set time on

declare

this_day       char(8);

part_name    char(10);

tab_name     varchar2(30);

v_num          number;

v_string        varchar2(4000);

cursor tab_cur IS select table_name from user_tables where PARTITIONED='YES'  and table_name in ('EVENT_LOG1','EVENT_LOG2');

begin

for table_rec in tab_cur loop

  exit when tab_cur%NOTFOUND;

  tab_name := table_rec.table_name;

  dbms_stats.gather_table_stats( ownname =>'SCOTT', tabname => tab_name, granularity=> 'GLOBAL', estimate_percent => 15, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', degree => 1, cascade => TRUE );

end loop;

end;

/

Note : granularity is GLOBAL, which means only GOBAL statustics on partition table  is going to be collected.

For other options, check Gather statistics of Partition Tables Part 1

Option # 2

begin

dbms_stats.gather_table_stats(ownname=>SCOTT',

tabname=>'EVENT_LOG1’,

granularity=>'ALL',

estimate_percent=>15,

method_opt=>'FOR ALL COLUMNS SIZE AUTO', degree => 1, cascade=>TRUE); end;

/

Note : granularity is ALL, which means Gathers all (subpartition, partition, and global) statistics.

Hope this help. Regards Rupam

Gather statistics of Partition Tables Part 1

Granularity of statistics to collect (only pertinent if the table is partitioned).

Constant	Description
ALL	Gathers all (subpartition, partition, and global) statistics
AUTO	Determines the granularity based on the partitioning type. This is the default value
DEFAULT	Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality
GLOBAL	Gathers global statistics
GLOBAL AND PARTITION	gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object
PARTITION	gathers partition-level statistics
SUBPARTITION	gathers subpartition-level statistics

Hope this help. Regards Rupam

Gather Statistics in Oracle part 2

-- collect statistics for missing statistics tables

declare

cursor miss_cur IS select table_name from user_tables where last_analyzed is null;

begin

for miss_rec in miss_cur loop

  exit when miss_cur%NOTFOUND;

  dbms_stats.Gather_Table_Stats('SCOTT',miss_rec.table_name,

           estimate_percent => 15,

           method_opt       =>'FOR ALL INDEXED COLUMNS SIZE 1',

           cascade          => TRUE);

  end loop;

end;

/

Hope this help. Regards Rupam

Gather Statistics in Oracle part 1

dbms_stats  package used for collecting statistics

summary

   gather statistics -   system, dictionary, fixed_objects , sys  

   gather schema statistics

   gather table stats

   gather index stats

Install Oracle InstantClient basic and instantclient sqlplus on win32

1. Download Oracle Instant Client  from Oracle site

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

e.g

   instantclient-basic-win32-11.1.0.7.0.zip
   instantclient-sqlplus-win32-11.1.0.7.0.zip

Oracle JDBC Connections

Example of connect string using jdbc

Recommend

the method we use for WebSphere:

jdbc:oracle:thin:@ldap://oid-uat:389/demo,cn=OracleContext,dc=csxt,dc=csx,dc=com  

A single instance:

   jdbc:oracle:thin:@lnx101-vip:1521:demo

Using TNSName: 

   jdbc:oracle:thin:@demo.world

Full URL:

   jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = lnx101-vip.csxt.csx.com)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = lnx102-vip.csxt.csx.com)(PORT = 1521))(LOAD_BALANCE = yes)(FAILOVER = on)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = demo)(FAILOVER_MODE =(TYPE = session)(METHOD = basic)(RETRIES = 10)(DELAY = 1))))

another option :

jdbc:oracle:thin:@ldap:// oid-dev:389/ demo,cn=OracleContext,dc=csxt,dc=csx,dc=com ldap:// oid-dev:389/ demo,cn=OracleContext,dc=csxt,dc=csx,dc=com

Hope this help. Regards Rupam

Install Oracle Instant Client Installation on UNIX / Linux Server

1. Confirm you can login as the oracle account (primary group dba) on the target server:

[oracle@lnx544 ~]$ id

uid=5005(oracle) gid=56(dba) groups=56(dba),57(oinstall),58(dbasudo),102(operator),2003(prpftp),2004(tcsgrp),5084(paiseasftp),5107(da)

Oracle Instant Client

Instant Client allows you to run your applications without installing the standard Oracle client or having an ORACLE_HOME. OCI, OCCI, Pro*C, ODBC, and JDBC applications work without modification, while using significantly less disk space than before. Even SQL*Plus can be used with Instant Client. No recompile, no hassle.

Hope this help. Regards Rupam

Recover database until cancel

Quick steps

1. Setup oracle database environment using . oraenv

2. restore controlfile

    $ rman target / nocatalog

    RMAN> set dbid=1185150074;

    RMAN> startup nomount;

    RMAN> run

                {

                restore controlfile from ‘/u01/BACKUP/demo/demo_control_backup.ctl’;

                }

RMAN Configuration

The default RMAN configuration may be changed as per need. Here is sample configuration change and also changing it back to default setting.

Connect to database using RMAN
$ rman target / nocatalog

# to check the current configuration

show all;

RMAN Backup quick reference guide

configure RMAN configuration

$ rman target / nocatalog

RMAN>configure controlfile autobackup on;

RMAN>configure retention policy to recovery window of 14 days;

RMAN backup Report - Oracle Database Backup Report

 script  : RMAN backup Report - Oracle Database Backup Report

Script : chkbkpstatus.sql

 set pages 999 lines 120
   col STATUS format a9
   col hrs format 999.99
   col start_time format a15
   col end_time format a15
   col dev format a5
   col inbytes format a10
   col outbytes format a10
   select
       -- SESSION_KEY,
       INPUT_TYPE, STATUS,
       to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
       to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
       elapsed_seconds/3600                   hrs,
       output_device_type dev,
       input_bytes_display inbytes,
       output_bytes_display outbytes
    from V$RMAN_BACKUP_JOB_DETAILS
   order by session_key;

Output

INPUT_TYPE    STATUS    START_TIME      END_TIME    HRS DEV   INBYTES    OUTBYTES

------------- --------- --------------- --------------- ------- ----- ---------- ----------

ARCHIVELOG    COMPLETED 08/11/10 15:00  08/11/10 15:01      .02 DISK    257.08M    107.06M

DB FULL       COMPLETED 08/11/10 21:00  08/11/10 23:57     2.96 DISK    227.16G     43.24G

ARCHIVELOG    COMPLETED 08/12/10 06:00  08/12/10 06:01      .02 DISK    260.92M     87.39M

ARCHIVELOG    COMPLETED 08/12/10 15:00  08/12/10 15:01      .02 DISK    250.10M    104.85M

Hope this help. Regards Rupam

Delete archive log using RMAN

Caution : By deleting archive log file, you may not be able to perform point-in-time recovery of the database.

Summary

1. connect to database using rman
2. issue archive log delete command and confirm
3. crosscheck archivelog all

Recover from Consistent Backup

Scenario  : One of more datafiles are lost of a nonarchive database

Action    :  Recover the database from consistent backup

RMAN Consistent Backup

A Consistent backup of a Database is made by starting the Database in MOUNT mode. The backup is consistent, and does not require recovery after it is restored. 

Negative Values For Tablespace

11g OEM Grid Control

OEM 11g Bug

bug 9548105

• OEM 11g doesn't list datafiles when tablespace is 100% full
• Negative Values Reported By Dbconsole For Tablespace With At Least Two Datafiles

AWR

Oracle Database Performance

AWR

Metalink Note:276103.1

STATISTICS_LEVEL initialization parameter must be set to the

TYPICAL or ALL to enable the Automatic Workload Repository

ADDM Report

Oracle Database Performance

ADDM Report

Option 1 : OEM

               Database -> Advisor Center -> ADDM

Option 2 : using sqlplus

@$ORACLE_HOME/rdbms/admin/addmrpt.sql  

Input : begin_snap

           ending_snap

and      filename

            

Hope this help. Regards Rupam

ADDM

Oracle Database Performance

ADDM

The Automatic Database Diagnostic Monitor ( ADDM ) is an advisor which detects problem area' s in the database and and which gives recommendations.

Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or O7_DICTIONARY_ACCESSIBILITY?

Oracle Roles

Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or O7_DICTIONARY_ACCESSIBILITY?

Datapump Orphaned Job

Datapump Orphaned Job

Follow the steps to cleanup Datapump orphaned job

DataPump

DataPump  

Setup

Connect to instance as sysdba using sqlplus

grant create any directory to scott;

create or replace directory dpdump as '/u01/app/oracle/export/';

grant read, write on directory dpdump to scott;

Check the directories

sql>  select * from dba_directories;

Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter.

Export

expdp scott/tiger@demo parfile=expdp.par

script : expdp.par

job_name=job1

directory=dpdump

filesize=20000000000

schemas=scott

dumpfile=expdp_demo_scott.dmp

logfile=expdp_demo_scott.log

FLASHBACK_TIME="TO_TIMESTAMP('05-10-2010 10:30:00', 'DD-MM-YYYY HH24:MI:SS')"

Import

impdp scott/tiger@demo parfile=impdp.par

script : impdp.par

directory=DPDUMP

job_name=job1

EXCLUDE=statistics

schemas=SCOTT

dumpfile=expdp_demo_scott.dmp

logfile=impdp_demo_scott.log

Monitoring

Sql> select count(*) from scott.job1;   #

Monitor export Using expdp

expdp scott/tiger@demo attach=job1

Hope this help. Regards Rupam

Gather Statistics Intro

Gather Statistics Intro

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.

expdp fails with ORA-31626, ORA-31633, ORA-06512, ORA-06512 and ORA-00955

expdp fails with ORA-31626, ORA-31633, ORA-06512, ORA-06512 and ORA-00955

While exporting data using expdp, got following error

Error

ORA-31626: job does not exist

ORA-31633: unable to create master table "SYS.JOB1"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT", line 1020

ORA-00955: name is already used by an existing object

Cause

There is already as job with same  name in the master table

Solution

Option 1: Change the job name

Option 2: Clean up Orphaned job. Read More

Refresh schema

Refresh schema

Summary

Refresh test database schema(scott) with production database schema(scott).

1. export production database schema(scott) using datapump
2. copy dump file to test server
3. generate script to drop objects from test database
4. drop objects from test schema(scott) and purge from recyclebin
5. import data into test database schema(scott)
6. recompile invalid objects
7. match the objects count between production and test schema
8. collect statistics in test database schema (scott)
9. open for business

Flashback Table to Before drop

Oracle FlashBack Table

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline.

Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints

Example:

 sql> DROP TABLE flashback_drop_test;

 sql> SHOW RECYCLEBIN

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE 2004-03-29:11:09:07

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name.

sql> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

You can retrieve it and assign it a new name using a RENAME TO clause.

sql> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP

RENAME TO flashback_drop_test_old;

### Several purge options exist:

PURGE TABLE tablename;

PURGE INDEX indexname;

PURGE TABLESPACE ts_name;

PURGE TABLESPACE ts_name USER username;

PURGE RECYCLEBIN;

PURGE DBA_RECYCLEBIN;   

### Query Recycle Bin

col original_name format a10

col owner format a10

col type format a10

col droptime format a10

col pace format a12

select owner, original_name, object_name, type, droptime

from dba_recyclebin

where can_undrop='YES';

### Query the dropped table data from Recycle bin

SELECT * FROM "BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0";

### Bypass the Recycle Bin

drop table flashback_drop_test purge;

Hope this help. Regards Rupam

Install OPatch

Install OPatch

Refer : Metalink link Note # 274526.1 and 224346.1

1) Please download the latest OPatch version from My Oracle Support (MOS)

a) Click on the "Patches & Updates" tab

b) In the "Patch Name or Number" field type 6880880

c) In the "Platform" field select the relevant platform

d) Click the Search button.

e) Select the patch that corresponds to the Oracle release installed:

6880880 Universal Installer: Patch OPatch 9i, 10.1
6880880 Universal Installer: Patch OPatch 10.2
6880880 Universal Installer: Patch OPatch 11.1
6880880 Universal Installer: Patch OPatch 11.2

f) Click the Download button

2) Upload or move the ZIP file to ORACLE_HOME and unzip it:

    % cd OPatch
    % opatch version

3) Now you can set the OPatch directory in your PATH variable so you can execute the OPatch command from anywhere.

Example:

For Korn / Bourne shell
% export PATH=$PATH:$ORACLE_HOME/OPatch

For C Shell
% setenv PATH $PATH:$ORACLE_HOME/OPatch

Hope this help. Regards Rupam

OPatch

OPatch Apply

Pre-implementation

1.  download the patch  

2.  unzip

Outage

1. Stop the listener
2. Stop all instances on the ORACLE_HOME on which patch is going to be applied

4. Set your environment to the ORACLE_HOME.

5. cd   patch number directory

6. Set your path to include the OPatch directory

     export PATH=$ORACLE_HOME/OPatch:$PATH

     Note : Check the OPatch version. Get the latest Version from Metalink

7. Apply the patch  

  On linux  

     option # 1   

         opatch apply –local

     option # 2   

         opatch napply –local –skip_subset –skip_duplicate

 on solaris 

      opatch apply –local OPatch.SKIP_VERIFY=true

8. Verify that all is well

     opatch lsinventory

9. Start all the instances

10. Wait until all instances have started.

11. Start the listener

12. check connectivity

Hope this help. Regards Rupam

Backup Archivelog

Backup Archivelog

Backup archive logs files

run {

    sql "ALTER SYSTEM ARCHIVE LOG CURRENT" ;

    allocate channel rman_disk1 type disk;

    set command id to 'DB_ARCH';

    backup   

      maxsetsize = 1G

      diskratio = 0

      skip inaccessible

      tag = 'Archive Logs'

      (archivelog all format '/orabkup1/oracle/BACKUP/demo/ar_%d_%t_%c_%s_%p');

}

Restore Archivelog

Restore Archive log using RMAN

Restore archive log using seqeunce

run {

allocate channel demo_t1 type disk;

restore archivelog from sequence 35347 until sequence 35359;

}

Archivelog Commands

Archivelog Useful Commands

alter system archive log [start|stop|all|...]

alter system switch logfile;
alter system archive log all;
alter system archive log next;
alter system archive log sequence 104;
alter system archive log current;
alter system archive log stop;
alter system archive log current noswitch;

Hope this help. Regards Rupam

Archivelog Size

Archivelog Size

Carefully plan Archivelog space, as, when archivelog space runs out, the database will freeze until space is available.

Enable ARCHIVELOG Mode

Enable ARCHIVELOG Mode

When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.

Change Archivelog destination in Oracle

Change archive log destination  in Oracle using ASM

Summary

To change archive log destination of Oracle database, follow the following simple steps

adrci alert

ADRCI  alert

 

Log into the oracle user.

Set Oracle database environment, using

. oraenv

Start the ADR command line interpreter(ADRCI) by issuing the following command in the database directory:

adrci purge

ADRCI  purge

The automated purge policy for Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more are governed by policy setting. 

Cleanup is done by MMON background process.

adrci commands

ADRCI  commands

Log into the oracle user.

Set Oracle database environment, using

. oraenv

Start the ADR command line interpreter(ADRCI) by issuing the following command in the database directory:

adrci

ADRCI: Release 11.2.0.2.0

ADR base = "/ora01/oracle"

adrci

ADR command line interpreter(ADRCI)

ADRCI enables you to:

• View diagnostic data within the Automatic Diagnostic Repository (ADR).
• View Health Monitor reports.
• Package incident and problem information into a zip file for transmission to Oracle Support.