1 / 55

Oracle Database Administration

Oracle Database Administration. Session 4 Database Creation. Database Creation Overview. Init<SID>.ora Create database scripts Directory Structure Created. Init<SID>.ora (PFILE). Pfile Example - initE256.ora Contains all required the parameters Location of the Control files

alexa-stanton
Download Presentation

Oracle Database Administration

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 Database Administration Session 4 Database Creation

  2. Database Creation Overview • Init<SID>.ora • Create database scripts • Directory Structure Created

  3. Init<SID>.ora (PFILE) • Pfile • Example - initE256.ora • Contains all required the parameters • Location of the Control files • Location of the dump and trace files

  4. Server Parameter File (SPFILE) • Used to make persistent changes to the initialization parameters. • This eliminates the need to make changes to the init.ora, to preserve updates due to ‘Alter System’ commands. • Default location /$ORACLE_HOME/dbs • File name spfile<SID>.ora

  5. Server Parameter File • Create spfile='/u01/app/oracle/product/11.1.0test/dbs/ spfile<SID>.ora' FROM pfile='/u01/app/oracle/admin/test/scripts/ init<SID>.ora';

  6. The SGADEF File • The contents of this file have been deleted,. • If it exists, do not delete this file. It will be desupported in a future release. • Location /$ORACLE_HOME/dbs • lk<sID> --- lk<test>

  7. The Most Crucial Parameters • The following init.ora parameters are the most crucial • Db_cache_size • * Db_block_buffers* • Db_block_size • Shared_pool_size • log_buffer • Sort_area_size • sga_target • pga_aggregate_target • Optimizer_mode

  8. Control Files • The init<SID>.ora file lists the control files • There should be at least 3 copies of the control file • On a multi-disk system, the control files should be stored on separate disks • The database keeps these files in-sync. • This reduces the risk of catastrophe failure due to media failures

  9. Control File Locations control_files = (/u03/oradata/E256/control01.ctl, /u04/oradata/E256/control02.ctl, /u05/oradata/E256/control03.ctl)

  10. Dump File Locations background_dump_dest = /u01/app/oracle/admin/E256/bdump core_dump_dest = /u01/app/oracle/admin/E256/cdump user_dump_dest = /u01/app/oracle/admin/E256/udump diagnostic_dest = /u01/app/oracle/admin/E256/diag

  11. Parameters • rollback_segments = (r01,r02,r03,r04) • Manual undo • #rollback_segments = (r0) • optimizer_mode = CHOOSE • db_block_size = 8192 (8k) • compatible = 11.1.0 • compatible = 10.2.0 (old)

  12. Parameter Value Examples • db_files = 100 (Number) • db_cache_size = 100 (Mb) • shared_pool_size = 1500 (Mb) • java_pool_size = 1024000 (mb) • processes = 90 (Number) • log_buffer = 32768 (kb) • undo_management = AUTO (mode) • undo_tablespace = UNDOTBS

  13. Parameters • To get the deprecated Initialization Parameters in Oracle 11g • SQL> SELECT name FROM v$parameter WHERE isdeprecated = ‘TRUE’; • Select * from sys.v_$obsolete_parameter;

  14. Parameters • Initialization Parameters Deprecated in Oracle Database 11g Release 1 (11.1): • BACKGROUND_DUMP_DEST (replaced by DIAGNOSTIC_DEST) • COMMIT_WRITE • CORE_DUMP_DEST (replaced by DIAGNOSTIC_DEST) • INSTANCE_GROUPS • LOG_ARCHIVE_LOCAL_FIRST

  15. Parameters • Initialization Parameters Deprecated in Oracle Database 11g Release 1 (11.1): • PLSQL_DEBUG (replaced by PLSQL_OPTIMIZE_LEVEL) • PLSQL_V2_COMPATIBILITY • REMOTE_OS_AUTHENT • STANDBY_ARCHIVE_DEST • TRANSACTION_LAG (attribute of the CQ_NOTIFICATION$_REG_INFO object) • USER_DUMP_DEST (replaced by DIAGNOSTIC_DEST)

  16. Optimizer • There are two main optimization modes • Choose uses a cost_based optimization, when all tables are analyzed. This is a data_centric solution • Rule uses a rule_based optimization. This is an Oracle_centric solution based on rules set by the rdbms. Deprecated in 10g

  17. Redo Log Files • Redo log files should be mirrored • To mirror online Redo Log files, use redo log groups • Using redo log groups, removes the need for the O/S to maintain mirrored copies • The database maintains them automatically, using LGWR

  18. Redo Log Files • The LGWR writes to the Redo Log Group • It then cycles through the groups • A member of each group should be stored on separate disks. This will remove disk contention between members of each group • LGWR will experience little change in performance

  19. Redo Log files logfile group 1 ('/u03/oradata/E256/log1mem1.log', '/u04/oradata/E256/log1mem2.log') size 5M, group 2 ('/u04/oradata/E256/log2mem1.log', '/u05/oradata/E256/log2mem2.log') size 5M, group 3 ('/u05/oradata/E256/log3mem1.log', '/u03/oradata/E256/log3mem2.log') size 5M

  20. Data Dictionary Views REM # install data dictionary scripts @/u01/app/oracle/product/11.1.0/rdbms/admin/catalog.sql @//u01/app/oracle/product/11.1.0/rdbms/admin/catproc.sql @/u01/app/oracle/product/11.1.0/rdbms/admin/catexp.sql

  21. Automatic Rollback • Automatic Undo Management allows Oracle to manage the rollback function • Choice of managing undo (rollback) segments automatically or manually • Controlled by the init.ora parameter ‘undo_management’

  22. Automatic Rollback • The undo data is managed in a single undo tablespace • Use ‘Create undo tablespace’ command or the ‘undo_tablespace clause of the ‘Create database’ command, to create the tablespace

  23. Automatic Rollback • Create UNDO TABLESPACE "UNDOTBS" DATAFILE '/u04/oradata/test/undotbs01.dbf' SIZE 200M

  24. Manual Rollback • The first tablespace created is the System tablespace • Create a rollback segment in the SYSTEM tablespace, before creating any more tablespaces • This rollback segment is usually called r0 • Alter this segment online. This way you do not need to shutdown and restart the database.

  25. Manual Rollback Connect as internal create rollback segment r0 tablespace system storage (initial 16k next 16k minextents 2 maxextents 20); Use ALTER ROLLBACK SEGMENT ONLINE to put r0 online alter rollback segment r0 online;

  26. Manual Rollback create tablespace rollback datafile '/u05/oradata/E256/rbs01.dbf' size 50M default storage ( initial 128k next 128k pctincrease 0 minextents 2 );

  27. Default Accounts REM * Alter SYS and SYSTEM users. alter user sys temporary tablespace temp; alter user system temporary tablespace temp;

  28. Temp Tablespace – Dictionary Managed create tablespace temp tempfile '/u04/oradata/E256/temp01.dbf' size 50M default storage ( initial 128k next 128k pctincrease 0 minextents 1 );

  29. Temp Tablespace – Locally Managed • CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE'/u04/oradata/test/temp01.dbf' SIZE 40M EXTENT MANAGEMENT LOCAL;

  30. Users Tablespace • CREATE TABLESPACE "USERS" DATAFILE '/u04/oradata/test/users01.dbf' SIZE 250M EXTENT MANAGEMENT LOCAL;

  31. Save the Output • REM * Log output of this script. • Give this file a meaningful name spool create_db.lst . . . spool off

  32. Directory Structure • Create an instance directory structure under the /admin directory • Example: create an E256 directory • Create the following directories under this • pfile • create • udump • bdump • cdump

  33. Directory Structure • Create /oradata directories under /u03, /u04 and /u05 etc • Under those directories, create /E256 directories. This ensures that the files related to this database is separate from those of another database. • Example: /u04/oradata/E256

  34. Useful commands • You can use the following commands to track and manage datafiles • ls -lasd /u*/oradata/<SID>/* lists all the files related to a database • rm /u*/oradata/<SID>/* removes all the files related to a database • rm /u*/oradata/<SID>/*.ctl removes the control files

  35. Server Manager • Use Sqlplus /nologin • CONNECT / as SYSDBA • or • CONNECT username/password as sysdba

  36. Start and stop • Startup • Startup nomount • Startup mount • Shutdown • Shutdown immediate • Shutdown abort

  37. V$database • SQLPLUS> select name from v$database; NAME --------- E256

  38. Global Name • SQLPLUS> select * from global_name; GLOBAL_NAME ------------------------- E256.HARVARD.EDU

  39. v$tablespace • SQLPLUS> select * from v$tablespace; TS# NAME ------ -------------- 0 SYSTEM 1 ROLLBACK 2 TEMP 3 TOOLS 4 USERS • 5 rows selected.

  40. Show SGA • Show sga, will give us the size of the sga and breaks it into fixed and variable parts • v$parameter • V$sga

  41. Unix Process • We can use • ps -eaf | grep dbw -- to show us that the database is up and running • ps -eaf | grep oracle -- will give us all the Oracle processes • ps -eaf | grep E256 -- will give all the processes related to our database.

  42. Unix Process elmo oracle $ ps -eaf | grep E256 oracle 16623 1 0 21:44:04 ? 0:02 ora_ckpt_E256 oracle 16625 1 0 21:44:04 ? 0:04 ora_smon_E256 oracle 16621 1 0 21:44:04 ? 0:19 ora_lgwr_E256 oracle 16617 1 0 21:44:04 ? 0:00 ora_pmon_E256 oracle 16627 1 0 21:44:04 ? 0:00 ora_reco_E256 oracle 16619 1 0 21:44:04 ? 0:11 ora_dbw0_E256

  43. Multiple Block Size Support • The standard database block size was set using db_block_size parameter • It cannot be changed after the database is created, it requires the recreation of the database • The standard block size is used to create the SYSTEM tablespace

  44. Multiple Block Size Support • To use non-standard block size tablespaces, sub-caches must be configured inside the buffer cache area of the SGA • Using the db_nK_cache_size parameter, where n = 2, 4, 8 16, or 32

  45. Multiple Block Size Support • The db_cache_size parameter replaces the db_block_buffers for the standard block size • The db_nK_cache_size parameter must be set for each block size used. • The default is 0

  46. <sid>.sh • #!/bin/sh • mkdir -p /u01/app/oracle/admin/test/diag • mkdir -p /u01/app/oracle/admin/test/create • mkdir -p /u01/app/oracle/admin/test/pfile • mkdir -p /u01/app/oracle/flash_recovery_area • mkdir -p /u04/app/oracle/oradata • mkdir -p /u18/oradata/test • ORACLE_SID=dev; export ORACLE_SID

  47. <sid>.sh • echo Add this entry in the oratab file dev:/u01/app/oracle/product/11.1.0test:Y • /u01/app/oracle/product/10.2.0test/bin/sqlplus /nolog @/u01/app/oracle/admin/test/scripts/dev.sql

  48. <sid>.sql • @/u01/app/oracle/admin/test/scripts/CreateDB.sql • @/u01/app/oracle/admin/test/scripts/CreateDBFiles.sql • @/u01/app/oracle/admin/test/scripts/CreateDBCatalog.sql • @/u01/app/oracle/admin/test/scripts/JServer.sql • @/u01/app/oracle/admin/test/scripts/interMedia.sql • @/u01/app/oracle/admin/test/scripts/xdb_protocol.sql • @/u01/app/oracle/admin/test/scripts/emRepository.sql • @/u01/app/oracle/admin/test/scripts/postDBCreation.sql

  49. Create Database • connect SYS/&&sysPassword as SYSDBA • set echo on • spool /u01/app/oracle/product/11.1.0test/assistants/dbca/logs/CreateDB.log • startup nomount pfile="/u01/app/oracle/admin/test/scripts/init.ora";

  50. Create Database • CREATE DATABASE "dev" • MAXINSTANCES 8 • MAXLOGHISTORY 1 • MAXLOGFILES 16 • MAXLOGMEMBERS 3 • MAXDATAFILES 100 • DATAFILE '/u18/oradata/test/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED • EXTENT MANAGEMENT LOCAL

More Related