Example of manual database creation and Deletion

First, plan the directories where ORACLE_SID and DB_NAME are stored in data files, REDO logs, and control files. For example, in my experiment: ORACLE_SIDbys3, DB_NAME is bys3, and data files, REDO logs, and control files are all stored in u01oradatabys3. The experiment is as follows: Environment: OEL5.7, ORACLE11.2.0.4, vbox vm -- before database creation OR

First, plan the directories where ORACLE_SID and DB_NAME are stored in data files, REDO logs, and control files. For example, in my experiment: ORACLE_SID = bys3, DB_NAME is bys3, data files, REDO logs, and control files are all stored in/u01/oradata/bys3/. The experiment is as follows: Environment: OEL5.7, ORACLE 11.2.0.4, vbox vm-before database creation OR

First, plan the directories where ORACLE_SID and DB_NAME are stored in data files, REDO logs, and control files.
For example, in my experiment: ORACLE_SID = bys3, DB_NAME is bys3, data files, REDO logs, and control files are all stored in/u01/oradata/bys3/
The experiment is as follows:
Environment: OEL5.7, ORACLE 11.2.0.4, VBOX Virtual Machine-The ORACLE software should be installed before the database is created ..
1. Set ORACLE_SID. You can view it in. bash_profile. You need to change source. bash_profile to make it take effect when the session is not exited,
Or use export ORACLE_SID = bys3 directly in the command line.
Echo $ ORACLE_SID.
2. Check whether the variable parameters in. bash_profile are correct. cat. bash_profile.
[Oracle @ bys3 ~] $ Cat. bash_profile
#. Bash_profile
# Get the aliases and functions
If [-f ~ /. Bashrc]; then
.~ /. Bashrc
Fi
# User specific environment and startup programs
PATH = $ PATH: $ HOME/bin
RACLE_BASE =/u01
ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID = bys3
PATH = $ ORACLE_HOME/bin: $ ORACLE_HOME/OPatch: $ PATH: $ HOME/bin
Export PATH ORACLE_BASE ORACLE_HOME ORACLE_SID
Export NLS_LANG = AMERICAN
Export NLS_DATE_FORMAT = 'yyyy/mm/dd hh24: mi: ss'
Alias sqlplus = 'maid sqlplus'
Alias rman = 'rlwrap rman'
ORACLE_HOSTNAME = bys3.bys.com
Export ORACLE_HOSTNAME
3. Create a password file. Note that the format of the password file in WINDOWS and LINUX is different. Note: The folder name in WIN is different from that in LINUX. $ ORACLE_HOME/database LINUX under WIN: $ ORACLE_HOME \ dbs \
For example, if the Instance name is orcl, the password file format in WINDOWS is: orapworcl in PWDorcl. ora linux.
Orapwd file =/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwbys3 password = oracle entries = 3 force = y
4. Create a directory to adjust the location of the data files, traces, and other files you plan. You can write a script similar to the following for calling.
[Oracle @ bys3 ~] $ Cat createdir. sh
#! /Bin/sh
OLD_UMASK = 'umask'
Umask 0027
Mkdir-p/u01/admin/bys3/adump
Mkdir-p/u01/admin/bys3/dpdump
Mkdir-p/u01/admin/bys3/pfile
Mkdir-p/u01/app/oracle/product/11.2.0/dbhome_1/dbs
Mkdir-p/u01/archivelog
Mkdir-p/u01/javastoollogs/dbca/bys3
Mkdir-p/u01/fast_recovery_area
Mkdir-p/u01/fast_recovery_area/bys3
Mkdir-p/u01/oradata/bys3
Umask $ {OLD_UMASK}
5. Create the initialization parameter file-set only the following two parameters-db_name, controlfilevi/home/oracle/bys. ora
Db_name = bys3
Control_files = '/u01/oradata/bys3/control01.ctl', '/u01/oradata/bys3/control02.ctl'
6. Start the instance -- corresponding to step 1, using ORACLE_SIDsqlplus/as sysdba
Create a SPFILE file, which is stored in: $ ORACLE_HOME \ database \ LINUX by default under: WINDOWS: $ ORACLE_HOME \ dbs \
Note: to create a pfile from a spfile, you must have the SYSDBA permission. If you send this statement with the DBA permission, an error is returned.
Create spfile from pfile = '/home/oracle/bys. ora ';
Start instance: -- The default search order is: Search SPFILESID.ORA--SPFILE.ORA--PFILESID.ORA under the default directory $ ORACLE_HOME/database
Startup nomount;
7. Use the create database Command to CREATE a DATABASE. We recommend that you use the following command in SQLPLUS using the ZHS16GBK Character Set: -- You can also write a script file and then call this script in SQLPLUS. Pay attention to the directory location and permissions for creating data files and log files.
Create database bys3
User sys identified by oraclesys
User system identified by oraclesys
Logfile group 1 ('/u01/oradata/bys3/redo01.log') size 50 m,
Group 2 ('/u01/oradata/bys3/redo02.log') size 50 m,
Group 3 ('/u01/oradata/bys3/redo03.log') size 50 m
Maxlogfiles 20
Maxlogmembers 5
# Maxloghistory 200
Maxdatafiles 100
Character set AL32UTF8
National character set AL16UTF16
Extent management local
Datafile '/u01/oradata/bys3/system01.dbf' size 500 m reuse
Sysaux datafile '/u01/oradata/bys3/sysaux01.dbf' size 325 m reuse
Default temporary tablespace temp
Tempfile '/u01/oradata/bys3/temp01.dbf' size 20 m reuse
Undo tablespace undotbs1
Datafile '/u01/oradata/bys3/undotbs01.dbf' size 200 m reuse;

