Oracle Study之-Oracle 11g OCM考试（1）

谁践踏了优雅 2022-07-26 06:18 134阅读 0赞

**Oracle Study之-Oracle 11g OCM考试（1）**

**系统环境：RedHat EL64**

**Oracle：   Oracle 11gR2**

**      Oracle 11g OCM考试第一题，手工建库，参考文档![Center][]。参考人员必须对文档非常熟悉，才可以在指定的时间内完成任务。**

11g OCM考试 纲:Server Configuration

1.  1  Create the database

**一、查看系统环境**

\[oracle@rh64 ~\]$ cat .bash\_profile  
\# .bash\_profile  
export EDITOR=vi  
export ORACLE\_SID=prod  
export ORACLE\_BASE=/u01/app/oracle  
export ORACLE\_HOME=$ORACLE\_BASE/product/11.2.0/db\_1  
export LD\_LIBRARY\_PATH=$ORACLE\_HOME/lib  
export PATH=$ORACLE\_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin  
umask 022  
  
\[oracle@rh64 ~\]$ id oracle  
uid=300(oracle) gid=501(oinstall) groups=501(oinstall),502(dba)  
  
**2、建立数据库pfile文件**  
\[oracle@rh64 dbs\]$ pwd  
**/u01/app/oracle/product/11.2.0/db\_1/dbs**

\[oracle@rh64 dbs\]$ ls  
hc\_DBUA3908922.dat  init.bk              inittest2.ora  lkTEST2           orapwtest2  
hc\_prod.dat         initDBUA3908922.ora  inittest.ora   orapwDBUA3908922  snapcf\_prod.f  
hc\_test2.dat        init.ora             lkPROD         orapwprod         spfileprod.ora  
hc\_test.dat         initprod.ora         lkTEST         orapwtest         spfiletest2.ora  
**\[oracle@rh64 dbs\]$ cat init.ora |grep -v "^\#"|grep -v "^$" >inittest1.ora**

**编辑pfile文件：  
\[oracle@rh64 dbs\]$ cat inittest1.ora**  
db\_name='test1'  
memory\_target=300m  
processes = 150  
audit\_file\_dest='$ORACLE\_BASE/admin/test1/adump'  
audit\_trail ='db'  
db\_block\_size=8192  
db\_domain=''  
diagnostic\_dest=$ORACLE\_BASE  
open\_cursors=300   
remote\_login\_passwordfile='EXCLUSIVE'  
undo\_tablespace='UNDOTBS1'  
control\_files ='/u01/app/oracle/oradata/test1/control01.ctl'  
compatible ='11.2.0'  
**建立相应的目录：**  
\[oracle@rh64 dbs\]$ mkdir -p /u01/app/oracle/admin/test1/adump  
\[oracle@rh64 dbs\]$ mkdir -p /u01/app/oracle/oradata/test1  
**建立 Instance 口令文件：**  
\[oracle@rh64 dbs\]$ orapwd file=orapwtest1 password=oracle entries=3  
  
**建立建库脚本：**

**参考：**

**![Center 1][]**  
**\[oracle@rh64 ~\]$ cat cr\_db.sql**  
CREATE DATABASE test1  
   USER SYS IDENTIFIED BY oracle  
   USER SYSTEM IDENTIFIED BY oracle  
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/test1/redo01a.log') SIZE 100M BLOCKSIZE 512,  
           GROUP 2 ('/u01/app/oracle/oradata/test1/redo02a.log') SIZE 100M BLOCKSIZE 512  
   MAXLOGFILES 5  
   MAXLOGMEMBERS 5  
   MAXLOGHISTORY 1  
   MAXDATAFILES 100  
   CHARACTER SET zhs16gbk  
   EXTENT MANAGEMENT LOCAL  
   DATAFILE '/u01/app/oracle/oradata/test1/system01.dbf' SIZE 325M REUSE  
   SYSAUX DATAFILE '/u01/app/oracle/oradata/test1/sysaux01.dbf' SIZE 325M REUSE  
   DEFAULT TEMPORARY TABLESPACE tempts1  
      TEMPFILE '/u01/app/oracle/oradata/test1/temp01.dbf'  
      SIZE 20M REUSE  
   UNDO TABLESPACE undotbs1  
      DATAFILE '/u01/app/oracle/oradata/test1/undotbs01.dbf'  
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;  
     
