Shulou(Shulou.com)05/31 Report--

This article mainly shows you "Oracle how to build a database by hand", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "Oracle how to build a database by hand" this article.

You can refer to the official documentation.

Administrator's Guid

Create and configure the database

Create a database using a database-building statement

1-14 steps to manually build the database for official documents, 9 for the statement to build the database

Step 1: specify the instance identifier (SID)

Step 2: make sure the required environment variables are set

Step 3: select the database administrator authentication method

Step 4: create an initialization parameter file

Step 5: (Windows only) create an instance

Step 6: connect to the instance

Step 7: create a server parameter file

Step 8: start the instance

Step 9: issue the CREATE DATABASE statement

Step 10: create additional tablespaces

Step 11: run the script to build a data dictionary view

Step 12: (optional) run the script to install additional options

Step 13: back up the database.

Step 14: (optional) enable automatic instance startup

1. Create a necessary file path

The purpose of these file paths is described below.

Adump: store audit information

The bdump:bdump directory stores the tracking information of various background processes in the process of database movement, in which the alert file is a warning file and its file name is alert_book.log. When there is a problem with the database, you can first check this file to find out the cause. Various problems in the process of manual creation can often be found by viewing this file.

Cdump: the database is used to store exception information when it encounters an exception.

Create: unknown

Pfile: stores initialization parameter files.

Scripts: unknown

Udump: the trace file of the session after placing the sql trace.

(2) create a TESTDB folder under the oradata folder

(3) create a TESDB folder under the flash_recovery_area folder

[oracle@ENMOEDU oracle] $cd / u01/app/oracle/

[oracle@ENMOEDU oracle] $mkdir-p admin/TESTDB/audmp

[oracle@ENMOEDU oracle] $mkdir-p flash_recovery_area

2. Create the initialization parameter file pfile

Method 1: copy a pfile parameter file from other libraries and modify the content and file name for this use. Meet initorcl.ora.

Orcl.__db_cache_size=855638016

Orcl.__java_pool_size=16777216

Orcl.__large_pool_size=16777216

Orcl.__oracle_base='E:\ app\ Administrator'#ORACLE_BASE set from environment

Orcl.__pga_aggregate_target=1191182336

Orcl.__sga_target=2248146944

Orcl.__shared_io_pool_size=0

Orcl.__shared_pool_size=1325400064

Orcl.__streams_pool_size=0

* .audit_file_dest='E:\ app\ Administrator\ admin\ orcl\ adump'

* .audit_trail='db'

* .compatible='11.2.0.0.0'

* .control_files='E:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ CONTROL01.CTL','E:\ APP\ ADMINISTRATOR\ FLASH_RECOVERY_AREA\ ORCL\ CONTROL02.CTL'#Restore Controlfile

* .db_block_size=8192

* .db_domain=''

* .db_name='orcl'

* .db_recovery_file_dest_size=4102029312

* .db_recovery_file_dest=''

* .diagnostic_dest='E:\ app\ Administrator'

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=orclXDB)'

* .log_archive_dest='E:\ log'

* .log_archive_format='arch_%t_%s_%r.log'

* .memory_target=3435134976

* .open_cursors=300

* .processes=150

* .remote_login_passwordfile='EXCLUSIVE'

* .undo_tablespace='UNDOTBS1'

Method 2: create the pfile file according to the init.ora of the installation software and adjust it

We install the software $ORACLE_HOME/dbs according to Oracle

(e:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ dbs) directory to customize the pfile we need.

(1) copy init.ora to the "E:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ database" path and rename it to "initTESTDB.ora".

(2) Edit the "initTESTDB.ora" file.

[oracle@ENMOEDU dbs] $vi initTESTDB.ora

Diagnostic_dest='/u01/app/oracle'

Db_name='TESTDB'

Memory_target=1G

Processes = 150

Audit_file_dest='/u01/app/oracle/admin/TESTDB/adump'

Audit_trail = 'db'

Db_block_size=8192

Db_domain=''

Db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

Db_recovery_file_dest_size=2G

Diagnostic_dest='/u01/app/oracle'

Open_cursors=300

