Resumable Space Allocation

Enabling and Disabling Resumable Space Allocation

Resumable space allocation is only possible when statements are executed within a session that has resumable mode enabled. There are two means of enabling and disabling resumable space allocation. You can control it at the system level with the RESUMABLE_TIMEOUT initialization parameter, or users can enable it at the session level using clauses of the ALTER SESSION statement.

Note:

Because suspended statements can hold up some system resources, users must be granted the RESUMABLE system privilege before they are allowed to enable resumable space allocation and execute resumable statements.

Setting the RESUMABLE_TIMEOUT Initialization Parameter

You can enable resumable space allocation system wide and specify a timeout interval by setting the RESUMABLE_TIMEOUT initialization parameter. For example, the following setting of the RESUMABLE_TIMEOUT parameter in the initialization parameter file causes all sessions to initially be enabled for resumable space allocation and sets the timeout period to 1 hour:

RESUMABLE_TIMEOUT = 3600

If this parameter is set to 0, then resumable space allocation is disabled initially for all sessions. This is the default.

You can use the ALTER SYSTEM SET statement to change the value of this parameter at the system level. For example, the following statement will disable resumable space allocation for all sessions:

ALTER SYSTEM SET RESUMABLE_TIMEOUT=0;

Within a session, a user can issue the ALTER SESSION SET statement to set the RESUMABLE_TIMEOUT initialization parameter and enable resumable space allocation, change a timeout value, or to disable resumable mode.

Using ALTER SESSION to Enable and Disable Resumable Space Allocation

A user can enable resumable mode for a session, using the following SQL statement:

ALTER SESSION ENABLE RESUMABLE;

To disable resumable mode, a user issues the following statement:

ALTER SESSION DISABLE RESUMABLE;

The default for a new session is resumable mode disabled, unless the RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value.

The user can also specify a timeout interval, and can provide a name used to identify a resumable statement. These are discussed separately in following sections.

See Also:

"Using a LOGON Trigger to Set Default Resumable Mode"

CREATE OR REPLACE TRIGGER TRG_RESUMABLE_NOTIFY AFTER SUSPEND ON DATABASE
BEGIN
UTL_MAIL.SEND (
sender => 'babu.b@edkal.com',
recipients => 'babu.b@edkal.com',
subject => 'Alert !!!' ,
message => 'No Space available'
);
END;

Specifying a Timeout Interval

A timeout period, after which a suspended statement will error if no intervention has taken place, can be specified when resumable mode is enabled. The following statement specifies that resumable transactions will time out and error after 3600 seconds:

ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;

The value of TIMEOUT remains in effect until it is changed by another ALTER SESSION ENABLE RESUMABLE statement, it is changed by another means, or the session ends. The default timeout interval when using the ENABLE RESUMABLE TIMEOUT clause to enable resumable mode is 7200 seconds.

Monitor Resumable

DBA_RESUMABLE, USER_RESUMABLE

These views contain rows for all currently executing or suspended resumable statements. They can be used by a DBA, AFTER SUSPEND trigger, or another session to monitor the progress of, or obtain specific information about, resumable statements.

Check Rman Syntax

In command prompt you can run the following cmd. Here you can check your rman command correct or not.

% RMAN CHECKSYNTAX

RMAN> RUN

2> {

3> BACKUP DATABASE;

4> }

The command has no syntax errors

Rman New Feature On 10.2.0

RMAN Encrypted Backups

RMAN now supports several forms of encryption for backups created as backup sets, whether on disk or on tape. Encryption can be based upon passwords provided through RMAN or transparent encryption capabilities based upon the Oracle Encryption Wallet. Once configured, existing RMAN backup procedures take advantage of encryption features with no change.

Flashback Database Enhancements

Flashback Database can now reverse the effects of OPEN RESETLOGS operations, returning a database to points in time in ancestor or even sibling incarnations. This allows its use in many more data recovery scenarios. It also integrates with guaranteed restore points.

Restore Points

Restore Points are aliases for SCNs, which eliminate the need to manually research and record SCNs or timestamps to use for Flashback Database and Flashback Table operations.

Guaranteed Restore Points

