Oracle 9i – New Features for DBAs

1. Oracle 9i – New Features for DBAs Presented by Mike Strong – Senior DBA Tobin International

2. Oracle 9i • Provides many new features, including • a number of new tools to ease administration of the Oracle database • Architecture changes • Performance improvements • availability and recoverability improvements

3. Oracle Managed Datafiles (OMF) • Provides the ability to have Oracle manage datafiles, tempfiles, online redo logfiles and control files automatically • Will create database datafiles and tempfiles in directory designated by the db_create_file_dest initialization parameter • Will create online redo logfiles and control files in the directory designated by the initialization parameter db_create_online_log_dest_n • Files get Oracle-generated names like ora_<ts_name>_<unique character string>.dbf, ora_<log group number>_<UCS>.log, etc. • Can automatically drop data file associated with tablespace • Can create a database using OMF just by issuing the command ‘CREATE DATABASE;’

4. Managing shared memory areas • Will support multiple block sizes • Can set new block size for any tablespace except system, temporary and redo tablespaces • Can set up subcaches in the SGA for each block size (db_<block size>_cache_size) • DB_BLOCK_BUFFERS parameter depreciated • Replaced by db_cache_size • BUFFER_POOL_KEEP parameter depreciated • Replaced by db_keep_cache_size • BUFFER_POOL_RECYCLE parameter depreciated • Replaced by db_recycle_cache_size • In Oracle 9i, the db_keep_cache_size and db_recycle_cache_size are separate memory areas and are not allocated out of the default buffer pool as they were in Oracle 8i

5. Managing shared memory areas • The alter system SQL command can dynamically alter the: • Default buffer cache size • Memory subcaches • Shared pool • The redo log buffer, large pool and java pool cannot be dynamically resized • The maximum size of the SGA is set by the sga_max_size initialization parameter

6. Default temporary tablespace • Provides for a default temporary tablespace used for sort operations, etc. • Can be set at database creation or set or changed after database creation using the ‘alter database default temporary tablespace’ SQL command • All new users will be assigned the default temporary tablespace • Tablespace will be of type TEMPORARY and no permanent object can be created in the tablespace • Can determine default temporary T/S using DATABASE_PROPERTIES view where column property_name = ‘TEMPORARY_TEMP_TABLESPACE’. The column property_value will show the T/S name

7. Automated UNDO management • Introduced to alleviate the need to manage rollback segments • Use of rollback segments depreciated in Oracle 9i • Create using ‘CREATE UNDO TABLESPACE’ SQL command or when database is created • Initialization parameter undo_management determines if automated UNDO management is enabled (AUTO/MANUAL) • Initialization parameter undo_tablespace defines the UNDO tablespace (defaults to the first available T/S defined as UNDO T/S)

8. Persistent Initialization Parameters • Before Oracle 9i, dynamic changes made to init parameters were valid only until the instance was shut down, i.e. the changes were not persistent • In Oracle 9i, the spfile is a binary version of the text init.ora file andcontains the changed values of init parameters • Created using CREATE SPFILE=‘<file name>’ FROM PFILE=‘<file name>’; • Shutdown the database and then restart using STARTUP PFILE=<spfile name>; • Default location of SPFILE is $ORACLE_HOME/dbs on UNIX and $ORACLE_HOME\database on Windows

9. Extraction of Object Metadata • DBMS_METADATA package allows you to extract DDL for database objects • dbms_metadata.get_ddl( object_type VARCHAR2, name VARCHAR2, schema VARCHAR2 DEFAULT NULL, version VARCHAR2 DEFAULT ‘COMPATIBLE’, model VARCHAR2 DEFAULT ‘ORACLE’, transform VARCHAR2 DEFAULT ‘DDL’) RETURN clob;

10. Extraction of Object Metadata • Usage: SQL> set pagesize 0; SQL> set long 90000; SQL> select dbms_metadata.get_ddl('TABLE','CODE_GRP_CODE','TLS') 2 from dual; CREATE TABLE "TLS"."CODE_GRP_CODE" ( "FILE_CATG_CODE" VARCHAR2(3) NOT NULL ENABLE, "GRP_CODE" VARCHAR2(3) NOT NULL ENABLE, "GRP_DESC" VARCHAR2(30) NOT NULL ENABLE, CONSTRAINT "PK_CODE_GRP_CODE" PRIMARY KEY ("GRP_CODE") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TLS_DATA" ENABLE ) PCTFREE 5 PCTUSED 90 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TLS_DATA"

11. Automated Segment-Space management • The old, and still default method, of managing free space within a segment uses a structure called a free list. This list keeps track of free blocks within a segment • The new method allows you to create a tablespace that is defined to use automated space management rather than free lists. • When an object is created in the tablespace, Oracle creates a bitmap within blocks in the object that indicate how much space is available within the blocks to insert rows • As the block changes, the bitmap changes also

12. Automated Segment-Space management • These bitmaps usually take up the first three blocks in a segment. The fourth block is the segment header. • Eliminates need to specify pctused, freelists and freelist groups when defining objects in the tablespace. • Only available for permanent and locally managed tablespaces • Cannot create LOBs in tablespaces with automated segment management • Once it is set, cannot go back to using free space management • Simpler and more efficient than free lists

13. Automated Segment-Space management CREATE TABLESPACE cabot_data DATAFILE ‘c:\oracle\oradata\tls\cabot_data.dbf’ SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

14. Flashback Query • Flashback Query allows you to look at the condition of a table at a point in time or at a specific System Change Number (SCN) • The undo_retention parameter must be set and the UNDO tablespace must be large enough that undo generated during the period we want to query will not be overwritten • The user executing the query must have execute on the dbms_flashback package

15. Flashback Query • Example Usage: EXEC dbms_flashback.enable_at_time( TIMESTAMP ‘2004-02-23 23:52:00’); SELECT * FROM code_grp_code; EXEC dbms_flashback.disable; • This example will show the data in code_grp_code table as it existed on February 23, 2004 at 11:52 PM.

16. Instance Failure-Recovery Features • In a media or instance recovery, Oracle will make two passes through the online redo logs. • Pass 1: quickly scans the redo log blocks to see which of those blocks need to be recovered. Blocks that need to be recovered will be marked. • Pass 2: processes only those redo log blocks that need to be applied to the Oracle data blocks to bring them to a consistent state

17. Instance Failure-Recovery Features • Fast Start Time-Based Recovery (FSTBR): the DBA defines a desired MTTR for the database in seconds with the parameter fast_start_mttr_target (0 – 3600 seconds). • Oracle will configure internal database settings to try to maintain this requirement • Oracle continuously checkpoints the database and advances the checkpoint counter to write out changed data at a rate that will allow recovery within the specified MTTR. • Recovery begins at the last checkpoint counter address rather than at the last normal checkpoint (as occurs at a redo log switch) • If the values of fast_start_io_target and log_checkpoint_interval are set, theywill override the values calculated by setting fast_start_mttr_target

18. Removal of internal user and svrmgr • You can no longer connect to a database as the internal user. Instead, you must connect as sysdba e.g. SQL> connect sys/coi@tls as sysdba • You can no longer user Server Manager (SVRMGR). You must use SQL*Plus or Oracle Enterprise Manager

19. Database Configuration Assistant • You can now create a database based on an existing database • You can use an existing database to clone a new database and its objects (tables, indexes, rollback segments, and so on) with or without data • Using templates, you can save the source structure and data to use when creating a database at a later time

20. Database Configuration Assistant • Allows you to fully customize the attributes of the database including tablespaces, datafiles, redo logs, and control file information and initialization parameters of the database • You can run custom scripts when creating the database

21. Questions?