Creating Redo Log Groups and Members

Create all required groups and members of redo log files during database creation. However, there are situations where you might want to create additional groups or members.

Creating Redo Log Groups

To create new redo log groups and members, you must have the ALTER DATABASE system privilege.

To create a new group of redo log files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause.

The following statement adds a new group of redo logs to the database:

 ALTER DATABASE

  ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 500K;

You can also specify the number that identifies the group using the GROUP clause:

ALTER DATABASE

  ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo')

      SIZE 500K;

Using group numbers can make administering redo log groups easier. However, the group number must be between 1 and MAXLOGFILES.

In some cases, it might not be necessary to create a complete group of redo log files. A group could already exist, but not be complete because one or more members of the group were dropped (for example, because of a disk failure). In this case, you can add new members to an existing group.

Creating Redo Log Members

To create new redo log members for an existing group, use the SQL statement ALTER DATABASE with the ADD LOGFILE MEMBER clause.

The following statement adds a new redo log member to redo log group number 2:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;

Notice that filenames must be specified, but sizes need not be. The size of the new members is determined from the size of the existing members of the group.

When using the ALTER DATABASE statement, you can alternatively identify the target group by specifying all of the other members of the group in the TO clause, as shown in the following example:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo'

    TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');

Note:

Fully specify the filenames of new log members to indicate where the operating system files should be created. Otherwise, the files will be created in either the default or current directory of the database server, depending upon your operating system. You may also note that the status of the new log member is shown as INVALID. This is normal and it will change to active (blank) when it is first used.

Read more

Multiplex & Move Oracle control files to different directory

1, shutdown database.

shutdown immediate;

2, edit Oracle init.ora file, e.g., C:\Oracle\10g\database\inittest.ora, find the line with control file location, change the location from old directory to new directory, e.g.:

         OLD:
         control_files=("H:\Oradata\test\control01.ctl")

         NEW:
         control_files=("I:\Oradata\test\control01.ctl",  "J:\Oradata\test\control02.ctl", "K:\Oradata\test\control03.ctl")

save init.ora file.

3, create spfile from pfile:

create spfile from pfile;

4, Copy control files from old directory to new directory

    copy H:\Oradata\test\control01.ctl I:\Oradata\test\control01.ctl
    copy H:\Oradata\test\control01.ctl J:\Oradata\test\ control02.ctl
    copy H:\Oradata\test\control01.ctl K:\Oradata\test\control03.ctl

5, startup database.

SQL> startup
ORACLE instance started.
Total System Global Area 1426063360 bytes
Fixed Size 2004264 bytes
Variable Size 352324312 bytes
Database Buffers 1056964608 bytes
Redo Buffers 14770176 bytes
Database mounted.
Database opened.
SQL>

Read more

Disable a table Constraints

ORA-02297: cannot disable constraint -dependencies exist

Whenever you try to disable a constraint of a table it fails with error message ORA-02297: cannot disable constraint -dependencies exist as below.

SQL> alter table transaction disable constraint TRANSACTION_PK;
alter table transaction disable constraint TRANSACTION_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) - dependencies exist

Cause :
Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint.

Solution :
Two solutions exist for this problem.

1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint.

SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
       p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
       FROM user_constraints p
       JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
       WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
       AND c.constraint_type = 'R'
       AND p.table_name = UPPER('&table_name');
Enter value for table_name: transaction
old   7:      AND p.table_name = UPPER('&table_name')
new   7:      AND p.table_name = UPPER('transaction')

Parent Table                   Child Table                    Parent Constraint            Child Constraint
----------------------------   --------------------------     -------------------------    ---------------------
TRANSACTION                    USER_SALARY_RECORD             TRANSACTION_PK               SYS_C005564
TRANSACTION                    TRANSACTION_DETAIL             TRANSACTION_PK               TRNSCTN_DTL_TRNSCTN_FK

SQL> alter table USER_SALARY_RECORD disable constraint SYS_C005564;
Table altered.

SQL> alter table TRANSACTION_DETAIL  disable constraint TRNSCTN_DTL_TRNSCTN_FK;
Table altered.

SQL> alter table transaction disable constraint TRANSACTION_PK;
Table altered.


2)Disable the constraint with cascade option.

SQL> alter table transaction disable constraint TRANSACTION_PK cascade;
Table altered.

Read more

DATABASE HEALTH CHECK

One of the daily task for oracle DBA to measure the health of databases.

This includes the database status,instance name, database open  mode, start up time,DR status,Archive log generation  etc.

The following script can be used to generate the oracle Database health Report. Below script is very basic script written to check   database status, additions can be made to script as per the requirements.

This report generates the following basic checks.

1. DATABASE STATUS

2. DATABASE NAME AND MODE

3. DR DATABASE STATUS

4. ARCHIVE LOG GENERATION LAST WEEK

5. DB PHYSICAL SIZE, DB ACTUAL SIZE

6. TABLESPACE USAGES

7. NO of USER/SESSIONS CONNECTED

8. DICTIONARY/LIBRARY HIT RATIO

9  BLOCKING QUERIES,BLOCKER AND WAITER

10. INVALID OBJECT COUNT LIST

Scripts Start.

*****************************************************************************

*****************************************************************************

set feedback off

set serverout on

set wrap off

set pages 300

set lines 200

PROMPT ================================================================

PROMPT DATABASE HEALTH CHECK REPORT

PROMPT ================================================================

PROMPT

PROMPT

PROMPT DATABASE STATUS

PROMPT =================

select INSTANCE_NAME,STATUS,DATABASE_STATUS,ACTIVE_STATE,STARTUP_TIME from v$instance;

