Remove ^M character from VI Unix

Once we copied the file from windows to unix machine we see the following ^M when we try to edit the file with VI .

The ^M is a carriage-return character. If you see this, you're probably looking at a file that originated in the DOS/Windows world,
where an end-of-line is marked by a carriage return/newline pair, whereas in the Unix world, end-of-line is marked by a single newline.

Vi test.sql

-- Please make any necessary changes as needed.^M
^M
^M
CREATE SEQUENCE  scott.emp MINVALUE 100 MAXVALUE 999999999999999999999 INCREMENT BY 1 START WITH 100 CACHE 400 NOORDER  NOCYCLE ;^M
^M



WORKAROUND :

which dos2unix
/usr/local/bin/dos2unix

/usr/local/bin/dos2unix test.sql

Dos2Unix: Cleaning file test.sql ...

Real Time Useful DBA queries

-- Instance startup info
set lines 190
col HOST_NAME for a20
select inst_id,host_name,instance_name,status,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') "LAST_STARTUP_TIME"
from gv$instance order by 1;

-- Tablespace utilization

set lines 200

SELECT tbs.tablespace_name,
 tot.bytes / 1024 / 1024 / 1024 total_gb,
 tot.bytes / 1024 / 1024 -SUM(nvl(fre.bytes, 0)) / 1024 / 1024 used_mb,
 SUM(nvl(fre.bytes, 0)) / 1024 / 1024 free_mb,
 round(((1 -SUM(nvl(fre.bytes, 0)) / tot.bytes) *100),2) pct,
 decode(greatest((1 -SUM(nvl(fre.bytes, 0)) / tot.bytes) *100, 90), 90, '', '*') pct_warn
FROM dba_free_space fre,
 (SELECT tablespace_name,
 SUM(bytes) bytes
 FROM dba_data_files
 GROUP BY tablespace_name)
tot,
 dba_tablespaces tbs
WHERE tot.tablespace_name = tbs.tablespace_name
 AND fre.tablespace_name(+) = tbs.tablespace_name
-- AND tbs.tablespace_name = 'USERS'
GROUP BY tbs.tablespace_name,
 tot.bytes / 1024,
 tot.bytes
ORDER BY 5, 1;


select     tablespace_name,
           round(used_space/(1024*1024),2),
           round(tablespace_size/(1024*1024),2),
           round(used_percent, 2)
from dba_tablespace_usage_metrics
where round(used_percent,2) > 90;

-- Datafile information check query:

set lines 190
col FILE_NAME for a50
select file_name,sum(bytes/1024/1024) "Size_MB",sum(maxbytes/1024/1024) "Maxbytes",autoextensible from dba_temp_files
where tablespace_name='TEMP'
group by file_name,maxbytes,autoextensible;

set lines 190
col FILE_NAME for a60
select file_id,file_name,status,sum(bytes/1024/1024) "Size_MB",sum(maxbytes/1024/1024) "Maxbytes",autoextensible from dba_data_files
where tablespace_name='SYSAUX'
group by file_id,file_name,status,maxbytes,autoextensible
order by 1;

-- Query to get all files datafile + tempfile + logfile:

set lines 100 pages 999
col name format a50