Guaranteed restore points ensure that RMAN FLASHBACK DATABASE can be used to return your database to a specific point in time. Using guaranteed restore points instead of regular logging for Flashback Database uses disk space more efficiently and reduces performance impact of flashback logging when the only requirement is return to a specific point in time. Used in this way, guaranteed restore points provide an efficient alternative to a storage snapshot.

Guaranteed restore points can also be used with normal Flashback Database logging, to guarantee FLASHBACK DATABASE works to any time as far back as the guaranteed restore point.

Incremental Roll Forward of Database Copy

RMAN incremental backups can now be used to update a standby database with changes from a primary since a given SCN.

Easy Conversion of Physical Standby Database to a Reporting Database

Easy conversion of a physical standby database to a reporting database and back to a standby is now possible, because Flashback Database can now reverse the activation of a standby database. A guaranteed restore point retains the state of the standby before activation, and after reporting the DBA can flash back the standby to that guaranteed restore point, use incremental backups to update the standby with changes from the primary during reporting, and resume managed recovery.

Database Transport Across Same Endian Platforms

RMAN now supports the CONVERT DATABASE command, which can prepare a whole database for transport to a new platform that uses the same endian format. Database transport across platforms provides a faster and easier way to move databases from one platform to another than previous solutions requiring the use of Data Pump.

Transportable Tablespaces from Backup

RMAN now automates the creation of transportable tablespace sets using backups instead of the datafiles of the running database. With a single RMAN command, you can now create transportable sets without making the source datafiles read-only.

Unused Block Compression

RMAN now creates more compact backups of datafiles, by skipping datafile blocks that are not currently used to store data. In previous releases, RMAN only supported NULL compression, which skipped space in datafiles that had never been allocated. No extra action is required on the part of the DBA to use this feature.

Temporary Datafiles Are Re-Created on RMAN Recovery

Temporary datafiles that belong to locally managed temporary tablespaces are automatically re-created during database recovery. This eliminates the need to manually create temporary tablespaces after recovery.

Support for Backup Vaulting in Media Managers

When used with a media manager that supports backup vaulting, RMAN RESTORE... PREVIEW now reports any backups that are currently stored remotely, and RMAN RESTORE... PREVIEW RECALL now initiates retrieval of vaulted backups for use in an actual RESTORE operation.

Backup and Recovery Enhancements in Enterprise Manager

Enterprise Manager now includes backup validation, enhanced backup reporting and scheduling, and automated creation and management of recovery catalog databases.

Cold Backup in Windows 2000 Server

Cold Backup in Windows 2000 Server

I have implemented cold backup in windows 2000 server. Here with i attached my script. Please try in testing environment and let me know your comments…

Coldbackup.bat

sqlplus -s system/manager@sngdb @C:\Dbbackup\CreateBackup.sql

Shutdown.bat

D:\oracle\ora92\bin\sqlplus -s "/@sngdb as sysdba" @C:\dbbackup\shutdown.sql

Startup.bat

D:\oracle\ora92\bin\sqlplus -s "/@sngdb as sysdba" @C:\dbbackup\startup.sql

TakeColdBackup.bat

/*** Script Generated On September 18, 2007 17:10:18 ***/

Spool E:\Dbbackup\HotBackup.log

SELECT TO_CHAR(SYSDATE, 'fmMONTH fmDD, YYYY HH24:MI:SS') "Started On" FROM DUAL;

PROMPT -- Starting Backup...

PROMPT -- Making Directory E:\Dbbackup\20070918171018

MkDir E:\Dbbackup\20070918171018

MkDir E:\Dbbackup\20070918171018\Oradata

Copy D:\ORACLE\ORADATA\SNGDB\SYSTEM01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\UNDOTBS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\CWMLITE01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\DRSYS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SYSTEM02.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\INDX01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\ODM01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\TOOLS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\USERS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\XDB01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\PERSONNEL01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\ANJUMIS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIPURCHASE01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIPURCHASE02.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\INVENTORY01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIINVENTORY01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIINVENTORY02.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIINVENTORY03.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\PRODUCTION01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIPRODUCTION01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIPRODUCTION02.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIEXPORTS01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIQC01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMIRD01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\SAMISALES01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\QC01.DBF E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\CONTROL01A.CTL E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\CONTROL02A.CTL E:\Dbbackup\20070918171018\Oradata\