Remote_login_passwordfile='EXCLUSIVE'

Undo_tablespace='UNDOTBS1'

Control_files = (/ u01qapax oradataUniverse control 01.ctlMagneur u01qaphoraclecontrol oradataandTESTDB control 02.ctl)

Compatible = '11.2.0'

The basic template is as follows:

Db_name='ORCL'

Memory_target=1G

Processes = 150

Audit_file_dest='/admin/orcl/adump'

Audit_trail = 'db'

Db_block_size=8192

Db_domain=''

Db_recovery_file_dest='/flash_recovery_area'

Db_recovery_file_dest_size=2G

Diagnostic_dest=''

Dispatchers=' (PROTOCOL=TCP) (SERVICE=ORCLXDB)'

Open_cursors=300

Remote_login_passwordfile='EXCLUSIVE'

Undo_tablespace='UNDOTBS1'

Control_files = (ora_control1, ora_control2)

Compatible = '11.2.0'

3. Set the name of the instance started by oracle

Set ORACLE_SID=TESTDB

4. Create a library instance

Enter the command on the CMD command line: oradim-new-sid TESTDB

5. Create a password file

The purpose of the Oracle password file is to store the passwords of all users who connect to the database with sysdba or sysoper privileges.

Open the CMD command and enter the command line:

Orapwd file=E:\ app\ SANMSUNG\ product\ 11.2.0\ dbhome_1\ database\ PWDTESTDB.ora password=oracle entries=30

Note that the entries parameter, which specifies the number of users in the database with DBA privileges, can be specified by the user.

6. Start the database to nomount status

Sqlplus / as sysdba

Startup nomount

7. Execute the database building statement

The following statements can be copied to the CMD command line or copied to a text file to be executed with @.

CREATE DATABASE TESTDBCREATE DATABASE testdb

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('E:\ oracle\ oradata\ testdb\ redo01.log') SIZE 100m

GROUP 2 ('E:\ oracle\ oradata\ testdb\ redo02.log') SIZE 100m

GROUP 3 ('E:\ oracle\ oradata\ testdb\ redo03.log') SIZE 100m

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

CHARACER SET zhs16gbk

NATIONL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFIE'e:\ oracle\ oradata\ testdb\ system01.dbf' SIZE 500m REUSE

SYSUX DATAFILE'e:\ oracle\ oradata\ testdb\ sysaux01dbf' SIZE 500m REUSE

DEFAULT TABLESPACE users

DATAFILE'e:\ oracle\ oradata\ testdb\ users01.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE temp

TEMPFILE'e:\ oracle\ oradata\ testdb\ temp01.dbf'

SIZE 20M REUSE autoextend on maxsize unlimited

UNDO TABLESPACE undotbs1

DATAFILE'e:\ oracle\ oradata\ testdb\ undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

7.1 write database building statements with the help of official documents

Check the official documentation reference: http://blog.csdn.net/huangyanlong/article/details/43925909

CREATE DATABASE mynewdb

USER SYS IDENTIFIED BY sys_password

USER SYSTEM IDENTIFIED BY system_password

