【数据库】ORACLE手工建库主要介绍使用脚本和命令行创建ORACLE单点数据库的基本步骤，包括创建non-CDB数据库

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第6天，点击查看活动详情。

实验1、Creating a Database with the CREATE DATABASE Statement-non CDB

（清理环境变量，有时不需要）
unset ORACLE_UNQNAME
unset ORACLE_SID
unset ORACLE_BASE
unset ORACLE_HOSTNAME
unset ORACLE_HOME

1、编辑环境变量
[oracle@odd dbs]$ vim ~/.PROD3_env
[oracle@odd dbs]$ source ~/.PROD3_env
export ORACLE_SID=PROD3
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
export PATH=$ORACLE_HOME/bin:$PATH

2、创建目录 mkdir -p /u01/app/oracle/oradata/PROD3
mkdir -p /u01/app/oracle/admin/PROD3/adump
mkdir -p /u01/app/oracle/fast_recovery_area/PROD3

3、创建密码文件
cd /u01/app/oracle/product/12.1.0.2/db_1/dbs
orapwd file=orapwPROD3 password=oracle

4、创建spfile
（从/u01/app/oracle/product/12.1.0.2/db_1/dbs/init.ora复制修改）
vim initPROD3.ora

db_name='PROD3'
memory_target=500M
processes = 150
audit_file_dest='/u01/app/oracle/admin/PROD3/adump'
audit_trail ='db'
db_block_size=8192
db_domain='us.oracle.com'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=PROD3XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u01/app/oracle/oradata/PROD3/control01.ctl, /u01/app/oracle/oradata/PROD3/control02.ctl)
compatible ='12.0.0'

5、创建spfile，启动数据库
[oracle@odd dbs]$ sqlplus / as sysdba #没有连接到数据库
SQL> create spfile from pfile;

SQL> startup nomount;

ORACLE instance started.  
Total System Global Area  524288000 bytes
Fixed Size            2926320 bytes
Variable Size          394266896 bytes
Database Buffers      121634816 bytes
Redo Buffers            5459968 bytes

6、编辑create database脚本

CREATE DATABASE PROD3
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY oracle
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD3/redo01a.log','/u01/app/oracle/oradata/PROD3/redo01b.log') SIZE 100M BLOCKSIZE 512,
   GROUP 2 ('/u01/app/oracle/oradata/PROD3/redo02a.log','/u01/app/oracle/oradata/PROD3/redo02b.log') SIZE 100M BLOCKSIZE 512,
   GROUP 3 ('/u01/app/oracle/oradata/PROD3/redo03a.log','/u01/app/oracle/oradata/PROD3/redo03b.log') SIZE 100M BLOCKSIZE 512
   MAXLOGHISTORY 1
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 1024
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/PROD3/system01.dbf'
     SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD3/sysaux01.dbf'
     SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/PROD3/users01.dbf'
   SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/PROD3/temp01.dbf'
      SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
   UNDO TABLESPACE undotbs1
      DATAFILE '/u01/app/oracle/oradata/PROD3/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
   USER_DATA TABLESPACE usertbs
      DATAFILE '/u01/app/oracle/oradata/PROD3/usertbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

7、执行脚本 [oracle@odd dbs]$ sqlplus / as sysdba
SQL> @/home/oracle/createDB.sql
Database created.

报错
SQL> @/home/oracle/createDB.sql
CREATE DATABASE PROD3
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-00301: error in adding log file '/u01/app/oracle/oradata/PROD3/redo01a.log'
- file cannot be created
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
Process ID: 15483
Session ID: 1 Serial number: 6694
  

文件路径写错了：应该是/u01/app/oracle/oradata/PROD3/
--------------------------------------------------------------------------------
SQL> @/home/oracle/createDB.sql
CREATE DATABASE PROD3
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u01/app/oracle/oradata/PROD3/control01.ctl'
ORA-27038: created file already exists
Additional information: 1

删除/u01/app/oracle/oradata/PROD3/下的文件

8、执行一些其他脚本
脚本所在位置：/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin
SQL>@?/rdbms/admin/catalog.sql
SQL>@?/rdbms/admin/catproc.sql
SQL>@?/rdbms/admin/utlrp.sql

切换system用户，sqlplus system/oracle as sysdba
SQL>@?/sqlplus/admin/pupbld.sql

报错
DATE_VALUE FROM PRODUCT_USER_PROFILE
ERROR at line 3:
ORA-00980: synonym translation is no longer valid
DROP TABLE PRODUCT_USER_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
                                         *
ERROR at line 1:
ORA-01430: column being added already exists in table
CREATE TABLE SQLPLUS_PRODUCT_PROFILE
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object
DROP TABLE PRODUCT_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist
DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist

9、开启归档，开启数据库
SQL> shutdown immediate;
SQL> startup mount;
SQL>alter database archivelog;
SQL>alter database open;

10、备份
>rman target /
RMAN>backup database plus archivelog;

实验2：creating database with script and OMF

1、vim initPROD3.ora
一定要设置DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'

# <ORACLE_HOME>/dbs/initPROD3.ora
db_name='PROD3'
memory_target=400M
processes = 150
db_block_size=8192
db_domain='us.oracle.com'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=PROD3XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ('/u01/app/oracle/oradata/PROD3/control01.ctl',
'/u01/app/oracle/oradata/PROD3/control02.ctl')
compatible ='12.0.0'
DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'

