Creation of the Oracle database starts and closes

Creation of the Oracle database starts and closes

Objective:

(1) master the creation and deletion of Oracle 11g database.

(2) mastering The start-up and shutdown of an Oracle db instance.

Content:

First, create a database

1. Planning Database

2. to create a database using the Oracle Database Configuration assistant

3. creating a database using the sql*plus script

Second, delete the database

Iii. startup and shutdown of an Oracle db instance

1. Steps for Database Startup

2. starting and shutting down the database in Sql*plus

Steps:

First, create a database

If you choose not to create a database when you install an Oracle system, just install the Oracle database Server software, in which case you will use the Oracle The system must create a database.

If you have created a database while installing the system, you can also create multiple databases in one Oracle database server, but in order to use The Oracle database service system to fully utilize the resources of the server, It is recommended that you create only one database on a single server.

Before you create a database, you need to do the following preparations:

1. Planning Database

2. Check system resources and configuration

3. Determine how the database was created

1. Planning Database

1) Determine the type of database

Depending on the purpose of the database, it can be divided into transaction processing (OLTP) types, Data Warehouse (DSS) types, and generic types that both have a general purpose.

2) Determine how the data is stored

Oracle 11g databases are stored in 3 main ways: file systems stored in operating system files, ASM with automated storage management, and bare devices in clustered systems.

3) Determine how the database is managed

Oracle 11g's database management is divided into centralized management using grid control and local management using Database control.

4) Specify the global database name for the newly installed database

The global database name is the globally unique identity of the Oracle database, in the form of XXX. Domain name, where xxx represents the SID, which is the service name.

5) familiar with relevant initialization parameters

2. to create a database using the Oracle Database Configuration assistant

(1) Start the database configuration Assistant

Select "Start" | " Program "|" Oracle Home directory (depends on installation) | Configuration and Removal Tool "|" Database

Configuration asssiant "shortcut. Or, you can start DBCA by typing DBCA in the Start menu run bar.

After the DBCA is started, the Database Configuration assiant: Welcome interface appears.

Click the Next button to go to the Action Selection dialog box and select the action you want to perform

(2) Select action Content

Choose CREATE DATABASE: Complete database creation or template creation. Click the Next button to go to the Select Database Type dialog box

(3) Select the type of database

The Database Type selection dialog box has a total of 3 database templates to choose from: General purpose or transactional, custom

Databases and data warehouses. When you are sure that you have created a template for the database, click the Next button to go to the Set Database Identity dialog box.

(4) Setting the database identity

In the Database Settings dialog box, you need to specify the global database name and SID for the database. After the global database name and SID settings are complete, click the Next button to enter the Configuration Database Management Options dialog box.

(5) Configuration database management

In the Database Management Options Configuration dialog box, you can set how the database is managed and can be managed by OEMs, and OEMs provide Web-based management tools for managing individual databases. After setting the database management options configuration, go to the Database Identity Certificate dialog box.

(6) Database identity Certificate

In the Database Authentication dialog box, set the password for the administrator. After setting the Database Identity Certificate dialog box, click the Next button to go to the Database File Location dialog box

(7) Where the database files are located

In the Database Files where dialog box, you can set the storage type of the database file, including: File system

Automatic storage management and bare devices. After setting the database storage options, click the Next button to go to the Restore Configuration dialog box.

(8) Restore Configuration

There are two recovery configuration options to choose from in the Restore Configuration dialog box. After the Restore configuration setting is complete, click the Next button to go to the database content setting.

(9) Database contents

In the Database Content Settings dialog box, you can choose whether to include the sample schema in the newly created database. You can select this option, and then click the Next button to go to the Initialize Parameters Settings dialog box.

(10) Initialize parameter settings

In the Initialize Parameter Settings dialog box, you can make memory, resize, character set,

The connection mode setting. Once you have set the parameters in the 4 tabs, click the All Initialization parameters button to view the parameter settings for the current database. After setting the database initialization parameters, click the Next button to enter the Database Storage dialog box shown in 4.17.

(11) Database storage

In the dialog box shown, you can make settings related to the physical structure and logical storage of the database, and can view or modify storage structure information such as database control files, redo log files, and data files.

(12) Create complete

Click the Finish button to finish setting up the database creation parameters and DBCA start the database creation.

3. creating a database using the sql*plus script

The following are the procedures for manually creating a database:

(1) Determine the name and instance name of the new database.

(2) Determine the login authentication method of the database administrator.

(3) Create initialization parameter file.

(4) Create an instance.

(5) Connect and launch the instance.

(6) Use the CREATE DATABASE statement.

(7) Create additional table spaces.

(8) Run the script to create a data dictionary view.

(9) Create a server initialization parameter file.

(10) Back up the database.

Second, delete the database

1. Start DBCA

Click on the "Next" button on the DBCA Welcome screen to enter the Action selection dialog box shown,

Select the action you want to perform.

2. Select action

In the Action Selection dialog box, select the "Delete database" action, click the "Next" button to enter the selection to delete

