Transcript Oracle 10.2 New Features

Oracle 10.2
New Features
Julian Dyke
Independent Consultant
Web Version
1
© 2005 Julian Dyke
juliandyke.com
Introduction
2
© 2005 Julian Dyke
juliandyke.com
A Brief History of Oracle
3
© 2005 Julian Dyke
Version
Date
2
June 1979
3
March 1983
4
October 1984
5.0
April 1985
6.0
July 1988
7.0
June 1992
7.1
May 1994
7.2
May 1995
7.3
February 1996
juliandyke.com
A Brief History of Oracle

4
Continued....
Version
Date
Release Name
8.0
June 1997
Oracle 8
8.1.5
February 1999
Oracle 8i Release 1
8.1.6
November 1999
Oracle 8i Release 2
8.1.7
August 2000
Oracle 8i Release 3
9.0.1
June 2001
Oracle 9i Release 1
9.2
May 2002
Oracle 9i Release 2
10.1
January 2004
Oracle 10g Release 1
10.2
July 2005
Oracle 10g Release 2
© 2005 Julian Dyke
juliandyke.com
Comparison Between 10.1 and 10.2
5
Version
10.1
10.2
Supported Parameters
255
258
Unsupported Parameters
918
1127
Dynamic Performance Views (V$)
340
396
Fixed Views (X$)
529
597
Events (Waits)
811
874
Statistics
332
363
Latches
348
382
Background Processes (Fixed SGA)
109
157
© 2005 Julian Dyke
juliandyke.com
DML Error
Logging
6
© 2005 Julian Dyke
juliandyke.com
DML Error Logging
7

Introduced in Oracle 10.2

Works with DML statements:
 INSERT
 UPDATE
 MERGE
 DELETE

Logs errors encountered during DML operations in error
logging table

Avoids rolling back entire statement if an error occurs
© 2005 Julian Dyke
juliandyke.com
DML Error Logging Table

Information about failed rows written to DML Error Logging
Table

Default name is ERR$_ plus first 25 characters of table name

Contains
 Mandatory columns - Oracle control information
 Optional columns - contain data from failed rows

Can be created manually or using DBMS_ERRLOG package
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG
(<DML table_name>,[<error_table_name>]);
8
© 2005 Julian Dyke
juliandyke.com
DML Error Logging Table

To create a DML error logging table use:
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG
(<DML table_name>);

For example:
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG
('CAR');

Creates DML error table called ERR$_CAR

Can optionally specify name for DML error table
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG
('CAR'.'ERR_CAR');

9
DBMS_ERRLOG creates columns with recommended data
types in DML Error Table
© 2005 Julian Dyke
juliandyke.com
Mandatory Columns

10
Required in DML Error Logging Table
Column Name
Data Type
Description
ORA_ERR_NUMBER$
NUMBER
Oracle error number
ORA_ERR_MESG$
VARCHAR2(2000)
Oracle error message text
ORA_ERR_ROWID$
ROWID
Rowid of the row in error
(update and delete only)
ORA_ERR_OPTYP$
VARCHAR2(2)
Type of operation
I = insert, U = update, D = delete
ORA_ERR_TAG$
VARCHAR2(2000)
User supplied tag
© 2005 Julian Dyke
juliandyke.com
Optional Columns




11
Can have zero, one or more columns
Contain data from failed rows
Error table column names same as DML table column names
Error table data types may differ from DML table data types
 Capture type conversion errors
 Column overflow
