DBAI_les04_Rev1_1

Views:
 
Category: Entertainment
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

Creating a Database:

Creating a Database

Objectives:

Objectives After completing this lesson, you should be able to do the following: Understand the prerequisites necessary for database creation Create a database using Oracle Database Configuration Assistant Create a database manually Create a database using Oracle Managed Files

Managing and Organizing a Database:

Managing and Organizing a Database Planning for your database is the first step in managing a database system Define the purpose of the database Define the type of the database Outline a database architectural design Choose the database name Create your database Oracle Data Migration Assistant is used to migrate from an earlier version of the database

Optimal Flexible Architecture (OFA):

Optimal Flexible Architecture (OFA) Oracle’s recommended standard database architecture layout OFA involves three major rules: Establish a directory structure where any database file can be stored on any disk resource. Separate objects with different behavior into different tablespaces. Maximize database reliability and performance by separating database components across different disk resources.

Oracle Software and File Locations:

oracle _ base / product /admin /inst_name /pfile /release_number /bin /dbs /rdbms /sqlplus Software oradata/ db01/ system01.dbf control01.ctl redo0101.log ... db02/ system01.dbf control01.ctl redo0101.log ... Files Oracle Software and File Locations

Creation Prerequisites:

Creation Prerequisites To create a new database, you must have the following: A privileged account authenticated by one of the following: Operating system Password file Sufficient memory to start the instance Sufficient disk space for the planned database

Planning Database File Locations :

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

Authentication Methods for Database Administrators:

Remote database administration Local database administration Yes Yes No No Do you have a secure connection? Use OS authentication Use a password file Authentication Methods for Database Administrators Do you want to use OS authentication?

Using Password File Authentication:

Using Password File Authentication Create the password file using the password utility Set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in initialization parameter file Add users to the password file Assign appropriate privileges to each user $ orapwd file=$ORACLE_HOME/dbs/orapwU15 password=admin entries=5 GRANT SYSDBA TO HR;

Creating a Database:

Creating a Database An Oracle database can be created by: Oracle Universal Installer Oracle Database Configuration Assistant Graphical user interface Java-based Launched by the Oracle Universal Installer Can be used as a standalone The CREATE DATABASE command

Operating System Environment:

Operating System Environment Set the following environment variables: ORACLE_BASE ORACLE_HOME ORACLE_SID ORA_NLS33 PATH LD_LIBRARY_PATH

Database Configuration Assistant:

Database Configuration Assistant The Database Configuration Assistant allows you to: Create a database Configure database options Delete a database Manage templates Create new template using pre-defined template settings Create new template from an existing database Delete database template

Create a Database Using Database Configuration Assistant:

Create a Database Using Database Configuration Assistant Select type of database to be created from predefined templates Specify global database name and SID Select features to use in your database Identify any scripts to be run after database creation Select mode you want the database to operate in

Create a Database Using Database Configuration Assistant:

Create a Database Using Database Configuration Assistant Specify options for memory, archiving, database sizing, and file locations Define database storage parameters Change file location variables as needed Select a database creation option to complete database creation

PowerPoint Presentation:

Database Information Specify: • Global database name and SID • The features you want to use for your database, such as: – Oracle Spatial – Oracle OLAP Services – Example Schemas • Mode in which you want the database to operate – Dedicated server mode – Shared server mode

PowerPoint Presentation:

Typical or Custom Install Choose between typical or custom install

PowerPoint Presentation:

Other Parameters • Archive Parameters – Use for database recovery – May also be used for a standby database • Data Block Sizing – Sets the default database block size – Helps to determine the SORT_AREA_SIZE • File Locations – Specify paths for trace files – Specify paths for parameter files • Database storage – Specify storage parameters

Creating a Database Manually:

Creating a Database Manually Choose a unique instance and database name. Choose a database character set. Set operating system variables. Create the initialization parameter file. Start the instance in NOMOUNT stage. Create and execute the CREATE DATABASE command. Open the database. Run scripts to generate the data dictionary and accomplish post creation steps. Create additional tablespaces as needed.

Complete Database Creation :

Complete Database Creation Complete database creation using the following options: • Create database • Save as a database template • Generate database creation scripts

PowerPoint Presentation:

Creating a Database Manually • Decide on a unique instance and database name • Choose a database character set • Set the operating system variables • Edit / Create the initialization parameter file • Start the instance (nomount) • Execute the CREATE DATABASE command • Run scripts to generate the data dictionary and accomplish post creation steps

Preparing the Parameter File :

Preparing the Parameter File • Create the new initSID.ora • Modify the initSID.ora by editing the parameters COPY init.ora D:\ORACLE_HOME\dbs\initdb01.ora

PowerPoint Presentation:

Creating SPFILE Create the SPFILE from initSID.ora CREATE SPFILE FROM PFILE;

Starting the Instance :

Starting the Instance • Connect as SYSDBA • Start the instance in NOMOUNT stage STARTUP NOMOUNT

Creating the Database:

Creating the Database CREATE DATABASE user01 LOGFILE GROUP 1 ('/$HOME/ORADATA/u01/redo01.log') SIZE 100M, GROUP 2 ('/$HOME/ORADATA/u02/redo02.log') SIZE 100M, GROUP 3 ('/$HOME/ORADATA/u03/redo03.log') SIZE 100M MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 DATAFILE '/$HOME/ORADATA/u01/system01.dbf' SIZE 325M UNDO TABLESPACE undotbs DATAFILE '/$HOME/ORADATA/u02/undotbs01.dbf' SIZE 200M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE temp CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 SET TIME_ZONE= 'America/New_York'

Creating a Database Using Oracle Managed Files (OMF):

Creating a Database Using Oracle Managed Files (OMF) Using OMF simplifies file administration on the operating system OMF are created and deleted by the Oracle server as directed by SQL commands OMF are established by setting two parameters: DB_CREATE_FILE_DEST : Set to give the default location for datafiles DB_CREATE_ONLINE_LOG_DEST_N : Set to give the default locations for online redo logs and control files Maximum of five locations

Creating a Database Using Oracle Managed Files (OMF):

Creating a Database Using Oracle Managed Files (OMF) Define the OMF parameters in the initialization parameter file. Example: DB_CREATE_FILE_DEST=/$HOME/ORADATA/u05 DB_CREATE_ONLINE_DEST_1=/$HOME/ORADATA/u01 DB_CREATE_ONLINE_DEST_2=/$HOME/ORADATA/u02 CREATE DATABASE command is simplified: @cddba01.sql > CREATE DATABASE dba01;

Troubleshooting:

Troubleshooting Creation of the database fails if: There are syntax errors in the SQL script Files that should be created already exist Operating system errors such as file or directory permission or insufficient space errors occur

After Database Creation:

After Database Creation The database contains: Datafiles, control files, and redo log files User SYS with the password change_on_install User SYSTEM with the password manager Internal tables (but no data dictionary views)

Summary:

Summary In this lesson, you should have learned to: Identify the prerequisites for creating a database Create a database using the Oracle Database Configuration Assistant Create a database manually Create a database using Oracle Managed Files

Practice 4 Overview:

Practice 4 Overview This lesson provides two specific ways to creating a database: Use the Database Configuration Assistant to create a database using graphical steps. Launched by: Start > Programs > Oracle-OraHome90 > Configuration and Migration Tools . Appendix A provides a step by step guide for creating a database manually on a UNIX system. Review the steps, and optionally create a database manually or by using the Database Configuration Assistant.

authorStream Live Help