OCM考纲F

Section 0: 创建一个数据库

修改环境变量：

[oracle@rac1 ~]$ cd ~

[oracle@rac1 ~]$ vi .bash_profile

export ORACLE_SID=PROD export EDITOR=vi

[oracle@rac1 ~]$ source .bash_profile - [oracle@rac1 ~]$ env|grep -i sid ORACLE_SID=PROD

建立相关目录：

[oracle@rac1 ~]$ cd $ORACLE_BASE

[oracle@rac1 oracle]$ mkdir -p admin/PROD/{a,b,c,u}dump [oracle@rac1 oracle]$ mkdir -p oradata/PROD/Disk{1,2,3,4,5}

修改/etc/oratab文件

[oracle@rac1 oracle]$ vi /etc/oratab

PROD:/u01/app/oracle/OracleHomes/db10g:N

建立初始化参数文件initPROD.ora

[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs

[oracle@rac1 dbs]$cat init.ora|grep -v ^#|grep -v ^$>initPROD.ora [oracle@rac1 dbs]$ vi initPROD.ora

参考联机文档：Administrator's Guide —> 第二章Creating an Oracle Database —> Understanding Initialization Parameters —> Sample Initialization Parameter File 修改如下：

修改里面的db_name和controlfile

删除其他内存参数:db_block_buffers、shared_pool_size,LOG_BUFFER

增加sga_max_size和sga_target均为300M

control_files = (/u01/app/oracle/oradata/PROD/Disk1/control01.ctl, /u01/app/oracle/oradata/PROD/Disk2/control02.ctl, /u01/app/oracle/oradata/PROD/Disk3/control03.ctl) db_name = PROD

log_archive_dest_1 = \TION=/home/oracle/arch\log_archive_dest_state_1 = enable db_block_size = 8192 undo_management = AUTO undo_tablespace = undotbs compatible = 10.2.0 sga_target = 300M sga_max_size = 300M

建立密码文件orapwPROD

[oracle@rac1 dbs]$ orapwd file=orapwPROD password=oracle entries=20

启动SQLPLUS

[oracle@rac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 16:08:29 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance.

创建SPFILE

SQL> create spfile from pfile; File created.

启动到nomount

SQL> startup nomount ORACLE instance started.

Total System Global Area 314572800 bytes Fixed Size 1219184 bytes Variable Size 104859024 bytes Database Buffers 205520896 bytes Redo Buffers 2973696 bytes SQL>

在Gedit中编辑创建数据库语句，参考联机文档Administrator's Guide —> 第二章Creating an Oracle Database —> Step 7: Issue the CREATE DATABASE Statement [oracle@rac1 scripts]$ touch createPROD.sql [oracle@rac1 scripts]$ gedit createPROD.sql CREATE DATABASE PROD

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/Disk1/redo101.log','/u01/app/oracle/oradata/PROD/Disk2/redo102.log') SIZE 100M, GROUP 2 ('/u01/app/oracle/oradata/PROD/Disk1/redo201.log','/u01/app/oracle/oradata/PROD/Disk2/redo202.log') SIZE 100M, GROUP 3 ('/u01/app/oracle/oradata/PROD/Disk1/redo301.log','/u01/app/oracle/oradata/PROD/Disk2/redo302.log') SIZE 100M

MAXLOGFILES 200 MAXLOGMEMBERS 5 MAXLOGHISTORY 200 MAXDATAFILES 100 MAXINSTANCES 2

CHARACTER SET AL32UTF8

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 TEMPORARY TABLESPACE temp

TEMPFILE '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf' SIZE 100M REUSE

UNDO TABLESPACE undotbs

DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE 2G; 到此，手动建库已完成。

Section 1: 数据库和网络配置

1. Database Setup and Undo Management

1.1 Run the minimum required scripts to complete the basic configuration of PROD database .

@?/rdbms/admin/catalog.sql和@?/rdbms/admin/catproc.sql

1.2 Set up automatic undo management in the PROD database to support the following requirements :

1.2.1 Avoid ORA-01555 Snapshot too old errors for queries running up to 90 minutes on

average .

1.2.2 The number of concurrent OLTP users will be approximately 120 during normal business hours .

1.2.3 The number of concurrent batch processes that will run in the evenings and weekings will be approximately 12 to 15 . 更改参数设置

Alter system set undo_retention=5400(1.2.1); (show parameter unto) 使以上参数生效：

Alter tablespace undotbs retention guarantee(1.2.1);

(select tablespace_name,retention from dba_tablespaces;)

2. Server-side Network Configuration

2.1. Create a listener using the default listener name .

2.1.1 The TCP/IP protocol will be used for all connections.Use the machine name (not the IP address) for host. 2.1.2 This listener will listener on the default port.

2.1.3 Database: PROD and EMREP (created later) will be serviced by this listener. SID_LIST_LISTENER = (SID_LIST = (SID_DESC =

(SID_NAME =emrep )

(ORACLE_HOME = /oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) )

LISTENER =

(DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )

2.2 Add a second listener,named LSNR2,which will listen on port 1526,Configure this listener to support only automatic instance registrations.

2.2.1 Set up the PROD instance to automatically register with the LSNR2. #SID_LIST_LSNR2 = # (SID_LIST = # (SID_DESC =

# (SID_NAME = PROD )

# (ORACLE_HOME = /oracle/product/10.2.0/db_1) # ) # )

LSNR2 =

(DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1526)) ) )

