1 / 33

Creating an Oracle Database

Creating an Oracle Database. Considerations before creating a DB. Planning for Database Creation Select the standard database block size. Use an undo tablespace to manage your undo records Develop a backup and recovery strategy to protect the database from failure

ivie
Download Presentation

Creating an Oracle Database

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. Creating an Oracle Database

  2. Considerations before creating a DB Planning for Database Creation • Select the standard database block size. • Use an undo tablespace to manage your undo records • Develop a backup and recovery strategy to protect the database from failure • Familiarize yourself with the principles and options of starting up and shutting down an instance and mounting and opening a database.

  3. Considerations before creating a DB Deciding How to Create an Oracle Database • Creating information structures, including the data dictionary, that Oracle requires to access and use the database • Creating and initializing the control files and redo log files for the database • Creating new datafiles or erasing data that existed in previous datafiles • Use the Database Configuration Assistant (DBCA). • Create the database manually from a script.

  4. Planning Database File Locations • Keep at least two active copies of a database control file on at least two different devices. • Multiplex the redo log files and put group members on different disks. • Separate data files whose data: • Will participate in disk resource contention across different physical disk resources • Have different life spans • Have different administrative characteristics

  5. Creating a Database Using DBCA • Selecting the Template • Including Datafiles • Specifying Global Database Name and Database Features • Specifying Database Features • Specifying Mode, Initialization Parameters, and Datafiles

  6. Manually creating an Oracle DB • Step 1: Decide on Your Instance Identifier (SID) • Step 2: Establish the Database Administrator Authentication Method • Step 3: Create the Initialization Parameter File • Step 4: Connect to the Instance • Step 5: Start the Instance. • Step 6: Issue the CREATE DATABASE Statement • Step 7: Create Additional Tablespaces • Step 8: Run Scripts to Build Data Dictionary Views • Step 9: Run Scripts to Install Additional Options (Optional) • Step 10: Create a Server Parameter File (Recommended) • Step 11: Back Up the Database.

  7. Create Database • CREATE DATABASE [ database ] { USER SYS IDENTIFIED BY password | USER SYSTEM IDENTIFIED BY password | CONTROLFILE REUSE | MAXDATAFILES integer | MAXINSTANCES integer | CHARACTER SET charset | NATIONAL CHARACTER SET charset | SET DEFAULT { BIGFILE | SMALLFILE } TABLESPACE | database_logging_clauses | tablespace_clauses | set_time_zone_clause }... ;

  8. Database Logging Clauses

  9. { LOGFILE [ GROUP integer ] file_specification [, [ GROUP integer ] file_specification ]... | MAXLOGFILES integer | MAXLOGMEMBERS integer | MAXLOGHISTORY integer | { ARCHIVELOG | NOARCHIVELOG } | FORCELOGGING }

  10. Tablespace Clauses

  11. Tablespace Clauses • { EXTENTMANAGEMENTLOCAL | DATAFILE file_specification [, file_specification ]... | SYSAUX DATAFILE file_specification [, file_specification ]... | default_tablespace | default_temp_tablespace | undo_tablespace }

  12. Default Tablespace

  13. DEFAULT TABLESPACE tablespace [ DATAFILE datafile_tempfile_spec ] extent_management_clause

  14. Extent Managment Clause

  15. EXTENT MANAGEMENT { DICTIONARY | LOCAL [ AUTOALLOCATE | UNIFORM [ SIZE size_clause ] ] }

  16. Database creations are of three types: • Copy an existing database and delete the old database. • Copy an existing database and keep the old database. • Create a new database when no database exists on your system.

  17. Considerations before creating a DB Planning for Database Creation • Plan the database tables and indexes and estimate the amount of space they will require. • Plan the layout of the underlying operating system files that are to comprise your database. • Select the global database name, DB_NAME, DB_DOMAIN • Familiarize yourself with the initialization parameters • Select the database character set. • Consider what time zones your database must support.

  18. Creating Directories

  19. Exporting an Existing Database C:\> exp SYSTEM/password FILE=myexp.dmp FULL=y LOG=myexp.log C:\> set ORACLE_SID=orcl C:\> exp SYSTEM/password FILE=myexp.dmp FULL=y LOG=myexp.log

  20. Deleting Database Files • Shut down starter database orcl at the command prompt: C:\> oradim -SHUTDOWN -SID orcl -USRPWD password -SHUTTYPE inst -SHUTMODE i

  21. Deleting Database Files 2. Delete the following database files located in directory C:\oracle\oradata\orcl:

  22. Modifying the Initialization Parameter File • Copy: C:\ORACLE_BASE\admin\orcl\pfile\init.ora • to C:\ORACLE_BASE\admin\prod\pfile\init.ora ------------------------------------------------------------------- • initsmpl.ora • ORACLE_BASE\ORACLE_HOME\admin\sample\pfile

  23. Creating and Starting an Oracle Service • You are required to create and start an Oracle service only if you do one of the following: • Copy an existing database to a new database and keep the old database • Create a new database when you have no other database to copy Using ORADIM Utility to Administer an Oracle Instance

  24. Nos conectamos a la instancia creada: - sqlplus “/ as sysdba” - startup nomount pfile=d:\oracle\admin\prod\pfile\init.ora

  25. Putting the CREATE DATABASE Statement in a Script

  26. Running the CREATE DATABASE Script • Verify that the service is started in the Control Panel. Check Status with: C:\> net START Listservices: C:\> net START OracleServicePROD 2. Make PROD the current SID: • C:\> set ORACLE_SID=PROD

  27. Running the CREATE DATABASE Script 3. Start SQL*Plus from the command prompt, and connect to the database as SYSDBA C:\> sqlplus / NOLOG SQL> CONNECT / AS SYSDBA 4. Turn on spooling to save messages: SQL> SPOOL script_name.log 5. Run script script_name.sql that you created SQL> @C:\oracle\ora92\rdbms\admin\script_name.sql;

  28. Crear los directorios: • d:\oracle\admin\cg4 • d:\oracle\admin\cg4\bdump • d:\oracle\admin\cg4\pfile • d:\oracle\admin\cg4\udump • d:\oracle\oradata\cg4 • Copiar init.ora y editarlo con las nuevas rutas • Crear instancia: • ORADIM -NEW -SID cg4 -INTPWD cg4 -STARTMODE manual -PFILE "d:\oracle\admin\cg4\pfile\init.ora" • Establecer la bd a trabajar: • Set ORACLE_SID = cg4 • Conectarse al SQLPLUS • Sqlplus "/ as sysdba" • Levantar la BD no montada con el pfile init.ora • Startup nomount pfile=d:\oracle\admin\cg4\pfile\init.ora

  29. 7. Crear Script de CREATE_DATABASE CREATE DATABASE cg4 USER SYS IDENTIFIED BY dba USER SYSTEM IDENTIFIED BY manager Maxinstances 1 Maxloghistory 1 Maxlogfiles 10 Maxdatafiles 100 LOGFILE group 1 ('d:\oracle\oradata\cg4\redolog1a.dbf', 'd:\oracle\oradata\cg4\redolog1b.dbf') SIZE 10M, group 2 ('d:\oracle\oradata\cg4\redolog2a.dbf', 'd:\oracle\oradata\cg4\redolog2b.dbf' ) SIZE 10M, group 3 ('d:\oracle\oradata\cg4\redolog3a.dbf', 'd:\oracle\oradata\cg4\redolog3b.dbf' ) SIZE 10M DATAFILE 'd:\oracle\oradata\cg4\system01.dbf' SIZE 200M CHARACTER SET WE8ISO8859P1 national character set utf8 EXTENT MANAGEMENT LOCAL sysaux datafile 'd:\oracle\oradata\cg4\sysaux01.dbf' size 50M autoextend on next 16M maxsize unlimited undo tablespace UNDOTBS1 datafile 'd:\oracle\oradata\cg4\UNDOTBS1.dbf' size 50M DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE 'd:\oracle\oradata\cg4\temp01.dbf' SIZE 20M REUSE /

  30. 8. Ejecutar scripts de catalogos y diccionario de datos ORACLE_HOME\rdbms\admin START d:\oracle\ora92\rdbms\admin\CATALOG.sql START d:\oracle\ora92\rdbms\admin\CATPROC.sql START d:\oracle\ora92\rdbms\admin\catexp.sql

More Related