Posts Tagged ‘ Oracle Database ’

Add REDO Group with Multiplexed Member

Use this script to add redo group with multiplexed Member.


alter database add logfile group 4 ('/redolog02fs/redo/ababil_redoG4M2.log','/redolog01fs/redo/ababil_redoG4M1.log')size 100M;

Thanks

A Rahim Khan

Oracle Database Cold Backup Script

Execute this bash script, it will do the rest of your Database Cold Backup.

ColdBackup.sh


#!/bin/sh
/oracle/app/product/10.2.0/db_1/bin/sqlplus /nolog <<EOF
conn /as sysdba
@/home/oracle/ColdBackupScript.sql
EOF

Continue reading

analyze redo/archive using logminer

First of all, query on V$DATABASE to check out if SUPPLEMENTAL_LOG_DATA_MIN is YES OR NO.


SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

You can use this following query to alter SUPPLEMENTAL_LOG_DATA_MIN flag.


ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ;

Continue reading

Oracle Database Redo Log Switch Frequency

I found this handy script to check out Oracle Log Switch Frequency from link.


--COL DAY FORMAT a15;
--COL HOUR FORMAT a4;
--COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
TO_CHAR(FIRST_TIME,'HH24') HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ASC;

Continue reading

Oracle Automatic Diagnostic Repository (adrci) Useful Commands

Automatic Diagnostic Repository (adrci) is one of the new features in Oracle 11g. it’s a handy Command Line Utility to deal with Database Alert Log. Followings are some useful adrci Commands.


-- To list Oracle Base
adrci> show base;

-- To list ADR HOMEs
adrci> show homes;

-- To set Single Home Path Location
adrci> set homepath 'location';

-- To tail Alert Log File
adrci> show alert -tail -f;

-- To search a Specific Message
adrci> show alert -p "message_text like 'ORA-00600'" -term

-- To search within Specific Time Limit
adrci> show alert -p "message_text like '%ORA-%' and originating_timestamp >= systimestamp - 1/24" -term

-- To purge 10080 Minutes old alert 
adrci> purge -alert 10080 -type alert;

-- To list Incidents
adrci> show incident;

-- To list Problems
adrci> show problem;

Thanks
A Rahim Khan

Rebuild All Indexes for specific User/Schema

Here, I will generate script to rebuild all index for a specific Schema. First of all, let’s all Index for a specific Owner.


SQL> select index_name from dba_indexes where owner=upper('hr');

INDEX_NAME
------------------------------
LOC_CITY_IX
LOC_ID_PK
REG_ID_PK
COUNTRY_C_ID_PK
LOC_STATE_PROVINCE_IX
LOC_COUNTRY_IX
DEPT_ID_PK
DEPT_LOCATION_IX
JOB_ID_PK
EMP_EMAIL_UK
EMP_EMP_ID_PK
EMP_DEPARTMENT_IX
EMP_JOB_IX
EMP_MANAGER_IX
EMP_NAME_IX
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX

19 rows selected.

Continue reading

Oracle External Table

This is the simplest demonstration of External Table in ORACLE Database.


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS

SQL> create user ext_user identified by ext default tablespace users temporary tablespace temp quota unlimited on users;

User created.

SQL> grant connect,resource,create session  to ext_user;

Grant succeeded.

SQL> create or replace directory external as 'F:\khanExternal';

Directory created.

SQL> grant read,write on directory external to exp_full_database,imp_full_database,system;

Grant succeeded.

Continue reading

Oracle Database Link

Oracle Database Links are of two types, Private or Public. Private is the default one. let’s start with connecting with database, in this Example, My two Database are on same Machine. For creating Database link, you need to have TNS entry for remote database.


C:\Documents and Settings\A Rahim Khan>set oracle_sid = catdb;

C:\Documents and Settings\A Rahim Khan>sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 2 10:52:28 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> select instance_name from v$instance;

INSTANCE_NAME
----------------
catdb

SQL> create user hr_link identified by hr;

User created.

SQL> grant connect,resource,CREATE DATABASE LINK hr_link;

Grant succeeded.

Continue reading

Oracle Database Control File Multiplexing

First of all, keep a backup of your existing Control File on other disk. If, you don’t know location of existing Control File then use this following query.


SQL> select name from v$controlfile;

NAME
------------------------------------------------------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\SIBL\CONTROL01.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\SIBL\CONTROL02.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\SIBL\CONTROL03.CTL

Continue reading

Oracle Redo Log Files Multiplexing

First of all, let’s see output from v$log and v$logfile.



SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                                   IS_
    ---------------------------------------------------------------------------------------------------------- ---------
         3 STALE   ONLINE  G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG                                         NO
         2         ONLINE  G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG					     NO
         1         ONLINE  G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG                                         NO


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         11   52428800          1 YES INACTIVE                799976 01-MAY-12
         2          1         12   52428800          1 NO  CURRENT                 800427 01-MAY-12
         3          1         10   52428800          1 YES INACTIVE                779491 29-APR-12

As shown above, Group 2 was the CURRENT one and Group 3 becomes CURRENT one after switching log. Continue reading