After the preceding statement is successfully executed, it is similar to database create complete. The database is set to OPEN at this time. You can query select status from v $ instance for verification.
Execute the following statement to add the USERS tablespace.
Create tablespace users datafile '/u01/oradata/bys3/user01.dbf' size 50 m;
Alter database default tablespace users; --- set the users tablespace to the system's default tablespace. If the user creates a table, the default tablespace is not specified.
8. After the above is created, run the following three scripts: I use the absolute path of the script here, or you can use @? /Rdbms/admin/catalog. SQL ;,
@ = Run? ==$ ORACLE_HOME mark (?) Is a SQL * Plus variable indicating the Oracle home directory
Catalog. SQL creates an SQL view,
Catproc. The Stored Procedure of SQL System creation...
Pupbld. The SQL script mainly creates the SQLPLUS_PRODUCT_PROFILE table and creates related views and synonyms on the table.
@/U01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catalog. SQL
@/U01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catproc. SQL
The following sentence needs to use the SYSTEM user login, can not use SYSDBA, will report an error, see the http://blog.csdn.net/q947817003/article/details/16117123
After the preceding two scripts are executed, run the conn system/oraclesys command to Log On As a sysetm user. You can run the show user command to verify.
@/U01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/pupbld. SQL
The creation is complete. The default mode is non-archive.
9. delete a database: You cannot delete a database created manually using DBCA. In this case, you need to run a command to delete the database. Use the following three commands:
SYS @ bys1> startup mount exclusive
SYS @ bys1> alter system enable restricted session;
SYS @ bys1> drop database;

The corresponding control files, log files, and data files of the database are automatically deleted, and spfiles are also deleted (archive logs are not deleted ). the content of the following official documents can be confirmed: Dropping a database involvesremoving its datafiles, redo log files, control files, and initialization parameter files. the drop database statement deletes all control files and all other database files listed in the control file.
TheDROP DATABASEStatement has no effect on archived log files, nor does it have any effect on copies or backups of the database. it is best to use RMAN to delete such files. if the database is on raw disks, the actual raw disk special files are not deleted.

10. ALERT logs for manual database creation

