手动创建数据库及创建过程遇到的错误（踩到的坑）
手动创建数据库及创建过程遇到的错误（踩到的坑）
##手动创建数据库的方法
1.定义相关环境变量
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE=/u01/app/oracle
$ echo $PATH
$ export PATH=$PATH:$ORACLE_HOME/OPatch/bin
2. 创建Password file，供远程Password认证连接
$ orapwd FILE=orapworcl ENTRIES=30
3.修改初期化参数
cd $ORACLE_HOME/dbs
cp init.ora initorcl.ora
vi initorcl.ora
initorcl.ora内容：
db_name='ORCL'
memory_target=800M
processes = 150
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=10G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ('/u01/app/oracle/oradata/orcl/ora_control01', '/u01/app/oracle/oradata/orcl/ora_control02')
compatible ='11.2.0'
4.修改CREATE DATABASE文
拷贝在线文档的CREATE DATABASE文模板
Home / Database / Oracle Database Online Documentation 11g Release 2 (11.2) / Database Administration
Database Administrator's Guide
https://docs.oracle.com/cd/E11882_01/server.112/e25494/create.htm#ADMIN11085
>Specifying CREATE DATABASE Statement Clauses
修改成以下内容：
CREATE DATABASE orcl
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01a.log') SIZE 100M ,
GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02a.log') SIZE 100M ,
GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03a.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf' SIZE 3250M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 3250M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE UNDOTBS1
DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
5.
通过initorcl.ora创建spfile，并启动数据库Nomount状态，创建数据库。
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 8 11:04:03 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1348160 bytes
Variable Size 490737088 bytes
Database Buffers 339738624 bytes
Redo Buffers 5152768 bytes
SQL> CREATE DATABASE orcl
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01a.log') SIZE 100M ,
GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02a.log') SIZE 100M ,
GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03a.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 325M REUSE
11 12 DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE UNDOTBS1
DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
13 14 15 16 17 18 19 20 21 22 23 24
Database created.
6. 执行相关脚本
conn / as sysdba
----创建数据字典视图和编译
@?/rdbms/admin/catalog.sql
----创建PL/SQL相关功能，还创建几个PL/SQL包用于扩展RDBMS功能
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
conn system/oracle
----创建SQLPLUS相关内容
@?/sqlplus/admin/pupbld.sql
7.配置emca
$ emca -config dbcontrol db
$ emca -config dbcontrol db -repos create
$ emca -config dbcontrol db -repos recreate --》建议使用，即使没有创建过，也会重新创建。
$ emca -config dbcontrol db -repos recreate
STARTED EMCA at Jan 8, 2018 1:00:49 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/dbhome_1 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Jan 8, 2018 1:35:44 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://XXXXX.XXXX:1158/em <<<<<<<<<<<
Jan 8, 2018 1:35:49 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. T
he encryption key has been placed in the file: /u01/app/oracle/product/11.2.0/dbhome_1/XXXXX.us.oracle.com_SID1/sysman/config/emkey.ora.
Ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 8, 2018 1:35:49 PM
8.archivelog mode的变更
SYS@orcl YY-MM-DD> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Current log sequence 17
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SYS@orcl YY-MM-DD> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
9. 设定UNDO表空间的大小
SYS@orcl YY-MM-DD> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@orcl YY-MM-DD> alter system set undo_retention=3600;
SYS@orcl YY-MM-DD> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS1
Refer：
https://docs.oracle.com/cd/E11882_01/server.112/e25494/undo.htm#ADMIN11466
>Sizing a Fixed-Size Undo Tablespace
select file#, bytes, name from v$datafile where name like '%undo%';
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf' RESIZE 400M;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf' AUTOEXTEND ON;
##创建Tips
■Tips1:
修改glogin.sql文件
$ORACLE_HOME/sqlplus/admin/glogin.sql
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "
修改前的效果：
SQL>
修改后的效果：
SYS@orcl YY-MM-DD>
参考：
Home / Database / Oracle Database Online Documentation 11g Release 2 (11.2) / Database Administration
SQL*Plus® User's Guide and Reference
https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_two.htm#SQPUG012
>2 Configuring SQL*Plus
>Default Site Profile Script
■Tips2:
其他简单的创建方法：
1.修改initorcl.ora
db_name='ORCL'
2.使用CREATE DATABASE orcl命令;
3.创建临时表空间。
##创建过程遇到的错误（踩到的坑）：
0.未设定环境变量
$ sqlplus /nolog
-bash: sqlplus: command not found
$ export ORACLE_SID=orcl
[dbs]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/
[dbs]$ sqlplus / as sysdba
-bash: sqlplus: command not found
$ echo $PATH
/home/oracle/bin:/usr/java/jdk1.5.0_16/bin:/bin:/home/oracle/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
[dbs]$ export PATH=$ORACLE_HOME/bin:$PATH
1.未修改默认的初始化参数文件中的<ORACLE_BASE>
db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=10G
diagnostic_dest='<ORACLE_BASE>'
SQL> startup nomount
ORA-48108: invalid value given for the diagnostic_dest init.ora parameter
ORA-48140: the specified ADR Base directory does not exist [/u01/app/oracle/product/11.2.0/dbhome_1/dbs/<ORACLE_BASE>]
ORA-48187: specified directory does not exist
Linux Error: 2: No such file or directory
Additional information: 1
2. flash_recovery_area未修改
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=10G
diagnostic_dest='/u01/app/oracle/'
SQL> startup nomount
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux Error: 2: No such file or directory
$ cd /u01/app/oracle/flash_recovery_area
-bash: cd: /u01/app/oracle/flash_recovery_area: No such file or directory
3.未创建相关的路径
CREATE DATABASE orcl
*
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/orcl/redo01a.log'
- file cannot be created
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Additional information: 1
Process ID: 10398
Session ID: 125 Serial number: 3
4.UNDO TABLESPACE名指定错误。
初期化参数是UNDOTBS1，但是Create database文却是UNDOTBS。
UNDO TABLESPACE UNDOTBS
DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
CREATE DATABASE orcl
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 5
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Process ID: 10958
Session ID: 125 Serial number: 3
5. 一次不成功的话，需删除以前生成的文件
CREATE DATABASE orcl
*
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/orcl/ora_control01'
ORA-27038: created file already exists
Additional information: 1
6.DOMAIN 没有设定，和tnsnam.ora ,导致TNS 错误
SQL> show parameter domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string us.oracle.com
SQL>
cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SID1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SID1.us.oracle.com)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.us.oracle.com)
)
)
$ sqlplus system/oracle@ORCL
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 8 11:55:32 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
修改db_domain：
SQL> alter system set db_domain=us.oracle.com;
SQL> show parameter domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string us.oracle.com
7.连接空实例问题
[oracle@XXXXX-orcl ~]$ export ORACLE_SID=orcl
[oracle@XXXXX-orcl ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/
[oracle@XXXXX-orcl ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@XXXXX-orcl ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@XXXXX-orcl ~]$
[oracle@XXXXX-orcl ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 8 12:16:05 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.★命名是启动的数据库，连接时却是idle的？？？
SQL> exit
当仅仅设定ORACLE_SID=orcl时，正常连接，非Idle。
export ORACLE_SID=orcl
[oracle@XXXXX-orcl ~]$ sqlplus / as sysdba
是由于环境变量ORACLE_HOME重复设定？？
[oracle@XXXXX-orcl admin]$ export ORACLE_SID=orcl
[oracle@XXXXX-orcl admin]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/
[oracle@XXXXX-orcl admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 8 12:30:51 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit
[oracle@XXXXX-orcl admin]$ env
...
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
G_BROKEN_FILENAMES=1
_=/bin/env
根据上面可以知道，由于设定ORACLE_HOME时多了一个"/"。
删除后，正常连接。
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 8 12:41:27 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
8.emca创建时，发生ORA-01000
Jan 8, 2018 1:04:28 PM oracle.sysman.emcp.EMReposConfig createRepository
WARNING: ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.DBMS_STATS", line 27377
ORA-06512: at "SYS.DBMS_STATS", line 27402
ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 1093
ORA-06512: at "SYS.DBMS_REGISTRY", line 578
ORA-06512: at line 1
修改open_cursors值后，重新运行
SYS@orcl YY-MM-DD> show parameter cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300
session_cached_cursors integer 50
SYS@orcl YY-MM-DD> alter system set open_cursors=500;
System altered.
$ emca -config dbcontrol db -repos create
STARTED EMCA at Jan 8, 2018 1:08:03 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/dbhome_1 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/dbhome_1
Local hostname ................ XXXXX.us.oracle.com
Listener ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/dbhome_1
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Jan 8, 2018 1:08:29 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/ORCL/emca_2018_01_08_13_08_03.log.
Jan 8, 2018 1:08:30 PM oracle.sysman.emcp.DatabaseChecks performReposChecks
SEVERE: Dbcontrol Repository already exists. Fix the error(s) and run EM Configuration Assistant again in standalone mode.
[oracle@XXXXX-orcl admin]$ .
EM创建再次执行时，发生SEVERE: Dbcontrol Repository already exists错误，
使用recreate命令。
$emca -config dbcontrol db -repos recreate
9.由于数据库关闭,报ORA-01034: ORACLE not available错误
$ emca -config dbcontrol db -repos recreate
STARTED EMCA at Jan 8, 2018 1:18:30 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Jan 8, 2018 1:18:35 PM oracle.sysman.emcp.DatabaseChecks checkDbAvailabilityImpl
WARNING: ORA-01034: ORACLE not available
Jan 8, 2018 1:18:35 PM oracle.sysman.emcp.DatabaseChecks throwDBUnavailableException
SEVERE:
Database instance is unavailable. Fix the ORA error thrown and run EM Configuration Assistant again.
Some of the possible reasons may be :
1) Database may not be up.
2) Database is started setting environment variable ORACLE_HOME with trailing '/'. Reset ORACLE_HOME and bounce the database.
For eg. Database is started setting environment variable ORACLE_HOME=/scratch/db/ . Reset ORACLE_HOME=/scratch/db and bounce the database.
10.扩大SYSTEM表空间的大小。
INFO: Creating the EM repository (this may take a while) ...
Jan 8, 2018 1:21:45 PM oracle.sysman.emcp.EMReposConfig createRepository
WARNING: ORA-00604: error occurred at recursive SQL level 1
ORA-01654: unable to extend index SYS.I_IDL_SB41 by 8 in tablespace SYSTEM
Jan 8, 2018 1:21:45 PM oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Error creating the repository
Jan 8, 2018 1:21:45 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/ORCL/emca_repos_create_<date>.log for more details.
Jan 8, 2018 1:21:45 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error creating the repository
Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/ORCL/emca_2018_01_08_13_19_31.log for more details.
Could not complete the configuration. Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/ORCL/emca_2018_01_08_13_19_31.log for more details.