LOGFILE GROUP 1 ('/ u01qlogsAccording to redo01a. SIZE BLOCKSIZE BLOCKSIZE 512)

GROUP 2 ('/ u01qlogsAccording to redo02a. SIZE BLOCKSIZE BLOCKSIZE 512)

GROUP 3 ('/ u01qlogsUnigram SIZE BLOCKSIZE 100m BLOCKSIZE 512)

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE'/ u01 SIZE SIZE oradata REUSE

SYSAUX DATAFILE'/ u01 SIZE REUSE

DEFAULT TABLESPACE users

DATAFILE'/ u01hash appUnitionoracleandoradataUS01.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE'/ u01qapplash oracle.oradataAccording to MynewdbDB temp01.dbf'.

SIZE 20M REUSE

UNDO TABLESPACE undotbs

DATAFILE'/ u01qapplash oracle.oradataAccording to undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

According to the actual modification of the script, delete some unnecessary settings

-- diversifying the members of the log group

-- modify path information

CREATE DATABASE HYL

LOGFILE GROUP 1 ('/ u01qapplash oracle.oradataUniqHYLscarp disk1andredo01a.log'

'/ u01 apprenticeship oracle.oradata _ HYL _

SIZE 100M BLOCKSIZE 512

GROUP 2 ('/ u01qapplash oracle.oradataUniqHYLscarp disk1andredo02a.log'

'/ u01 apprentice.oracle.oradata / HYLUnix disk2scarp redo02b.log')

SIZE 100M BLOCKSIZE 512

GROUP 3 ('/ u01qapplash oracle.oradataUniqHYLscarp disk1Actionredo03a.log'

'/ u01 apprentice.oracle.oradata / HYLUnix disk2scarp redo03b.log')

SIZE 100M BLOCKSIZE 512

CHARACTER SET zhs16gbk

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE'/ u01 SIZE SIZE oradata REUSE

SYSAUX DATAFILE'/ u01 SIZE SYSAUX DATAFILE'/ u01 REUSE

DEFAULT TABLESPACE users

DATAFILE'/ u01qqappActionoradataOnHYLOnDISK3CompUSUS01.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE'/ u01qapplash oracle.oradata, HYL, disk4andtemp01.dbf'.

SIZE 20M REUSE

UNDO TABLESPACE undotbs

DATAFILE'/ u01 apprenticeship oracle.oradataUndotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

-- put the above script in the oracle directory and execute it

$cd / u01/app/oracle/

$vi createDB.sql

SQL > @ / u01/app/oracle/createDB.sql

7.2 set data files and temporary files to be automatically extended

(1) obtain all data files and temporary file information

SQL > col name for A60

SQL > select file#,name from v$datafile union select file#,name from v$tempfile

FILE# NAME

1/ u01/app/oracle/oradata/secooler/dfile/system01.dbf

1/ u01/app/oracle/oradata/secooler/dfile/temp01.dbf

2 / u01/app/oracle/oradata/secooler/dfile/undotbs01.dbf

3 / u01/app/oracle/oradata/secooler/dfile/sysaux01.dbf

4 / u01/app/oracle/oradata/secooler/dfile/tbs_1.dbf

(2) adjust it to expand automatically.

SQL > alter database datafile 1 autoextend on

SQL > alter database datafile 2 autoextend on

SQL > alter database datafile 3 autoextend on

SQL > alter database datafile 4 autoextend on

SQL > alter database tempfile 1 autoextend on

8. Follow the catalog steps to create a data dictionary

Execute under the CMD command line:

Or SQL > @? / rdbms/admin/catalog.sql

@ E:\ app\ SANMSUNG\ product\ 11.2.0\ dbhome_1\ RDBMS\ ADMIN\ catalog.sql

It takes about 5 minutes to create a data dictionary.

9. Execute catproc to create package package

Execute under the CMD command line:

Or SQL > @? / rdbms/admin/catproc.sql

@ E:\ app\ SANMSUNG\ product\ 11.2.0\ dbhome_1\ RDBMS\ ADMIN\ catproc.sql

It takes about 10 minutes. After running these two scripts, the library has been built. Its function is to create stored procedures and packages.

10. Execute pupbld

Switch the user to system before executing pupbld.

SQL > connect system/oracle

Or SQL > @? / sqlplus/admin/pupbld.sql

SQL > E:\ app\ SANMSUNG\ product\ 11.2.0\ dbhome_1\ sqlplus\ admin\ pupbld.sql

Run this script as a system user for about 1 minute to set up some packages for sqlplus so that other users can log in to sqlplus

11. Create spfile by pfile

The creation of spfile requires dba permission.

SQL > con sys as sysdba

Create spfile from pfile

SQL > connect system/oracle

12. Execute scott script to create scott mode

This link can be omitted, of course, if you need a practice environment, it is best not to omit.

Enter @ E:\ app\ SANMSUNG\ product\ 11.2.0\ dbhome_1\ RDBMS\ ADMIN\ scott.sql on the CMD command line

13. Open the database to the normal state

Restart the database and verify that the database was built successfully.

Shutdown immediate

Startup

Select instance_name,status from v$instance

The above is all the contents of the article "how to build the library manually by Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.