1 / 58

Paper 520 Presentation by Ari Kaplan CEO, Expand Beyond Corporation www.xb.com

Welcome to Oracle 10g Data Dictionary and Initialization Parameters. Paper 520 Presentation by Ari Kaplan CEO, Expand Beyond Corporation www.xb.com. © 2003 Expand Beyond Corp. Confidential. Recipient may not forward without permission. Agenda. Server parameter file Summary

arabela
Download Presentation

Paper 520 Presentation by Ari Kaplan CEO, Expand Beyond Corporation www.xb.com

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. Welcome to Oracle 10g Data Dictionary and Initialization Parameters Paper 520 Presentation by Ari Kaplan CEO, Expand Beyond Corporation www.xb.com © 2003 Expand Beyond Corp. Confidential. Recipient may not forward without permission.

  2. Agenda • Server parameter file • Summary • New initialization parameters • New static data dictionary views • New dynamic data dictionary views • New columns in existing data dictionary views • 10g Details • Server Manageability • Database Self-Management • Database Manual Monitoring / Management / Tuning • Database Automatic Statistics • Object Management • Backup and Recovery • High Availability • Security • Tips • ALTER SYSTEM • Viewing undocumented parameters

  3. The SPFILE • “Server Parameter” file • Stored on server • Binary, rather than text • Can maintain dynamic changes to parameter values

  4. The SPFILE • Example 1: CREATE SPFILE FROM PFILE=‘/u01/oracle/dbs/initPROD.ora’ • Example 2: CREATE SPFILE ‘/u01/oracle/dbs/spfilePROD.ora’ FROM PFILE=‘/u01/oracle/dbs/initPROD.ora’ • Must have SYSDBA or SYSOPER privileges SQLPLUS> SHOW PARAMETERS SPFILE /u01/oracle/dbs/spfilePROD.ora

  5. The SPFILE • 9iR2 RMAN: BACKUP is used to back-up the SPFILE - RMAN backs up the SPFILE in the backup set If the SPFILE is lost / damaged, use RESTORE SPFILE: - Start the instance without the SPFILE - Run RESTORE SPFILE in RMAN

  6. The SPFILE • *SPPARAMETER – contents of the SPFILE • *RMAN_CONFIGURATION - maintain the persistent configuration parameters for RMAN, the standard Oracle backup and recovery utility

  7. Initialization Parameter History • Oracle 10g • 27 new parameters • 10 marked “ISDEPRECATED” • 110 in V$OBSOLETE_PARAMETER • Oracle 9iR1 • 46 new parameters • 16 obsolete • 3 renamed • Oracle 8.1 • 28 new parameters • 62 obsolete • Oracle 8.0 • 75 new parameters • 26 obsolete

  8. 10g Initialization Parameters • New V$PARAMETER columns • ISDEPRECATED (10 parameters are TRUE) • buffer_pool_keep buffer_pool_recycle drs_start • fast_start_io_target global_context_pool_size log_archive_start • max_enabled_roles parallel_automatic_tuning parallel_server • parallel_server_instances plsql_compiler_flags • ISINSTANCE_MODIFIABLE (144 TRUE, 112 FALSE): for clustered databases such as RAC, this shows if the parameter can be modified on global (TRUE) or local (FALSE) • Examples of FALSE: spfile, sessions, processes, sessions, dml_locks, utl_file_dir, … • Examples of TRUE: sga_target, cpu_count, shared_pool_size, log_archive_dest, undo_retention, statistics_level, … • DISPLAY_VALUE: displays data slightly better in some cases. For example, db_cache_size’s VALUE=25165824 and DISPLAY_VALUE=24M db_recovery_file_dest_size’s VALUE=2147483648 and DISPLAY_VALUE=2G

  9. 10g initialization parameters In 10g, the init parameters are broken into: • “BASIC”, with 29 parameters that should be modified for tuning • “ADVANCED” for the fine-tuning of databases and to keep us all employed. This is a nice reduction in the number of parameters. For example, shared server environments are handled by Oracle (SHARED_SERVERS init parameter, no longer the DISPATCHER init parameter)

  10. 10g initialization parameters • “Basic” parameters: • CLUSTER_DATABASE • COMPATIBLE • CONTROL_FILES • DB_BLOCK_SIZE • DB_CREATE_FILE_DEST • DB_CREATE_ONLINE_LOG_DEST_n • DB_DOMAIN • DB_NAME • DB_RECOVERY_FILE_DEST • DB_RECOVERY_FILE_DEST_SIZE • DB_UNIQUE_NAME • INSTANCE_NUMBER • JOB_QUEUE_PROCESSES • LOG_ARCHIVE_DEST_n • LOG_ARCHIVE_DEST_STATE_n • NLS_LANGUAGE, NLS_TERRITORY • OPEN_CURSORS • PGA_AGGREGATE_TARGET • PROCESSES • REMOTE_LISTENER • REMOTE_LOGIN_PASSWORDFILE • ROLLBACK_SEGMENTS • SESSIONS • SGA_TARGET • SHARED_SERVERS • STAR_TRANSFORMATION_ENABLED • UNDO_MANAGEMENT • UNDO_TABLESPACE

  11. New 10g initialization parameters • New 10g parameters (to be discussed later): • asm_diskgroups • asm_diskstring • asm_power_limit • create_stored_outlines • db_flashback_retention_target • db_recovery_file_dest • db_recovery_file_dest_size • db_unique_name • ddl_wait_for_locks • fileio_network_adapters • gcs_server_processes • instance_type • ldap_directory_access • log_archive_config • log_archive_local_first • plsql_code_type • plsql_debug • plsql_optimize_level • plsql_warnings • resumable_timeout • sga_target • skip_unusable_indexes • smtp_out_server • sqltune_category • streams_pool_size

  12. Static Data Dictionary History • Oracle 10g • 497 new static tables • 295 SYS • 202 non-SYS • 127 deprecated static tables • Oracle 9iR2 • 64 new static views • Oracle 9iR1 • 96 new static views • 117 existing views with changed columns • Oracle 8.1 • 166 new static views • 113 existing views with changed columns • Oracle 8.0 • 119 new static views • 72 existing views with changed columns

  13. 10g Static Data Dictionary • Deprecated tables (from Oracle9iR2): • 46 AQ - 6 Census - 10 CWM2 - 10 LBAC • 22 ODM - 11 QA - 6 SA • New tables (from Oracle9iR2): • 295 SYS (OLAP, AQ, Streams, DIR, WRI / WRH, Rule_Set, Scheduler, …) • 7 SYSTEM • 7 DBSNMP • 15 WMSYS • 15 EXFSYS • 3 ORDSYS • 17 MDSYS • 1 XDB • 73 OLAPSYS • 43 DMSYS • 5 CTXSYS • 12 WKSYS • 12 WK_TEST

  14. Static Data Dictionary Views • All following views have USER_, ALL_ and DBA_ versions, unless otherwise noted

  15. Dynamic Data Dictionary History • Oracle 10g • 671 new dynamic SYS views • 275 new dynamic non-SYS views (OLAPSYS, ORDSYS, DMSYS, etc.) • 227 deprecated dynamic views • Oracle 9iR2 • 4 new v$views • Oracle 9iR1 • 69 new V$ views • 64 renamed V$ views • Oracle 8.1 • 84 new V$ views • 35 existing views with changed columns • Oracle 8.0 • 180 new V$ views • 20 existing views with changed columns

  16. 10g Dynamic Data Dictionary • Deprecated views in Oracle 10g (from Oracle9iR2): • 101 EXU7 - 18 DBA_SA_ - 18 ALL_SA_ • 13 DBA_LBAC - 30 AQ$ • New SYS views in Oracle 10g (from Oracle9iR2): • 6 ALL_* views - 16 AQ$* views - 2 DATABASE_ • 6 DATAPUMP_ - 173 DBA*_ - 27 DBA_ADVISOR_* • 64 DBA_HIST_* - 15 DBA_SCHEDULER*_ • 7 DBA_STREAMS*_ - 22 EXU10* - 76 GV_$* • 97 KU$_* - USER_ - 77 V_$* • New non-SYS Views in Oracle 10g (from Oracle9iR2): • 91 OLAPSYS - 42 WMSYS - 32 EXFSYS • 5 ORDSYS - 9 MDSYS - 6 WK_TEST • 71 DMSYS - 7 CTXSYS - 12 WKSYS

  17. Dynamic views • V$ and GV$ • Can extract individual node from GV$ view

  18. Server Manageability • 10g provides for easier installation, configuration, management (and dropping!) of databases. • Much improved Oracle Enterprise Manager • Automatically configure RAC clustered environments • Automatic Storage Management (this was part of Oracle 9i, but there are additional improvements in 10g): new volume manager for Oracle-based files. Oracle attempts to optimally create and distribute datafiles, log files, and controlfiles. Mirroring and available disks are taken into consideration, and Oracle can redistribute the files for balancing if there are I/O bottlenecks. • Transparent gateway: use SELECT to refer to remote functions in non-Oracle databases. • 10g Initialization Parameters: • instance_type

  19. Server Manageability • 10g Initialization Parameters: • asm_diskgroups • asm_diskstring

  20. Server Manageability • 10g Initialization Parameters: • asm_power_limit • fileio_network_adapters

  21. Database Self-Management • Automatic Workload Repository: a set of tables that is a repository of workload statistics, enabling Oracle 10G to self-manage. Querying against this repository is a wealth of information, analogous to the BSTAT/ESTAT and tracing options for tuning in Oracle 9. Stored in a separate repository database with OEM. • 10g Views: New columns: • V$AW_CALC (SESSION_ID, CURR_DML_COMMAND, PREV_DML_COMMAND, AGGR_FUNC_LOGICAL_NA, AGGR_FUNC_LOGICAL_NA, AGGR_FUNC_PRECOMPUTE, AGGR_FUNC_CALCS) • V$AW_SESSION_INFO: new columns (SESSION_ID) • Automatic Storage Management: (previously described) • Automatic Database Diagnostic Monitor: Oracle 10G self-analyzes its performance, looks for bottlenecks, and can automatically adjust database configuration.

  22. Database Self-Management • Automatic UNDO RETENTION tuning: Oracle 10G can dynamically adjust the init parameter UNDO_RETENTION, which looks at the space in the UNDO tablespaces and automatically adjusts as new SQL queries are executed. This helps to avoid the “snapshot too old” errors. • 10g Views: • V$UNDOSTAT: new columns • MAXQUERYID • ACTIVEBLKS • UNEXPIREDBLKS • EXPIREDBLKS • TUNED_UNDORETENTION • Automatic Checkpoint tuning: Oracle 10G self-tunes checkpoint frequencies to optimize recovery time.

  23. Database Self-Management • Users can now have more than one TEMP tablespace defined with a “tablespace group”, so creating large objects such as indexes or running large GROUP BY or DISTINCT queries can spill into additional TEMP tablespaces without failing as in Oracle 9 and earlier versions. • SGA: • 10g Initialization Parameter: • sga_target • Cache: • 10g View: V$DB_CACHE_ADVICE new columns: • ESTD_PHYSICAL_READ_TIME • ESTD_PCT_OF_DB_TIME_FOR_READS

  24. Database Manual Monitoring / Management / Tuning • SGA: • Fixed SGA Size, Redo Buffers, Buffer Cache Size, Shared Pool Size, Large Pool Size, Java Pool Size, Granule Size, Maximum SGA Size, Free SGA Memory Available • 10g Initialization Parameters: • BUFFER_CACHE and SHARED_POOL_SIZE are now self-tuning • JAVA_POOL_SIZE is now dynamic • 10g Initialization Parameter: • STREAMS_POOL_SIZE

  25. Database Manual Monitoring / Management / Tuning • SGA: • 10g Views: • New V$SGAINFO ; more high-level than V$SGASTAT Name Null? Type ---------------------------------- -------- ---------------------------- INST_ID NUMBER NAME VARCHAR2(25) BYTES NUMBER RESIZEABLE VARCHAR2(3) • V$SGA_DYNAMIC_COMPONENTS: new column: • USER_SPECIFIED_SIZE • V$SHARED_POOL_ADVICE: new columns: • ESTD_LC_LOAD_TIME • ESTD_LC_LOAD_TIME_FACTOR

  26. Database Manual Monitoring / Management / Tuning • Sessions: • 10g Views: V$SESSION: new columns: • SQL_ID • SQL_CHILD_NUMBER • PREV_SQL_ID • PREV_CHILD_NUMBER • BLOCKING_SESSION_STATUS • BLOCKING_SESSION • SEQ# • EVENT# • EVENT • P1TEXT • P1 • P1RAW • P2TEXT • P2 • P2TAW • P3TEXT • P3 • P3RAW • WAIT_CLASS_ID • WAIT_CLASS# • WAIT_CLASS • WAIT_TIME • SECONDS_IN_WAIT • STATE • SERVICE_NAME

  27. Database Manual Monitoring / Management / Tuning • Sessions: • V$SESSION_EVENT: new column: • EVENT_ID • V$SESSION_LONGOPS: new column: • SQL_ID • V$SESSION_WAIT: new columns: • WAIT_CLASS_ID • WAIT_CLASS# • WAIT_CLASS

  28. Database Manual Monitoring / Management / Tuning • WAIT statistics: • 10g now stores historical repository of waits, and includes better analysis capabilities to see what sessions are waiting for. • 10g Views: new column (HASH) in: • V$LATCH • V$LATCH_CHILDREN • V$LATCH_PARENT • V$LATCHNAME • Tablespace: • Rename Tablespace: before Oracle 10G, the DBA would need to create a new tablespace, copy (or export/import data) into the new tablespace, and drop the original tablespace. Now you can simply RENAME the tablespace extremely quickly. •  10g Views: • DBA_TABLESPACES: new columns: • RETENTION • BIGFILE • V$TABLESPACE: new columns: • BIGFILE • FLASHBACK_ON

  29. Database Manual Monitoring / Management / Tuning • Database: • 10g Views: • V$DATABASE: new columns: • SWITCHOVER# • PLATFORM_ID • PLATFORM_NAME • RECOVERY_TARGET_INCARNATION • LAST_OPEN_INCARNATION# • CURRENT_SCN • FLASHNACK_ON • SUPPLEMENTAL_LOG_DATA_FK • SUPPLEMENTAL_LOG_DATA_ALL • DB_UNIQUE_NAME • STANDBY_BECAME_PRIMARY_SCN • V$DATABASE_INCARNATION: new columns: • INCARNATION# • STATUS • RESETLOGS_ID • PRIOR_INCARNATION

  30. Database Manual Monitoring / Management / Tuning • SQL Tuning: • 10g Views: • V$SQL: new columns: • SQL_FULLTEXT SQL_ID END_OF_FETCH_COUNT • DIRECT_WRITES APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME • USER_IO_WAIT_TIME PLSQL_EXEC_TIME JAVA_EXEC_TIME • OPTIMIZER_ENV OPTIMIZER_ENV_HASH_VALUE OLD_HASH_VALUE • SQL_PROFILE PROGRAM_ID PROGRAM_LINE# • V$SQL_PLAN: new columns: • SQL_ID PLAN_HASH_VALUE OBJECT_ALIAS • OBJECT_TYPE PROJECTION TIME • QBLOCK_NAME REMARKS • V$SQL_PLAN_STATISTICS: new columns: • SQL_ID PLAN_HASH_VALUE • V$SQL_PLAN_STATISTICS_ALL: new columns: • SQL_ID PLAN_HASH_VALUE OBJECT_ALIAS • OBJECT_TYPE PROJECTION TIME • QBLOCK_NAME REMARKS • V$SQL_SHARED_CURSOR: new columns: • SQL_ID CHILD_ADDRESS CHILD_NUMBER • ANYDATA_TRANSFORMATION INCOMPLETE_CURSOR TOP_LEVEL_RPI_CURSOR • DIFFERENT_LONG_LENGTH LOGICAL_STANDBY_APPLY DIFF_CALL_DURN • BIND_UACS_DIFF PLSQL_CMP_SWITCHS_DIFF • V$SQL_SHARED_MEMORY: new columns: • SQL_FULLTEXT SQL_ID • V$SQLAREA: new columns: • SQL_ID END_OF_FETCH_COUNT DIRECT_WRITES • APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME CLUSTER_WAIT_TIME • USER_IO_WAIT_TIME PLSQL_EXEC_TIME JAVA_EXEC_TIME • OLD_HASH_VALUE PROGRAM_ID

  31. Database Manual Monitoring / Management / Tuning • SQL Tuning: • 10g Initialization Parameter: • create_stored_outlines • ddl_wait_for_locks

  32. Database Manual Monitoring / Management / Tuning • SQL Tuning: • 10g Initialization Parameter: • resumable_timeout • sqltune_category

  33. Database Manual Monitoring / Management / Tuning • PL/SQL Tuning: • 10g Initialization Parameter: • plsql_code_type • plsql_debug

  34. Database Manual Monitoring / Management / Tuning • PL/SQL Tuning: • 10g Initialization Parameter: • plsql_optimize_level • plsql_warnings

  35. Database Automatic Statistics • Automatic Optimizer Statistics Collection: collects statistics for objects that are either stale or don’t have them at all. • 10g Initialization Parameter: • Statistics_level: default is TYPICAL, and the other option is BASIC. The SMON process wakes up every 2-3 hours, reviews the number of rows affected by INSERT / UPDATE / DELETE commands since the last statistics gathering. This process replaces the old DBMS_STATS built-in package. • 10g Views: • *_TAB_MODIFICATIONS: determines which tables have stale statistics and which should be updated via the SMON background process. • Database Advisors: • Oracle tracks usage more than Tivo. The DBA can collect data on what Oracle functions are being used (Parallel Queries, IOTs, etc) for information and tracking. • Segment Advisor: Stores data on object growth trends and helps make DBAs with capacity planning. The Segment Advisor also looks within tables to see if any should be defragmented. This new online defragmentation includes reducing the highwater mark from empty blocks, or if data is out of whack with PCTUSED / PCTFREE, tables can be rebuilt online to compact data. • Redo Log Sizing Advisor: self-explanatory • SQL Tuning Advisor, SQL Access Advisor: makes suggestions for improving the syntax of SQL statements, the way some 3rd-party vendors did prior to Oracle 10G. Also recommends adding indexes, materialized views, etc. • Transaction Rollback and Recovery Monitoring: predict how long a rollback will take, monitor long-running transactions being recovered for performance.

  36. Object Management • Tablespaces: New SYSAUX tablespace is for system auxiliary objects. Now you do not need to put everything into the SYSTEM tablespace. • Object Types:New “Sorted Hash Cluster” object type. Useful to retrieve data in the exact order it was INSERTed • Tables: • DBA_TABLES: • MAXTRANS deprecated. • New columns COMPRESSION (DISABLED, ENABLED, null) and DROPPED (YES, NO) • DBA_TAB_COLS: new columns (HISTOGRAM, QUALIFIED_COL_NAME) • DBA_TAB_COLUMNS: new column (HISTOGRAM) • DBA_TAB_COL_STATISTICS: new column (HISTOGRAM) • DBA_TAB_MODIFICATIONS: new column (DROP_SEGMENTS) • Indexes: • DBA_INDEXES new columns (IOT_REDUNDANT_PKEY_ELIM, DROPPED) • 10g Initialization Parameter: • skip_unusable_indexes

  37. Object Management • LOBs: • DBA_LOG_GROUPS: new columns (TABLESPACE_NAME, FORMAT, PARTITIONED) • V$TEMPORARY_LOBS: new column (ABSTRACT_LOBS) • Log Groups: • DBA_LOG_GROUPS: new columns (LOG_GROUP_TYPE, GENERATED) • Materialized Views: • DBA_MVIEWS: new columns (UNKNOWN_TRUSTED_FD, STALE_SINCE) • Online Segment Shrink: This new online defragmentation includes reducing the highwater mark from empty blocks, or if data is out of whack with PCTUSED / PCTFREE, tables can be rebuilt online to compact data. This is possible with tables, indexes, and materialized views.

  38. Backup and Recovery • 10g has new automated disk-based backup & recovery features. • New / improved commands: • DROP DATABASE command: physically deletes all datafiles, spfiles, controlfiles, redo logs • ALTER DATABASE OPEN RESETLOGS: no longer need to backup the database instance following this command • ALTER DATABASE BEGIN BACKUP: No longer need to cycle through each tablespace to put into hot backup mode. Can now do the entire database • Automatic TSPITR (tablespace point-in-time recovery) •  10g Views: • V$ARCHIVE_DEST: new columns (VALID_NOW, VALID_TYPE, VALID_ROLE, DB_UNIQUE_NAME, VERIFY, EXPEDITE) • V$ARCHIVE_DEST_STATUS: new columns (DB_UNIQUE_NAME) • V$ARCHIVED_LOG (RESETLOGS_ID, IS_RECOVERY_DEST_FILE, FAL) • V$FAST_START_SERVERS: new column (XID) • V$FAST_START_TRANSACTIONS: new columns (XIS, PXID, RCVSERVERS) • V$INSTANCE_RECOVERY: new columns (OPTIMAL_LOGFILE_SIZE, ESTD_CLUSTER_AVAILABLE_TIME, WRITES_MTTR, WRITES_LOGFILE_SIZE, WRITES_LOG_CHECKPOINT_SETTINGS, WRITES_OTHER_SETTINGS, WRITES_AUTOTUNE, WRITES_FULL_THREAD_CKPT)

  39. Backup and Recovery • 10g Initialization Parameters: • db_flashback_retention_target • db_recovery_file_dest

  40. Backup and Recovery • 10g Initialization Parameters: • db_recovery_file_dest_size

  41. Backup and Recovery • RMAN: • 10g can automatically create and recover datafiles that have never been backed up. Also when RMAN detects a corrupt backup it can automatically go one backup into the past to recover. • The DURATION clause of the BACKUP command sets the seconds that a backup is allowed to run, at which point RMAN can pause and/or resume. • Can alert the DBA when the backup is about to run out of space. • 10g Views: • V$DATAFILE_COPY: new columns (IS_RECOVERY_DEST_FILE, RMAN_STATUS_RECID, RMAN_STATUS_STAMP) • V$BACKUP_PIECE: new columns (BYTES, IS_RECOVERY_DEST_FILE, RMAN_STATUS_RECID, RMAN_STATUS_STAMP, COMPRESSED) • V$BACKUP_SINC_ID: new columns (RMAN_STATUS_RECID, RMAN_STATUS_STAMP) • V$DATAFILE_COPY: new columns (IS_RECOVERY_DEST_FILE, RMAN_STATUS_RECID, RMAN_STATUS_STAMP) • V$PROXY_ARCHIVELOG: new columns (TAG, RMAN_STATUS_RECID, RMAN_STATUS_STAMP) • V$PROXY_DATAFILE: new columns (RMAN_STATUS_RECID, RMAN_STATUS_STAMP)

  42. Backup and Recovery • Flashback Query Improvements: • Can recover an entire database to an earlier point-in-time, reducing or removing the need to use the traditional tape backup and recovery methods. • FLASHBACK DATABASE: entire database restored to a point-in-time • FLASHBACK TABLE command: entire table restored to a point-in-time. Oracle now puts dropped tables into the “recycle bin” for quick and easy recovery. In SQL*Plus you can type “SHOW RECYCLEBIN bin_name” • Flashback drop: restore a table that was dropped entirely • Flashback version query: view data at the row-level and the history of changes • Flashback transaction query: view data and changes on a transaction-level for more finely-grained auditing and investigating. • 10g Views: New FLASHBACK_TRANSACTION_QUERY: view database transaction-level changes • 10g Initialization Parameters: db_flashback_retention_target, db_recovery_file_dest, db_recovery_file_dest_size (previously explained)

  43. Backup and Recovery • LogMiner Improvements: • 10g’s log miner now supports index-organized tables, LONG datatypes, multibyte data • 10g Views: • V$LOGMNR_CONTENTS: new columns (TABLE_NAME, DATA_OBJV#, SAFE_RESUME_SCN, XID, PXID, AUDIT_SESSIONID) • V$LOGMNR_DICTIONARY: new columns (DB_CREATED, DB_VERSION_TIME, DB_CHARACTER_SET, DB_VERSION, DB_STATUS, DB_TXN_SCN) • V$LOGMNR_LOGS: new columns (TYPE, BLOCKSIZE, FILESIZE) • V$LOGMNR_PARAMETERS: new columns (REQUIRED_START_DATE, REQUIRED_START_SCN, OPTIONS)

  44. High Availability • Data Guard: 10g Initialization Parameters: • db_unique_name • log_archive_config

  45. High Availability • Data Guard: • 10g Initialization Parameters: • log_archive_local_first

  46. High Availability • RAC (Real Application Clusters): • As part of the new Enterprise Manager, Oracle now provides clusterware management software similar to the 3rd-party software available prior to Oracle 10G. It provides a nice dashboard of overall node statuses with drilldown capabilities. • Automatic Workload Management: DBAs create workloads and then manage CPU resources for each process. • Zero Downtime Patching: by using RAC, you can apply patches (database or server) to one node at a time to avoid downtime. • Upgrade Oracle versions “online” with the Rolling Upgrade. Uses logical standby databases and SQL apply technology • New RAC 10g Views: • DB_UNIQUE_NAME: can dynamically add a standby database without shutting down the primary database. • 10g Initialization Parameter: gcs_server_processes

  47. Security • Kerberos: SASL in addition to PKI. Supports Simple Authentication and Security Layer in addition to Public Key Infrastructure. Used while communicating among databases. Can also use Kerberos-enabled database links to protect data as it is transmitted. • Fine-grained auditing: • Fine-grained auditing now includes UPDATE, INSERT, DELETE • 10g Views: New columns in: • DBA_FGA_AUDIT_TRAIL (STATEMENT_TYPE, EXTENDED_TIMESTAMP, PROXY_SESSIONID, GLOBAL_UID, INSTANCE_NUMBER) • Auditing: • 10g Views: New columns in: • DBA_AUDIT_EXISTS, DBA_AUDIT_OBJECT, DBA_AUDIT_STATEMENT, DBA_AUDIT_TRAIL (EXTENDED_TIMESTAMP, PROXY_SESSIONID, GLOBAL_UID, INSTANCE_NUMBER, OS_PROCESS, TRANSACTIONID, SCN, SQL_BIND, SQL_TEXT) • DBA_AUDIT_POLICIES (SEL, INS, UPD, DEL, AUDIT_TRAIL, POLICY_COLUMN_OPTIONS) • DBA_AUDIT_SESSION (EXTENDED_TIMESTAMP, PROXY_SESSIONID, GLOBAL_UID, INSTANCE_NUMBER, OS_PROCESS) • EUS: Enterprise User Security. Single sign-on capabilities. A central OID/LDAP repository. Can manage Oracle Label Security policies and user label authorities. • 10g Views: New columns in: • DBA_POLICIES: (IDX, POLICY_TYPE, LONG_PREDICATE)

  48. Security • VPD (Virtual Private Database): supports parallel query operations. • 10g Views: New columns in: • V$VPD_POLICY (SQL_ID) • 10g Initialization Parameter: • ldap_directory_access

  49. Tips

  50. ALTER SYSTEM • The ALTER SYSTEM command can improve uptime • SELECT NAME, VALUE • FROM v$parameter • WHERE issys_modifiable IN (’DEFERRED’, ‘IMMEDIATE’); • ALTER SYSTEM SET max_dump_file_size = 100000; • Oracle9/8i updates for this command • Change the initSID.ora file (unless using SPFile)

More Related