Manual Oracle database creation

Manual Oracle database creation

Read navigation
Database Version:
1. Set environment variables and create directories
2. Create an initialization parameter file
3. Start the instance to nomount to create a database
4. run scripts to create dictionaries, views, packages, roles, permissions, and synonyms.
5. Check database information
6. Enable archiving logs

Database Version:

SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production

1. Set environment variables and create directories

$ Export ORACLE_HOME =/u01/app/product/11.2.0/db_1
$ Export ORACLE_SID = mandb
$ Mkdir-p/disk2/oradata/mandb/datafile
$ Mkdir-p/disk2/oradata/mandb/logfile

2. Create an initialization parameter file

$ Vim/disk2/oradata/mandb/datafile/mandb. ora
Db_name = 'dbb' # database name
Instance_name = 'mandb'
Control_files = '/disk2/oradata/mandb/datafile/mandb_control01.ctl'
Memory_target = 500 m
DB _ block_size = 32768
Db_create_file_dest = '/disk2/oradata/mandb/datafile'

Db_block_size specifies the default block size of the created database. If this parameter is not specified, the default block size is 8192. Note: The default block size cannot be modified after the database is created.
When db_create_file_dest does not specify the data file path, the data file is created by default.

3. Start the instance to nomount to create a database

$ Sqlplus/as sysdba
SQL> create spfile from pfile = '/disk2/oradata/mandb/datafile/mandb. ora ';
SQL> startup nomount
SQL> create database mandb
2 user sys identified by oracle
3 user system identified by oracle
4 logfile group 1'/disk2/oradata/mandb/logfile/redo01.log 'size 50 m blocksize 512,
5 group 2'/disk2/oradata/mandb/logfile/redo02.log 'size 50 m blocksize 512,
6 group 3'/disk2/oradata/mandb/logfile/redo03.log' size 50 m blocksize 512
7. maxlogfiles 10
8 maxlogmembers 5
9 maxloghistory 5
10 maxdatafiles 200
11 character set al32UTF8
12 National character set al16UTF16
13 extent management local
14 datafile '/disk2/oradata/mandb/datafile/system01.dbf' size 500 m reuse
15 sysaux
16 datafile '/disk2/oradata/mandb/datafile/sysaux01.dbf' size 500 m reuse
17 default tablespace users
18 datafile '/disk2/oradata/mandb/datafile/user01.dbf' size 100 m reuse
19 default temporary tablespace tempfile
20 tempfile '/disk2/oradata/mandb/datafile/temp01.dbf' size 20 m autoextend on
21 undo tablespace undotbs01
22 datafile '/disk2/oradata/mandb/datafile/undotbs01.dbf' size 20 m autoextend on
23 maxsize unlimited;

The block size of the log file is 512 bytes. You can set 1024 bytes and 4096 bytes to 16 kb;
Note: if an error occurs or an unexpected stop occurs during the process, delete files that do not specify a reuse or cannot specify a reuse (such as control files, tempfile, and undofile), and then run:

Rm/disk2/oradata/mandb/datafile/mandb. ora
Rm/disk2/oradata/mandb/datafile/undotbs01.dbf
Rm/disk2/oradata/mandb/datafile/undotbs01.dbf

4. run scripts to create dictionaries, views, packages, roles, permissions, and synonyms.

SQL> @? /Rdbms/admin/catalog. SQL #### dictionary, view, public synonym, role, permission
SQL> @? /Rdbms/admin/catproc. SQL #### pl/SQL packages required by the system

The official document says you need to log on to the system user to execute @? The/sqlplus/admin/pupbld. SQL script is not found in the installed version 11.2.0.1.0. The database has been created;

5. Check database information

SQL> column name format a10
SQL> select DBID, name, DB_UNIQUE_NAME, CURRENT_SCN, open_mode from v $ database; -- database information
Dbid name DB_UNIQUE_NAME CURRENT_SCN OPEN_MODE
---------------------------------------------------------------------------------
3530583721 MANDB 385901 READ WRITE
SQL> column name format a50
SQL> select file #, name, block_size from v $ datafile; -- Data file Information
FILE # NAME BLOCK_SIZE
----------------------------------------------------------------------
1/disk2/oradata/mandb/datafile/system01.dbf 32768
2/disk2/oradata/mandb/datafile/sysaux01.dbf 32768
3/disk2/oradata/mandb/datafile/undotbs01.dbf 32768
4/disk2/oradata/mandb/datafile/user01.dbf 32768
SQL> column member format a40
SQL> select * from v $ Logfile; -- Log File Information
GROUP # status type member is _
-------------------------------------------------------------------
1 ONLINE/disk2/oradata/mandb/logfile/redo01.log NO
2 ONLINE/disk2/oradata/mandb/logfile/redo02.log NO
3 ONLINE/disk2/oradata/mandb/logfile/redo03.log NO
SQL> column name format a60
SQL> select * from v $ controlfile; -- Control File Information
Status name is _ BLOCK_SIZE FILE_SIZE_BLKS
----------------------------------------------------------------------------------------------
/Disk2/oradata/mandb/datafile/mandb_control01.ctl NO 16384 668
SQL> column window_name format a17
SQL> select WINDOW_NAME, WINDOW_NEXT_TIME, WINDOW_ACTIVE, AUTOTASK_STATUS from DBA_AUTOTASK_WINDOW_CLIENTS; -- window information
WINDOW_NAME WINDOW_NEXT_TIME WINDO AUTOTASK
---------------------------------------------------------------------------------------------------------
WEDNESDAY_WINDOW 08-APR-15 10.00.00.000000 PM PRC FALSE ENABLED
FRIDAY_WINDOW 10-APR-15 10.00.00.000000 PM PRC FALSE ENABLED
TUESDAY_WINDOW 07-APR-15 10.00.00.000000 PM PRC FALSE ENABLED
SATURDAY_WINDOW 11-APR-15 06.00.00.000000 AM PRC FALSE ENABLED
SUNDAY_WINDOW 05-APR-15 06.00.00.000000 AM PRC FALSE ENABLED
MONDAY_WINDOW 06-APR-15 10.00.00.000000 PM PRC FALSE ENABLED
THURSDAY_WINDOW 09-APR-15 10.00.00.000000 PM PRC FALSE ENABLED

Check for object errors:

SQL> select COUNT (*) "OBJECTS WITH ERRORS" from obj $ where status = 3;

If a package or java code error occurs, you can use utlrp. SQL provided by oracle to re-compile the code.

SQL> @? /Rdbms/admin/utlrp. SQL

6. Enable archiving logs

$ Sqlplus/as sysdba <EOF
Alter system checkpoint;
Alter system checkpoint;
Alter system checkpoint;
Shutdown immediate;
Startup mount
Alter database archivelog;
Alter database open;
EOF

-- The end