[Oracle @ bys3 ~] $ Cat alert_bys3.log
Thu Nov 14 14:17:19 2013
Starting ORACLE instance (normal)
* ********************** Large Pages Information ************* ******
Per process system memlock (soft) limit = 3418 MB

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
Total System Global Area size is 146 MB. For optimal performance,
Prior to the next instance restart:
1. Increase the number of unused large pages
At least 73 (page size 2048 KB, total size 146 MB) system wide
Get 100% of the System Global Area allocated with large pages
**************************************** ****************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 1
Shared memory segment for instance monitoring created
CELL communication is configured to use 0 interface (s ):
Cell ip affinity details:
NUMA status: non-NUMA system
Cellaffinity. ora status: N/
CELL communication will use 1 IP group (s ):
Grp 0:
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Autotune of undo retention is turned on.
IMODE = BR
ILAT = 22
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: bys3.bys.com
Release: 2.6.32-200.13.1.el5uek
Version: #1 SMP Wed Jul 27 20:21:26 EDT 2011
Machine: i686
Using parameter settings in server-side spfile/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilebys3.ora
System parameters with non-default values:---- Corresponds to the parameters in the previously set initialization parameter file
Control_files = "/u01/oradata/bys3/control01.ctl"
Control_files = "/u01/oradata/bys3/control02.ctl"
Db_name = "bys3"
Thu Nov 14 14:17:21 2013
PMON started with pid = 2, OS id = 4389
Thu Nov 14 14:17:21 2013
PSP0 started with pid = 3, OS id = 4393
Thu Nov 14 14:17:23 2013
VKTM started with pid = 4, OS id = 4397 at elevated priority
VKTM running at (1) millisec precision with DBRM quantum (100) MS
Thu Nov 14 14:17:23 2013
GEN0 started with pid = 5, OS id = 4403
Thu Nov 14 14:17:23 2013
DIAG started with pid = 6, OS id = 4407
Thu Nov 14 14:17:23 2013
DBRM started with pid = 7, OS id = 4411
Thu Nov 14 14:17:23 2013
DIA0 started with pid = 8, OS id = 4415
Thu Nov 14 14:17:23 2013
MMAN started with pid = 9, OS id = 4419
Thu Nov 14 14:17:24 2013
DBW0 started with pid = 10, OS id = 4423
Thu Nov 14 14:17:24 2013
LGWR started with pid = 11, OS id = 4427
Thu Nov 14 14:17:24 2013
CKPT started with pid = 12, OS id = 4431
Thu Nov 14 14:17:24 2013
SMON started with pid = 13, OS id = 4435
Thu Nov 14 14:17:24 2013
RECO started with pid = 14, OS id = 4439
Thu Nov 14 14:17:24 2013
MMON started with pid = 15, OS id = 4443
ORACLE_BASE not set in environment. It is recommended
That ORACLE_BASE be set in the environment
Thu Nov 14 14:17:25 2013
MMNL started with pid = 16, OS id = 4447
Thu Nov 14 14:23:19 2013
Create database bys3
User sys identified by * user system identified by * logfile group 1 ('/u01/oradata/bys3/redo01.log') size 50 m,
Group 2 ('/u01/oradata/bys3/redo02.log') size 50 m,
Group 3 ('/u01/oradata/bys3/redo03.log') size 50 m
Maxlogfiles 20
Maxlogmembers 5
# Maxloghistory 200
Maxdatafiles 100
Character set AL32UTF8
National character set AL16UTF16
Extent management local
Datafile '/u01/oradata/bys3/system01.dbf' size 500 m reuse
Sysaux datafile '/u01/oradata/bys3/sysaux01.dbf' size 325 m reuse
Default temporary tablespace temp
Tempfile '/u01/oradata/bys3/temp01.dbf' size 20 m reuse
Undo tablespace undotbs1
Datafile '/u01/oradata/bys3/undotbs01.dbf' size 200 m reuse
Database mounted in Exclusive Mode
Lost write protection disabled
Thu Nov 14 14:23:38 2013
Successful mount of redo thread 1, with mount id 3358374039
Thu Nov 14 14:23:38 2013
Database SCN compatibility initialized to 1
Assigning activation ID 3358374039 (0xc82cb897)
Thread 1 opened at log sequence 1
Current log #1 seq #1 mem #0:/u01/oradata/bys3/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Nov 14 14:23:38 2013
SMON: enabling cache recovery
Processing? /Rdbms/admin/dcore. bsq
Create tablespace SYSTEM datafile '/u01/oradata/bys3/system01.dbf' size 500 m reuse