2.3 Start both listeners. LSNR_2 =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1526)) )

(CONNECT_DATA =

(SERVICE_NAME = LSNR_2) ) )

需要在tnsnames.ora里配置： prod_2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1526))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521)) –将dispatcher也注册到1521的监听上

)

alter system set local_listener=?prod_2?(2.2.1) alter system regitster(2.2.1) lsnrctl start lsnrctl start lsnr2

3. Shared Server Configuartion

3.1 Configure the PROD database to support up to 300 sessions,reserving 100 for dedicated connection.

SQL> alter system set sessions=300 scope=spfile ;

SQL> alter system set shared_server_sessions=200 scope=both; 3.2 Configure the PROD database to support. 3.2.1 Default of 3 TCP dispatchers

SQL>alter system set dispatchers=\TCHERS=3)\scope=both;

alter system set dispatchers=\alter system set dispatchers=\

* LISTENER参数和local_listener参数都可以设定pmon进程将把dispatcher注册到哪个listener上去，但是dispatchers参数的子参数LISTENER优先于local_listener的配

置。 3.2.2 Maximum of 10 dispatchers

SQL> alter system set max_dispatchers=10 scope=both;

3.3.Configure the PROD database to support: 3.3.1 Minimum of 10 shared server processes

SQL> alter system set shared_servers=10 scope=both;

3.3.2 Maximum of 30 shared server processes

SQL> alter system set max_shared_servers=30 scope=both;

4. Client-side Network configuartion

4.1. Create the client-side network configuration files providing connect descriptors to your databases using local naming and easy connect methods. 4.1.1 The prod alias should connect to the PROD instance using the default listener and always use a dedicated server connection.

prod =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521)) )

(CONNECT_DATA =

(SERVER = DEDICATED) (SERVICE_NAME = PROD) ) )

4.1.2 The prod_s alias should connect to the PROD instance using LSNR2 and use a shared server connection.

prod_s =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1526)) )

(CONNECT_DATA = (SERVER = SHARED)

(SERVICE_NAME = PROD) ) )

*******************************************

用户通过prod_s连接上来后，查询session目前连接的结果如下： SYS>select sid, server from v$session;

SID SERVER ---------- ---------

277 DEDICATED 278 NONE

279 DEDICATED 281 DEDICATED

NONE就代表了通过dispatcher连接上来的session,当前没有执行会话，如果执行了

会话，就会变为shared。

4.2. The racdb alias should connect to the RACDB service (created later) with a dedicated server connection. 4.2.1 The RACDB service will be running on your RAC Cluster.

racdb =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521)) )

(CONNECT_DATA =

(SERVICE_NAME = RACDB) ) )

4.3. The emrep alias should connect to the EMREP instance instance (created later) with a dedicated server connection.

emrep =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521)) )

(CONNECT_DATA =

(SERVICE_NAME = EMREP) ) )

5. Tablespace Creation and Configuration

Note:Tablespaces must be named as specified in each task to receive credit .

5.1 Create a temporary tablespace group that contains two(2) temporary tablespaces to support batch processing.the creation of large indexes, and analyzing tables.Use the following specifications: 5.1.1Temporary tablespace group named TEMP_GRP containing temporary tablespaces TEMP1 and TEMP2.

create temporary tablespace temp1

tempfile '/oracle/oradata/temp1_01_grp.dbf' size 20m AUTOEXTEND ON MAXSIZE UNLIMITED tablespace group TEMP_GRP ;

create temporary tablespace temp2

tempfile '/oracle/oradata/temp2_01_grp.dbf' size 20m AUTOEXTEND ON MAXSIZE UNLIMITED tablespace group TEMP_GRP ;

5.1.2 Make TEMP_GRP the default temporary tablespace for all new users.

alter database default temporary tablespace TEMP_GRP; (SQL> select * from dba_tablespace_groups;)

5.2 Create a permanent tablespace to store sample test data.Use the following specifications: 5.2.1 Tablespace name of EXAMPLE 5.2.2 Inital datafile size of 400MB with the file expected to grow to 4TB. 5.2.3 Initial extent size of 1MB 5.2.4 Next extent size of 1MB

-- drop tablespace EXAMPLE including contents and datafiles ; CREATE BIGFILE TABLESPACE example

DATAFILE '/oracle/oradata/example01.dbf' SIZE 400M AUTOEXTEND ON NEXT 1M MAXSIZE 4T

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ;

5.3 Create a permanent tablespace to store indexes.Use the following specifications: 5.3.1 Tablespace name of INDX 5.3.2 File size of 40MB