select     name, bytes
from    (select    name, bytes
from    v$datafile
union   all
select  name, bytes
from    v$tempfile
union   all
select  lf.member "name", l.bytes
from    v$logfile lf
,       v$log l
where   lf.group# = l.group#) used
,  (select sum(bytes) as poo
from dba_free_space) free
/

-- Redo log file info

set linesize 120
set pagesize 35
col group# format 999
col thread# format 999
col member format a50 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/

-- Session info

-- Last Call Activity in minutes:

set lines 190
col USERNAME for a15
col MACHINE for a20
col PROGRAM for a20

select to_char(a.logon_time,'dd-mon-yyyy hh24:mi:ss') "Login_Time", a.sid, a.serial#, b.spid,a.username , a.machine, a.program
,      floor(a.last_call_et / 60) "Minutes"
,      a.status
--,      EVENT
from   v$session a, v$process b
where a.paddr = b.addr and a.username!='SYS' and a.last_call_et > 1800
order by a.last_call_et desc
/

set lines 200
set pages 1000
col MACHINE for a30
col  username for a15
select inst_id,username,machine, count(1) from gv$session
where username='NXTL_APP_OVMEKS' and status='ACTIVE' group by inst_id,machine,username order by 1 ,2 ;

select inst_id, username,count(1) INACTIVE_COUNT from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and status='INACTIVE'
group by inst_id,username order by 1
/

select inst_id, username,count(1) ACTIVE_COUNT from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and status='ACTIVE'
group by inst_id,username order by 1
/

-- Query info

set lines 190

col SQL_TEXT for a40
col USERNAME for a10
col USERNAME for a10
col OSUSER for a10
col SPID for a10
col MACHINE for a15
col PROGRAM for a15
col LOGIN for a15

select distinct b.sid,b.inst_id,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,c.spid,
substr(b.machine,1,22) machine,
     to_char(b.logon_time,'ddMon hh24:mi') login,
     round(b.last_call_et/3600,2) "ACTIVE_SINCE_HRS",a.sql_text
from gv$sql a, gv$session b, gv$process c
where b.sql_address = a.address
and b.sql_hash_value = a.hash_value and b.paddr = c.addr and b.status='ACTIVE' and b.username not in ('SYS','SYSTEM','DBSNMP')
order by 2;

-- Run it from Os prompt:

while true
do
date; echo "select inst_id, status, count(*) from gv\$session where username is not null and username not in ('SYS','SYSTEM','DBSNMP') group by inst_id,
status order by 1,2;"| sqlplus -s / as sysdba
echo "select 'SESSIONS '||'LOCKING'||' COUNT '|| count(*) FROM gv\$lock c, dba_objects b, gv\$locked_object d, gv\$session s WHERE b.object_id = d.object_id
and d.session_id = c.sid and s.sid = c.sid and d.session_id=s.sid AND c.block = 1 and c.ctime > 60;"| sqlplus -s / as sysdba
echo "set pages 30;"| sqlplus -s / as sysdba
echo "select event, count(*) from gv\$session where status='ACTIVE' and username is not null group by event order by 2;"| sqlplus -s / as sysdba
sleep 30
done;


-- SPID info

Session + Process Information:

select a.logon_time, a.sid, a.username, a.machine, a.program,b.spid
from v$session a, v$process b
where a.paddr = b.addr
and a.sid=101
/

-- Monitoring Long Running:

set lines 190
col pct_comp for a10
col opname for a20
col message for a68
col START_TIME for a25
col MESSAGE for a40

SELECT USERNAME,
OPNAME, TO_CHAR(START_TIME,'DD-MON-YYYY HH12:MI:SS AM') "START_TIME",
round(elapsed_seconds/60,2) "Elaps(min)", round(TIME_REMAINING/60,2) "TimeRem(min)", round(SOFAR/TOTALWORK*100,2)||'%' "PCT_COMP" ,
TOTALWORK, message
FROM GV$SESSION_LONGOPS
WHERE --USERNAME = 'SYS' and
sofarORDER BY ELAPSED_SECONDS DESC
/

-- DATAGUARD info

-- STANDALONE DATAGUARD CHECK

select max(sequence#) "Last Primary Seq Generated" from v$archived_log ;

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from gv$archived_log
where resetlogs_change#=(select resetlogs_change# from gv$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from gv$log_history
where first_time=(select max(first_time) from gv$log_history)
group by thread#) lh
where al.thrd = lh.thrd;

      Thread Last Seq Received Last Seq Applied
------------ ----------------- ----------------
           1              2955             2786


-- RAC DATAGUARD CHECK

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  39136                 39136          0
         2                  45284                 45284          0
         3                  46699                 46699          0


select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log group by thread# order by 1;

select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log where applied='YES' group by thread# order by 1;

-- ASM info

set lines 300
col PATH for a30
SELECT instance_name,status,TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS AM') db_date FROM v$instance, dual;
select PATH,HEADER_STATUS,OS_MB,TOTAL_MB,FREE_MB,NAME,STATE from v$asm_disk order by name ;
select NAME,STATE,TOTAL_MB/1024,FREE_MB/1024,USABLE_FILE_MB/1024,ROUND((1- (free_mb / total_mb))*100, 2) Pct_used from v$asm_diskgroup;

SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,
SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,
SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible
FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c
WHERE dg.group_number = c.group_number;

select * from v$asm_operation;

-- Log Switch History:

SET VERIFY OFF
SET LINESIZE 150
SET PAGESIZE 999

COL DAY FOR A10
COLUMN H00   FORMAT 999     HEADING '00'
COLUMN H01   FORMAT 999     HEADING '01'
COLUMN H02   FORMAT 999     HEADING '02'
COLUMN H03   FORMAT 999     HEADING '03'
COLUMN H04   FORMAT 999     HEADING '04'
COLUMN H05   FORMAT 999     HEADING '05'
COLUMN H06   FORMAT 999     HEADING '06'
COLUMN H07   FORMAT 999     HEADING '07'
COLUMN H08   FORMAT 999     HEADING '08'
COLUMN H09   FORMAT 999     HEADING '09'
COLUMN H10   FORMAT 999     HEADING '10'
COLUMN H11   FORMAT 999     HEADING '11'
COLUMN H12   FORMAT 999     HEADING '12'
COLUMN H13   FORMAT 999     HEADING '13'
COLUMN H14   FORMAT 999     HEADING '14'
COLUMN H15   FORMAT 999     HEADING '15'
COLUMN H16   FORMAT 999     HEADING '16'
COLUMN H17   FORMAT 999     HEADING '17'
COLUMN H18   FORMAT 999     HEADING '18'
COLUMN H19   FORMAT 999     HEADING '19'
COLUMN H20   FORMAT 999     HEADING '20'
COLUMN H21   FORMAT 999     HEADING '21'
COLUMN H22   FORMAT 999     HEADING '22'
COLUMN H23   FORMAT 999     HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'

SELECT
    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)                          DAY
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  v$log_history  a
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY 1
/

-- Archive generation per day

SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
SELECT To_Char(First_Time,'YYYY-MON-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max#
FROM v$log_history
GROUP BY To_Char(First_Time,'YYYY-MON-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;

Flashaback Database 10g

Flashback Oracle10gR1 10.1.0.2.0:

In Oracle 10g, the flashback functionality has been greatly extended.

Flashback Database:

Flashback Database is faster than traditional point-in-time recovery. Traditional recovery uses redo log files and backups. Flashback Database is implemented using a new type of log file called Flashback Database logs. The Oracle database server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.

Flashback Database reduces the time required to recover the database to a previous point. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.

RVWR Background Process:

When Flashback Database is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.



Figure 12.1 RVWR Background process and Flashback Database Logs Figure 12.1 RVWR Background process and Flashback.


Using Flashback Drop and Managing the Recycle Bin:

A user can view his objects in the recycle bin using the following statement:

SELECT * FROM RECYCLEBIN;

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names.
The renaming convention is as follows:BIN$unique_id$version

where:unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databasesversion is a version number assigned by the database
You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:

Oracle Flashback Table: Returning Individual Tables to Past States:

SQL> create table hr ( name varchar2(20));
Table created.
SQL> drop table hr;
Table dropped.
SQL> select * from recyclebin;
OBJECT_NAME ORIGINAL_NAME
OPERATION
------------------------------ --------------------------------
---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$74ANPU11QhWw15ah+JM2gQ==$0 HR
DROPTABLE USERS 2006-11-16:17:56:252006-11-16:17:56:34
445402 YES YES
50424 50424 50424 8

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE
DROP TIME
---------------- ------------------------------ ------------ -------------------
HR BIN$74ANPU11QhWw15ah+JM2gQ==$0 TABLE
2006-11-16:17:56:34

SQL> select * from "BIN$74ANPU11QhWw15ah+JM2gQ==$0";
no rows selected

Restoring Tables from the Recycle Bin:

SQL> flashback table hr to before drop rename to scott;
Flashback complete.


You can directly drop table using "purge" cluase.
if use "purge" clause then object not goes in recyclebin. it will delete parmanently.

SQL> drop table scott purge;
Table dropped.

You can also "purge" recyclebin
SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------
SCOTT BIN$QFb9kDMkRq2RpWJjjW9+ug==$0 TABLE 2006-11-16:18:14:26
SQL> purge recyclebin;
Recyclebin purged.
SQL> show recyclebin;

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

Flashback Query using Row Movement:

Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement.
You can enable row movement with the following SQL statement

SQL> conn hr/hrConnected.
SQL> create table big_table as select * from all_objects;
Table created.
SQL> alter table big_table enable row movement;
Table altered.
SQL> alter session set nls_date_format = 'hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------
18:43:49
SQL> select count(*) from big_table;
COUNT(*)
----------
46335
SQL> delete from big_table where rownum <= 445; 445 rows deleted.

SQL> commit;
Commit complete.

SQL> flashback table big_table to TIMESTAMP 2 to_timestamp ( '2006-11-16 18:44:00','YYYY-MM-DD HH24:MI:SS');
Flashback complete.
SQL> select count(*) from big_table;
COUNT(*)
----------
46335


Flashback Query:

you must choose an undo retention interval that is long enough to enable users to construct a snapshot of the database for the oldest version of the database that they are interested in.


SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter system set undo_retention = 3600;
System altered.
SQL> conn hr/hr
Connected.
SQL> alter session set nls_date_format = 'hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------
19:04:49
SQL> create table hrtest ( no number, name varchar2(20));
Table created.
SQL> begin
2 insert into hrtest values ( 1, 'aaa');
3 insert into hrtest values ( 2, 'bbb');
4 insert into hrtest values ( 3, 'ccc');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
--------
19:06:23
SQL> delete from hrtest where name = 'ccc';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb

SQL> edWrote file afiedt.buf
1 select * from hrtest AS OF TIMESTAMP 2 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
3* where name = 'ccc'
4 /
NO NAME
---------- --------------------
3 ccc
SQL> insert into hrtest 2 ( select * from hrtest AS OF TIMESTAMP 3 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
4 where name = 'ccc');
1 row created.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb
3 ccc

x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x
SQL> create table SCN_TEST (no number, name varchar2(20));

Table created.

SQL> insert into scn_test values (1,'a');

1 row created.

SQL> insert into scn_test values (2,'b');

1 row created.

SQL> insert into scn_test values (3,'c');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scn_test;

NO NAME
---------- --------------------
1 a
2 b
3 c

SQL> show parameter undo_retention

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 22719
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
15893109

SQL> delete from scn_test where no = 2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from scn_test AS OF SCN 15893109 where no = 2;

NO NAME
---------- --------------------
2 b

SQL> insert into scn_test ( select * from scn_test AS OF SCN 15893109
2 where no = 2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scn_test;

NO NAME
---------- --------------------
1 a
3 c
2 b


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

Using Flashback Version Query:

SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb
3 ccc
SQL> begin
2 insert into hrtest values ( 4, 'ddd');
3 insert into hrtest values ( 5, 'eee');
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> select sysdate from dual;
SYSDATE
--------
19:35:26
SQL> select sysdate from dual;
SYSDATE
--------
19:38:57
SQL> delete from hrtest where rownum <= 3;
3 rows deleted.
SQL> commit;
Commit complete.
SQL> begin
2 update hrtest set no = 0;
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select sysdate from dual;
SYSDATE
--------
19:48:42
SQL> select * from hrtest;
NO NAME
---------- --------------------
0 ddd
0 eee
SQL> ed
Wrote file afiedt.buf
1 SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 hrtest.*
5 FROM hrtest
6 VERSIONS BETWEEN TIMESTAMP
7 TO_TIMESTAMP('2006-11-16 19:35:00', 'YYYY-MM-DD HH24:MI:SS')
8 AND TO_TIMESTAMP('2006-11-16 19:48:00', 'YYYY-MM-DD HH24:MI:SS')
9* WHERE name in ('aaa','bbb','ccc');

SQL> column versions_starttime format a25
SQL> column versions_endtime format a25
SQL> /
VERSIONS_STARTSCN VERSIONS_STARTTIME
VERSIONS_ENDSCN
----------------- -------------------------
---------------
VERSIONS_ENDTIME VERSIONS_XID V NO NAME
------------------------- ---------------- - ---------- -------------------- 449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 3 ccc
449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 2 bbb
449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 1 aaa
44971816-NOV-06 07.39.10 PM
1 aaa

44971816-NOV-06 07.39.10 PM
2 bbb

44971816-NOV-06 07.39.10 PM
3 ccc
6 rows selected.


The available pseudocolumn meanings are:

* VERSIONS_STARTSCN or VERSIONS_STARTTIME - Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN ot TIMESTAMP.
* VERSIONS_ENDSCN or VERSIONS_ENDTIME - Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN ot TIMESTAMP.
* VERSIONS_XID - ID of the transaction that created the row in it's current state.
* VERSIONS_OPERATION - Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)

SELECT commit_timestamp , logon_user FROM FLASHBACK_TRANSACTION_QUERY
WHERE xid IN
(SELECT versions_xid FROM employee VERSIONS BETWEEN
TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS')
and TO_TIMESTAMP('2003-04-04 17:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');


Flashback Transaction Query:

Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view like:

SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('0600030021000000');


XID OPERATION START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------
0600030021000000 UPDATE 725208 725209
SCOTT
update "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'ONE' where ROWID = 'AAAMP9AAEAAAA
AYAAA';

0600030021000000 BEGIN 725208 725209
SCOTT

XID OPERATION START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------



2 rows selected.



Flashback Database:

Flashback Database is introduce with 10gr1, It is alternative for PITR (Point In Time Recovery).

It is superb feature, we can also called "rewind button" for database. becuase through this option we can rewind database to any point of time.

1. Database must run in archivelog mode

2. connect with SYS user and shutdown database with normal

SQL> conn / as sysdba

Connected.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

3. startup database with MOUNT mode

SQL> startup mount

4. Enable FLASHBACK DATABASE

SQL> alter database flashback on;
Database altered.

4. Open the database with normal operation

SQL> alter database open;
Database altered.

5. Check flashback database option enable for the database

SQL> select flashback_on from v$database;
FLA

---

YES

NOTE: FLASHBACK LOGS created in Flash recovery area

Up to this stage we only ENABLE the flashback database, now we need to configure one more parameter for How far can we bring the database back in time?

for that we need to configure below parameter

db_flashback_retention_target

NOTE: Parameter is dynamic, and default value is 1440 minutes means 24 hrs, parameter value is set in MINUTES

We can set FLASHBACK database option for particular tablespace also.

SQL> alter tablespace TEST1 flashback on;

SQL> alter tablespace TEST1 flashback off;

Estimating flashback database storage reqirement

We can get this information through below query after enable flashback database option.

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

How Flashback Database is important for DBA? lets check with below example

Nive (DBA) working in ABC bank as an DBA, suddenly one application user called and told he was by mistake DROP wrong production table with PURGE option.

Database version is 10gr1, so we can easily recovered dropped table through FLASHBACK TABLE but the problem is here user dropped table with PURGE option so we can't recovered dropped table without perform Point In Time Recover option.

Nive is happy becuase FLASHBACK DATABASE option is ENABLE.

So Miss Nive done the following steps

1. shutdown the database

2. flashback database before table was dropped

3. open database with read only

4. export dropped table

5. shutdown and startup database with mount mode

6. perform recover database

7. open database with normal operation without resetlogs

SQL> select current_scn from v$database;
CURRENT_SCN

-----------

542151

SQL> drop table SCOTT.EMP purge;
Table dropped.

SQL> select current_scn from v$database;
CURRENT_SCN

-----------

542182

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 144964076 bytes

Database Buffers 25165824 bytes

Redo Buffers 1048576 bytes

Database mounted.

SQL> flashback database to scn 542151;
Flashback complete.

SQL> alter database open read only;
Database altered.

SQL> desc scott.emp

Name Null? Type

----------------------------------------- -------- ---------------------------
[output cut]

SQL> host exp system/oracle file=c:\emp.dmp tables=SCOTT.EMP
Export: Release 10.1.0.2.0 - Production on

Sun Jul 20 17:42:03 2008
Copyright (c) 1982, 2004, Oracle.

All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prodution

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...

Current user changed to SCOTT. .

exporting table EMP 14 rows exported

Export terminated successfully without warnings.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 144964076 bytes

Database Buffers 25165824 bytes

Redo Buffers 1048576 bytes

Database mounted.

SQL> recover database

Media recovery complete.

SQL> alter database open;
Database altered.

SQL> desc scott.emp

ERROR:ORA-04043: object scott.emp does not exist

SQL> host imp system/oracle file=c:\emp.dmp fromuser=scott touser=scott
Import: Release 10.1.0.2.0 - Production on

Sun Jul 20 17:43:10 2008
Copyright (c) 1982, 2004, Oracle.

All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prodution

With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional pathimport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set.

importing SCOTT's objects into SCOTT. .

importing table "EMP" 14 rows imported

About to enable constraints...

Import terminated successfully without warnings.

SQL> desc scott.emp

Name Null? Type ----------------------------------------- -------- ---------------------------
[output cut]

Finally Nive able to recover dropped table :)

We can flashback database through SCN or TIMESTAMP

How to get back dropped Tablespace using Flashback Database:


It needs to remember that if you drop your tablespace with including datafiles option then it is not possible to get your tablespace back with flashback database. While dropping if you use DROP TABLESPACE INCLUDING CONTENTS option then it is possible to get back tablespace.

Procedures with and Example:
---------------------------------
1)Create a Tablespace and Table inside it.

SQL> create tablespace test_restore datafile '/oradata2/test_restore01.dbf' size 5m;
Tablespace created.

SQL> create table test tablespace test_restore as select level a1 from dual connect by level <99;
Table created.

2)Note the SCN and Drop the Tablespace with including contents option.
---------------------------------------------------------------------------
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
938686

SQL> drop tablespace test_restore including contents;
Tablespace dropped.

3)Mount the database.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 104857640 bytes
Database Buffers 54525952 bytes
Redo Buffers 6369280 bytes
Database mounted.