DML Table Column Type
Error Logging Table Column Type
NUMBER
VARCHAR2(4000)
CHAR/VARCHAR2(n)
VARCHAR2(4000)
NCHAR/NVARCHAR2(n)
NVARCHAR2(4000)
DATE/TIMESTAMP
VARCHAR2(4000)
RAW
RAW(2000)
ROWID
UROWID
LONG/LOB
Not supported
User-defined types
Not supported
© 2005 Julian Dyke
juliandyke.com
Example: CAR table
Column Name
Data Type
SEASON_KEY
VARCHAR2(4)
RACE_KEY
VARCHAR2(2)
POSITION
NUMBER
DRIVER_KEY
VARCHAR2(4)
TEAM_KEY
VARCHAR2(3)
ENGINE_KEY
VARCHAR2(3)
LAPS_COMPLETED
NUMBER
CLASSIFICATION_KEY
VARCHAR2(4)
NOTES
VARCHAR2(100)
DRIVER_POINTS
NUMBER
TEAM_POINTS
NUMBER
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG
('CAR'.'ERR_CAR');
12
© 2005 Julian Dyke
juliandyke.com
Example: ERR_CAR table
13
Column Name
Data Type
ORA_ERR_NUMBER$
NUMBER
ORA_ERR_MESG$
VARCHAR2(2000)
ORA_ERR_ROWID$
ROWID
ORA_ERR_OPTYP$
VARCHAR2(2)
ORA_ERR_TAG$
VARCHAR2(2000)
SEASON_KEY
VARCHAR2(4000)
RACE_KEY
VARCHAR2(4000)
POSITION
VARCHAR2(4000)
DRIVER_KEY
VARCHAR2(4000)
TEAM_KEY
VARCHAR2(4000)
ENGINE_KEY
VARCHAR2(4000)
LAPS_COMPLETED
VARCHAR2(4000)
CLASSIFICATION_KEY
VARCHAR2(4000)
NOTES
VARCHAR2(4000)
DRIVER_POINTS
VARCHAR2(4000)
TEAM_POINTS
VARCHAR2(4000)
© 2005 Julian Dyke
Mandatory
Columns
Optional
Columns
juliandyke.com
LOG ERRORS Clause

Syntax is:
LOG ERRORS INTO <error_table>[('<tag>')]
REJECT LIMIT <limit>;
14

Can optionally specify a REJECT LIMIT subclause
 number of errors before statement terminates and rolls
back
 can also specify UNLIMITED
 default value is 0
 if statement rolls back, error logging table retains log
entries

Can optionally specify tag to correlate failed rows with DML
statement
© 2005 Julian Dyke
juliandyke.com
Example: INSERT STATEMENT

For example:
INSERT INTO car
(
season_key, race_key, position, driver_key, team_key, engine_key,
laps_completed, classification_key, notes
)
SELECT
season_key, race_key, position, driver_key, team_key, engine_key,
laps_completed, classification_key, notes
FROM external_car
LOG ERRORS INTO err_car REJECT LIMIT UNLIMITED;

15
Note: INSERT statement does not return any error messages
even if rows are written to DML error table
© 2005 Julian Dyke
juliandyke.com
Asynchronous
Commit
16
© 2005 Julian Dyke
juliandyke.com
Asynchronous Commit


In Oracle 10.2 and above COMMITs can be optionally deferred
New syntax for COMMIT statement
COMMIT [ WRITE [ IMMEDIATE|BATCH] [WAIT | NOWAIT] ]

WRITE clause
 IMMEDIATE specifies redo should be written immediately
by LGWR process when transaction is committed (default)
 BATCH causes redo to be buffered to redo log


17
WAIT specifies commit will not return until redo is
persistent in online redo log (default)
NOWAIT allows commit to return before redo is persistent
in redo log
© 2005 Julian Dyke
juliandyke.com
Asynchronous Commit

18
COMMIT Statement Examples
COMMIT;
-- IMMEDIATE WAIT
COMMIT WRITE;
-- Same as COMMIT;
COMMIT WRITE IMMEDIATE;
-- Same as COMMIT;
COMMIT WRITE IMMEDIATE WAIT;
-- Same as COMMIT;
COMMIT WRITE BATCH;
-- BATCH WAIT
COMMIT WRITE BATCH NOWAIT;
-- BATCH NOWAIT
© 2005 Julian Dyke
juliandyke.com
Asynchronous Commit

