ocm考试手工建库

DB server: PROD1, PROD2
Mgr server: PROD4, EMREP, [PROD3]


查看考试大纲和考试环境：
https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=5001&get_params=p_exam_id:11GOCM&p_org_id=&lang=


第一天
数据库管理 45+120分钟
备份恢复 60分钟
数据管理 120分钟
数据仓库 90分钟


第二天
性能优化 120分钟
Data guard 60分钟
grid control 60分钟
Grid Infra 60分钟
RAC 60分钟




Scenario 1
1. 建库
建立audit dump目录：
mkdir -p /u01/app/oracle/admin/PROD1/adump
mkdir -p /u01/app/oracle/oradata/PROD1
mkdir -p /u01/app/oracle/oradata/PROD1/disk1
mkdir -p /u01/app/oracle/oradata/PROD1/disk2
mkdir -p /u01/app/oracle/oradata/PROD1/disk3


vi /etc/oratab
PROD1:/u01/app/oracle/product/11.2.0/dbhome_1:Y


建立password file：
orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwPROD1 force=y password=oracle ignorecase=n


编辑初始化参数文件：
vi /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD1.ora
db_block_size=4096
db_domain="oracle.com"
db_name="PROD1"
instance_name=PROD1
control_files=("/u01/app/oracle/oradata/PROD1/control01.ctl","/u01/app/oracle/oradata/PROD1/control02.ctl")
compatible=11.2.0.0.0
undo_tablespace=UNDOTBS1
deferred_segment_creation=false


启动实例到nomount状态：
sqlplus / as sysdba
startup nomount
CREATE DATABASE "PROD1"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE '/u01/app/oracle/oradata/PROD1/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE 2G
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD1/sysaux01.dbf' SIZE 250M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE 2G
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/PROD1/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE 2G
UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/PROD1/undotbs01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE 2G
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
LOGFILE 
GROUP 1 ('/u01/app/oracle/oradata/PROD1/disk1/redo01a.log',
         '/u01/app/oracle/oradata/PROD1/disk2/redo01b.log',
         '/u01/app/oracle/oradata/PROD1/disk3/redo01c.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/PROD1/disk1/redo02a.log',
         '/u01/app/oracle/oradata/PROD1/disk2/redo02b.log',
         '/u01/app/oracle/oradata/PROD1/disk3/redo02c.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/PROD1/disk1/redo03a.log',
         '/u01/app/oracle/oradata/PROD1/disk2/redo03b.log',
         '/u01/app/oracle/oradata/PROD1/disk3/redo03c.log') SIZE 100M
USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle;


执行相关的脚本：
sqlplus / as sysdba
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catalog.sql;
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catblock.sql;
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catproc.sql;
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catoctk.sql;
@/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/owminst.plb;
connect SYSTEM/oracle
@/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/pupbld.sql;
connect SYSTEM/oracle
@/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/help/hlpbld.sql helpus.sql;


重编译失效对象：
execute utl_recomp.recomp_serial();


create spfile from pfile;


shutdown immediate;
startup;


2.
alter system set undo_retention=5400;


3.
create tablespace users
datafile '/u01/app/oracle/oradata/PROD1/users01.dbf' size 64M autoextend on maxsize 2G
extent management local uniform size 1M
segment space management manual;
alter database default tablespace users;


create tablespace INDX
datafile '/u01/app/oracle/oradata/PROD1/INDX01.dbf' size 32M autoextend on maxsize 2G
extent management local autoallocate
segment space management manual;


create bigfile tablespace EXAMPLE
datafile '/u01/app/oracle/oradata/PROD1/EXAMPLE01.dbf' size 512M autoextend on maxsize 8T
extent management local uniform size 512K
segment space management auto;


create tablespace OLTP
datafile '/u01/app/oracle/oradata/PROD1/OLTP01.dbf' size 64M autoextend on maxsize 2G
extent management local uniform size 1M
segment space management auto;