4)Perform FlashBack.

SQL> flashback database to scn 938686;
flashback database to scn 938686
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'

5)The datafile Become Unnamed. So rename it with original data file location.

SQL> alter database rename file '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' to
2 '/oradata2/test_restore01.dbf';
Database altered.

6)Now perforem Flashback and Open the database with read only mode.

SQL> flashback database to scn 938686;
Flashback complete.

SQL> alter database open read only;
Database altered.

SQL> select count(*) from test;
COUNT(*)
----------
98

sql> alter database open resetlogs;


FLASHBACK USING RMAN:


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Robert>cd\

C:\>net start oracleservicedba
The OracleServiceDBA service is starting............
The OracleServiceDBA service was started successfully.


C:\>set oracle_sid=dba

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 1 22:46:03 2009

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 flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> conn robo/robo
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE

SQL> select * from test;

NUM
----------
1
2
3

SQL> set time on;
22:46:58 SQL> select * from test;

NUM
----------
1
2
3

22:46:59 SQL> drop table test purge;

Table dropped.

22:47:10 SQL> ho rman target sys/sys

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Aug 1 22:48:50 2009

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

connected to target database: DBA (DBID=1152934289)

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 289406976 bytes

Fixed Size 1248576 bytes
Variable Size 75498176 bytes
Database Buffers 205520896 bytes
Redo Buffers 7139328 bytes