Copy C:\ORABAK\SNGDB\CONTROL\CONTROL01B.CTL E:\Dbbackup\20070918171018\Oradata\

Copy C:\ORABAK\SNGDB\CONTROL\CONTROL02B.CTL E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\REDO03.LOG E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\REDO02.LOG E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\REDO01.LOG E:\Dbbackup\20070918171018\Oradata\

Copy D:\ORACLE\ORADATA\SNGDB\TEMP01.DBF E:\Dbbackup\20070918171018\Oradata\

PROMPT -- Making Directory E:\Dbbackup\20070918171018\NETWORK\ADMIN

MkDir E:\Dbbackup\20070918171018\NETWORK\ADMIN

PROMPT -- Copying Listener.Ora From D:\oracle\ora92\NETWORK\ADMIN To E:\Dbbackup\20070918171018\NETWORK\ADMIN

XCopy D:\oracle\ora92\NETWORK\ADMIN\listener.ora E:\Dbbackup\20070918171018\NETWORK\ADMIN

PROMPT -- Copying TnsNames.Ora From D:\oracle\ora92\NETWORK\ADMIN To E:\Dbbackup\20070918171018\NETWORK\ADMIN

XCopy D:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora E:\Dbbackup\20070918171018\NETWORK\ADMIN

PROMPT -- Copying SqlNet.Ora From D:\oracle\ora92\NETWORK\ADMIN To E:\Dbbackup\20070918171018\NETWORK\ADMIN

XCopy D:\oracle\ora92\NETWORK\ADMIN\sqlnet.ora E:\Dbbackup\20070918171018\NETWORK\ADMIN

PROMPT -- Making Directory E:\Dbbackup\20070918171018\PFILE

MkDir E:\Dbbackup\20070918171018\PFILE

PROMPT -- Copying Initialization File From D:\oracle\admin\sngdb\pfile To E:\Dbbackup\20070918171018\PFILE

XCopy D:\oracle\admin\sngdb\pfile\init.ora E:\Dbbackup\20070918171018\PFILE

PROMPT -- Copying SPFile D:\oracle\ora92\database\SPFILEsngdb.ORA To E:\Dbbackup\20070918171018\PFILE

Copy D:\oracle\ora92\database\SPFILEsngdb.ORA E:\Dbbackup\20070918171018\PFILE

PROMPT -- Copying Password File D:\oracle\ora92\database\PWDsngdb.ora To E:\Dbbackup\20070918171018\PFILE

XCopy D:\oracle\ora92\database\PWDsngdb.ora E:\Dbbackup\20070918171018\PFILE

PROMPT -- ... Ending Backup

SELECT TO_CHAR(SYSDATE, 'fmMONTH fmDD, YYYY HH24:MI:SS') "Ended On" FROM DUAL;

Spool Off

Exit

/*** End of Script ***/

CreateBackup.sql

SET SERVEROUTPUT ON

SET LINESIZE 120

SET HEADING OFF

SET FEEDBACK OFF

SET TERMOUT OFF

SPOOL C:\DBBackup\TakeColdBackup.bat

DECLARE

v_backupfolder VARCHAR2(500) DEFAULT 'E:\Dbbackup';

v_backupnetworkfolder VARCHAR2(500);

v_backuppfilefolder VARCHAR2(500);

v_networkfolder VARCHAR2(500) DEFAULT 'D:\oracle\ora92\NETWORK\ADMIN';

v_pfilefolder VARCHAR2(500) DEFAULT 'D:\oracle\admin\sngdb\pfile';

v_spfile VARCHAR2(500) DEFAULT 'D:\oracle\ora92\database\SPFILEsngdb.ORA';

v_pwdfile VARCHAR2(500) DEFAULT 'D:\oracle\ora92\database\PWDsngdb.ora';

v_currentdate VARCHAR2(100) DEFAULT TO_CHAR(SYSDATE, 'Month DD, YYYY HH24:MI:ss');

