OCM考试实验-Skillset1----手动建库

博主用的实验环境为：

---

服务器：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