2、createDB.sql的内容有所不同

CREATE DATABASE PROD3
     USER SYS IDENTIFIED BY oracle
     USER SYSTEM IDENTIFIED BY oracle
     EXTENT MANAGEMENT LOCAL
     DEFAULT TEMPORARY TABLESPACE temp;
     UNDO TABLESPACE undotbs1
     DEFAULT TABASESPACE users;

其他步骤与non CDB相同

实验3:creating database with script CDB

1、编辑环境变量
[oracle@odd PROD3]$vim /etc/oratab
CDB3:/u01/app/oracle/product/12.1.0.2/db_1:N
[oracle@odd PROD3]$ . oraenv
ORACLE_SID = [PROD3] ? CDB3
The Oracle base remains unchanged with value /u01/app/oracle

2、创建目录 mkdir -p /u01/app/oracle/oradata/CDB3
mkdir -p /u01/app/oracle/oradata/CDB3/pdbseed
mkdir -p /u01/app/oracle/admin/CDB3/adump
mkdir -p /u01/app/oracle/fast_recovery_area/CDB3

3、编辑pfile
cd /u01/app/oracle/product/12.1.0.2/db_1/dbs
vim initCDB3.ora
(db_recovery_file_dest_size改大了一点，防止不够)

db_name='CDB3'
memory_target=500M
processes = 150
audit_file_dest='/u01/app/oracle/admin/CDB3/adump'
audit_trail ='db'
db_block_size=8192
db_domain='[us.oracle.com](http://us.oracle.com/)'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=4G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=CDB3XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u01/app/oracle/oradata/CDB3/control01.ctl, /u01/app/oracle/oradata/CDB3/control02.ctl)
compatible ='12.0.0'
ENABLE_PLUGGABLE_DATABASE=TRUE

ENABLE_PLUGGABLE_DATABASE=TRUE

4、创建spfile，启动数据库
[oracle@odd dbs]$ sqlplus / as sysdba #没有连接到数据库
SQL> create spfile from pfile;
SQL> startup nomount;

5、编辑数据库创建语句
createCDB.sql的内容

CREATE DATABASE CDB3
    USER SYS IDENTIFIED BY oracle
    USER SYSTEM IDENTIFIED BY oracle
    LOGFILE GROUP 1 ('/u01/app/oracle/oradata/CDB3/redo01a.log','/u01/app/oracle/oradata/CDB3/redo01b.log')
        SIZE 100M BLOCKSIZE 512,
            GROUP 2 ('/u01/app/oracle/oradata/CDB3/redo02a.log','/u01/app/oracle/oradata/CDB3/redo02b.log')
        SIZE 100M BLOCKSIZE 512,
            GROUP 3 ('/u01/app/oracle/oradata/CDB3/redo03a.log','/u01/app/oracle/oradata/CDB3/redo03b.log')
        SIZE 100M BLOCKSIZE 512
    MAXLOGHISTORY 1
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    CHARACTER SET AL32UTF8
    NATIONAL CHARACTER SET AL16UTF16
    EXTENT MANAGEMENT LOCAL
    DATAFILE '/u01/app/oracle/oradata/CDB3/system01.dbf'
        SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
    SYSAUX DATAFILE '/u01/app/oracle/oradata/CDB3/sysaux01.dbf'
        SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
    DEFAULT TABLESPACE deftbs
    DATAFILE '/u01/app/oracle/oradata/CDB3/deftbs01.dbf'
        SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE tempts1
    TEMPFILE '/u01/app/oracle/oradata/CDB3/temp01.dbf'
        SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
    UNDO TABLESPACE undotbs1
    DATAFILE '/u01/app/oracle/oradata/CDB3/undotbs01.dbf'
        SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
 ENABLE PLUGGABLE DATABASE
 SEED
 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/CDB3/',
 '/u01/app/oracle/oradata/CDB3/pdbseed/')
 SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
 SYSAUX DATAFILES SIZE 100M
 USER_DATA TABLESPACE usertbs
 DATAFILE '/u01/app/oracle/oradata/CDB3/pdbseed/usertbs01.dbf'
 SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

6、执行创建sql > sqlplus / as sysdba
SQL> @/home/oracle/createCDB.sql
Database created

ORA-00202: control file: '/u01/app/oracle/oradata/PROD3/control01.ctl'

ORA-27038: created file already exists
报错清理
[oracle@odd CDB3]$ rm -rf /u01/app/oracle/oradata/CDB3/*
[oracle@odd CDB3]$ mkdir -p /u01/app/oracle/oradata/CDB3/pdbseed

7、运行脚本
>export PATH=$ORACLE_HOME/perl/bin/:$PATH
>which perl
(这个执行前要先配置好prel)
SQL>@?/rdbms/admin/catcdb.sql

8、开启归档，开启数据库
SQL> shutdown immediate;
SQL> startup mount;
SQL>alter database archivelog;
SQL>alter database open;

9、备份
>rman target /
RMAN>backup database plus archivelog;

PS.其他小知识 QQ截图20210725160112.png QQ截图20210725160101.png