OCM实验-手工建库
检查环境变量
[oracle@ocm1 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
#以下是添加的环境变量
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=PROD
修改sqlplus的命令提示付
vi $ORACLE_HOME/sqlplus/admin/glogin.sql
将set sqlprompt"_user'@'_connect_identifier>"添加到文件的最后
创建所需的目录
mkdir -p $ORACLE_BASE/admin/PROD/adump
mkdir -p $ORACLE_BASE/admin/PROD/bdump
mkdir -p $ORACLE_BASE/admin/PROD/cdump
mkdir -p $ORACLE_BASE/admin/PROD/udump
mkdir -p $ORACLE_BASE/oradata/PROD/disk1
生成密码文件
[oracle@ocm1 ~]$ cd $ORACLE_HOME/dbs
[oracle@ocm1 dbs]$ orapwd help #不会命令时,可以这样查看
Usage: orapwd file= password= entries= force=
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA and force - whether to overwrite existing file (opt),
OPERs (opt),
There are no spaces around the equal-to (=) character.
[oracle@ocm1 dbs]$ orapwd file=orapwPROD password=oracle entries=30
[oracle@ocm1 dbs]$ ls
initdw.ora init.ora orapwPROD
创建参数文件
[oracle@ocm1 dbs]$ cat init.ora | grep -v ^# | grep -v ^$ > initPROD.ora
[oracle@ocm1 dbs]$ vi initPROD.ora
db_name=PROD
db_files = 80 # SMALL
db_file_multiblock_read_count = 8 # SMALL
#db_block_buffers = 100 # SMALL
#shared_pool_size = 3500000 # SMALL
log_checkpoint_interval = 10000
processes = 300 # SMALL
parallel_max_servers = 5 # SMALL
log_buffer = 32768 # SMALL
max_dump_file_size = 10240
global_names = FALSE
control_files = (/u01/app/oracle/oradata/PROD/disk1/control01.ctl, /u01/app/oracle/oradata/PROD/disk1/control02.ctl)
sga_max_size=500M
sga_target=500M
通过pfile文件创建spfile
SYS@PROD>create spfile from pfile;
File created.
修改一些参数
SYS@PROD>show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string
SYS@PROD>
SYS@PROD>alter system set undo_management=auto scope=spfile;#将undo表空间设置为自动管理
System altered.
SYS@PROD>show parameter job;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0
SYS@PROD>
SYS@PROD>alter system set job_queue_processes=30 scope=spfile;#开启作业
System altered.
将数据库重启一下。
SYS@PROD>startup nomount
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 146800864 bytes
Database Buffers 373293056 bytes
Redo Buffers 2973696 bytes
SYS@PROD>
检查路径参数
SYS@PROD>col name format a40
SYS@PROD>col value format a50
SYS@PROD>
SYS@PROD>select name,value from v$parameter where name like '%dest%';
NAME VALUE
---------------------------------------- --------------------------------------------------
。。。。。。
standby_archive_dest ?/dbs/arch
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
db_recovery_file_dest
db_recovery_file_dest_size 0
background_dump_dest /u01/app/oracle/admin/PROD/bdump
NAME VALUE
---------------------------------------- --------------------------------------------------
user_dump_dest /u01/app/oracle/admin/PROD/udump
core_dump_dest /u01/app/oracle/admin/PROD/cdump
audit_file_dest /u01/app/oracle/admin/PROD/adump
确认这些路径已经创建
创建建库脚本
vi create_database.sql
CREATE DATABASE PROD
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/disk1/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/PROD/disk1/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/PROD/disk1/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/PROD/disk1/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/PROD/disk1/users01.dbf' size 200M REUSE
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/PROD/disk1/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
开始执行
SYS@PROD>@create_database.sql
Database created.
打开数据文件的自动扩展。
SYS@PROD>alter database datafile 1 autoextend on;
Database altered.
SYS@PROD>alter database datafile 2 autoextend on;
Database altered.
SYS@PROD>alter database datafile 3 autoextend on;
Database altered.
SYS@PROD>alter database datafile 4 autoextend on;
Database altered.
执行脚本
在sys用户下执行
SYS@PROD>spool script.log
SYS@PROD>@?/rdbms/admin/catalog.sql
SYS@PROD>@?/rdbms/admin/catproc.sql
SYS@PROD>@?/rdbms/admin/catblock.sql
SYS@PROD>@?/rdbms/admin/catoctk.sql
SYS@PROD>@?/rdbms/admin/owminst.plb
在system用户下执行
SYSTEM@PROD>@?/sqlplus/admin/help/hlpbld.sql
SYSTEM@PROD>@?/sqlplus/admin/help/helpus.sql
SYSTEM@PROD>@?/sqlplus/admin/pupbld.sql
下面是各个脚本的解释。
CATALOG.SQL
Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms
CATPROC.SQL
Runs all scripts required for or used with PL/SQL.
catblock.sql
Creates views that can dynamically display lock dependency graphs
catoctk.sql
Creates the Oracle Cryptographic Toolkit package
owminst.plb
A Installing Workspace Manager with Custom Databases
http://docs.oracle.com/cd/B12037_01/appdev.101/b10824/long_inst.htm
pupbld.sql
help/hlpbld.sql
help/helpus.sql
sqlplus的帮助信息
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:584223242750
检查环境变量
[oracle@ocm1 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
#以下是添加的环境变量
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=PROD
修改sqlplus的命令提示付
vi $ORACLE_HOME/sqlplus/admin/glogin.sql
将set sqlprompt"_user'@'_connect_identifier>"添加到文件的最后
创建所需的目录
mkdir -p $ORACLE_BASE/admin/PROD/adump
mkdir -p $ORACLE_BASE/admin/PROD/bdump
mkdir -p $ORACLE_BASE/admin/PROD/cdump
mkdir -p $ORACLE_BASE/admin/PROD/udump
mkdir -p $ORACLE_BASE/oradata/PROD/disk1
生成密码文件
[oracle@ocm1 ~]$ cd $ORACLE_HOME/dbs
[oracle@ocm1 dbs]$ orapwd help #不会命令时,可以这样查看
Usage: orapwd file= password= entries= force=
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA and force - whether to overwrite existing file (opt),
OPERs (opt),
There are no spaces around the equal-to (=) character.
[oracle@ocm1 dbs]$ orapwd file=orapwPROD password=oracle entries=30
[oracle@ocm1 dbs]$ ls
initdw.ora init.ora orapwPROD
创建参数文件
[oracle@ocm1 dbs]$ cat init.ora | grep -v ^# | grep -v ^$ > initPROD.ora
[oracle@ocm1 dbs]$ vi initPROD.ora
db_name=PROD
db_files = 80 # SMALL
db_file_multiblock_read_count = 8 # SMALL
#db_block_buffers = 100 # SMALL
#shared_pool_size = 3500000 # SMALL
log_checkpoint_interval = 10000
processes = 300 # SMALL
parallel_max_servers = 5 # SMALL
log_buffer = 32768 # SMALL
max_dump_file_size = 10240
global_names = FALSE
control_files = (/u01/app/oracle/oradata/PROD/disk1/control01.ctl, /u01/app/oracle/oradata/PROD/disk1/control02.ctl)
sga_max_size=500M
sga_target=500M
通过pfile文件创建spfile
SYS@PROD>create spfile from pfile;
File created.
修改一些参数
SYS@PROD>show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string
SYS@PROD>
SYS@PROD>alter system set undo_management=auto scope=spfile;#将undo表空间设置为自动管理
System altered.
SYS@PROD>show parameter job;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0
SYS@PROD>
SYS@PROD>alter system set job_queue_processes=30 scope=spfile;#开启作业
System altered.
将数据库重启一下。
SYS@PROD>startup nomount
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 146800864 bytes
Database Buffers 373293056 bytes
Redo Buffers 2973696 bytes
SYS@PROD>
检查路径参数
SYS@PROD>col name format a40
SYS@PROD>col value format a50
SYS@PROD>
SYS@PROD>select name,value from v$parameter where name like '%dest%';
NAME VALUE
---------------------------------------- --------------------------------------------------
。。。。。。
standby_archive_dest ?/dbs/arch
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
db_recovery_file_dest
db_recovery_file_dest_size 0
background_dump_dest /u01/app/oracle/admin/PROD/bdump
NAME VALUE
---------------------------------------- --------------------------------------------------
user_dump_dest /u01/app/oracle/admin/PROD/udump
core_dump_dest /u01/app/oracle/admin/PROD/cdump
audit_file_dest /u01/app/oracle/admin/PROD/adump
确认这些路径已经创建
创建建库脚本
vi create_database.sql
CREATE DATABASE PROD
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/disk1/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/PROD/disk1/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/PROD/disk1/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/PROD/disk1/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/PROD/disk1/users01.dbf' size 200M REUSE
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/PROD/disk1/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
开始执行
SYS@PROD>@create_database.sql
Database created.
打开数据文件的自动扩展。
SYS@PROD>alter database datafile 1 autoextend on;
Database altered.
SYS@PROD>alter database datafile 2 autoextend on;
Database altered.
SYS@PROD>alter database datafile 3 autoextend on;
Database altered.
SYS@PROD>alter database datafile 4 autoextend on;
Database altered.
执行脚本
在sys用户下执行
SYS@PROD>spool script.log
SYS@PROD>@?/rdbms/admin/catalog.sql
SYS@PROD>@?/rdbms/admin/catproc.sql
SYS@PROD>@?/rdbms/admin/catblock.sql
SYS@PROD>@?/rdbms/admin/catoctk.sql
SYS@PROD>@?/rdbms/admin/owminst.plb
在system用户下执行
SYSTEM@PROD>@?/sqlplus/admin/help/hlpbld.sql
SYSTEM@PROD>@?/sqlplus/admin/help/helpus.sql
SYSTEM@PROD>@?/sqlplus/admin/pupbld.sql
下面是各个脚本的解释。
CATALOG.SQL
Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms
CATPROC.SQL
Runs all scripts required for or used with PL/SQL.
catblock.sql
Creates views that can dynamically display lock dependency graphs
catoctk.sql
Creates the Oracle Cryptographic Toolkit package
owminst.plb
A Installing Workspace Manager with Custom Databases
http://docs.oracle.com/cd/B12037_01/appdev.101/b10824/long_inst.htm
pupbld.sql
help/hlpbld.sql
help/helpus.sql
sqlplus的帮助信息
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:584223242750
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11590946/viewspace-1064143/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11590946/viewspace-1064143/