**  3、启动Instance到nomount，运行建库脚本：**   
\[oracle@rh64 ~\]$ export ORACLE\_SID=test1  
\[oracle@rh64 ~\]$ sqlplus '/as sysdba'  
SQL\*Plus: Release 11.2.0.3.0 Production on Fri Apr 8 17:09:02 2016  
Copyright (c) 1982, 2011, Oracle.  All rights reserved.  
Connected to an idle instance.  
  
**17:09:02 SYS@ test1>startup nomount;**  
ORACLE instance started.  
Total System Global Area  313159680 bytes  
Fixed Size                  2227944 bytes  
Variable Size             218104088 bytes  
Database Buffers           88080384 bytes  
Redo Buffers                4747264 bytes  
17:14:17 SYS@ test1>select status from v$instance；  
STATUS  
\------------  
STARTED  
  
**17:15:15 SYS@ test1>@/home/oracle/cr\_db.sql**  
Database created.  
  
**4、建库完成后，通过脚本建立数据字典**  
**\[oracle@rh64 ~\]$ cat cr\_dict.sql **  
**@?/rdbms/admin/catalog.sql  
@?/rdbms/admin/catproc.sql  
conn system/oracle  
@?/sqlplus/admin/pupbld.sql**  
  
**17:31:44 SYS@ test1>@/home/oracle/cr\_dict.sql**

**5、建库成功，建立default tablespace**  
  
19:24:34 SYSTEM@ test1>**create tablespace users**  
                    **datafile '/u01/app/oracle/oradata/test1/users01.dbf' size 100m;**  
Tablespace created.  
  
**19:25:57 SYSTEM@ test1>alter database default tablespace users;**  
Database altered.  
  
19:27:52 SYSTEM@ test1>**select tablespace\_name,file\_id,file\_name from dba\_data\_files;**  
  
TABLESPACE\_NAME                   FILE\_ID FILE\_NAME  
\------------------------------ ---------- --------------------------------------------------  
SYSTEM                                  1 /u01/app/oracle/oradata/test1/system01.dbf  
SYSAUX                                  2 /u01/app/oracle/oradata/test1/sysaux01.dbf  
UNDOTBS1                                3 /u01/app/oracle/oradata/test1/undotbs01.dbf  
USERS                                   4 /u01/app/oracle/oradata/test1/users01.dbf  
4 rows selected.  
  
**6、配置网络（tnsnames）**  
**\[oracle@rh64 admin\]$ cat tnsnames.ora **  
TEST1 =  
  (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = rh64)(PORT = 1521))  
    (CONNECT\_DATA =  
      (SERVER = DEDICATED)  
      (SERVICE\_NAME = test1)  
    )  
  )  
    
**  \[oracle@rh64 admin\]$ lsnrctl status**  
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 08-APR-2016 19:33:50  
Copyright (c) 1991, 2011, Oracle.  All rights reserved.  
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rh64)(PORT=1521)))  
STATUS of the LISTENER  
\------------------------  
Alias                     LISTENER  
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production  
Start Date                08-APR-2016 19:32:48  
Uptime                    0 days 0 hr. 1 min. 2 sec  
Trace Level               off  
Security                  ON: Local OS Authentication  
SNMP                      OFF  
Listener Parameter File   /u01/app/oracle/product/11.2.0/db\_1/network/admin/listener.ora  
Listener Log File         /u01/app/oracle/diag/tnslsnr/rh64/listener/alert/log.xml  
Listening Endpoints Summary...  
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh64.cuug.cn)(PORT=1521)))  
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))  
Services Summary...  
Service "test1" has 1 instance(s).  
  Instance "test1", status READY, has 1 handler(s) for this service...  
The command completed successfully  
  
**测试网络连接：**  
\[oracle@rh64 admin\]$ sqlplus system/oracle@test1  
SQL\*Plus: Release 11.2.0.3.0 Production on Fri Apr 8 19:34:27 2016  
Copyright (c) 1982, 2011, Oracle.  All rights reserved.  
Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options  
  
**19:34:27 SYSTEM@ test1>**

**-----  至此，手工建库成功；如果有时间，可以对数据库做一个冷备 。**

\---------- OCM考试考点练习，后续继续推出。。。

[Center]: /images/20220724/b1e9143569794391a91493f24c3c2103.png
[Center 1]: /images/20220724/37ecb6a1cc4145d08129b5fb4410c85b.png