create tablespace INDX

datafile '/oracle/oradata/index01.dbf' size 40m AUTOEXTEND ON ;

5.4 Create a permanent tablespace to store data collected from various Oracle tools.Use the following specifications: 5.4.1 Tablespace name of TOOLS 5.4.2 File size of 10MB

-- drop tablespace TOOLS including contents and datafiles ; create tablespace TOOLS

datafile '/oracle/oradata/tools01.dbf' size 10m AUTOEXTEND ON ;

5.5 Create a default permanent tablespace using the following specifications: 5.5.1 Tablespace name of USERS 5.5.2 File size of 48MB 5.5.3 Initial extent size of 4MB 5.5.4 Next extent size of 4MB

-- drop tablespace USERS including contents and datafiles ; create tablespace USERS

datafile '/oracle/oradata/users01.dbf' size 48m AUTOEXTEND ON NEXT 4m

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M SEGMENT SPACE MANAGEMENT AUTO ; alter database default tablespace USERS ;

5.6 Create a permanent tablespace for storing segments associated with online transaction processing high insert rates.Due to the potential high volume of concurrent inserts,every effort should be taken to reduce contention for each of the tables that will be stored in this tablespace.Use the following specifications: 5.6.1 Tablespace name of OLTP 5.6.2 File size of 48MB 5.6.3 Initial extent size of 2MB 5.6.4 Next extent size of 2MB

-- drop tablespace OLTP including contents and datafiles ; create tablespace OLTP

datafile '/oracle/oradata/oltp01.dbf' size 48m AUTOEXTEND ON NEXT 2M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M SEGMENT SPACE MANAGEMENT AUTO ;

6. Log File Management

6.1. Due to the expected high volume of transactions,the database should have the following configuration: 6.1.1 A minimum of 5 redo log groups. 6.1.2 Each redo log group should not be a single point of failure 6.1.3 File size of 100MB 6.1.4 Specify the location such that it minimizes contention and reduces the risk of a single point of failure in case of disk drive failure.