COMMIT_WRITE initialization parameter
 Controls default behaviour for commit operation
 Default value is
 Determines default value of COMMIT WRITE statement
 If neither is set then commit records are written to disk
before control is returned to user (Oracle 10.1 behaviour)
 Can be modified using ALTER SESSION statement
ALTER SESSION SET COMMIT_WRITE = 'IMMEDIATE,WAIT';
ALTER SESSION SET COMMIT_WRITE = 'BATCH,NOWAIT';
19
© 2005 Julian Dyke
juliandyke.com
Asynchronous Commit
20

Advantages:
 Eliminates the wait for an I/O to the redo log
 Can improve performance by reducing latency
 Improves response times

Disadvantages:
 Must be able to tolerate loss of asynchronously committed
transaction

Applicable where:
 high volume of update transactions generate frequent redo
log writes to disk
 response times degraded by waits for redo log writes to
disk
© 2005 Julian Dyke
juliandyke.com
Asynchronous Commit

21
Some thoughts...
 Difficult to test on single CPU systems

Can lose data therefore cannot be used in OLTP or web
environments unless middleware detects and resolves
errors

Might work in DSS and batch environments where
processes are repeatable e.g.
 Parallel batch processing
 Parallel direct load

But only of benefit if there are high number of transactions
 Does not apply in DSS or batch environments
 Change application to reduce number of COMMITs
© 2005 Julian Dyke
juliandyke.com
PL/SQL
Conditional
Compilation
22
© 2005 Julian Dyke
juliandyke.com
Conditional Compilation



In Oracle 10.2 and above, PL/SQL can include conditional
compilation directives
Useful for
 Compatibility between releases (10.2 and upwards only)
 Trace / Debugging
 Testing / Quality Assurance
For example
CREATE OR REPLACE PROCEDURE p1
BEGIN
DBMS_OUTPUT.PUT_LINE ('Before');
$IF $$trace_enabled $THEN
DBMS_OUTPUT.PUT_LINE ('Conditional Code');
$END
DBMS_OUTPUT.PUT_LINE ('After');
END;
/
23
© 2005 Julian Dyke
juliandyke.com
Conditional Compilation

By default conditional compilation flag will be NULL
SET SERVEROUTPUT ON
EXECUTE p1;
Before
After

Conditional compilation can be enabled as follows:
ALTER PROCEDURE p1 COMPILE
PLSQL_CCFLAGS = 'trace_enabled: true' REUSE SETTINGS;
EXECUTE p1;
Before
Conditional code
After
24
© 2005 Julian Dyke
juliandyke.com
Column
Encryption
25
© 2005 Julian Dyke
juliandyke.com
Column Encryption
26

In Oracle 10.2 and above individual columns can be encrypted
 Columns are encrypted transparently to applications
 Columns are encrypted on disk

Requires a wallet
 Directory containing encryption keys
 Password protected
 Defined in SQLNET.ORA
 Directory must be created manually
© 2005 Julian Dyke
juliandyke.com
Column Encryption

Define wallet in SQLNET.ORA
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/admin/TEST/wallet)
)
)

Restart instance and open wallet
ALTER SYSTEM
SET ENCRYPTION WALLET OPEN
IDENTIFIED BY <password>;

Set up an encryption key
ALTER SYSTEM
SET ENCRYPTION KEY
IDENTIFIED BY <password>;
27
© 2005 Julian Dyke
juliandyke.com
Column Encryption

Create a table with an encrypted column
CREATE TABLE t1
(
c1 VARCHAR2(10),
c2 VARCHAR2(10) ENCRYPT
);
INSERT INTO t1 VALUES ('ABCDEFGHIJ','ABCDEFGHIJ');
INSERT INTO t1 VALUES ('ABCDEFGHIJ','ABCDEFGHIJ');

