1 / 72

Managing Database Storage Structures

Managing Database Storage Structures. Objectives. After completing this lesson, you should be able to do the following: Describe the uses of the SYSAUX tablespace Specify a default user tablespace Create bigfile tablespaces Create a default temporary tablespace

ronna
Download Presentation

Managing Database Storage Structures

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. Managing Database Storage Structures

  2. Objectives • After completing this lesson, you should be able to do the following: • Describe the uses of the SYSAUX tablespace • Specify a default user tablespace • Create bigfile tablespaces • Create a default temporary tablespace • Create temporary tablespace groups • Rename tablespaces • Use the Redo Logfile Sizing Advisor

  3. SYSAUX Tablespace: Overview • Auxiliary tablespace to the SYSTEM tablespace • Mandatory tablespace • Created at time of database creation • Same security attributes as SYSTEM tablespace PERMANENT READ WRITE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO SYSTEM SYSAUX

  4. . . . SYSAUX Tablespace: Benefits • Reduces the number oftablespaces to manage • Load reduction on SYSTEM • Easier RAC management TOOLS DRSYS SYSTEM CWMLITE ODM SYSAUX

  5. SYSAUX Occupants

  6. Creating SYSAUX with DBCA

  7. Creating the SYSAUX Tablespace by Using CREATE DATABASE CREATE DATABASE DATAFILE '/u0/datafiles/system1.dbf' SIZE 200M SYSAUX DATAFILE'/u0/datafiles/sysaux1.dbf' SIZE 300M DEFAULT TEMPORARY TABLESPACE stemp_tbs TEMPFILE '/u3/datafiles/stemp_tbs1.dbf' SIZE 60M UNDO TABLESPACE sundo_tbs DATAFILE '/u2/datafiles/sundo_tbs1.dbf' SIZE 100M;

  8. Altering the SYSAUX Tablespace • You cannot alter mandatory attributes. • You must have the SYSDBA privilege. • Adding a data file: • Taking the tablespace offline: ALTER TABLESPACE sysaux ADD DATAFILE '/u0/datafiles/sysaux2.dbf' SIZE 200M ; ALTER TABLESPACE sysaux OFFLINE;

  9. Managing the SYSAUX Tablespace DROP TABLESPACE sysaux; ALTER TABLESPACE sysaux RENAME TO auxiliary; SYSAUX Data file transfer

  10. Using EM to View SYSAUX Occupants

  11. Relocating SYSAUX Occupants with EM

  12. Relocating SYSAUX Occupants with SQL • Monitor space usage of SYSAUX occupants: • Determine which procedure to use to move occupants from SYSAUX: SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTESFROM V$SYSAUX_OCCUPANTS; SELECT OCCUPANT_NAME, SCHEMA_NAME, MOVE_PROCEDUREFROM V$SYSAUX_OCCUPANTS;

  13. Relocating SYSAUX Occupants with SQL (continued) • Move SYSAUX occupants to the DRSYS tablespace: • Move occupants back to the SYSAUX tablespace: EXEC WKSYS.MOVE_WK('DRSYS'); EXEC WKSYS.MOVE_WK('SYSAUX');

  14. Locally Managed SYSTEM Tablespace • Locally managed SYSTEM tablespace was supported beginning with Oracle9i, Release 2. • The benefits of this feature include: • Better performance • Ease of administration • A locally managed SYSTEM tablespace is of allocation type AUTOALLOCATE. • If your database has a locally managed SYSTEM tablespace, some restrictions apply.

  15. Locally Managed SYSTEMTablespace Restrictions • A locally managed SYSTEM tablespace places the following restrictions on the database: • COMPATIBLE must be at least 9.2.0. • Cannot have read/write dictionary-managed tablespaces • There must be a default temporary tablespace, which cannot be the SYSTEM tablespace. • You cannot have rollback segments in a dictionary-managed tablespace.

  16. Creating a Locally Managed SYSTEM Tablespace at Database Creation Time To create a locally managed SYSTEM tablespace at database creation time, you must specify the EXTENT MANAGEMENTLOCAL clause in the CREATEDATABASE statement: SQL> CREATE DATABASE 2 EXTENT MANAGEMENT LOCAL 3 DATAFILE SIZE 300M 4 UNDO TABLESPACE UNDO 5 DEFAULT TEMPORARY TABLESPACE TEMP;

  17. Migrating the SYSTEM Tablespaceto a Locally Managed Tablespace • Use the DBMS_SPACE_ADMIN package: • The locally managed SYSTEM tablespace restrictions are enforced by the migration procedure. • Migration is possible only when the system is in restricted mode while all tablespaces other than SYSTEM, UNDO, and TEMP are in READONLY mode. SQL> EXECUTE DBMS_SPACE_ADMIN. - 2 TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

  18. Default Permanent Tablespace: Overview • SYSTEM is the default permanent tablespace. • You can now change the default permanent tablespace for nonsystem users. System users Nonsystem users … SYS OUTLN SYSTEM HR SH OE Default SYSTEM

  19. Using EM to Designate a Default Permanent Tablespace

  20. Specifying a Default Permanent Tablespace by Using SQL CREATE DATABASE DATAFILE '/u0/datafiles/system1.dbf' SIZE 200M SYSAUX DATAFILE '/u0/datafiles/sysaux1.dbf' SIZE 300M DEFAULT TABLESPACE users DATAFILE '/u1/datafiles/users1.dbf' SIZE 100M SEGMENT SPACE MANAGEMENT AUTO DEFAULT TEMPORARY TABLESPACE stemp_tbs TEMPFILE '/u3/datafiles/stemp_tbs1.dbf' SIZE 60M UNDO TABLESPACE sundo_tbs DATAFILE '/u2/datafiles/sundo_tbs1.dbf' SIZE 100M; ALTER DATABASE DEFAULT TABLESPACE newusers; SELECT property_value FROM database_properties WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';

  21. Oracle Managed Files (OMF): Overview • Oracle database server creates and deletes files as needed for tablespaces, temporary files, online logs, and control files. • You only need to specify the OS directory to be used for each file type. • OMF and non-OMF files can coexist. • Benefits of using OMF: • Reduces corruption caused by administrators • Reduces disk space consumed by obsolete files • Simplifies creation of test and development databases • Makes development of portable applications easier

  22. Deciding to Use Oracle Managed Files • Databases that are supported by the following: • A logical volume manager that supports striping/RAID and dynamically extensible logical volumes • A file system that provides large, extensible files • Low-end or test databases • Not supported with raw devices

  23. Defining the Location for Oracle Managed Files • Default OS directory: DB_CREATE_FILE_DEST • Control files and online log files: DB_CREATE_ONLINE_LOG_DEST_n • Two basic OMF configurations: • All files in one file system directory (DB_CREATE_FILE_DEST) • Data files andtemporary files separated from log files and control files (DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n)

  24. Implementing OMF: Example • 1. Set the initialization parameters: • 2. Create a database: • Every data fileand log file is initially 100 MB by default, auto-extensible, and of unlimited size. DB_CREATE_FILE_DEST = '/u01/oradata/' DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata/' DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata/' SQL> CREATE DATABASE;

  25. OMF File Names: Structure • OMF file names comply with Optimal Flexible Architecture (OFA): • Control files o1_mf_%u_.ctl • Redo log files o1_mf_%g_%u_.log • Data files o1_mf_%t_%u_.dbf • Temporary data files o1_mf_%t_%u_.tmp • With SQL commands, you can manipulate existing OMF files just as you manipulatenormal files. • To determine whether a database file is an OMF: • Review the alert.log file. • Observe the file name.

  26. Managing OMF Control Files:Database Creation • If no CONTROL_FILES parameter is specified,Oracle database server uses (in order of precedence): • DB_CREATE_ONLINE_LOG_DEST_n • DB_CREATE_FILE_DEST • If none of the above are specified, the default destination is used (non-OMF). • After database creation, create a CONTROL_FILES entry in the initialization parameter file. • If there is a CONTROL_FILES parameter specified, then the behavior is unchanged from previous releases.

  27. Impact of OMF on the CREATECONTROLFILE Command • If the DB_CREATE_… parameters are specified,then the created control file will be OMF. • Create a CONTROL_FILES entry in the parameter file, unless you use a server parameter file. • Supply file names in the DATAFILE clauseeven for existing OMF files. • Depending on the [NO]RESETLOGS clause: • Supply log file name if using NORESETLOGS • Allow Oracle database server to create OMF redo logs if using RESETLOGS

  28. Managing OMF Redo Log Files • Add a complete group with ALTERDATABASEADDLOGFILE (no file specification, initialization parameters are used): • You continue to add or remove individual members by specifying full file names. • If you drop a group, all corresponding OMF files are deleted at the OS level. • Archived redo log files cannot be OMF files. SQL> ALTER DATABASE ADD LOGFILE; SQL> ALTER DATABASE ADD LOGFILE SIZE 10M;

  29. Managing OMF Tablespaces • The CREATETABLESPACE command no longer needs a DATAFILE clause: • When you drop a tablespace, all OMF files are also deleted at the OS level. • Add OMF files to a tablespace: • Change the default directory dynamically: SQL> CREATE TABLESPACE TBS1 [DATAFILE SIZE 200M]; SQL> ALTER TABLESPACE TBS1 ADD DATAFILE; SQL> ALTER SYSTEM SET 2 DB_CREATE_FILE_DEST='/oradata/';

  30. OMF Examples SQL> CREATE TABLESPACE tbs02 2 DATAFILE SIZE 300M, SIZE 300M; SQL> ALTER DATABASE ADD LOGFILE SIZE 400M; SQL> ALTER DATABASE RECOVER DATAFILE 2 '/u03/oradata/pay/o1_mf_tbs1_2ixfh90q_.dbf'; SQL> ALTER TABLE emp ALLOCATE EXTENT (DATAFILE 2 '/u03/oradata/pay/o1_mf_tbs1_2ixfh90q_.dbf'); SQL> ALTER DATABASE CREATE DATAFILE 2 '/u03/oradata/pay/o1_mf_tbs1_sd84oqy9_.dbf' 3 AS NEW;

  31. Bigfile Tablespaces: Overview • A bigfile tablespace contains a single file. • Maximum file size ranges from 8 TB to 128 TB. • Tablespaces are logically equivalent to data files. Database Tablespace Datafile Datafile Smallfile Bigfile

  32. Bigfile Tablespace Benefits • Significantly increases the storage capacity • Simplifies data file management for large databases by making tablespaces the main units of disk space administration … BFT n BFT 1 One-to-one mapping 8 EB 4 billionblocks

  33. Using Bigfile Tablespaces • Only supported for locally managed tablespaces using automatic segment space management • Use with logical volume managers or Automatic Storage Management (ASM) • OMF provides complete data file transparency.

  34. Creating Bigfile Tablespaces

  35. SQL Statement Changes and Additions • BIGFILE and SMALLFILE can be used to override the default tablespace type when creating a tablespace. • New commands and options were added to perform data file operations on bigfile tablespaces without specifying a data file name. • Specify file size in gigabytes and terabytes using the letters G and T, respectively.

  36. Specifying Bigfile Tablespaces in SQL Commands CREATE DATABASE SET DEFAULT BIGFILE TABLESPACE DATAFILE '/u0/data/system.dbf' SIZE 200M SYSAUX DATAFILE '/u0/data/sysaux.dbf' SIZE 300M SMALLFILE DEFAULT TEMPORARY TABLESPACE stemp_tbs TEMPFILE '/u3/data/stemp_tbs1.dbf' SIZE 60M SMALLFILE UNDO TABLESPACE sundo_tbs DATAFILE '/u2/data/sundo_tbs1.dbf' SIZE 100M; ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE; CREATE BIGFILE UNDO TABLESPACE bundo_tbs DATAFILE '/u1/data/bundo_tbs.dbf' SIZE 1G; ALTER TABLESPACE users RESIZE 2G; ALTER TABLESPACE users AUTOEXTEND ON ;

  37. Viewing Information About Bigfile Tablespaces SELECT property_value FROM database_properties WHERE property_name='DEFAULT_TBS_TYPE'; SELECT tablespace_name, bigfile FROM DBA_TABLESPACES; SELECT name, bigfile FROM V$TABLESPACE;

  38. Using DBVERIFY with Bigfile Tablespaces Smallfile tablespace Bigfile tablespace SFile1 SFile2 BFile1 $dbv FILE=SFile1 $dbv FILE=BFile1 START=1 END=10000 $dbv FILE=SFile2 $dbv FILE=BFile1 START=10001

  39. Modifying Configuration Parameters for Bigfile Tablespaces MAXDATAFILES Control files DB_FILES Data files Smallfiles Bigfiles

  40. Using the DBMS_UTILITY Package with Bigfile Tablespaces • Functions are not needed with bigfile tablespaces: • Bigfile tablespace file number is 1024. DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE() DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK()

  41. Migrating Objects to Bigfile Tablespaces SMALLFILE tablespace BIGFILE tablespace ALTER TABLE MOVE CREATE TABLE AS SELECT Data Pump

  42. Extended ROWID Format and Bigfile Tablespaces DBMS_ROWID.ROWID_BLOCK_NUMBER Smallfile tablespaces Bigfile tablespaces OOOOOO FFF BBBBBB RRR OOOOOO LLL LLLLLL RRR Rownumber Data objectnumber Data blocknumber Data objectnumber Encoded blocknumber Relative filenumber Rownumber

  43. DBMS_ROWID Package Changes TS_TYPE_IN ROWID_INFO ROWID_BLOCK_NUMBER ROWID_RELATIVE_FNO SELECTDBMS_ROWID.ROWID_RELATIVE_FNO(ROWID,'BIGFILE') FROM employees;

  44. Default Temporary Tablespace: Overview • Specify a database-wide default temporary tablespace at database creation or at a later time. • If you do not specify a default temporary tablespace at database creation: • SYSTEMis the default temporary tablespace • A warning is written to the alert.log file • Users not explicitly assigned to a temporary tablespace are assigned to the default temporary tablespace.

  45. Obtaining Information About the Default Temporary Tablespace • Query DATABASE_PROPERTIES to retrieve information about the current default temporary tablespace: SQL> SELECT property_value 2 FROM database_properties 3 WHERE property_name = 4 'DEFAULT_TEMP_TABLESPACE';

  46. Creating a Default Temporary Tablespaceat Database Creation Time SQL> CREATE DATABASE db1CONTROLFILE REUSE 2 LOGFILE 'log1.log' SIZE 10M 3 LOGFILE 'log2.log' SIZE 10M 4 DATAFILE'df1.dbf' AUTOEXTEND ON 5 'df2.dbf' AUTOEXTEND ON 6 NEXT 10M MAXSIZE UNLIMITED 7 DEFAULT TEMPORARY TABLESPACE dts1 8 TEMPFILE 'dts_1.f' SIZE 60M 9 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

  47. Altering the Default Temporary Tablespace • Change the default temporary tablespacewith the ALTERDATABASE command: • Users are automatically reassigned to the new default temporary tablespace. SQL> ALTER DATABASE db1 2 DEFAULT TEMPORARY TABLESPACE dts2;

  48. Managing Default TemporaryTablespace Restrictions • You cannot drop a default temporary tablespace until after a new one is made available. • Altering the default temporary tablespace to a permanent tablespace is not allowed (except for the SYSTEM tablespace). • You cannot take the default temporary tablespace offline.

  49. Temporary Tablespace Group: Overview • Groups temporary tablespaces together • Characteristics: • At least one temporary tablespace • Same namespace as tablespaces • Created implicitly on first assignment • No explicit deletion Default temporary tablespace group TEMP Default tablespace EXAMPLE Tablespace TEMP1 Tablespace TEMPn …

  50. Temporary Tablespace Group: Benefits • Enables a user to use multiple temporary tablespaces: • Same user in multiple sessions • One particular parallel operation HR Serial Parallel HR Tablespace TEMP1 Tablespace TEMP2 Tablespace TEMP3 Temporary tablespace group TEMP

More Related