--alter database drop logfile group 1; --alter database drop logfile group 2; --alter database drop logfile group 3; --alter database add logfile group 1 ('

/oracle/oradata/redo11.log','/oracle/oradata/redo12.log') size 100m; --alter database add logfile group 2 (

'/oracle/oradata/redo21.log','/oracle/oradata/redo22.log') size 100m; --alter database add logfile group 3 ('

/oracle/oradata/redo31.log','/oracle/oradata/redo32.log') size 100m;

Alter database add logfile member ?/oracle/oradata/redo12.log? to group 1; Alter database add logfile member ?/oracle/oradata/redo22.log? to group 2; Alter database add logfile member ?/oracle/oradata/redo32.log? to group 3; alter database add logfile group 4 (

'/oracle/oradata/redo41.log','/oracle/oradata/redo42.log') size 100m; alter database add logfile group 5 (

'/oracle/oradata/redo51.log','/oracle/oradata/redo52.log') size 100m; 6.2. Triplex the controlfile to minimize recovery in case of disk drive failure.

Alter database backup controlfile to trace;

7. Schema Creation

7.1. As user SYS,run the script /home/oracle/scripts/create_bishhr.sql,Ignore any errors concerning OE.But do not ignore any other errors.

@/home/oracle/scripts/create_bishhr.sql Hr/users/temp/oracle/ ?/rdbms/log/

Conn system/oracle

@?/sqlplus/admin/pupbld.sql

8. Schema Statistics and Parameter File Configuration

8.1. Compute statistics for the various schemas in the database as necessary for use with cost based optimization.

8.2. Investigate the parameter file for reasonable sizes for each parameter listed.Add additional parameters as you deem necessary to support an optimal database environment.In addition,modify or add the following listed parameters:

UTL_FILE_DIR=('/home/oracle','/home/oracle/temp','/home/oracle/scripts')

Note: Appalications that use Oracle 10g features will be running therefore,ensure the database and instance are appropriately configured.

exec dbms_stats.gather_database_stats(degree=>5);

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

9. Database Backup and Availability

9.1. Backup the database to prepare for complete recovery under all circumstances. 9.2. OPEN the database . rman target / run {

backup full database format '/oracle/bak/full_%U.bak'; backup archivelog all format '/oracle/bak/arc_%U.bak'; copy current controlfile to '/oracle/bak/control_bak'; }

至此，数据库与网络配置部分完成

Section 2: Grid Control安装配置

1.Grid Control Installation

1.1 Create a database for your repository

1.1.1 Use EMREP for database name and instance name 1.1.2 on your even machine

1.2 Install Grid Control on your Management Server

图解：

DBCA建库

然后开始安装GC

NEXT 根据提示信息完成相应的小操作即可。

然后在ODD机上下载 agent

22K的文件

在考试环境中需要我们手动把PROD数据库添加到GC里面

1.4 Create a Grid Control super user called EMADMIN with password EMADMIN

右上角Setup->Administrators->create

2.Using Grid Control

2.1. Using Grid Control,change the PGA_AGGREGATE_TARGET on your PROD server to 500MB so that it will revert when the instance is restarted.

targets-> database-> administration-> Database Configuration-> All Initialization Parameters-> PGA-> Aggregate PGA Target 500M ->Apply

2.2. Using Grid Control,configure the instance to ensure that it will take up to five(5) minutes to recover your instance following an instance failure.

targets-> database-> administration-> Database Configuration-> All Initialization Parameters-> MTTR

2.3. Configure an alert on the SYSTEM tablespace of the PROD database.The alert should register as a warning at 87% full and critical at 95% full.

Metric and Policy Settings->Tablespace Space Used (%) ->edit-> SYSTEM

2.4. Setup notifications to be sent to the email address'dba@ocm.com'Notification messages should be sent to this address at anytime. 右上角Preferences

2.5. Using Grid Control,create a new tablespace in the PROD database called REGISTRATION 2.5.1 Create with on 90MB datafile

2.5.2 Make sure this datafile can grow to 120MB if need be

2.5.3 Configure the tablespace for optimal block space utilization

targets-> database-> administration->Tablespaces-> create-> REGISTRATION ->add ->continue->ok

3.Implementing Schedules and Jobs

3.1. Using Grid Control,create a schedule for the PROD database. 1.1 Call this schedule DAILYREBUILD 1.2 Configure it to run at 2PM every day

targets-> database-> administration-> Schedules-> create link -> DAILYREBUILD -> ok

3.2. Create a program for the PROD database called EMP_IND_REBUILD that rebuilds all indexes on the HR.EMPLOYEES table.

targets-> database-> administration-> Programs -> create -> EMP_IND_REBUILD -> ok

3.3. Create a window that utilites the DIALYREBUILD schedule and SYSTEM_PLAN resource manager plan.

targets-> database -> administration -> Oracle Scheduler -> Scheduler Windows -> Use an existing schedule ->HR. DAILYREBUILD -> ok

3.4. Create a job called REBUILD_JOB that uses the DAILYREBUILD schedule and EM_IND_REBUILD program.

targets-> database-> administration-> Oracle Scheduler -> Jobs -> create ->name REBUILD_JOB -> Command (change command type) EMP_IND_REBUILD

-> Schedule Schedule Type (Use Per-defined Schedule) DAILYREBUILD -> ok

至此，GC部分完成。

Section 3: 数据库备份恢复

1.Create an RMAN Catalog

1.1 Create a tablespace in your EMREP database called RC_DATA

1.1.1 Make it locally managed

1.1.2 Create it with one datafile of size 100MB Sqlplus sys/oracle@emrep as sysdba SQL>create tablespace RC_DATA Datafile ?/home/oracle/oradata/PROD/rc_data01.dbf? size 100M Autoextend on next 10M

Extent management local Segment space management auto;

1.2. Create a user named RC_ADMIN with password RC_ADMIN in your EMREP

1.2.1 The user must have a default tablespace of RAC_DATA 1.2.2 Give the user the ability to manage a Recovery Catalog Sqlplus sys/oracle@emrep as sysdba

SQL>create user RC_ADMIN identified by RC_ADMIN

default tablespace RC_ADMIN;

SQL>grant connect,resource,recovery_catalog_owner to RC_ADMIN; 1.3. Create a Recovery Catalog

1.3.1 Create the catalog in the EMREP database conned by RC_ADMIN Rman catalog RC_ADMIN/RC_ADMIN@emrep RMAN>create catalog tablespace RC_DATA; 1.3.2 Register the PROD database with the catalog

Rman target sys/oracle@prod catalog RC_ADMIN/RC_ADMIN@emrep RMAN>register database; RMAN> resync catalog;

2.Using RMAN

2.1. Configure RMAN options for the PROD database

2.1.1 Turn backup optimization on

RMAN>CONFIGURE BACKUP OPTIMIZATION ON;

2.1.2 Set your default channel to write to /home/oracle/backup (you may have to create this directory)

RMAN>CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT

?/home/oracle/bakup/%d_%T_%U.bak?;

2.1.3 Turn on controlfile autobackup to write to /home/oracle/backup/control (you may have to create this directory)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT

FOR DEVICE TYPE DISK TO '/home/oracle/backup/control/%F'

2.1.4 Configure a reteation window of 7 days

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; 2.2. Perform a backup

2.2.1 Perform a backup using your default channel,with compreesion 2.2.2 Include all datafiles in the backup

2.2.3 Include your current control file and spfile

2.2.4 Include all archive logs.then remove the originals RMAN>backup as compressed bakupset database include current controlfile plus archivelog delete all input;

3. Flashback Database

3.1. Turn on Flashback Database

3.1.1 Configure a flash recovery area of 4GB

3.1.2 Put your flash recovery area in /home/oracle/flash (you may have to create this directory)

SQL>alter system set db_recovery_file_dest_size=4G scope=both;

SQL>alter system set db_recovery_file_dest='/home/oracle/flash' scope=both; SQL>shutdown immediate; SQL>startup mount;

SQL>alter database flashback on; (SQL>alter database archivelog;) 3.2. your database open for review

SQL>alter database open;

至此，数据库备份恢复部分完成。

Section 4: 数据仓库管理

1. Fast Refreshable Materialized View

1.1 Using the query found in the mview1.txt text file.create a fast refreshable materialized view named PROD_MV in the SH schema.

SELECT time_id,prod_subcategory,SUM(unit_cost), COUNT(unit_cost),COUNT(*) FROM costs c,products p where c.prod_id=p.prod_id

GROUP BY time_id,prod_subcategory;

1.先创建表costs、products的 Materialized View Log

选择schema和表以后点击这里

2.创建Materialized View

填写题目中给出的SQL语句

按照题目要求指定刷新方式

CREATE MATERIALIZED VIEW LOG ON costs

WITH SEQUENCE

(prod_id, unit_cost, time_id, channel_id, promo_id, unit_price) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON products WITH SEQUENCE, ROWID,PRIMARY (/*所有字段*/)

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW PROD_MV PCTFREE 0 TABLESPACE demo

STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0) BUILD IMMEDIATE REFRESH FAST

