1 / 21

Oracle 9i – New Features for DBAs

Oracle 9i – New Features for DBAs. Presented by Mike Strong – Senior DBA Tobin International. Oracle 9i . Provides many new features, including a number of new tools to ease administration of the Oracle database Architecture changes Performance improvements

jonco
Download Presentation

Oracle 9i – New Features for DBAs

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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?

More Related