PROMPT

PROMPT

PROMPT DATABASE NAME AND MODE

PROMPT ========================

select name, open_mode, log_mode from v$database;

PROMPT

PROMPT

PROMPT DR DATABASE STATUS

PROMPT ===========================================================

Col APPLIED_TIME format a20

Col destination format a20

Col Status format a20

SELECT DB_NAME,destination,  APPLIED_TIME, LOG_APPLIED,LOG_ARCHIVED,

(

  CASE

    WHEN ((APPLIED_TIME            IS NOT NULL    AND (LOG_ARCHIVED-LOG_APPLIED) IS NULL)

    OR (APPLIED_TIME               IS NULL    AND (LOG_ARCHIVED-LOG_APPLIED) IS NOT NULL)

    OR ((LOG_ARCHIVED-LOG_APPLIED)  > 1))

    THEN 'Error! Log Gap is '

    ELSE 'OK!'

  END) Status,

   LOG_ARCHIVED-LOG_APPLIED LOG_GAP

FROM

  ( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE WHERE INST_ID = 1 ),

  (SELECT MAX(SEQUENCE#) LOG_ARCHIVED   FROM V$ARCHIVED_LOG    WHERE DEST_ID=1 ),

   (select applied_seq# as LOG_APPLIED,destination as destination  from v$archive_dest_status WHERE DEST_ID=3 ),

  (SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME  FROM V$ARCHIVED_LOG  WHERE DEST_ID=1 );

PROMPT

PROMPT

PROMPT ARCHIVE LOG GENERATION LAST WEEK

PROMPT ===========================================================

SELECT A.*,  ROUND (A.Count# * B.AVG# / 1024 / 1024) Daily_Avg_Mb

FROM

  (SELECT TO_CHAR (First_Time,'YYYY-MM-DD') DAY,COUNT (1) Count#, MIN (RECID) Min#,MAX (RECID) Max#,MIN(sequence#),MAX(sequence#)  

   FROM v$log_history  GROUP BY TO_CHAR (First_Time, 'YYYY-MM-DD')  ORDER BY 1 DESC ) A,

  (SELECT AVG (BYTES) AVG#,COUNT (1) Count#,MAX (BYTES) Max_Bytes,MIN (BYTES) Min_Bytes  

   FROM v$log ) B   where rownum < 8;

PROMPT

PROMPT

PROMPT DB PHYSICAL SIZE

PROMPT =====================================

select sum(bytes/1024/1024/1024) "DB Physical Size(GB)" from dba_data_files;

PROMPT

PROMPT

PROMPT DB ACTUAL SIZE

PROMPT =====================================

select sum(bytes/1024/1024/1024) "DB Actual Size(GB)" from dba_segments;

PROMPT

PROMPT

PROMPT TABLESPACE USAGES

PROMPT ===========================================================

SELECT /* + RULE */  df.tablespace_name "Tablespace",

       df.bytes / (1024 * 1024) "Size (Mb)",

       SUM(fs.bytes) / (1024 * 1024) "Free (Mb)",

       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",

       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"

  FROM dba_free_space fs,

       (SELECT tablespace_name,SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,df.bytes

UNION ALL

SELECT /* + RULE */ df.tablespace_name tspace,

       fs.bytes / (1024 * 1024),

       SUM(df.bytes_free) / (1024 * 1024),

       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),

       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)

  FROM dba_temp_files fs,

       (SELECT tablespace_name,bytes_free,bytes_used

          FROM v$temp_space_header

         GROUP BY tablespace_name,bytes_free,bytes_used) df

 WHERE fs.tablespace_name (+)  = df.tablespace_name

 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

 ORDER BY 4 DESC;

PROMPT

PROMPT

PROMPT NO of USER CONNECTED

PROMPT ===========================================================

select count(distinct username) "No. of users Connected" from v$session where username is not null;

PROMPT

PROMPT

PROMPT NO of SESSIONS CONNECTED

PROMPT ===========================================================

Select count(*) AS "No of Sessions connected" from v$session where username is not null;

PROMPT

PROMPT

PROMPT DISTINCT USERNAME CONNECTED

PROMPT ===========================================================

Select distinct(username) AS "USERNAME" from v$session;

PROMPT

PROMPT

PROMPT DICTIONARY HIT RATIO. THIS VALUE SHOULD BE GREATER 85%

PROMPT ==========================================================

select   (  1 - ( sum (decode (name, 'physical reads', value, 0)) / (  sum (decode (name, 'db block gets',value, 0)) + sum (decode (name, 'consistent gets', value, 0))))) * 100 "Buffer Hit Ratio" from v$sysstat;

PROMPT

PROMPT

PROMPT LIBRARY CACHE HIT RATIO. THIS VALUE SHOULD BE GREATER 90%

PROMPT ===========================================================

select (sum(pins)/(sum(pins)+sum(reloads))) * 100 "Library Cache Hit Ratio" from v$librarycache;

PROMPT

PROMPT

PROMPT BLOCKING QUERY

PROMPT ===========================================================

select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' )  is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2;

PROMPT

PROMPT

PROMPT BLOCKER AND WAITER

PROMPT ===========================================================

Select sid , decode(block,0,'NO','YES') Blocker , decode (request ,0,'NO','YES')WAITER from v$lock where request>0 or block>0 order by block desc;

PROMPT

PROMPT

PROMPT INVALID OBJECT COUNT LIST

PROMPT ===========================================================

select count(*),owner ,  object_type from dba_objects where status='INVALID' group by owner ,  object_type order by owner;

*****************************************************************************

*****************************************************************************

Script End.

Read more