Resumable space allocation in 10g

 

Resumable space allocation, introduced in Oracle 9i, is for all tablespaces at the session level. Database operations are suspended when an out-of-space condition is encountered. These suspended operations automatically resume when the error condition disappears. In Oracle Database 10g, this can be enabled at the instance level. Besides this improvement, automatic alert notification is sent when an operation is suspended.

In  Oracle 10gR1 the parameter RESUMABLE_TIMEOUT
can also be set on system level for all sessions.
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Mar 31 11:58:08 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user test identified by test
  2  default tablespace example
  3  quota 1m on example;

User created.

SQL> grant create session ,create table to test;

Grant succeeded.

SQL> conn test
Enter password:
Connected.
SQL> create table t1 (eno number);

Table created.
SQL> BEGIN FOR i IN 1..10000 LOOP
  2   INSERT INTO t1 VALUES(i);
  3  END LOOP;
  4   COMMIT;
  5   END;
  6  /

PL/SQL procedure successfully completed.
SQL>  insert into t1 select * from t1;

10000 rows created.

SQL>  insert into t1 select * from t1;

20000 rows created.

SQL>  insert into t1 select * from t1;
 insert into t1 select * from t1
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘EXAMPLE’

 

SQL> SELECT COUNT(*) FROM t1;

  COUNT(*)
———-
     40000

SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 600;
ERROR:
ORA-01031: insufficient privileges
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant resumable to test;

Grant succeeded.
SQL> conn test
Enter password:
Connected.
SQL>  insert into t1 select * from t1;
 insert into t1 select * from t1
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘EXAMPLE’
SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 600;

Session altered.

SQL>  insert into t1 select * from t1;(this statement will wait )

 

————————from Another session (session 2) ——————-

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Mar 31 12:19:46 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Enter user-name: / as sysdba
SQL> select sid,state,event from v$session_wait where sid=159;

       SID STATE         EVENT
———- ———– ——————————————-

       159 WAITING     statement suspended, wait error to be cleared

 

SQL> alter user test quota unlimited on example;

User altered.
————————Now check the SESSION 1—————————-
SQL>  insert into t1 select * from t1;

40000 rows created.

RMAN -Block change tracking

Hello DBA’S,

In our Production database(10.2.0.4) , currently we are using a backup  stratergy with
a full backup once a month and an incremental backup each week and the archived logs are backed up once a day.
All backups are directly taken in the  tape drives through a network , These tape drives are located in the different server…

In case of any recovery issues we need to transfer the backup sets from the tape to the disk through the network,so we found that there is some increase in the  recovery time .
And we decided to increase the frequency of the incremental backup in order to reduce its size .So we decided to use the Oracle 10g new feature of Block Change Tracking .

Block change tracking causes the changed database blocks to be flagged in a file.As data blocks change, the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory. When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file.To achive this we need to enable the block change traking in our database:

SQL> conn sys as sysdba;
connected.

SQL> SELECT status FROM v$block_change_tracking;

STATUS
—————————————-
DISABLED

To enable this you need to create a new file for block change traking.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
  2  using file ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRD\change_track_file.dbf’ reuse;

Database altered.

SQL> SELECT status FROM v$block_change_tracking;

STATUS
—————————————-
ENABLED

If a block is changed in the database, the fact is recorded in this  file:D:\ORACLE\PRODUCT\10.2.0\ORADATA\PRD\change_track_file.dbf.
During incremental backup, RMAN checks this file to see which blocks need to be backed up instead of checking all the blocks of a data file.

It dramatically reduces CPU cycles and speeds up incremental backup in the process.
The block change tracking file, if present, is used automatically by the incremental backup; no special RMAN syntax is required. Using a block change tracking file saves enough time and CPU cycles  to take incremental backups every  night instead of taking once a week.

In case if you want to disable it ,you can use the below syntax.

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
 
Database altered.