Manual Oracle database creation in Linux

Today I found that the character set of an Oracle Test Library is incorrect, the original character set is UTF-8, the correct character set should be ZHS16GBK, because the UTF-8 is the superset of ZHS16GBK,

Today I found that the character set of an Oracle Test Library is incorrect, the original character set is UTF-8, the correct character set should be ZHS16GBK, because the UTF-8 is the superset of ZHS16GBK,

Today I found that an Oracle Test Library character set is incorrect, the original character set is UTF-8, the correct character set should be ZHS16GBK, because the UTF-8 is the superset of ZHS16GBK, can not be modified, can only re-build the database, fortunately, no data exists in the test database.

Refer:

Manually create an Oracle database instance

Create an Oracle database manually

Create an Oracle 10 Gb database manually in Linux

Although DBCA can also be used to create databases, I personally prefer to create databases manually. The main steps are as follows (database version 11.2 ):

1. Set ORACLE_SID

The default ORACLE_SID is orcl. I personally strongly recommend a unique and meaningful name, such as ** testdb, ** proddb, and so on. Execute the following command under the Oracle user:

Export ORACLE_SID = lxdbtest

2. Rebuild PFILE

In the $ ORACLE_HOME/dbs directory, recreate pfile (initORACLE_SID.ora, in this example, initlxdbtest. ora). You do not need to set other parameters except the following three parameters (default value ):

Db_name = lxdbtest
Control_files = '/home/oracle/app/oradata/lxdbtest/control01.ctl', '/home/oracle/app/oradata/lxdbtest/control02.ctl ', '/home/oracle/app/oradata/lxdbtest/control03.ctl'
Memory_target = 2G

For the sake of security, the control file must be specified in multiple copies. In the production database, they should be separated and placed on different physical disks. This is because the test database is used, so it is placed on the same disk.

3. Start the instance to the nomount status

With pfile, you can start the instance to the nomount status:

$ Sqlplus/as sysdba

SQL * Plus: Release 11.2.0.1.0 Production on Thu Jul 25 21:01:16 2013

Copyright (c) 1982,200 9, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1224737640 bytes
Database Buffers 905969664 bytes
Redo Buffers 4964352 bytes
SQL>

4. Execute the create database statement

Create database lxdbtest
Logfile group 1 ('/home/oracle/app/oradata/lxdbtest/redo01a. log','/data/oradata/lxdbtest/redo01b. log') SIZE 100 m blocksize 512,
GROUP 2 ('/home/oracle/app/oradata/lxdbtest/redo02a. log','/data/oradata/lxdbtest/redo02b. log') SIZE 100 m blocksize 512,
GROUP 3 ('/home/oracle/app/oradata/lxdbtest/redo03a. log','/data/oradata/lxdbtest/redo03b. log') SIZE 100 m blocksize 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 600
Character set ZHS16GBK
National character set AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/home/oracle/app/oradata/lxdbtest/system01.dbf' SIZE 2048 M REUSE AUTOEXTEND OFF
Sysaux datafile '/home/oracle/app/oradata/lxdbtest/sysaux01.dbf' SIZE 2048 M REUSE AUTOEXTEND OFF
Default tablespace users
DATAFILE '/home/oracle/app/oradata/lxdbtest/users01.dbf'
SIZE 2048 M REUSE AUTOEXTEND OFF
Default temporary tablespace temptbs
TEMPFILE '/home/oracle/app/oradata/lxdbtest/temp01.dbf'
SIZE 2048 M REUSE AUTOEXTEND OFF
Undo tablespace undotbs
DATAFILE '/home/oracle/app/oradata/lxdbtest/undotbs01.dbf'
SIZE 2048 m reuse autoextend off;

Pay attention to the following points:

1) logs in each log file group should be stored on different physical disks. In case of a disk corruption, data will not be lost.

2) Select the appropriate character set as needed

3) five basic tablespaces are created: SYSTEM, SYSAUX, default tablespace, default temporary tablespace, and default UNDO tablespace.

5. Create the tablespace required by the application as needed

For data in key services, you should create a separate tablespace for it (it is best to separate the table and index in the tablespace)

SQL> create tablespace p95169tbs
2 datafile '/home/oracle/app/oradata/lxdbtest/p95169tbs01. dbf' size 10 Gb,
3 '/home/oracle/app/oradata/lxdbtest/p95169tbs02. dbf' size 10 Gb;

Tablespace created.

6. Build a data dictionary View

@? /Rdbms/admin/catalog. SQL
@? /Rdbms/admin/catproc. SQL
@? /Sqlplus/admin/pupbld. SQL

7. Modify the password of system and sys

Alter user sys identified ***;
Alter user system identified ***;

8. Create a SPFILE

SQL> create spfile from pfile;

File created.

After the database is restarted, pfile will be replaced with spfile.

Finally, let's talk about how to delete a database. Before deleting a database, the database must be in the mount and restricted session statuses, as shown below:

SQL> startup mount

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.

After the database is deleted successfully, all online log files and data files are deleted, but archive logs and backup files are not deleted.