create tablespace BATCH
datafile '/u01/app/oracle/oradata/PROD1/BATCH01.dbf' size 128M autoextend on maxsize 2G
extent management local uniform size 2M
segment space management manual;


4.
create temporary tablespace tmp1 
tempfile '/u01/app/oracle/oradata/PROD1/tmp1.dbf' size 64M autoextend on maxsize 2G
extent management local uniform size 1M
tablespace group TMPGRP;


create temporary tablespace tmp2 
tempfile '/u01/app/oracle/oradata/PROD1/tmp2.dbf' size 64M autoextend on maxsize 2G
extent management local uniform size 1M
tablespace group TMPGRP;


create temporary tablespace tmp3
tempfile '/u01/app/oracle/oradata/PROD1/tmp3.dbf' size 64M autoextend on maxsize 2G
extent management local uniform size 1M
tablespace group TMPGRP;


alter database default temporary tablespace TMPGRP;


5.
cd $ORACLE_HOME/network/admin
vi listener.ora
LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=odd.oracle.com)(PORT=1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=PROD1.oracle.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=PROD1)
    )
    (SID_DESC=
      (GLOBAL_DBNAME=EMREP.oracle.com)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=EMREP)
    )
  )


lsnrctl start




vi listener.ora
LSNR2=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=odd.oracle.com)(PORT=1526))
    )
  )


lsnrctl start lsnr2


sqlplus / as sysdba
alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=odd.oracle.com)(PORT=1526)))';




#######考试不考的部分，再进一步，需要动态注册到1521和1526：
vi tnsnames.ora
dynamic_reg=
   (address_list=
      (ADDRESS=(PROTOCOL=tcp)(HOST=odd.oracle.com)(PORT=1521))
      (ADDRESS=(PROTOCOL=tcp)(HOST=odd.oracle.com)(PORT=1526))
   )
sqlplus / as sysdba
alter system set local_listener='dynamic_reg';


6.
alter system set sessions=300 scope=spfile;
alter system set shared_server_sessions=200 scope=spfile;
alter system set circuits=200 scope=spfile;
shutdown immediate;
startup;
alter system set dispatchers='(protocol=tcp)(dispatchers=3)';
alter system set max_dispatchers=10;
alter system set shared_servers=10;
alter system set max_shared_servers=30;


7.
PROD=
 (DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=odd.oracle.com)(PORT=1521))
   )
   (CONNECT_DATA=
    (SERVICE_NAME=PROD1.oracle.com)
    (server=dedicated)
   )
 )


PROD_S=
 (DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=odd.oracle.com)(PORT=1526))
   )
   (CONNECT_DATA=
    (SERVICE_NAME=PROD1.oracle.com)
    (server=shared)
   )
 )


racdb =
 (DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=cluster01.example.com)(PORT=1521))
   )
   (CONNECT_DATA=
    (SERVICE_NAME=RACDB)
    (server=dedicated)
   )
 )


PROD2=
 (DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=odd.oracle.com)(PORT=1521))
   )
   (CONNECT_DATA=
    (SERVICE_NAME=PROD2.oracle.com)
    (server=dedicated)
   )
 )


EMREP=
 (DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=even.oracle.com)(PORT=1621))
   )
   (CONNECT_DATA=
    (SERVICE_NAME=EMREP.oracle.com)
    (server=dedicated)
   )
 )


PROD3=
 (DESCRIPTION= 
   (ADDRESS_LIST=
     (ADDRESS=(PROTOCOL=tcp)(HOST=even.oracle.com)(PORT=1521))
   )
   (CONNECT_DATA=
    (SERVICE_NAME=PROD3.oracle.com)
    (server=dedicated)
   )
 )


sqlplus system/oracle@prod
sqlplus system/oracle@prod_s


8.
alter database add logfile 
group 4( '/u01/app/oracle/oradata/PROD1/disk1/redo04a.log',
         '/u01/app/oracle/oradata/PROD1/disk2/redo04b.log') SIZE 100M,