RMAN> flashback database to time "to_timestamp('01-AUG-2009 22:46:58','DD-MON-YYYY HH24:MI:SS')";

Starting flashback at 01-AUG-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished flashback at 01-AUG-09

RMAN> open resetlogs database;


-------->

RMAN> FLASHBACK DATABASE TO SCN 46963;

RMAN> FLASHBACK DATABASE TO SEQUENCE 5304;

RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-1/24); ----> 1 Hour before.

RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-10/(24*60)); ----> 10 minutes minus from sysdate and if it is +10 then it add 10 minutes.

If you want 10 seconds ago, use SYSDATE - (10 * (1 / 24 / 60 / 60)), because (1 / 24 / 60 / 60) is the number of days in a second.

RMAN> FLASHBACK DATABASE TO TIME timestamp('2002-11-05 14:00:00');

DMT and LMT

Difference between Dictionary managed tablespace (DMT) and Locally managed tablespace (LMT)

Introduction:
The CREATE TABLESPACE command has a new clause introduced in Oracle8i, the "extent management clause", that specifies how the extents of the tablespace are managed. This clause uses one of the following parameters:

- DICTIONARY:
Specifies that the tablespace is managed using dictionary tables. This is the default in Oracle8i.

- LOCAL:
Specifies that tablespace is locally managed. This is the default in Oracle9i. Exception for the SYSTEM tablespace

