Oracle Database creation, startup, and Shutdown

Oracle Database creation, startup, and Shutdown

Oracle Database creation, startup, and Shutdown

Purpose:

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

(2) master the startup and shutdown of Oracle database instances.

Content:

1. Create a database

1. Plan the database

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

3. Use the SQL * Plus script to create a database

Ii. delete a database

Iii. Oracle database instance startup and Shutdown

1. Database startup steps

2. Start and close the database in SQL * Plus

Steps:

1. Create a database

If you choose not to create a database but to install the Oracle database server software when installing the Oracle system, you must create a database to use the Oracle system.

If you have created a database when installing the system, you can also create multiple databases on an Oracle database server. However, to use the Oracle database service system to make full use of server resources, we recommend that you create only one database on one server.

Make the following preparations before creating a database:

1. Plan the database

2. Check System Resources and configurations

3. Determine the database creation method.

1. Plan the database

1) determine the Database Type

Based on the purpose of the database, it can be divided into transaction processing (OLTP) type, data warehouse (DSS) type, and the general types of both types for general purpose.

2) determine the data storage method

There are three primary storage methods for Oracle 11g databases: file systems that store operating system files, ASM that use automatic storage management, and bare devices in Cluster Systems.

3) determine the Database Management Method

Oracle 11 GB 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 Identifier of the Oracle database. Its format is XXX. Domain Name. XXX indicates the SID, that is, the service name.

5) familiar with initialization parameters

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

(1) Start the Database Configuration Assistant

Select "start" | "program" | "Oracle main directory" (depending on the installation) | "configuration and migration tools" | "Database

Configuration Asssiant shortcut. You can also type DBCA in the Start menu to start DBCA.

After DBCA is started, the "Database Configuration Assiant: Welcome to use" interface appears.

Click "Next" to go to the operation selection dialog box and select the operation to be executed.

(2) Select Operation content

Select create database: Create a database or template. Click "Next" to go to the "select database type" dialog box.

(3) Select the Database Type

The Database Type Selection dialog box contains three types of database templates: general purpose, transaction processing, and customization.

Databases and data warehouses. After confirming the template for creating the database, click "Next" to enter the "set database ID" dialog box.

(4) set the database ID

In the database Settings dialog box, specify the global database name and SID for the database ". After setting the global database name and SID, click "Next" to enter the "manage options" dialog box for configuring the database.

(5) Configure Database Management

In the database management option configuration dialog box, you can set how to manage the database. You can manage the database through OEM. OEM provides a Web-based management tool for managing each database. After configuring the database management options, go to the "database Identity Authentication" dialog box.

(6) database Identity Authentication

In the "Database Authentication" dialog box, set the administrator password. After setting the "Database Authentication" dialog box, click "Next" to go to the "database file location" dialog box.

(7) Location of database files

In the "Location of database files" dialog box, you can set the storage type of database files, including: File System

System, automatic storage management, and bare devices. After setting the database storage options, click "Next" to go to the "Restore configuration" dialog box.

(8) Restore Configuration

In the "recovery configuration" dialog box, there are two recovery configuration options to choose from. After "Restore configuration" is set, click "Next" to go to "database content" settings.

(9) database content

In the "database content" Setting dialog box, you can choose whether to include the "sample solution" in the newly created database ". You can select this option and click "Next" to go to the "initialize Parameters" Setting dialog box.

(10) initialization parameter settings

In the "initialization parameter settings" dialog box, you can perform "Memory", "resize", "Character Set ",

Set "connection mode. After setting parameters in the four tabs, click "all initialization parameters" to view the parameter settings of the current database. After setting the database initialization parameters, click "Next" to go to the "database storage" dialog box shown in 4.17.

(11) database storage

In the displayed dialog box, you can set the physical and logical storage of the database to view or modify the storage structure information such as the database control file, redo log file, and data file.

(12) Creation complete

Click "finish" to complete the database creation parameter settings. DBCA starts database creation.

3. Use the SQL * Plus script to create a database

The procedure for creating a database manually is as follows:

(1) determine the name and Instance name of the new database.

(2) determine the logon authentication method of the database administrator.

(3) create an initialization parameter file.

(4) Create an instance.

(5) connect and start the instance.

(6) use the create database statement to CREATE a DATABASE.

(7) create an additional tablespace.

(8) run the script to create the data dictionary view.

(9) create a server initialization parameter file.

(10) back up the database.

Ii. delete a database

1. Start DBCA

On the DBCA welcome page, click "Next" To Go To The displayed "operation" selection dialog box,

Select the operation to perform.

2. Select Operation