group 5( '/u01/app/oracle/oradata/PROD1/disk1/redo05a.log',
         '/u01/app/oracle/oradata/PROD1/disk2/redo05b.log') SIZE 100M;


9.
alter system set control_files='/u01/app/oracle/oradata/PROD1/control01.ctl','/u01/app/oracle/oradata/PROD1/control02.ctl','/u01/app/oracle/oradata/PROD1/control03.ctl' scope=spfile;
shutdown immediate;
host cp /u01/app/oracle/oradata/PROD1/control01.ctl /u01/app/oracle/oradata/PROD1/control03.ctl
startup


10.
exec dbms_stats.gather_database_stats;
exec dbms_stats.gather_schema_stats('HR');


11.
alter system set utl_file_dir='/home/oracle','/home/oracle/temp', '/home/oracle/scripts' scope=spfile;


12.
online help:
Oracle Grid Infrastructure Installation Guide
11g Release 2 (11.2) for Linux
3.2.8 Enabling Direct NFS Client Oracle Disk Manager Control of NFS


--准备环境
[root@gcsrv u01]# mkdir shared_mount
[root@gcsrv u01]# chmod -R 777 shared_mount
[root@gcsrv u01]# service nfs start
Starting NFS services:                                     [  OK  ]
Starting NFS quotas:                                       [  OK  ]
Starting NFS daemon:                                       [  OK  ]
Starting NFS mountd:                                       [  OK  ]


[root@gcsrv ~]# vi /etc/exports
把以下内容添加进去：
/u01/shared_mount   192.168.92.80(rw,sync)


[root@gcsrv u01]# exportfs -a
[root@gcsrv u01]# exportfs -v
/u01/shared_mount
                <world>(rw,wdelay,root_squash,no_subtree_check,anonuid=65534,anongid=65534)


--edit /etc/fstab, add followings:
192.168.92.90:/u01/shared_mount   /nfsdir        nfs      nolock             0 0


--then mount /nfsdir
[oracle@ocmsrv trace]$ cd $ORACLE_HOME/dbs
[oracle@ocmsrv dbs]$ vi oranfstab
--add followings:
server: ocmnfs
path: 192.168.92.90
export:/u01/shared_mount mount:/nfsdir


[oracle@ocmsrv dbs]$ cd $ORACLE_HOME/rdbms/lib
[oracle@ocmsrv lib]$ make -f ins_rdbms.mk dnfs_on


--restart the database
-- check V$DNFS_SERVERS
SQL> select * from v$dnfs_servers;


no rows selected


SQL> create tablespace dnfs_tbs datafile '/nfsdir/dnfs_tab01.dbf' size 5M;


Tablespace created.


SQL> select * from v$dnfs_servers;


        ID
----------
SVRNAME
--------------------------------------------------------------------------------
DIRNAME
--------------------------------------------------------------------------------
   MNTPORT    NFSPORT      WTMAX      RTMAX
---------- ---------- ---------- ----------
         1
192.168.4.160
/11gDB
      1234       2049      32768      32768


13.
/u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/roothas.pl
/u01/app/11.2.0/grid/bin/crsctl status res -t
/u01/app/11.2.0/grid/bin/crsctl start res ora.cssd
/u01/app/11.2.0/grid/bin/crsctl status res -t


/u01/app/11.2.0/grid/bin/srvctl add database -d PROD1 -o /u01/app/oracle/product/11.2.0/dbhome_1 -p /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilePROD1.ora


lsnrctl stop listener
lsnrctl stop lsnr2
/u01/app/11.2.0/grid/bin/srvctl add listener -l listener -p 1521 -o /u01/app/oracle/product/11.2.0/dbhome_1
/u01/app/11.2.0/grid/bin/srvctl add listener -l lsnr2 -p 1526 -o /u01/app/oracle/product/11.2.0/dbhome_1
/u01/app/11.2.0/grid/bin/srvctl start listener -l listener
/u01/app/11.2.0/grid/bin/srvctl start listener -l lsnr2


连接到PROD1，并执行：alter system register