Locally Managed Tablespaces:
A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables (like sys.uet$, sys.fet$) in the data dictionary (except for special cases such as tablespace quota information).

When you create a locally managed tablespace, header bitmaps are created for each datafile. If more datafiles are added, new header bitmaps are created for each added file.

Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.

Dictionary Managed Tablespaces:
In DMT, to keep track of the free or used status of blocks, oracle uses data dictionry tables. When an extent is allocated or freed for reuse, free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.

Advantages of Locally Managed Tablespaces(LMT) over Dictionary Managed Tablespaces(DMT):

1. Reduced recursive space management
2. Reduced contention on data dictionary tables
3. No rollback generated
4. No coalescing required

Converting DMT to LMT:
SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');
PL/SQL procedure successfully completed.

Converting LMT to DMT:
SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');
PL/SQL procedure successfully completed.

Important Points:
1. LMTs can be created as
a) AUTOALLOCATE: specifies that the tablespace is system managed. Users cannot specify an extent size.
b) UNIFORM: specifies that the tablespace is managed with uniform extents of SIZE bytes. The default SIZE is 1 megabyte.
2. One cannot create a locally managed SYSTEM tablespace in 8i.
3. This is possible with in 9.2.0.X, where SYSTEM tablespace is created by DBCA as locally managed by default. With a locally managed SYSTEM tablespace, the rest of the tablespaces in such database have to be locally managed as well.
4. Locally managed temporary tablespaces can not be of type "permanent"