ENABLE QUERY REWRITE

AS SELECT time_id,prod_subcategory,SUM(unit_cost), COUNT(unit_cost),COUNT(*) FROM costs c.products p where c.prod_id=p.prod_id

GROUP BY time_id,prod_subcategory;

2.Creating an Updatable Materialized View

2.1. Using the HR.EMPLOYEES table in the PROD database. create an updatable materialized view in the EMREP database named EMP_UPD_MV consisting of the following columns: EMPLOYEE_ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER,SALARY.

1.创建database link

create database link lk_prod connect to hr identified by hr using 'prod'; 2.验证database link是否可用 select

EMPLOYEE_ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER,SALARY

from

hr.employees@lk_prod; 3.创建Updatable Materialized View

按照题目要求指定刷新方式

3.Oracle_Loader External Tables

3.1. In the scripts directory. you will find prod_master.dat and prod_master.ctl.Using the information found in these files.create and external table named PROD_MASTER in the SH schema of the PROD database.

CREATE TABLE sh.prod_master

(根据prod_master.ctl定义表 )

ORGANIZATION EXTERNAL (

TYPE ORACLE_LOADER DEFAULT DIRECTORY dir1 LOCATION ('prod_master.dat') )

PARALLEL

REJECT LIMIT UNLIMITED;

4. Oracle_Datapump External Table

4.1. Create an external table called COUNTRIES_EXT in the PROD database owned by SH. containing the data from the COUNTRY_ID,COUNTRY_NAME,and COUNTRY_REGION columns of the SH.COUNTRIES table.

1.创建目录

Create directory dir1 as '/home/oracle/'; Grant read,write on directory dr1 to sh; 2.创建外部表

CREATE TABLE sh.COUNTRIES_EXT ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY dir1

LOCATION ('COUNTRIES_EXT.dat') )

PARALLEL

REJECT LIMIT UNLIMITED

as

select COUNTRY_ID,COUNTRY_NAME,COUNTRY_REGION from SH.COUNTRIES;

4.2. Create another external table called COUNTRIES_EXT in the EMREP database owned by SYSTEM.The source of the data is the external file(s) created in the previous step.

CREATE TABLE sh.COUNTRIES_EXT (

COUNTRY_ID NUMBER,

COUNTRY_NAME VARCHAR2(40), COUNTRY_REGION VARCHAR2(20) )

ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY dir1

LOCATION ('COUNTRIES_EXT.dat') )

PARALLEL

REJECT LIMIT UNLIMITED;

至此，数据仓库管理部分完成。

Section 5: 数据库管理

Transportable Tablespace

1. Use the import utility to import all of the objects contained in the sst.dmp file into the OLTP_USER schema in the PROD database.(The exported user was SST.)

2. Transport a copy of the OLTP tablespace from the PROD database to the EMREP database. After you have completed the task.the OLTP tablespace should be available for both reading and writing in both databases.All of the objects owned by the user OLTP_USER in the PROD database should be present in the EMREP database after the tablespace is transported.

参考文档：Administrator's Guide=> 8 Managing Tablespaces=> Transporting Tablespaces

Between Databases

题目说明：1.用Imp将sst.dmp文件的所有对象导入到OLTP_USER schema中（导出的用户是sst）

2.将PROD数据库中的OLTP表空间传输到EMREP数据库中，传输完成后，OLTP表空间必须在两个数据库均可读可写，所有在PROD数据库OLTP_USER用户下的对象必须在EMREP数据库中存在。

准备工作：

生成sst.dmp文件：

[oracle@rac1 scripts]$ export ORACLE_SID=PROD [oracle@rac1 scripts]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 10:17:58 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> create user sst identified by sst default tablespace oltp; User created.

