oracle11G手动建库

原创

wx63159e54cdd9a 2023-02-24 09:33:20 ©著作权

文章标签 手动建库 oracle11g手动建库 SQL oracle sql 文章分类 HarmonyOS 后端开发

©著作权归作者所有：来自51CTO博客作者wx63159e54cdd9a的原创作品，请联系作者获取转载授权，否则将追究法律责任

oracle11g手动建库

1. 设置环境变量
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
export ORACLE_SID=ogg02
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export PATH

2. 创建ogg02目录
[oracle@ogg04 ~]$ pwd
/u01/app/oracle/diag/rdbms/ogg02/ogg02

rm -rf $ORACLE_BASE/admin/$ORACLE_SID
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/scripts
chmod -R 750 $ORACLE_BASE/admin

rm -rf $ORACLE_BASE/diag/rdbms/$ORACLE_SID
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/alert
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/cdump
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/hm
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/incident
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/incpkg
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/ir
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/lck
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/metadata
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/stage
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/sweep
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
chmod -R 750 $ORACLE_BASE/diag/rdbms/$ORACLE_SID

3. 创建密码文件
[oracle@ogg04 dbs]$ pwd
/u01/app/oracle/product/11.2.0.3/db_1/dbs
[oracle@ogg04 dbs]$ orapwd file=orapwogg02 password=oracle entries=5

4.建初始化参数文件
[oracle@ogg02 dbs]$ vi initogg02.ora

ogg02.__db_cache_size=348127232
ogg02.__java_pool_size=4194304
ogg02.__large_pool_size=4194304
ogg02.__oracle_base='/u01/app/oracle'
ogg02.__pga_aggregate_target=343932928
ogg02.__sga_target=515899392
ogg02.__shared_io_pool_size=0
ogg02.__shared_pool_size=150994944
ogg02.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ogg02/adump'
*.audit_trail='db'
*.compatible='11.2.0.3.0'
*.control_files='/oradata/ogg02/control01.ctl','/oradata/ogg02/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ogg02'
*.diagnostic_dest='/u01/app/oracle'
*.memory_target=858783744
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

5. 启动实例到nomount状态
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initogg02.ora'
ORACLE instance started.

Total System Global Area 855982080 bytes
Fixed Size      2233160 bytes
Variable Size    503319736 bytes
Database Buffers   348127232 bytes
Redo Buffers      2301952 bytes

6.创建spfile
SQL> create spfile from pfile='$ORACLE_HOME/dbs/initogg02.ora';
File created.

7.建库脚本
SQL> create database ogg02 CONTROLFILE REUSE
   MAXINSTANCES 8
   MAXDATAFILES 2000
   MAXLOGHISTORY 1024
   MAXLOGFILES 64
   MAXLOGMEMBERS 4
   character set ZHS16GBK national character set AL16UTF16
   logfile
   group 1 ('/oradata/ogg02/redo01.log') size 50M reuse,
   group 2 ('/oradata/ogg02/redo02.log') size 50M reuse,
   group 3 ('/oradata/ogg02/redo03.log') size 50M reuse
datafile '/oradata/ogg02/system01.dbf' size 1000M reuse extent management local
sysaux datafile '/oradata/ogg02/sysaux01.dbf' size 800M
default temporary tablespace temp tempfile '/oradata/ogg02/temp01.dbf' size 500M reuse
undo tablespace undotbs1 datafile '/oradata/ogg02/undotbs01.dbf' size 126M reuse
USER SYS IDENTIFIED BY "oracle" USER SYSTEM IDENTIFIED BY "oracle";

Database created.

8. 运行数据字典脚本，其中catalog和catproc是必需的，其它可选：
SQL> spool /home/oracle/cat_ogg02.log
SQL> @?/rdbms/admin/catalog.sql ;      （建数据字典视图）
SQL> @?/rdbms/admin/catproc.sql ;      （建存储过程包）
SQL> @?/rdbms/admin/catblock.sql ;     （建锁相关的几个视图）
SQL> @?/rdbms/admin/catoctk.sql ;      （建密码工具包dbms_crypto_toolkit）
SQL> @?/rdbms/admin/owminst.plb ;      （建工作空间管理相关对象，如dmbs_wm）
SQL> spool off
执行完后检查/orahome/cat.log看看有什么不可接受的错误没有。

9. 新建sqlplus属性和帮助、USERS表空间
SQL> connect system/oracle
SQL> @?/sqlplus/admin/pupbld.sql
SQL> @?/sqlplus/admin/help/hlpbld.sql helpus.sql
SQL> connect /as sysdba
SQL> CREATE TABLESPACE USERS LOGGING DATAFILE '/oradata/ogg02/user01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
SQL> ALTER DATABASE DEFAULT TABLESPACE USERS;

10. 最后修改为归档模式并重启(省略此步骤）
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     1
Current log sequence           2

SQL> shutdown immediate
SQL> connect /as sysdba
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;