RAC FAQ

Oracle Real Application Clusters (RAC) FAQ - previously called Oracle Parallel Server (OPS):

What is RAC and how is it different from non RAC databases?

RAC stands for Real Application Clusters. It allows multiple nodes in a clustered system to mount and open a single database that resides on shared disk storage. Should a single system fail (node), the database service will still be available on the remaining nodes.

A non-RAC database is only available on a single system. If that system fails, the database service will be down (single point of failure).

Creating Real Application Clusters Data Dictionary Views with CATCLUST.SQL

Because you manually created your Real Application Clusters database, you must run the CATCLUST.SQL script to create Real Application Clusters-related views and tables. You must have SYSDBA privileges to run this script.

Can any application be deployed on RAC?

Most applications can be deployed on RAC without any modifications and still scale linearly (well, almost).

However, applications with 'hot' rows (the same row being accessed by processes on different nodes) will not work well. This is because data blocks will constantly be moved from one Oracle Instance to another. In such cases the application needs to be partitioned based on function or data to eliminate contention.

Do you need special hardware to run RAC?

RAC requires the following hardware components:

• A dedicated network interconnect - might be as simple as a fast network connection between nodes; and
• A shared disk subsystem.

Example systems that can be used with RAC:

• Windows Clusters
• Linux Clusters
• Unix Clusters like SUN PDB (Parallel DB).
• IBM z/OS in SYSPLEX
• HP Serviceguard extension for RAC

How many OCR and voting disks should one have?

For redundancy, one should have at lease two OCR disks and three voting disks (raw disk partitions). These disk partitions should be spread across different physical disks.

How does one convert a single instance database to RAC?

Oracle 10gR2 introduces a utility called rconfig (located in $ORACLE_HOME/bin) that will convert a single instance database to a RAC database.

$ cp $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC.xml racconv.xml

$ vi racconv.xml

$ rconfig racconv.xml

One can also use dbca and enterprise manager to convert the database to RAC mode.

For prior releases, follow these steps:

• Shut Down your Database:

SQL> CONNECT SYS AS SYSDBA

SQL> SHUTDOWN NORMAL

• Enable RAC - On Unix this is done by relinking the Oracle software.

• Make the software available on all computer systems that will run RAC. This can be done by copying the software to all systems or to a shared clustered file system.

• Each instance requires its own set of Redo Log Files (called a thread). Create additional log files:

SQL> CONNECT SYS AS SYSBDA

SQL> STARTUP EXCLUSIVE

SQL> ALTER DATABASE ADD LOGFILE THREAD 2

SQL>     GROUP G4 ('RAW_FILE1') SIZE 500k,

SQL>     GROUP G5 ('RAW_FILE2') SIZE 500k,

SQL>     GROUP G6 ('RAW_FILE3') SIZE 500k;

SQL> ALTER DATABASE ENABLE PUBLIC THREAD 2;

• Each instance requires its own set of Undo segments (rollback segments). To add undo segments for New Nodes:

UNDO_MANAGEMENT = auto

UNDO_TABLESPACE = undots2

• Edit the SPFILE/INIT.ORA files and number the instances 1, 2,...:

CLUSTER_DATABASE = TRUE           (PARALLEL_SERVER = TRUE prior to Oracle9i).  

INSTANCE_NUMBER = 1

THREAD = 1

UNDO_TABLESPACE = undots1          (or ROLLBACK_SEGMENTS if you use UNDO_MANAGEMENT=manual)

# Include %T for the thread in the LOG_ARCHIVE_FORMAT string.