SQL> grant dba to sst; Grant succeeded.

SQL> conn sst/sst Connected.

SQL> create table t as select * from all_objects; Table created.

[oracle@rac1 ~]$ exp sst/sst@prod file=sst.dmp

Export: Release 10.2.0.1.0 - Production on Fri Mar 9 10:26:24 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...

. exporting pre-schema procedural objects and actions . exporting foreign function library names for user SST . exporting PUBLIC type synonyms . exporting private type synonyms

. exporting object type definitions for user SST About to export SST's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions

. about to export SST's tables via Conventional Path ...

. . exporting table T 9612 rows exported . exporting synonyms . exporting views

. exporting stored procedures . exporting operators

. exporting referential integrity constraints . exporting triggers . exporting indextypes

. exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues

. exporting refresh groups and children . exporting dimensions

. exporting post-schema procedural objects and actions . exporting statistics

Export terminated successfully without warnings.

SQL> conn / as sysdba Connected.

SQL> drop user sst cascade; User dropped.

步骤：

1.创建oltp_user用户

SQL> create user oltp_user identified by oracle account unlock; User created.

2.给OLTP_USER赋予基本的权限

SQL> grant connect,resource to oltp_user; Grant succeeded.

3. 使用imp导入sst.dmp到oltp_user schema

[oracle@rac1 ~]$ imp system/oracle@prod file=sst.dmp buffer=100000 fromuser=sst touser=oltp_user

Import: Release 10.2.0.1.0 - Production on Fri Mar 9 10:34:51 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SST, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing SST's objects into OLTP_USER

. . importing table \ 9612 rows imported Import terminated successfully without warnings.

4.检查OLTP表空间是否是自包含表空间（要被传输的表空间中的对象没有引用被传输的表空间之外的对象，这种表空间就是自包含表空间） [oracle@rac1 ~]$ sqlplus sys/oracle@prod as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 10:36:17 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> exec dbms_tts.transport_set_check('OLTP',true); PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; no rows selected

[oracle@rac1 ~]$ mkdir dir

5.创建传输集的导出目录

SQL> create directory dir as '/home/oracle/dir'; Directory created.

6.将OLTP表空间置为只读模式 SQL> alter tablespace oltp read only; Tablespace altered.

7.生成传输表空间的传输集 [oracle@rac1 ~]$ expdp system/oracle@prod dumpfile=oltp.dmp directory=dir TRANSPORT_TABLESPACES=oltp;

Export: Release 10.2.0.1.0 - Production on Friday, 09 March, 2012 10:43:44

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