Database dialog box in addition to

3. Select the database you want to delete

In the Select Delete Database dialog box, select the database that you want to delete and correctly enter the SYSDBA permission

User name and password, click the Finish button to perform the delete operation for the database.

Iii. startup and shutdown of an Oracle db instance

1. Steps for Database startup:

A. creating and launching an instance (initializing a parameter file)

Creates an instance of a database, initiates a series of background processes and service processes, and creates memory structures such as the SGA area, based on the database initialization parameters file.

B. loading a database (control file)

The instance opens the control file for the database, where it obtains database physical structure information such as database name, data file and redo log file location, name, and prepares the database for opening. If the control file is corrupted, the instance will not be able to mount the database.

C. opening a database (data files vs. redo log files)

At this stage, the instance opens all the data files and redo log files that are online. If any of the data files or redo log files do not open properly, the database returns an error message, and the database needs to be restored.

The start of the Oracle database is divided into 3 steps to create and launch an instance, mount a database, open a database, respectively, corresponding to the Nomount, Mount, and open 3 states.

1) Start the Listening and database service using the command line method

Open the listener.

C:\>linster start;

Open the database service.

n c:\> oradim-startup-sid Orcl-starttype SRVC

n c:\> NET START ORACLESERVICEORCL (ORCL as database name)

C:\>oracle ORCL

2) Start the Listening and database service using the Service Manager mode

Select "Start" | " Set "|" Control Panel "|" Administrative Tools "|" Services option, open Windows Service Manager. Select the Listening service Oracle<oracle_home_name>tnslistener and the database service Oracle<sid> respectively, and right-click, in the Pop-up window, select the startup option.

The System Services window looks like this:

First you have to start the monitoring and service of Oracle.

The Database Service Properties dialog box looks like this:

2. starting and shutting down the database in Sql*plus

In order to start or shut down the database in Sql*plus, you need to start Sql*plus and connect to Oracle as SYSDBA. The steps are as follows:

(1) Start sql*plus in the Command line window.

C:\>sqlplus/as Syadba;

Start the database

The basic syntax for starting a database is as follows:

STARTUP [nomount| Mount| OPEN] [Force][restrict][pfile=filename]

Modifying the state of a database

During database startup, you can convert from a low state to a high state with the following syntax:

ALTER DATABASE [Mount][open]

Shutting down the database in Sql*plus

As opposed to the database startup process, the database shutdown is also divided into 3 steps: Shutting down the database, unloading the database, and shutting down the instance.

N Close the database

Oracle writes the redo log buffer contents to the Redo log file, writes the dirty cache block in the data cache to the data file, and then closes all data files and redo log files.

N Unload Database

After the database is closed, the instance unloads the database and closes the control file.

N Close Instance

After uninstalling the database, terminate all background processes and server processes and reclaim the memory space.

n Basic syntax for shutting down a database

SHUTDOWN [NORMAL | TRANSACTION | IMMEDIATE | ABORT]

SHUTDOWN [NORMAL]

n prevents any user from establishing a new connection;

n waits for the active disconnection of all currently connected users;

N Once all users are disconnected, close the database;

n the next time the database is started, no recovery process is required for any instances.

N SHUTDOWN IMMEDIATE

n prevents any user from establishing a new connection and does not allow the current connected user to initiate any new transactions;

n rollback of all currently uncommitted transactions;

n Terminates all users ' connections and shuts down the database directly;

n the recovery process for any instance is not required for the next startup of the database.

N SHUTDOWN TRANSACTION

n prevents all users from establishing a new connection, and does not allow the current connection user to initiate any new transactions;

n waits for the user to roll back or commit any currently uncommitted transactions, and then immediately disconnects the user;

n Close the database;

n the recovery process for any instance is not required for the next startup of the database.

N SHUTDOWN ABORT

n prevents any user from establishing a new connection and prevents the current connected user from starting any new transactions.

N immediately ends the currently executing SQL statement.

N Any uncommitted transactions are not rolled back.

N Interrupts all user connections and immediately shuts down the database.

n A recovery is required after the DB instance restarts.

Summary:

1. Database Startup steps:A. Create and launch an instance (initialize parameter file) B. Load Database (control file) c. Open Database (data files and redo log files)

2. As opposed to the database startup process, the database shutdown is also divided into 3 steps: Shutting down the database, unloading the database, and shutting down the instance.

3. Oracle 11g databases are stored in 3 main ways: file systems stored in operating system files, ASM with automated storage management, and bare devices in clustered systems. The start of the Oracle database is divided into 3 steps to create and launch an instance, mount a database, open a database, respectively, corresponding to the Nomount, Mount, and open 3 states.

4. Close the database: Oracle writes the redo log buffer contents to the Redo log file, writes the dirty cache block in the data cache to the data file, and then closes all data files and redo log files.

5. Unmount the database: After the database shuts down, the instance unloads the database and closes the control file. Close instance: After uninstalling the database, terminate all background processes and server processes and reclaim the memory space.

Creation of the Oracle database starts and closes