Extent management local online
Thu Nov 14 14:24:23 2013
Completed: create tablespace SYSTEM datafile '/u01/oradata/bys3/system01.dbf' size 500 m reuse

Extent management local online
Create rollback segment SYSTEM tablespace SYSTEM
Storage (initial 50 K next 50 K)
UNDO_SEG_CRT: cocould not find usn tail
Completed: create rollback segment SYSTEM tablespace SYSTEM
Storage (initial 50 K next 50 K)
Undo initialization finished serial: 0 start: 12514064 end: 12514074 diff: 10 (0 seconds)
Processing? /Rdbms/admin/dsqlddl. bsq
Processing? /Rdbms/admin/dmanage. bsq
Create tablespace sysaux DATAFILE '/u01/oradata/bys3/sysaux01.dbf' size 325 m reuse

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
Thu Nov 14 14:24:54 2013
Completed: create tablespace sysaux DATAFILE '/u01/oradata/bys3/sysaux01.dbf' size 325 m reuse

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
Processing? /Rdbms/admin/dplsql. bsq
Processing? /Rdbms/admin/dtxnspc. bsq
Create undo tablespace UNDOTBS1 DATAFILE '/u01/oradata/bys3/undotbs01.dbf' size 200 m reuse
Thu Nov 14 14:25:12 2013
[2, 4450] Successfully onlined Undo Tablespace 2.
Completed: create undo tablespace UNDOTBS1 DATAFILE '/u01/oradata/bys3/undotbs01.dbf' size 200 m reuse
Create temporary tablespace temp tempfile '/u01/oradata/bys3/temp01.dbf' size 20 m reuse