In the operation selection dialog box, select "delete database" and click "Next" to go to the selection and Deletion page.

"Database" dialog box

3. Select the database to be deleted

In the Select Delete database dialog box, select the database to be deleted, and enter the SYSDBA permission correctly.

And click "finish" to delete the database.

Iii. Oracle database instance startup and Shutdown

1. Database startup steps:

A. Create and start the instance (initialize the parameter file)

Create an instance for the database based on the database initialization parameter file, start a series of background processes and service processes, and create memory structures such as the SGA zone.

B. Load the database (control file)

Open the Database Control file for the instance, and obtain the database physical structure information such as the database name, data file, and redo log file location and name, to prepare for opening the database. If the control file is corrupted, the instance cannot load the database.

C. Open the database (data files and redo log files)

In this phase, the instance opens all online data files and redo log files. If any data file or redo log file cannot be opened normally, the database returns an error message, and the database needs to be restored.

Oracle Database startup involves three steps: Creating and starting an instance, loading a database, and opening a database: NOMOUNT, MOUNT, and OPEN.

1) Use the command line method to start listeners and database services

Open the listener.

C: \> linster start;

Open the database service.

N C: \> oradim-startup-sid orcl-starttype srvc

N C: \> net start OracleServiceORCL (ORCL is the database name)

C: \> ORACLE ORCL

2) use the service manager to start listeners and database services

Select Start, set, control panel, administrative tools, and services to open the Windows Service Manager. Select the listening service Oracle <ORACLE_HOME_NAME> TNSListener and Database Service Oracle <SID>, right-click, and select the start option in the displayed window.

The system "service" window is shown in:

First, you must start Oracle listening and services.

The Database Service Properties dialog box is shown in:

2. Start and close the database in SQL * Plus

To start or shut down the database in SQL * Plus, you must start SQL * Plus and connect to Oracle AS SYSDBA. The procedure is 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]

● Modify the database status

During database startup, you can switch from a low state to a high state. The syntax format is as follows:

Alter database [MOUNT] [OPEN]

● Shut down the database in SQL * Plus

In response to the database startup process, database shutdown is also divided into three steps: shutting down the database, detaching the database, and shutting down the instance.

N shut down the database

Oracle writes the redo log buffer content to the redo log file, writes dirty cache blocks in the data cache to the data file, and then closes all data files and redo log files.

N detach a database

When the database is closed, the instance uninstalls the database and closes the control file.

N close the instance

After the database is detached, all background and server processes are terminated and the memory space is reclaimed.

N basic syntax for shutting down a database

SHUTDOWN [NORMAL | TRANSACTION | IMMEDIATE | ABORT]

SHUTDOWN [NORMAL]

N blocks any user from establishing new connections;

N waiting for all currently connected users to actively disconnect;

N once all users are disconnected, the database is closed;

N the database does not need any instance recovery process at the next startup.

N SHUTDOWN IMMEDIATE

N blocks any user from establishing new connections and does not allow the current connected user to start any new transactions;

N roll back all uncommitted transactions;

N terminate the connection of all users and directly close the database;

N no instance recovery is required for the next database startup.

N SHUTDOWN TRANSACTION

N blocks all users from establishing new connections and does not allow the current connected user to start any new transactions;

N wait for the user to roll back or commit any uncommitted transactions, and then immediately disconnect the user;

N shut down the database;

N no instance recovery is required for the next database startup.

N SHUTDOWN ABORT

N Stops any user from establishing new connections and stops the current connected user from starting any new transactions.

N immediately ends the SQL statement being executed.

N any uncommitted transactions are not rolled back.

N. Disconnect all user connections and immediately close the database.

N the database instance needs to be restored after being restarted.

Summary:

1. Database startup steps: a. Create and start an instance (initialize the parameter file) B. Load the database (control file) c. Open the database (data files and redo log files)

2. In response to the database startup process, database shutdown is also divided into three steps: shutting down the database, detaching the database, and shutting down the instance.

3. There are three main Oracle 11g Database Storage Methods: file systems that store operating system files, ASM that use automatic storage management, and bare devices in Cluster Systems. Oracle Database startup involves three steps: Creating and starting an instance, loading a database, and opening a database: NOMOUNT, MOUNT, and OPEN.

4. Close the database: Oracle writes the redo log buffer content to the redo log file, writes dirty cache blocks in the data cache to the data file, and closes all data files and redo log files.

5. unmount the database: After the database is closed, the instance uninstalls the database and closes the control file. Shut down the instance: After the database is detached, all background and server processes are terminated and the memory space is reclaimed.