Shulou(Shulou.com)06/01 Report--

When I first joined the job, a server hung up, so I had the opportunity to learn in the "crisis". In the dead of night, I want to recall the creation of Oracle instances. One method, DBCA, graphical interface, but needs to rely on software such as xming or xmanager; the other is to create an Oracle database instance by script. The first method is simple and easy to use, but for beginners, you can't learn more about the database; the second method uses scripts to better understand the various files and parameters of the database. This article only introduces scripts to create Oracle instances.

Remember that in 2010, I still used Oracle10g, and now I use 11g. The difference between them is that the dump directory is slightly different. Let's take Oracle 11g as an example to see what points we need to pay attention to.

Step 1: create a file directory and authorize the file directory to oracle users. Take webdb as an example, and do the following: adump,dpdump,fast_recovery_area is necessary (these directories 11g and 10g are slightly different from 10g, which should be noted). According to administrator habits, oradata/webdb and / tabdata/webdb are generally used to store system-level files, such as redolog,system file,undo file, etc. Tabdata/webdb is used to put newly added tablespace files (data files).

Mkdir-p / u00/app/oracle/admin/webdb/adump

Mkdir-p / u00/app/oracle/admin/webdb/dpdump

Mkdir-p / u00/app/oracle/fast_recovery_area/webdb

Mkdir-p / u01/oradata/webdb

Mkdir-p / u01/tabdata/webdb

Chown oracle:oinstall / u00/app/oracle/admin/webdb/adump

Chown oracle:oinstall / u00/app/oracle/admin/webdb/dpdump

Chown oracle:oinstall / u00/app/oracle/fast_recovery_area/webdb

Chown oracle:oinstall / u01/oradata/webdb

Chown oracle:oinstall / u01/tabdata/webdb

Step 2: export instance name

ORACLE_SID=webdb; export ORACLE_SID

Step 3: manually add the instance name under the / etc/oratab file (this file is very important, which will be described in a later article)

Echo "webdb:$ORACLE_HOME:Y" > > / etc/oratab

Step 4: manually add the pfile file to $ORACLE_HOME/dbs (this file is an important file for instance parameter values)

Vi $ORACLE_HOME/dbs/initwebdb.ora

The contents of the document are as follows:

Webdb.__db_cache_size=679477248

Webdb.__java_pool_size=4194304

Webdb.__large_pool_size=4194304

Webdb.__oracle_base='/u00/app/oracle'#ORACLE_BASE set from environment

Webdb.__pga_aggregate_target=104857600

Webdb.__sga_target=943718400

Webdb.__shared_io_pool_size=0

Webdb.__shared_pool_size=243269632

Webdb.__streams_pool_size=0

* .audit_file_dest='/u00/app/oracle/admin/webdb/adump'

* .audit_trail='db'

* .compatible='11.2.0.0.0'

* .control_files='/u01/oradata/webdb/control01.ctl','/u00/app/oracle/fast_recovery_area/webdb/control02.ctl'

* .db_block_size=8192

* .db_domain='webex.com'

* .db_name='webdb'

* .db_recovery_file_dest='/u00/app/oracle/fast_recovery_area'

* .db_recovery_file_dest_size=2097152000

* .diagnostic_dest='/u00/app/oracle'

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

* .open_cursors=500

* .pga_aggregate_target=104857600

* .processes=1000

* .remote_login_passwordfile='EXCLUSIVE'

* .sessions=1105

* .sga_target=943718400

* .undo_tablespace='UNDOTBS1'

Step 5: generate passwords by manual orap

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwwebdb password=dbpassword force=y

Step 6: run some column scripts until the database is created.

(1) $ORACLE_HOME/bin/sqlplus / nolog

(2) sql > connect "SYS" / "password" as SYSDBA

/ / the creation of spfile is based on the pfile just given. Why create one? this is very useful. In case pfile goes wrong or is accidentally changed, spfile is of great use. Later, we will introduce the use of spfile and pfile in detail. If there is a default limit for spfile,oracle, spfile, read pfile only when spfile is not available. And spfile is binary, pfile is an ordinary file, readable and modifiable. Convenient and easy to be misoperated at the same time.

(3) sql > create spfile='$ORACLE_HOME/dbs/spfilewebdb.ora' from pfile='$ORACLE_HOME/dbs/initwebdb.ora'

(4) sql > startup nomount

(5) @ createdb.sql

/ / the createdb.sql script is as follows, including redolog size, location, and system files, etc., especially pay attention to the character set setting here, do not make a mistake, later should be very troublesome. With regard to the setting of this character set, the editor also has a history of blood and tears. I will introduce some strange techniques to you later.

CREATE DATABASE webdb

LOGFILE group 1 ('/ u01qoradata SIZE) SIZE 51200K

Group 2 ('/ u01qoradata SIZE) SIZE 51200K

Group 3 ('/ u01qoradata size) size 51200K

DATAFILE'/ u01 autoextend on next oradata maxsize unlimited system 01.dbf' SIZE 200m oradata maxsize unlimited

Sysaux datafile'/ u01 autoextend on next oradata size webdbCompact sysaux01.dbf' oradata 100m oradata 10m maxsize unlimited

Character set WE8ISO8859P1

EXTENT MANAGEMENT LOCAL

Undo tablespace UNDOTBS1

Datafile'/ u01Compact oradata Universe webdbUnides01.dbf'

Size 50M autoextend on next 5M maxsize UNLIMITED

Default temporary tablespace temp

Tempfile'/ u01qoradata Universe webdbUniverse temp01.dbf'

Size 50M autoextend on next 640K maxsize UNLIMITED

Startup force

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

ALTER SYSTEM SWITCH LOGFILE

(6) @ dic.sql

/ / run the data dictionary dic.sql as follows:

SET VERIFY OFF

Connect "SYS" / "password" as SYSDBA

Spool dic

@? / rdbms/admin/catalog.sql

@? / rdbms/admin/catproc.sql

@? / rdbms/admin/catblock.sql

@? / rdbms/admin/catoctk.sql

@? / rdbms/admin/owminst.plb

Spool off

Connect "SYSTEM" / "password"

Spool pub

@? / sqlplus/admin/pupbld.sql

@? / sqlplus/admin/help/hlpbld.sql helpus.sql

Spool off

(7) @ ctab.sql

/ / ctab.sql is to create tablespaces. The syntax format is as follows.

Connect "SYS" / "password" as SYSDBA

Spool ctab

DROP TABLESPACE BACKUPINCLUDING CONTENTS AND DATAFILES

CREATE TABLESPACE BACKUP DATAFILE

'/ u01 AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON

(8) @ cuser.sql

/ / cuser.sql is used to create users.

Sqlplus / as sysdba

CREATE USER MAINT

IDENTIFIED BY password

DEFAULT TABLESPACE BACKUP

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK

-- 2 Roles for MAINT

GRANT CONNECT TO MAINT

GRANT RESOURCE TO MAINT

ALTER USER MAINT DEFAULT ROLE ALL

-- 1 System Privilege for WBXMAINT

GRANT UNLIMITED TABLESPACE TO MAINT

=

Done

Based on the above steps, you can so easy and create an instance.

You also need to put Tianjian on the listener and tns files in order to connect to the network. When I will share the OracleDB connection method with you later, I will introduce it in detail.

Good night ~ ~ I hope I can help you. What can I do for you? Please leave a message.

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.