Completed: create temporary tablespace temp tempfile '/u01/oradata/bys3/temp01.dbf' size 20 m reuse

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Processing? /Rdbms/admin/dfmap. bsq
Processing? /Rdbms/admin/denv. bsq
Processing? /Rdbms/admin/drac. bsq
Processing? /Rdbms/admin/dsec. bsq
Processing? /Rdbms/admin/doptim. bsq
Processing? /Rdbms/admin/dobj. bsq
Processing? /Rdbms/admin/djava. bsq
Processing? /Rdbms/admin/dpart. bsq
Processing? /Rdbms/admin/drep. bsq
Processing? /Rdbms/admin/daw. bsq
Processing? /Rdbms/admin/dsummgt. bsq
Processing? /Rdbms/admin/dtools. bsq
Processing? /Rdbms/admin/dexttab. bsq
Processing? /Rdbms/admin/ddm. bsq
Processing? /Rdbms/admin/dlmnr. bsq
Processing? /Rdbms/admin/ddst. bsq
Thu Nov 14 14:25:21 2013
SMON: enabling tx recovery
Starting background process SMCO
Thu Nov 14 14:25:22 2013
SMCO started with pid = 18, OS id = 4476
Thu Nov 14 14:25:24 2013
Replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Nov 14 14:25:25 2013
QMNC started with pid = 19, OS id = 4480
Completed: create database bys3
User sys identified by * user system identified by * logfile group 1 ('/u01/oradata/bys3/redo01.log') size 50 m,
Group 2 ('/u01/oradata/bys3/redo02.log') size 50 m,
Group 3 ('/u01/oradata/bys3/redo03.log') size 50 m
Maxlogfiles 20
Maxlogmembers 5
# Maxloghistory 200
Maxdatafiles 100
Character set AL32UTF8
National character set AL16UTF16
Extent management local
Datafile '/u01/oradata/bys3/system01.dbf' size 500 m reuse
Sysaux datafile '/u01/oradata/bys3/sysaux01.dbf' size 325 m reuse
Default temporary tablespace temp
Tempfile '/u01/oradata/bys3/temp01.dbf' size 20 m reuse
Undo tablespace undotbs1
Datafile '/u01/oradata/bys3/undotbs01.dbf' size 200 m reuse
Thu Nov 14 14:25:57 2013
Create tablespace users datafile '/u01/oradata/bys3/sysaux01.dbf' size 50 m
ORA-1537 signalled during: create tablespace users datafile '/u01/oradata/bys3/sysaux01.dbf' size 50m...
Thu Nov 14 14:26:25 2013
Create tablespace users datafile '/u01/oradata/bys3/user01.dbf' size 50 m
Completed: create tablespace users datafile '/u01/oradata/bys3/user01.dbf' size 50 m
Thu Nov 14 14:30:31 2013
Thread 1 advanced to log sequence 2 (LGWR switch)
Current log #2 seq #2 mem #0:/u01/oradata/bys3/redo02.log
Thu Nov 14 14:31:16 2013
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log #3 seq #3 mem #0:/u01/oradata/bys3/redo03.log
Thu Nov 14 14:33:22 2013
Thread 1 advanced to log sequence 4 (LGWR switch)
Current log #1 seq #4 mem #0:/u01/oradata/bys3/redo01.log
Thu Nov 14 14:33:52 2013
Thread 1 advanced to log sequence 5 (LGWR switch)
Current log #2 seq #5 mem #0:/u01/oradata/bys3/redo02.log
Thu Nov 14 14:34:11 2013
Create Relation IPS_PACKAGE_UNPACK_HISTORY
Thu Nov 14 14:34:28 2013
Thread 1 advanced to log sequence 6 (LGWR switch)
Current log #3 seq #6 mem #0:/u01/oradata/bys3/redo03.log
Thu Nov 14 14:34:43 2013
Thread 1 advanced to log sequence 7 (LGWR switch)
Current log #1 seq #7 mem #0:/u01/oradata/bys3/redo01.log
Thu Nov 14 14:35:04 2013
Thread 1 advanced to log sequence 8 (LGWR switch)
Current log #2 seq #8 mem #0:/u01/oradata/bys3/redo02.log
Thu Nov 14 14:36:16 2013
Thread 1 advanced to log sequence 9 (LGWR switch)
Current log #3 seq #9 mem #0:/u01/oradata/bys3/redo03.log
Thu Nov 14 14:37:43 2013
Thread 1 advanced to log sequence 10 (LGWR switch)
Current log #1 seq #10 mem #0:/u01/oradata/bys3/redo01.log
Thu Nov 14 14:38:55 2013
Thread 1 advanced to log sequence 11 (LGWR switch)
Current log #2 seq #11 mem #0:/u01/oradata/bys3/redo02.log
Thu Nov 14 14:40:10 2013
Thread 1 advanced to log sequence 12 (LGWR switch)
Current log #3 seq #12 mem #0:/u01/oradata/bys3/redo03.log
Thu Nov 14 14:40:34 2013
Thread 1 advanced to log sequence 13 (LGWR switch)
Current log #1 seq #13 mem #0:/u01/oradata/bys3/redo01.log
Thu Nov 14 14:40:46 2013
Starting background process CJQ0
Thu Nov 14 14:40:46 2013
CJQ0 started with pid = 32, OS id = 4598
Thu Nov 14 14:41:31 2013
Thread 1 advanced to log sequence 14 (LGWR switch)
Current log #2 seq #14 mem #0:/u01/oradata/bys3/redo02.log
Thu Nov 14 14:41:40 2013
Server component id = CATPROC: timestamp = 14:41:40
Thu Nov 14 15:06:30 2013
Spfile/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilebys3.ora is in old pre-11 format and compatible> = 11.0.0; converting to new H. A.R. D. compliant format.