# Set LM_PROCS to the number of nodes * PROCESSES

# etc....

• Create the dictionary views needed for RAC by running catclust.sql (previously called catparr.sql):

SQL> START ?/rdbms/admin/catclust.sql

• On all the computer systems, startup the instances:

SQL> CONNECT / as SYSDBA

SQL> STARTUP;

How does one stop and start RAC instances?

There are no difference between the way you start a normal database and RAC database, except that a RAC database needs to be started from multiple nodes. The CLUSTER_DATABASE=TRUE (PARALLEL_SERVER=TRUE) parameter needs to be set before a database can be started in cluster mode.

In Oracle 10g one can use the srvctl utility to start instances and listener across the cluster from a single node. Here are some examples:

$ srvctl status database -d RACDB

$ srvctl start database -d RACDB

$ srvctl start instance -d RACDB -i RACDB1

$ srvctl start instance -d RACDB -i RACDB2

$ srvctl stop database -d RACDB

$ srvctl start asm -n node2

Before Oracle 8.0, use the following command sequence from each node (using the old server manager):

SVRMGR> connect INTERNAL

SVRMGR> set retries 5

SVRMGR> startup parallel retry      .. or SVRMGR> startup shared

You can also use the SET INSTANCE instanceN command to switch between instances (if defined in TNSNAMES.ORA).

Can I test if a database is running in RAC mode?

Use the DBMS_UTILITY package to determine if a database is running in RAC mode or not. Example:

BEGIN

  IF dbms_utility.is_cluster_database THEN

      dbms_output.put_line('Running in SHARED/RAC mode.');

  ELSE

      dbms_output.put_line('Running in EXCLUSIVE mode.');

  END IF;

END;

/

For Oracle 8i and prior releases:

BEGIN

  IF dbms_utility.is_parallel_server THEN

      dbms_output.put_line('Running in SHARED/PARALLEL mode.');

  ELSE

      dbms_output.put_line('Running in EXCLUSIVE mode.');

  END IF;

END;

/

Another method is to look at the database parameters. For example, from SQL*Plus:

SQL> show parameter CLUSTER_DATABASE

If the value of CLUSTER_DATABASE is FALSE then database is not running in RAC Mode.

How can I keep track of active instances?

You can keep track of active RAC instances by executing one of the following queries:

SELECT * FROM SYS.V_$ACTIVE_INSTANCES;

SELECT * FROM SYS.V_$THREAD;

To list the active instances from PL/SQL, use DBMS_UTILITY.ACTIVE_INSTANCES().

How does one setup SQL*Net for RAC?

See Transparent Application Failover.

Can one see how connections are distributed across the nodes?

Select from gv$session. Some examples:

SELECT inst_id, count(*) "DB Sessions" FROM gv$session

 WHERE type = 'USER' GROUP BY inst_id;

With login time (hour):

SELECT inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24') "Hour when connected", count(*) "DB Sessions"

  FROM gv$session

 WHERE type = 'USER'

 GROUP BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24')

 ORDER BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24');

[edit] What is pinging and why is it so bad?

Starting with Oracle 9i, RAC can transfer blocks from one instance to another across the interconnect (cache fusion). This method is much faster than the old "pinging" method, where one instance had to write the block to disk before another instance could read it.

Oracle 8i and below:

Pinging is the process whereby one instance requests another to write a set of blocks from its SGA to disk so it can obtain it in exclusive mode. This method of moving data blocks from one instance's SGA to another is extremely slow. The challenge of tuning RAC/OPS is to minimize pinging activity.

Real Application Clusters

From Oracle FAQ

Jump to: navigation, search

Oracle Real Application Clusters (RAC) is a database clustering solution that allows more than one instance to mount and open an Oracle database. RAC can only be used on special clustered systems with shared disk storage and a private network interconnect.

A normal Oracle installation consists of a single Oracle instance that accesses a database on the same computer system. In contrast, RAC allows multiple instances on different computer systems (nodes in a cluster) to access the same database files simultaneously. Communication between instances is managed by the Distributed Lock Manager (DLM). To address the possibility of two or more instances attempting to modify the same information simultaneously, Oracle uses up to ten additional background processes, named LCK0 through LCK9, to lock the resources in use by these instances.

RAC is available with Oracle Enterprise Edition, and under certain conditions, with Standard Edition as well. These restrictions (for Standard Edition) include:

• Must use Oracle Clusterware (no third-party clusterware allowed);
• Must use ASM to store database files; and
• Can only use a max of 4 CPU sockets in the cluster (either 2 nodes with 2 CPUs each, or 4 nodes with 1 CPU each).

Contents [hide] 1 History 2 Benefits 3 RAC Storage Options 4 Oracle Clusterware 4.1 Virtual IP 4.2 Voting Disk 4.3 Cluster Registry 4.4 Clusterware processes 4.5 Stop and start 4.6 Starting and stopping resources 5 RAC parameters 6 GV$ views 7 Configure archiving 8 Configure Services 9 Also see 10 External links

[edit] History