Starting \ system/********@prod dumpfile=oltp.dmp directory=dir TRANSPORT_TABLESPACES=oltp Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table \****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /home/oracle/dir/oltp.dmp

Job \

8.在EMREP数据库中创建用户oltp_user

[oracle@rac1 ~]$ export ORACLE_SID=EMREP [oracle@rac1 ~]$ sqlplus sys/oracle@emrep as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 10:45:09 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> create user oltp_user identified by oracle; User created.

9.赋予oltp_user基本的权限 SQL> grant dba to oltp_user; Grant succeeded.

10.创建传输集导入的逻辑目录

SQL> create directory dt_ws5 as '/home/oracle/dir'; Directory created.

11.检查源端和目标端的BLOCK_SIZE是否一致 源端：

SQL> select block_size from dba_tablespaces where tablespace_name='OLTP';

BLOCK_SIZE ---------- 8192 目标端：

SQL> show parameter db_block_size

NAME TYPE ------------------------------------ ---------------------- VALUE

------------------------------

db_block_size integer 8192

如不一致可在目标端通过诸如alter system set db_4k_cache_size=8M;语句来修改

12．将OLTP表空间对应的数据文件拷贝到EMREP数据库中 [oracle@rac1 ~]$ cp /u01/app/oracle/oradata/PROD/Disk1/oltp1.dbf /u01/app/oracle/oradata/EMREP/

13.导入传输集到EMREP数据库中，使OLTP表空间注册到EMREP数据库中

[oracle@rac1 ~]$ impdp system/oracle@emrep dumpfile='oltp.dmp' directory=dt_ws5 TRANSPORT_DATAFILES='/u01/app/oracle/oradata/EMREP/oltp1.dbf'

Import: Release 10.2.0.1.0 - Production on Friday, 09 March, 2012 11:04:00

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

Master table \Starting \ system/********@emrep dumpfile=oltp.dmp directory=dt_ws5 TRANSPORT_DATAFILES=/u01/app/oracle/oradata/EMREP/oltp1.dbf Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job \

14.将OLTP表空间在PROD和EMREP数据库中均置为可读可写状态 [oracle@rac1 ~]$ sqlplus sys/oracle@emrep as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 11:05:09 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> alter tablespace oltp read write; Tablespace altered.

[oracle@rac1 scripts]$ sqlplus sys/oracle@prod as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 11:05:58 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> alter tablespace oltp read write; Tablespace altered.

Create Additional Buffer Cache

1. Create an additional buffer cache within the SGA of the PROD database for use with 16KB blocks.Ensure that the 16KB buffer cache will always be available in the SGA.

题目说明：在PROD数据库的SGA中创建额外的块大小为16k的buffer cache，保证它将一直在SGA中可用。

[oracle@rac1 scripts]$ sqlplus sys/oracle@prod as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 13:43:00 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> alter system set db_16k_cache_size=16M; System altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area 314572800 bytes Fixed Size 1219184 bytes Variable Size 138413456 bytes Database Buffers 171966464 bytes Redo Buffers 2973696 bytes Database mounted. Database opened.

Working with LOB Data

1. Create a new tablespace named LOB_DATA in the PROD database to store lob data and lob indexes with the following specifications:

1.1 Create 2 datafiles each in a different location. 1.2 Each file should be 64MB in size. 1.3 Block size 16KB

1.4 Determine which type of extent management would be best for lob data and configure the extents appropriately.

方法一：在Sqlplus中用命令行创建：

create tablespace LOB_DATA datafile '/u01/app/oracle/oradata/PROD/Disk1/lob_data01.dbf' size 64M

autoextend on next 2M,

'/u01/app/oracle/oradata/PROD/Disk2/lob_data02.dbf' size 64M autoextend on next 2M

extent management local uniform size 2M segment space management auto blocksize 16k;

将以上创建LOB_DATA表空间的脚本保存为lob_data.sql，然后在Sqlplus中运行lob_data.sql [oracle@rac1 scripts]$ sqlplus sys/oracle@prod as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 13:55:32 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> @lob_data.sql Tablespace created.

方法二：用GC图形界面创建：

点击OK

Manage Schema Data

1. Create a new table in the HR schema in the PROD database with the following specifications: 1.1 Table name MAGAZINE_ARTICLES 1.2 Tablespace USERS 1.3 Column names

1.3.1 AUTHOR VARCHAR2(30)

1.3.2 ARTICLE_NAME VARCHAR2(50) 1.3.3 ARTICLE_DATE DATE 1.3.4 ARTICLE_DATA CLOB

1.3.4.1 Tablespace LOB_DATA with a 16KB chunk size,initial and next extents each with a size of 2MB.

1.3.4.2 Use the nocache option and disable storage in row.

1.4 Use import to populate the HR.MAGAZINE_ARTICLES table with data from the exp_mag.dmp file.

方法一：用GC图形界面：

方法二：用Sqlplus命令行，以hr身份登录PROD，执行以下SQL语句： CREATE TABLE MAGAZINE_ARTICLES ( AUTHOR VARCHAR2(30), ARTICLE_NAME VARCHAR2(50), ARTICLE_DATE DATE, ARTICLE_DATA CLOB

)

TABLESPACE USERS LOB(ARTICLE_DATA) STORE AS (TABLESPACE LOB_DATA STORAGE (INITIAL 2m NEXT 2m) CHUNK 16384 NOCACHE DISABLE STORAGE IN ROW);

生成exp_mag.dmp文件：

SQL> insert into hr.magazine_articles values('prince','MY BOOK',sysdate,'I am princeOracle9iOracle9iOracle9i'); 1 row created.

SQL> insert into hr.magazine_articles values('prince360','prince',sysdate,'ABCDEFG'); 1 row created.

SQL> commit; Commit complete.

[oracle@rac1 ~]$ expdp hr/oracle@prod directory=dir dumpfile=exp_mag.dmp tables=MAGAZINE_ARTICLES

Export: Release 10.2.0.1.0 - Production on Friday, 09 March, 2012 15:09:59

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Starting \ hr/********@prod directory=dir dumpfile=exp_mag.dmp tables=MAGAZINE_ARTICLES Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 6 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported \ 5.984 KB 2 rows Master table \

****************************************************************************** Dump file set for HR.SYS_EXPORT_TABLE_01 is: /home/oracle/exp_mag.dmp

Job \

或者：

[oracle@rac1 scripts]$ exp hr/oracle@prod file=exp_mag.dmp tables=MAGAZINE_ARTICLES

Export: Release 10.2.0.1.0 - Production on Tue Apr 10 14:22:34 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table MAGAZINE_ARTICLES 2 rows exported Export terminated successfully without warnings.

SQL> delete HR.MAGAZINE_ARTICLES; 2 rows deleted.

SQL> commit; Commit complete.

用exp_mag.dmp文件执行导入：

[oracle@rac1 ~]$ impdp hr/oracle@prod directory=dir dumpfile=exp_mag.dmp tables=MAGAZINE_ARTICLES

Import: Release 10.2.0.1.0 - Production on Friday, 09 March, 2012 15:17:23

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

Master table \Starting \ hr/********@prod directory=dir dumpfile=exp_mag.dmp tables=MAGAZINE_ARTICLES Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39151: Table \exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Job \

此时发现表已存在，无法导入，于是在导入时加上一个参数 CONTENT=data_only 在导入的时候将HR用户退出Sqlplus，否则导入时会卡住不动。

[oracle@rac1 ~]$ impdp hr/oracle@prod directory=dir dumpfile=exp_mag.dmp tables=MAGAZINE_ARTICLES CONTENT=data_only

Import: Release 10.2.0.1.0 - Production on Friday, 09 March, 2012 15:46:03

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

Master table \Starting \ hr/********@prod directory=dir dumpfile=exp_mag.dmp tables=MAGAZINE_ARTICLES CONTENT=data_only Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported \ 5.984 KB 2 rows Job \

至此，导入成功。

或者：

[oracle@rac1 scripts]$ imp hr/oracle@prod file=exp_mag.dmp tables=MAGAZINE_ARTICLES ignore=y

Import: Release 10.2.0.1.0 - Production on Tue Apr 10 14:32:40 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing HR's objects into HR . importing HR's objects into HR

. . importing table \ 2 rows imported Import terminated successfully without warnings.

验证：

SQL> select count(*) from MAGAZINE_ARTICLES;

COUNT(*) ---------- 2

2. Create a new table in the HR schema in the PROD database with the following specifications: 2.1 Table name ORACLE9I_REFERENCES 2.2 Tablespace USERS 2.3 Table structure:

2.3.1 ORACLE9I_ARTICLE ROWID

2.3.2 INSERT_TIME TIMESTAMP WITH LOCAL TIME ZONE

3. For any row in the HR.MAGAZINE_ARTICLES table that contains three or more references to Oracle9i insert the corresponding rowid and a timestamp for the time that it was inserted into the ORACLE9I_REFERENCES table.

CREATE TABLE \(

\

\ TIMESTAMP WITH LOCAL TIME ZONE )

TABLESPACE \

将以上脚本保存为oracle9i_references.sql，然后在Sqlplus中执行： SQL> @oracle9i_references.sql Table created.

SQL> insert into HR.ORACLE9I_REFERENCES select ROWID,SCN_TO_TIMESTAMP(ORA_ROWSCN) from HR.MAGAZINE_ARTICLES where instr(AUTHOR||ARTICLE_NAME||ARTICLE_DATE||ARTICLE_DATA,'Oracle9i',1,3)>0;

1 row created.

INSTR(源字符串 ，要查找的字符串，从第几个字符开始， 要找到第几个匹配的序号) 例如：

SQL> select instr('Oracle9iOracle9iOracle9iOracle9i','Oracle9i',1,3) from dual;

INSTR('ORACLE9IORACLE9IORACLE9IORACLE9I','ORACLE9I',1,3) --------------------------------------------------------

17 返回的是第三个Oracle9i开始的位置

SQL> SELECT * FROM HR.ORACLE9I_REFERENCES;

ORACLE9I_ARTICLE ------------------ INSERT_TIME

--------------------------------------------------------------------------- AAAClkAAAAAACBRAAA 30-MAR-12 05.07.40.528266 PM

Partitioning

1. Create 5 new tablespaces in the PROD database as follows:

1.1 Use the name DATA01, DATA02, DATA03, DATA04 and DATA05. 1.2 Spread the datafiles across different disk directories. 1.3 Each file should be 250MB in size. 1.4 Use uniform extents of 4MB. 1.5 Block size should be 16KB

create tablespace data01 datafile '/u01/app/oracle/oradata/PROD/Disk1/data01.dbf' size 250M autoextend on next 10M

extent management local uniform size 4M segment space management auto blocksize 16k;

create tablespace data02 datafile '/u01/app/oracle/oradata/PROD/Disk2/data02.dbf' size 250M autoextend on next 10M

extent management local uniform size 4M segment space management auto blocksize 16k;

create tablespace data03 datafile '/u01/app/oracle/oradata/PROD/Disk3/data03.dbf' size 250M autoextend on next 10M

extent management local uniform size 4M

segment space management auto blocksize 16k;

create tablespace data04 datafile '/u01/app/oracle/oradata/PROD/Disk4/data04.dbf' size 250M autoextend on next 10M

extent management local uniform size 4M segment space management auto blocksize 16k;

create tablespace data05 datafile '/u01/app/oracle/oradata/PROD/Disk5/data05.dbf' size 250M autoextend on next 10M

extent management local uniform size 4M segment space management auto blocksize 16k;

将以上脚本保存为partition.sql，并在Sqlplus中执行： [oracle@rac1 scripts]$ sqlplus sys/oracle@prod as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 16:08:02 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> @partition.sql

Tablespace created.

Tablespace created.

Tablespace created.

Tablespace created.

Tablespace created.

2. Create a partitioned table named SALES_HISTORY in the SH schema in the PROD database. Use the following specifications:

2.1 The column names and definitions will be the same as the OLTP_USER.SALES table. 2.2 partition the table into 5 different partitions on the SDATE column using the following specifications:

2.2.1 Partition P1 will contain data for 1998 and should be placed in the DATA01 tablespace

本文来源：https://www.bwwdw.com/article/j4so.html