v_oradatafolder varchar2(500) DEFAULT '\Oradata' ;

CURSOR c_dfile

IS

SELECT NAME from v$datafile

union all

SELECT name from v$controlfile

union all

SELECT MEMBER from v$logfile

union all

SELECT name from v$tempfile;

BEGIN

dbms_output.enable(20000);

dbms_output.put_line('/*** Script Generated On ' || v_currentdate || ' ***/');

dbms_output.put_line('Spool ' || v_backupfolder || '\HotBackup.log');

v_backupfolder := v_backupfolder || '\' || TO_CHAR(SYSDATE, 'yyyymmddhh24miss');

v_backupnetworkfolder := v_backupfolder || '\NETWORK\ADMIN';

v_backuppfilefolder := v_backupfolder || '\PFILE';

dbms_output.put_line('SELECT TO_CHAR(SYSDATE, ''fmMONTH fmDD, YYYY HH24:MI:SS'') "Started On" FROM DUAL;');

dbms_output.put_line('PROMPT -- Starting Backup...');

dbms_output.put_line('PROMPT -- Making Directory ' || v_backupfolder);

dbms_output.put_line(' MkDir ' || v_backupfolder );

dbms_output.put_line('MkDir ' || v_backupfolder || v_oradatafolder );

FOR r_tbsp IN c_dfile LOOP

dbms_output.put_line(' Copy' || ' ' || r_tbsp.name || ' ' || v_backupfolder || v_oradatafolder ||'\' );

END LOOP;

dbms_output.put_line('PROMPT -- Making Directory ' || v_backupnetworkfolder);

dbms_output.put_line(' MkDir ' || v_backupnetworkfolder);

dbms_output.put_line('PROMPT -- Copying Listener.Ora From ' || v_networkfolder || ' To ' || v_backupnetworkfolder);

dbms_output.put_line(' XCopy ' || v_networkfolder || '\listener.ora ' || v_backupnetworkfolder);

dbms_output.put_line('PROMPT -- Copying TnsNames.Ora From ' || v_networkfolder || ' To ' || v_backupnetworkfolder);

dbms_output.put_line(' XCopy ' || v_networkfolder || '\tnsnames.ora ' || v_backupnetworkfolder);

dbms_output.put_line('PROMPT -- Copying SqlNet.Ora From ' || v_networkfolder || ' To ' || v_backupnetworkfolder);

dbms_output.put_line(' XCopy ' || v_networkfolder || '\sqlnet.ora ' || v_backupnetworkfolder);

dbms_output.put_line('PROMPT -- Making Directory ' || v_backuppfilefolder);

dbms_output.put_line(' MkDir ' || v_backuppfilefolder);

dbms_output.put_line('PROMPT -- Copying Initialization File From ' || v_pfilefolder || ' To ' || v_backuppfilefolder);

dbms_output.put_line(' XCopy ' || v_pfilefolder || '\init.ora ' || v_backuppfilefolder);

dbms_output.put_line('PROMPT -- Copying SPFile ' || v_spfile || ' To ' || v_backuppfilefolder);

dbms_output.put_line(' Copy ' || v_spfile || ' ' || v_backuppfilefolder);

dbms_output.put_line('PROMPT -- Copying Password File ' || v_pwdfile || ' To ' || v_backuppfilefolder);

dbms_output.put_line(' XCopy ' || v_pwdfile || ' ' || v_backuppfilefolder);

dbms_output.put_line('PROMPT -- ... Ending Backup');

dbms_output.put_line('SELECT TO_CHAR(SYSDATE, ''fmMONTH fmDD, YYYY HH24:MI:SS'') "Ended On" FROM DUAL;');

dbms_output.put_line('Spool Off');

dbms_output.put_line('Exit');

dbms_output.put_line('/*** End of Script ***/');

END;

/

SPOOL OFF

SET TERMOUT ON

SET FEEDBACK ON

SET HEADING ON

SET LINESIZE 80

SET SERVEROUTPUT OFF

EXIT

Shutdown.sql

Shutdown immediate;

Exit;

Startup.sql

startup;

exit;