RAC was previously called Oracle Parallel Server (OPS). Oracle re-branded OPS as RAC when they released Oracle 9i.

[edit] Benefits

Running Oracle in RAC mode can benefit you in the following ways:

• High availability - If some nodes fail, the remainder of the nodes will still be available for processing requests. Failover support is available from Oracle 8 with Oracle's Transparent Application Failover (TAF) technology and from Oracle 10g, server side load balancing.

• Speedup (increased transaction response time) - RAC normally adds some overhead. However, for some DSS applications one can achieve increased performance by running operations in parallel (mainly for DSS applications).

• Scale-up (increased transaction volume) - RAC can be used to provide increased application scalability (mainly used for OLTP applications).

[edit] RAC Storage Options

The database's data and control files are shared between the instances. However, each instance must have its own UNDO and REDO:

• UNDO: Each instance must have its own UNDO_TABLESPACE
• REDO: Each instance must have its own REDO LOG files (called a thread)

Shared files can be stored on:

• RAW volumes
• ASM
• Clustered file systems (like OCFS)

[edit] Oracle Clusterware

Oracle Clusterware (previously called Cluster Ready Services or CRS) provides Cluster Management Services and High Availability Services to Oracle and 3rd party software that wants to hook into it. For example, if you kill your LGWR process, CRS will detect the failure and automatically restart the database.

Oracle clusterware eliminates the need for 3rd party clusterware software like SUN Clusters, IBM HACMP and HP Omniguard. Oracle clusterware is provided at no additional cost with the 10g database.

Oracle clusterware needs to be installed on all nodes of the cluster before installing the database software. During installation, you will be prompted to configure a virtual IP, voting disk and cluster registry.

[edit] Virtual IP

IP address that will fail-over to another node in the cluster when a failure is detected. This will allow connected session to (almost) immediately fail-over to another node when a problem is experienced.

RAC requires one or more private interconnects, and two or more public network interfaces.

[edit] Voting Disk

A Voting Disk is a shared disk device or file used to determine node availability (establishes quorum) and resolve split-brain scenarios. All instances write to the voting disk (check in) to indicate that they are still active. This is required as instances may not always be able to communicate across the network with each other.

The voting disk must be stored in a raw partition or regular file in a clustered filesystem (like OCFS, but not on ASM!).

The voting disk, like the OCR, should be multiplexed and backed-up to protect them against media failures.

[edit] Cluster Registry

The Cluster Registry (OCR) is used to store cluster wide settings and status information. For example: node names, IP and VIP addresses, voting disk locations, node applications, database names, instance names, listener names, etc.

The Oracle Cluster Registry (OCR) is a binary file that is maintained by the CRS daemon. The OCR must be stored in a raw partition or regular file in a clustered filesystem (not on ASM!).

To see what is in the OCR, use the "ocrdump" command to dump it contents to a flat file.

[edit] Clusterware processes

When clusterware is started, the following process will be running:

crsd – Cluster Resource Services Daemon cssd – Cluster Synchronization Services Daemon evmd – Event Manager Daemon

Corresponded executables are located in $ORA_CRS_HOME/bin/.

[edit] Stop and start

Oracle Clusterware is started at boot with the /etc/rc.d/init.d/init.crs script.

Commands to manually start or stop:

/etc/init.d/init.crs start 

/etc/init.d/init.crs stop 

Commands to manually disable or enable start at boot time:

/etc/init.d/init.crs disable 

/etc/init.d/init.crs enable 

Check status of components registered in the OCR:

crs_stat

Status in tabular format:

crs_stat -t

[edit] Starting and stopping resources

Stops all RAC instances:

$ srvctl stop database –d myracdb 

Stops Listener, VIP, GSD, ONS:

$ srvctl stop nodeapps –n racnode1 

Starts ASM on racnode1 and all required dependencies:

$ srvctl start asm –n racnode1

Starts one instance and all required dependencies:

$ srvctl start instance –d myracdb –i mydb1

For more info, read article srvctl.

RAC parameters

Some of the INIT.ORA/SPFILE parameters required for RAC:

• CLUSTER DATABASE=TRUE -- start the database in RAC mode
• INSTANCE_NUMBER=n -- a unique instance number
• THREAD=n -- each instance must have its own thread of redo logs
• UNDO_TABLESPACE=... -- each instance must have its own undo tablespace
• Etc.

[edit] GV$ views

The V$ views only shows details from the currently connected instance. Use the GV$ parameters to query all values across all instances (note the INST_ID column).

For example: instead of using V$THREAD, query GV$THREAD to see all threads across all nodes.

[edit] Configure archiving

Since each instance has its own thread of redo logs, all instances needs to archive its own redo logs. For convenience, it is recommended that each instance archives to a shared cluster file system.

[edit] Configure Services

One can use DBCA or srvctl to configure a service with "primary" instances and "available" instances. Client connections will be routed to the "primary" instances. When one of the "primary" instances becomes unavailable, one of the "available" instance will be promoted to "primary".

NOTE: while fail-over to a "available" instance happens automatically, you need to manually switch them back to "primary" when they become available again.