博主用的实验环境为:
服务器:Linux Redhat 5.5 32位
服务器IP地址:172.23.8.120
Oracle:11g —11.2.0.1
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_BASE=/u01/app/oracle
实验过程中所有密码均为:oracle
Questition:
1. Create Database
1.1 Create a database the SID name is PROD3 at Management Server.
Reference: Administrator's Guide->2 Creating and Configuring an Oracle Database->Creating a Database with the CREATE DATABASE Statement
问题:
1.创建数据库
1.1在Management Server上创建SID名称为PROD3的数据库。
参考:Administrator’s Guide->2 Creating and Configuring an Oracle Database->Creating a Database with the CREATE DATABASE Statement
—-官方手册操作步骤:
Step 1: Specify an Instance Identifier (SID)
Step 2: Ensure That the Required Environment Variables Are Set
Step 3: Choose a Database Administrator Authentication Method
Step 4: Create the Initialization Parameter File
Step 5: (Windows Only) Create an Instance
Step 6: Connect to the Instance
Step 7: Create a Server Parameter File
Step 8: Start the Instance
Step 9: Issue the CREATE DATABASE Statement
Step 10: Create Additional Tablespaces
Step 11: Run Scripts to Build Data Dictionary Views
Step 12: (Optional) Run Scripts to Install Additional Options
Step 13: Back Up the Database.
Step 14: (Optional) Enable Automatic Instance Startup
实际考试过程中因使用Linux服务器步骤5、步骤12、步骤14 可以不用执行,步骤10因题目未要求创建其他表空间,可暂时不做。以下为实际操作步骤:
Step1. 指定实例标识符(SID)
[oracle@rookie ~]$ export ORACLE_SID=PROD3
Step2. 确保设置所需的环境变量
[oracle@rookie ~]$ env |grep ORACLE
ORACLE_SID=PROD3
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
Step3. 选择数据库管理员身份验证方法
[oracle@rookie database]$ cd $ORACLE_HOME/dbs
[oracle@rookie dbs]$ orapwd file=orapwPROD3 password=oracle entries=30
[oracle@rookie dbs]$ ll
total 12
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 5120 Jul 11 18:09 orapwPROD3
Step4. 创建初始化参数文件
认真的同学可直接修改模板内容,添加其他参数,最简单的写法如下:
[oracle@rookie dbs]$ cat initPROD3.ora
db_name='PROD3'
db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=2G
参数文件保留了闪回区的配置,方便之后进行数据库备份,如不设置闪回区,备份文件保留位置不能确定,建议保留。
Step6. 连接到实例
[oracle@rookie dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 11 18:16:25 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
Step7. 创建服务器参数文件
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initPROD3.ora';
File created.
Step8. 启动实例
SQL> startup nomount
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes
SQL>
Step9. 发出create database 语句
拷贝官方手册的example到服务器上,注意修改里面的参数为自己的sid和目录,并需要提前创建不存在的文件夹。
[root@rookie u01]# cat createdb.sql
CREATE DATABASE prod3
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prod3/redo/redo01a.log','/u01/app/oracle/oradata/prod3/redo/redo01b.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/prod3/redo/redo02a.log','/u01/app/oracle/oradata/prod3/redo/redo02b.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/prod3/redo/redo03a.log','/u01/app/oracle/oradata/prod3/redo/redo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/prod3/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/prod3/sysaux01.dbf' SIZE 325M REUSE
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
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/prod3/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
[root@rookie u01]# su - oracle
[oracle@rookie ~]$ mkdir -p /u01/app/oracle/oradata/prod3/redo
[oracle@rookie ~]$
在oracle命令行运行脚本
SQL> @/u01/createdb.sql
Database created.
此时查看数据库状态为read write即可
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
Step11. 运行脚本以构建数据字典视图
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql
还需要切换到system用户执行脚本:
SQL> alter user system identified by oracle account unlock;
User altered.
SQL> conn system/oracle
Connected.
SQL> @?/sqlplus/admin/pupbld.sql
每个包的含义:
catalog.sql 创建许多视图的数据字典表,动态性能视图和公共同义词的视图。 授予PUBLIC访问同义词。
catproc.sql 运行PL / SQL所需的或与PL / SQL一起使用的所有脚本。
utlrp.sql 重新编译处于无效状态的所有PL / SQL模块,包括包,过程和类型。
pupbld.sql SQL * Plus必需。 启用SQL * Plus以禁用用户命令。
Step13. 备份数据库
重中之重,这一步必须做,打开归档日志,并进行备份,防止之后的试验中不小心或者被监控老师有意破坏!!!!这是保命的!!
打开归档;
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
备份数据库和归档日志:
[oracle@rookie dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 11 18:50:09 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD3 (DBID=1562257515)
RMAN> backup database plus archivelog ;
Starting backup at 11-JUL-17
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting archived log backup set
.....
.....
.....
channel ORA_DISK_1: starting piece 1 at 11-JUL-17
channel ORA_DISK_1: finished piece 1 at 11-JUL-17
piece handle=/u01/app/oracle/flash_recovery_area/PROD3/backupset/2017_07_11/o1_mf_annnn_TAG20170711T190157_dp9d15z4_.bkp tag=TAG20170711T190157 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-JUL-17