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

Advertisements

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

Advertisements