Columns are decrypted when queried
SELECT c1, c2 FROM t1;
C1
C2
---------- ---------ABCDEFGHIJ ABCDEFGHIJ
28
© 2005 Julian Dyke
juliandyke.com
Column Encryption

Columns are encrypted on disk
SELECT
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO (ROWID,USER,'T1'),
DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)
FROM t1;
File Number Block Number
----------- -----------4
63
ALTER SESSION SET EVENTS
'10389 trace name context forever, level 1';
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 63;
29
© 2005 Julian Dyke
juliandyke.com
Column Encryption

Columns are encrypted on disk
tab 0, row 0, @0x1f55
tl: 67 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [10] 41 42 43 44 45 46 47 48 49 4a
col 1: [52]
c0 55 15 73 ec 73 c8 a9 ed b2 6f fa e0 17 c8 be 45 f1 7e 19 7a 6a 49 77 0a
31 83 19 b3 4f b9 78 ef f2 fb 38 7e 57 13 75 a0 fe 98 b7 ed ae d4 a6 78 a4
df 8d
tab 0, row 1, @0x1f12
tl: 67 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [10] 41 42 43 44 45 46 47 48 49 4a
col 1: [52]
7d b1 af 57 74 e0 65 48 c9 c3 ec f6 de 2d 31 65 cf 40 eb 54 7e 76 6a a5 5b
67 21 b0 1f 6d 11 44 07 b9 ca 92 b0 30 b0 55 9c 28 46 b9 53 62 69 eb 15 4f
94 50


30
Column length changes on disk
Actual lengths not reported by DUMP or VSIZE
© 2005 Julian Dyke
juliandyke.com
Space
Management
31
© 2005 Julian Dyke
juliandyke.com
Online Shrink

In Oracle 10.1 and above, tables and indexes can be shrunk
 Must be using Automatic Segment Space Management
 Must enable row movement
ALTER TABLE t1 ENABLE ROW MOVEMENT;


Must also disable ROWID-based triggers
To shrink table
ALTER TABLE t1 SHRINK SPACE;


By default
 compacts space
 adjusts high water mark
 releases space
To shrink dependent objects
ALTER TABLE t1 SHRINK SPACE CASCADE;
32
© 2005 Julian Dyke
juliandyke.com
Online Shrink
33

In Oracle 10.1 and above can shrink
 Tables
 Indexes
 IOT
 IOT Secondary Indexes
 Partitions
 Subpartitions
 Materialized Views
 Materialized View Logs

In Oracle 10.2 and above can also shrink
 LOB Segments
 Function Based Indexes
 IOT Overflow Segments
© 2005 Julian Dyke
juliandyke.com
Dropping Empty Datafiles


In Oracle 10.2 and above, empty datafiles can be dropped
Useful in conjunction with online shrink
ALTER TABLESPACE test DROP DATAFILE 'test1.dbf';

Cannot drop non-empty datafiles
ORA-03262: the file is non-empty

Cannot drop first file in tablespace
ORA-03263: cannot drop the first file of tablespace TEST
34
© 2005 Julian Dyke
juliandyke.com
Renaming Temporary Files



In Oracle 10.2 and above temporary files can be renamed
For example to rename temp1.dbf to temp2.dbf
Take file offline using:
ALTER DATABASE TEMPFILE 'temp1.dbf' OFFLINE;

Move file at operating system level
$ mv temp1.dbf temp2.dbf

Rename file using:
ALTER DATABASE RENAME FILE 'temp1.dbf' TO 'temp2.dbf';

Bring file online again using:
ALTER DATABASE TEMPFILE 'temp2.dbf' ONLINE;
35
© 2005 Julian Dyke
juliandyke.com
Trace and
Diagnostics
36
© 2005 Julian Dyke
juliandyke.com
Database / Instance Level Trace


In Oracle 10.2 and above includes new procedures to enable
and disable trace at database and/or instance level
New procedures in DBMS_MONITOR package


37
PROCEDURE DATABASE_TRACE_ENABLE
Argument Name
Type
In/Out
Default
WAITS
BOOLEAN
IN
DEFAULT
BINDS
BOOLEAN
IN
DEFAULT
INSTANCE_NAME
VARCHAR2
IN
DEFAULT
PROCEDURE DATABASE_TRACE_DISABLE
Argument Name
Type
In/Out
Default
INSTANCE_NAME
VARCHAR2
IN
DEFAULT
© 2005 Julian Dyke
juliandyke.com
Database / Instance Level Trace

Examples - database level
EXECUTE dbms_monitor.database_trace_enable;
EXECUTE dbms_monitor.database_trace_enable (binds=>TRUE);
EXECUTE dbms_monitor.database_trace_enable (waits=>TRUE);

Example - instance level
EXECUTE dbms_monitor.database_trace_enable
(instance_name=>'RAC1);

38
Note - cannot disable instance level trace in Oracle 10.2.0.1
© 2005 Julian Dyke
juliandyke.com
V$PROCESS_MEMORY


39
Introduced in Oracle 10.2
Name
Type
PID
NUMBER
SERIAL#
NUMBER
CATEGORY
VARCHAR2(15)
ALLOCATED
NUMBER
USED
NUMBER
MAX_ALLOCATED
NUMBER
Included in STATSPACK report in Oracle 10.2 and above
© 2005 Julian Dyke
juliandyke.com
V$PROCESS_MEMORY

Example - can be used to verify size of SQL and PL/SQL areas
for a process
SELECT * FROM V$PROCESS_MEMORY
WHERE pid = 26;
PID
SERIAL#
CATEGORY
26
3
SQL
26
3
PL/SQL
26
3
26
3

40
ALLOCATED
USED
MAX ALLOCATED
102588
33164
806508
27328
21560
31480
Freeable
1310720
0
Other
3119097
3119097
Base view is X$KSMPGST
© 2005 Julian Dyke
juliandyke.com
SQL*Plus
41
© 2005 Julian Dyke
juliandyke.com
SQL*Plus XQUERY


In Oracle 10.2 and above SQL*Plus can run XQuery 1.0
commands against database
For example
SQL> SET LONG 200
SQL> SET LINESIZE 200
SQL> XQUERY FOR $i IN ORA:VIEW ("CIRCUIT") RETURN $i
SQL> /

Returns
<ROW><CIRCUIT_KEY>SHA</CIRCUIT_KEY><CIRCUIT_NAME>Shang
hai</CIRCUIT_NAME><COUNTRY_KEY>CHI</COUNTRY_KEY></ROW>
<ROW><CIRCUIT_KEY>BAH</CIRCUIT_KEY><CIRCUIT_NAME>Bahrai
n</CIRCUIT_NAME><COUNTRY_KEY>BAH</COUNTRY_KEY></ROW>
...
55 item(s) selected
42
© 2005 Julian Dyke
juliandyke.com
DBMS_OUTPUT

DBMS_OUTPUT maximum line length
 In Oracle 10.1 and below - 255 bytes
 In Oracle 10.2 and above - 32767 bytes
SET SERVEROUTPUT ON
DBMS_OUTPUT.ENABLE (1000000);
DBMS_OUTPUT.PUT_LINE (...);
SET SERVEROUTPUT ON SIZE 1000000
DBMS_OUTPUT.PUT_LINE (...);

43
DBMS_OUTPUT maximum output buffer size
 In Oracle 10.1 and below - 1000000 bytes
 In Oracle 10.2 and above - unlimited
© 2005 Julian Dyke
juliandyke.com
Parameters
44
© 2005 Julian Dyke
juliandyke.com
V$PARAMETER_VALID_VALUES


45
Introduced in Oracle 10.2
Returns one row for each valid value for each parameter
taking scalar values
© 2005 Julian Dyke
Name
Type
NUM
NUMBER
NAME
VARCHAR2(64)
ORDINAL
NUMBER
VALUE
VARCHAR2(255)
ISDEFAULT
VARCHAR2(64)
juliandyke.com
V$PARAMETER_VALID_VALUES

E.g in Oracle 10.2 valid values for CURSOR_SHARING
parameter are:
 FORCE
 EXACT
 SIMILAR
SELECT name, value, isdefault
FROM v$parameter_valid_values
WHERE name = 'cursor_sharing'
ORDER BY ordinal;
46
© 2005 Julian Dyke
Parameter Name
Value
IsDefault?
cursor_sharing
FORCE
FALSE
cursor_sharing
EXACT
TRUE
cursor_sharing
SIMILAR
FALSE
juliandyke.com
V$PARAMETER_VALID_VALUES

View definition
SELECT view_definition FROM v$fixed_view_definition
WHERE view_name = 'GV$PARAMETER_VALID_VALUES';
SELECT
inst_id,
parno_kspvld_values,
name_kspvld_values,
ordinal_kspvld_values,
value_kspvld_values,
isdefault_kspvld_values
FROM x$kspvld_values
WHERE TRANSLATE (name_kspvld_values,'_','#')
NOT LIKE '#%';
47
© 2005 Julian Dyke
juliandyke.com
X$KSPVLD_VALUES



48
Introduced in 10.2
Contains all valid parameter values
Includes supported and unsupported parameters
Name
Type
ADDR
RAW(4)
INDX
NUMBER
INST_ID
NUMBER
PARNO_KSPVLD_VALUES
NUMBER
NAME_KSPVLD_VALUES
VARCHAR2(64)
ORDINAL_KSPVLD_VALUES
NUMBER
VALUE_KSPVLD_VALUES
VARCHAR2(255)
ISDEFAULT_KSPVLD_VALUES
VARCHAR2(64)
© 2005 Julian Dyke
juliandyke.com
X$KSPVLD_VALUES

To select valid values for all unsupported parameters use
SELECT
name_kspvld_values,
value_kspvld_values,
isdefault_kspvld_values
FROM x$kspvld_values
WHERE TRANSLATE (name_kspvld_values,'_','#')
LIKE '#%';
ORDER BY
name_kspvld_values,
ordinal_kspvld_values;
49
© 2005 Julian Dyke
juliandyke.com
Auditing
50
© 2005 Julian Dyke
juliandyke.com
XML Audit Trail

In Oracle 10.2 and above auditing records can be output in
XML
ALTER SYSTEM SET audit_trail = 'XML'
SCOPE = SPFILE;


51
Requires instance restart
Audit trail files are written to directory specified by
AUDIT_DUMP_DEST parameter
 Defaults to $ORACLE_BASE/admin/<SID>/adump
 Filenames like ora_3221174380.xml
© 2005 Julian Dyke
juliandyke.com
XML Audit Trail

Sample output
SELECT COUNT (*)
FROM table1;
<Audit>
<Version>10.2</Version>
<AuditRecord>
<Audit_Type>1</Audit_Type>
<Session_Id>700</Session_Id>
<StatementId>7</StatementId>|
<EntryId>1</EntryId>
<Extended_Timestamp>2005-09-09T15:30:01.584940</Extended_Timestamp>
<DB_User>USER1</DB_User>
<OS_User>oracle</OS_User>
<Userhost>server1</Userhost>
<OS_Process>26000</OS_Process>
<Terminal>pts/0</Terminal>
<Instance_Number>0</Instance_Number>
<Object_Schema>USER1</Object_Schema>
<Object_Name>TABLE1</Object_Name>
<Action>103</Action>
<Returncode>0</Returncode>
<Scn>3277463</Scn>
<SesActions>---------S------</SesActions>
</AuditRecord>
</Audit>
52
© 2005 Julian Dyke
juliandyke.com
Restore Points
53
© 2005 Julian Dyke
juliandyke.com
Restore Points

In Oracle 10.2 and above restore points can be named
 Previously required SCN or timestamp

Requires
 Archiving
 Flashback

To enable flashback set the following parameters:
ALTER SYSTEM SET db_recovery_file_dest_size = 1000M;
ALTER SYSTEM SET db_recovery_file_dest = '<filename>';
ALTER SYSTEM SET db_flashback_retention_target = 1440;

Enable flashback mode:
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
54
© 2005 Julian Dyke
juliandyke.com
Restore Points

Restore points can be
 Guaranteed restore points
 FLASHBACK must be enabled before creation
 Must be manually deleted
 Can restore beyond time specified by
DB_FLASHBACK_RETENTION_TARGET parameter

55
Normal restore points
 Can be created before FLASHBACK is enabled
 Can be automatically deleted
 Cannot restore beyond time specified by
DB_FLASHBACK_RETENTION_TARGET parameter
© 2005 Julian Dyke
juliandyke.com
Restore Points

To create a restore point
CREATE RESTORE POINT restore_point1;
CREATE RESTORE POINT restore_point2
GUARANTEE FLASHBACK DATABASE;

To drop a restore point
DROP RESTORE POINT restore_point1;

To flashback database to a restore point
SHUTDOWN IMMEDIATE
STARTUP MOUNT
FLASHBACK DATABASE TO RESTORE POINT restore_point1;
ALTER DATABASE OPEN RESETLOGS;
56
© 2005 Julian Dyke
juliandyke.com
Restore Points


57
New dynamic performance view - V$RESTORE_POINT
Name
Type
SCN
NUMBER
DATABASE_INCARNATION#
NUMBER
GUARANTEE_FLASHBACK_DATABASE
VARCHAR2(3)
STORAGE_SIZE
NUMBER
TIME
TIMESTAMP(9)
NAME
VARCHAR2(128)
Based on X$KCCNRS
 Stored in controlfile
 Maximum 2048 rows
© 2005 Julian Dyke
juliandyke.com
Restore Points

In Oracle 10.2 and above can also flashback table to restore
point
 Does not require flashback database
 Row movement must be enabled on table
ALTER TABLE t1 ENABLE ROW MOVEMENT;

Create a restore point
CREATE RESTORE POINT restore_point3;

Update table (not DROP or TRUNCATE)
DELETE * FROM t1;

Flashback table to restore point
FLASHBACK TABLE TO RESTORE POINT restore_point3;
58
© 2005 Julian Dyke
juliandyke.com
RMAN
59
© 2005 Julian Dyke
juliandyke.com
RMAN

In Oracle 10.2 and above RMAN is supported by a number of
new dynamic performance views including:
V$BACKUP_ARCHIVELOG_DETAILS
V$BACKUP_ARCHIVELOG_SUMMARY
V$BACKUP_CONTROLFILE_DETAILS
V$BACKUP_CONTROLFILE_SUMMARY
V$BACKUP_COPY_DETAILS
V$BACKUP_COPY_SUMMARY
V$BACKUP_DATAFILE_DETAILS
V$BACKUP_DATAFILES_SUMMARY
V$BACKUP_PIECE_DETAILS
V$BACKUP_SET_DETAILS
V$BACKUP_SET_SUMMARY
V$BACKUP_SPFILE_DETAILS
V$BACKUP_SPFILE_SUMMARY
60
© 2005 Julian Dyke
juliandyke.com
Conclusion
61

If you are implementing a new system use Oracle 10.2

If you are considering upgrading an existing system

Upgrade if you use
 ASM
 Data Guard
 Spatial / OLAP etc

Consider upgrading if you use RAC

Otherwise perform a cost / benefit analysis before
upgrading
© 2005 Julian Dyke
juliandyke.com
Thank you for listening
[email protected]
62
© 2005 Julian Dyke
juliandyke.com