ocm考试大纲

原创 charles 2021-03-19

6087

考题
OCM考试一共有9个section,两天完成考试过程

第一天
section 0:
手动建库 45分钟不计分
section 1:
配置数据库和网络 120分钟
中午休息
section 2:
GC搭建 120分钟
section 3:
备份恢复 60分钟
section 4:
数据仓库 90分钟

第二天
section 5:
数据库管理 120分钟
section 6:
性能管理 120分钟
section 7:
搭建RAC 75分钟
section 8:
搭建DG 60分钟

每个人面前会有两台机器，一台称为奇数机（ODD），一台称为偶数机（EVEN），
注意，考题上也会这样表述的，要你在ODD机器上做什么或者在 EVEN 机器上做什么，
偶数还是奇数是依靠机器的hostname最后一位或者两位数字来定的，

八、datagurad

1.在奇数机器上创建physical standby

2.添加standby logfile ，使用lgwr async方式

3.切换到standby ，运行一个脚本，然后在切换回，运行一个脚本

4.read only打开standby
section 1

Database Setup and Undo Management
1.1 Run the minimum required scripts to complete the basic configuration of the PROD database.
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 minues on average.
1.2.2 The number or concurrent OLTP users will be approximately 120 during normal business hours.
1.2.3 The number or concurrent batch processes that will run in the evenings will approximately 12 to 15

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 listen on the default port.
2.1.3 Databases: PROD and EMREP(created later) will be serviced by this listener.
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.
2.2.2 TRACE LSNR2
2.3 Start both listeners.

Shared Server Configuration
3.1 Configure the PROD database to support up to 300 sessions, reserving 100 for dedicated connections.
3.2 Configure the PROD database to support.
3.2.1 Default of 3 TCP dispatchers
3.2.2 Maximum of 10 dispatchers
3.3 Configure the PROD database to support:
3.3.1 Minimum of 10 shared server processes
3.3.2 Maximum of 30 shared server processes

Client-side Network configuration
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.
4.1.2 The prod_s alias should connect to the PROD instance using LSNR2 and use a shared server connection.
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
4.3 The emrep alias should connect to the EMREP instance (created later) wiht a dedicated server connection.

新增:
先链接1526,如果1526监听不可用,使用1521

mytns1=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.com)(PORT=1526))
(CONNECT_DATA=
(SERVICE_NAME=PROD)(SERVER=SHARED)))
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.com)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=PROD)(SERVER=dedicated)
)
)
)

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.1 Temporary tablespace group named TEMP_GRP containing temporary tablespaces TEMP1 and TEMP2.
5.1.2 Make TEMP_GRP the default temporary tablespace for all new users.

create temporary tablespace temp1 tempfile ‘/home/oracle/app/oracle/oradata/prod/temp11.dbf’ size 10m autoextend on maxsize 1g extent management local uniform size 1m tablespace group TEMP_GRP;
create temporary tablespace temp2 tempfile ‘/home/oracle/app/oracle/oradata/prod/temp12.dbf’ size 10m autoextend on maxsize 1g extent management local uniform size 1m tablespace group TEMP_GRP;
alter database default temporary tablespace TEMP_GRP;

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 Initial datafile size of 400MB with the file expectd to grow to 4TB.
5.2.3 Initial extent size of 1MB
5.2.4 next extent size of 1MB

create bigfile tablespace example datafile ‘’ size 400m autoextend on maxsize 4t extent management local uniform size 1m;

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 ‘’ size 40m;

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

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

5.6 Create a permanent tablespace for storing segments associated with online transaction processing with 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

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 driver failure
6.2 Triplex the controlfile to minimize recovery in case of disk drive failure.

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

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: Applications that use Oracle 10g features will be running therefore,
ensure the database and instance are appropriately configured.

exec dbms_stats.gather_database_stats();

Database Backup and Availability
9.1 Backup the database to prepare for complete recovery under all circumstances.
9.2 Open the database.

section 2
section 2 :

Grid Control Installation
1.1 Create a database for your repository on youre Database Server
1.1.1 Use EMREP for database name and instance name.
1.1.2 Label Security is the only option needed with the EMREP database.
1.2 Install Grid Control on your Management Server using the installation media found under /stage/gc/Disk1.
1.3 Deploy an Oracle Management Agent on your Database Server
1.4 Create a Grid Control super user called EMADMIN with the password EMADMIN

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.

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.

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

2.4 Setup notifications to be sent to the email address ‘dba@ocm.com’ Notification messages
should be sent to this address at anytime.

2.5 Using Grid Control, create a new tablespace in then PROD database called REGISTRATION
2.5.1 Create with one 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

Implementing Schedules and Jobs
3.1 Using Grid Control, create a schedule for the PROD database.
3.1.1 Call this schedule DAILYREBUILD
3.1.2 Configure it to run at 2PM every day

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

3.3 Create a window that utilizes the DAILYREBUILD schedule and SYSTEM_PLAN resource manager plan.

OCM书中错误 49页.

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

section 3

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.

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 RC_DATA.
1.2.2 Give the user the ability to manage a Recovery Catalog.

1.3 Create a Recovery Catalog
1.3.1 Create the catalog in the EMREP database owned by RC_ADMIN
1.3.2 Register the PROD database with the catalog

Using RMAN
2.1 Configure RMAN options for the PROD database
2.1.1 Turn backup optimization on
2.1.2 Set your default channel to write to /home/oracle/backup (you may have to create this directory )
2.1.3 Turn on controlfile autobackups to write to /home/oracle/backup/control (you may have to create this directory)
2.1.4 Configure a retention window of 7 days.
2.1.5 归档进程每小时归档一次.
2.1.6 使用copy方式备份归档.

2.2 Perform a backup
2.2.1 Perform a backup using your default channel with compression
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.

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 diretory)
3.2 Leave your databases open for review.

section 4
section 4 :

Fast Refreshable Materialized View
1.1 Using the query found in the mviewl.txt text file,
create a fast refreshable materialized view named PROD_MV in the SH schema.

mviewl.txt
/* query sql
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;
*/

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.

2.2
select distinct COL1,col2 from T
建立物化视图要求快速刷新 + 查询重写.

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 an external table names PROD_MASTER in the SH schema of the PROD database.

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.

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 setp.

section 5
section 5 :
/*
export ORACLE_SID=PROD
SQL> create user sst identified by sst default tablespace oltp;
SQL> grant dba to sst;
SQL> conn sst/sst
SQL> create table t as select * from all_objects;
[oracle@vmrac1 ~]$imp sst/sst@prod file=sst.dmp
SQL> drop user sst cascade;
./import_oltp_batch.sh
*/

Transportable Tablespace
1.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 ).
1.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.

Create Additional Buffer Cache
2.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.

Working with LOB Data
3.1 Create a new tablespace named LOB_DATA in the PROD database to store lob data
and lob indexes with the following specifications:
3.1.1 Create 2 datafiles each in different location.
3.1.2 Each file should be 64MB in size
3.1.3 Block size 16KB
3.1.4 Determine which type of extent management would be best for lob data and configure the extents appropriately.

Manage Schema Data
4.1 Create a new table in the HR schema in the PROD database with the following specifications:
4.1.1 Table name MAGAZINE_ARTICLES
4.1.2 Tablespace USERS
4.1.3 Column names
4.1.3.1 AUTHOR VARCHAR2(30)
4.1.3.2 ARTICLE_NAME VARCHAR2(50)
4.1.3.3 ARTICLE_DATE DATE
4.1.3.4 ARTICLE_DATA CLOB
4.1.3.4.1 Tablespace LOB_DATA with a 16KB chunk size, initial and next extents each with a size of 2MB
4.1.3.4.2 Use the nocache option and disable storage in row.
4.1.4 Use import to populate the HR.MAGAZINE_ARTICLES table with data from the exp_mag.dmp file.

4.2 Create a new table in the HR schema in the PROD database with the following specifications:
4.2.1 Table name ORACLE9I_REFERENCES
4.2.2 Tablespace USERS
4.2.3 Table structure:
4.2.3.1 ORACLE9I_ARTICLE ROWID
4.2.3.2 INSERT_TIME TIMESTAMP WITH LOCAL TIME ZONE

4.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.

Partitioning
5.1 Create 5 new tablespaces in the PROD database as follows:
5.1.1 Use the name DATA01, DATA02, DATA03, DATA04 and DATA05.
5.1.2 Spread the datafiles across different disk directories.
5.1.3 Each file should be 250MB in size.
5.1.4 Use uniform extents of 4MB.
5.1.5 Block size should be 16KB.

5.2 Create a partitioned table named SALES_HISTORY in the SH schema in the PROD database. Use the following specifications:
5.2.1 The column names and definitions will be the same as the OLTP_USER.SALES table
5.2.2 Partition the table into 5 different partitions on the SDATE column using the following specifications:
5.2.2.1 Partition P1 will contain data for 1998 and should be placed in the DATA01 tablespace.
5.2.2.2 Partition P2 will contain data for 1999 and should be placed in the DATA02 tablespace.
5.2.2.3 Partition P3 will contain data for 2000 and should be placed in the DATA03 tablespace.
5.2.2.4 Partition P4 will contain data for 2001 and should be placed in the DATA04 tablespace.
5.2.2.5 Partition P5 will contain data for 2002 and should be placed in the DATA05 tablespace.

5.3 Run the populate_sales_hist.sql script located in the /home/oracle/scripts directory
to populate the SALES_HISTORY table with data.

5.4 Create a unique index named SALES_HISTORY_PK in the SH schema of PROD database on the SALES_HISTORY table.
Partition the index into 4 partitions with each partition containing approximately
the same amount of entries following specification:
5.4.1 Create the index to include the ORDERID column.
5.4.2 Create the index in the INDX tablespace.
5.4.3 Create the index with parallelism degree 4.

5.5 Create an index named SALES_HISTORY_DATE_IDX in the SH schema on the SALES_HISTORY table.
Use the following specifications:
5.5.1 Create the index on the SDATE column
5.5.2 Partition the index into 5 partitions that are based on the SDATE column

5.6 Query the data in the SALES_HISTORY table to validate the use of each of the indexes.

5.7 Truncate partition P1 of the SALES_HISTORY and ensure all indexes are availble
for use both during and after the truncate command complete

Fine-Grained Auditing
6.1 Set up FGA on the SALARY and COMMISSION_PCT columns of the EMPLOYEES table in the HR schema of the PROD database.
An audit record should be created if either of these two columns are
selected as part of the output of a query or are used in the where condition of
a select statement and their values are not null.
6.2 Validate that the FGA is taking place by executing statements that should
result in auditing records being created and by executing statements
where no auditing records will be generated. Do not delete your audit records.

Flashback
7.1 Create a table named ORIGINAL_SALARY in the HR schema in the PROD database
that includes the employee_id, commission_pct and salaries of all records in the HR.EMPLOYEES table.
Note the date and time and then commit the changes.
7.2 Delete all employees in the HR.ORIGINAL_SALARY table whose HIRE_DATE is before 1994.
Commit the changes.
Create a view HR.SALARY_VIEW that will show all the original rows before the deletion.

7.3 从回收站中闪回一个包含某个字段的表.

section 6

section6

Instance Configuration
1.1 Configure your database to record checkpoints in the alert.log file.
1.2 Ensure all user trace files are placed in the USER_DUMP_DEST location.

Set Up and Configure Resource Manager
2.1 Set up and configure Resource Manager using the following specifications:
2.1.1 Assign the user SH as the resource administrator.
2.1.2 Create two resource manager consumer groups, OLTP and DSS.
(Use comments with each to denote what the objects will be used for.)

2.1.3 Create a plan named WEEKDAYS with the following directives only;
2.1.3.1 For OLTP group, we cannot allow more than 20 active sessions.
If the 21st user attempts an activity,
the request should be aborted if the wait exceeds 60 seconds.
2.1.3.2 The maximum number of active sessions for the DSS group to 5.
If more than 5 sessions are requested, then the request should abort at 120 seconds.
2.1.3.3 The maximum execution time for a query for a session in the OLTP group should be set to 5 seconds.
If the query is estimated to take longer than 5 seconds
the session should be automatically switched to the DSS group.
2.1.3.4 The maximum amount of undo that the OLTP group can use should be set to 200MB.
2.1.3.5 Set CPU ratios for OLTP,DSS and OTHER_GROUPS as 50,30 and 20 respectively.
2.1.3.6 DSS group has parallel degree limit of 20.
2.1.3.7 Make sure that an idle OLTP sessions cannot block a DML statement for more than 60 seconds.

2.1.4 Assign the default consumer group for the OLTP_USER to OLTP group.

2.1.5 Assign the default consumer group for the SH user to DSS group.

2.1.6 Specify that the WEEKDAYS plan the used by the instance as default.

Manage Instance Memory Structures
3.1 Create a view owned by user SYS that lists the packages,procedures,
triggers and functions that are in memory and occupy more than 50KB.
The view should be named LARGE_PROC and visible to all users through a public synonym named LARGE_PROC.

3.2 Set your maximum SGA to 512MB. Turn on Automatic Shared Memory Management.
Restart the instance after specifying.
3.3 Your developers notify you that they will need the Java Pool set to a minimum of 200MB.
3.4 Limit the total amount of PGA that can be used on an instance-wide basis to 150MB.

Manage Objects for Performance
4.1 Our application needs to access the CUST_LAST_NAME column in the CUSTOMERS table in the SH schema.
The problem is that the users can supply names without regard to case.
The application changes all the user-supplied names to uppercase.
Analysis reveals that a normal index we placed on the column is not used.
Create an index on the aforementioned column that will be usable by the application.

4.2 Turn on monitoring for all of the indexes on the SALES table in the OLTP_USER schema.

4.3 Create two new tables named STUDENTS and ATTENDEES in the OLTP_USER schema.
This STUDENTS table will contain three columns.
STUD_ID will be a number and primary key.
FNAME and LNAME will be the other two columns and may vary in length with a maximum of 20 characters.
ATTENDEES will be an intersection table in a many-to-many relationship
between the STUDENTS and CLASSES tables also in the OLTP_USER schema.
The ATTENDEES table will contain the primary keys of each of the other tables as its primary key.
Create the ATTENDEES table so what the primary key index and the table itself are the same object.

4.4 Because of the unevenly distributed data in the DEPARTMENT_ID column of the EMPLOYEES table of the HR schema,
you need to supply more information to the optimizer to allow for more efficient use of indexes.
Regenerate statistics on the EMPLOYEES table to solve this problem.

4.5 Analysis has revealed that the COUNTRY_ID column of the CUSTOMERS table of the SH schema has very low cardinality.
This column is never updated. Create an index that can take advantage of the above attributes of this column.

4.6 Create an index on the COUNTRY_ID and CUST_CITY column in the CUSTOMERS table of the SH schema.
The application requires that leading column of this index must be the COUNTRY_ID column.
Take advantage of the Oracle feature of indexes that allows
the creation of the index to use less space when the leading column is not the most unique.

4.7 Make certain that the package named STANDARD is always kept in memory.

4.8 Analysis reveals that a 3rd party application is not usinig bind variables and has skewed data.
In addition,
we find shared pool latch contention. Find the best solution to reduce shared pool usage.

4.9 对cust_tbs表空间中的对象rebuild到其他表空间。重建该表空间为assm。然后再将对象rebuild 回来。

4.10 对提供的一条select语句固定执行计划

4.11 There’s an index created on a column
of which the value is populated by sequence.
Do appropriate tuning to reduce shared pool latch configuration.
You are not allowed to use reverse index.

4.12
将HR schema中的两个表KEEP到 KEEP-pool 或 recycle-pool

Using STATSPACK
5.1 Install the STATSPACK package.
5.1.1 Assign the TOOLS tablespace as the default tablespace for the PERFSTAT user.
5.1.2 Assign the TEMP1 tablespace as the temporary tablespace for the PERFSTAT user.

5.2 Generate the initial set of statistics using STATSPACK
making certain that timing data and segment level statics are included.
Specify a comment for the statistics MANUAL.

建立快照时增加 comment
建立快照手动增加一个 comment

5.3 Collect statistics using STATSPACK every five minutes for 15 minutes and then remove the job.
During this interval run the oltp_workload.sql script.

5.4 Generate a STATSPACK report using any two sets of stats.
Name the report statspack.ls and place it in the /home/oracle directory.

section 7
搭建RAC

RAC题:
1.建立一个sequence顺序的被两节点使用.
初始值100,最大值99999
就是使用nocache属性的序列
参考 books ==>
sql reference ==> create sequence

SQL> create sequence s1 maxvalue 99999 nocache order nocycle;

改归档.
归档几路,归档位置看题.
在rac下修改参数非全局生效要加 sid=‘INST_NAME’;
开启闪库功能.
放到ASM里不可以建立目录.只能设置 ‘+DG1’
将所有实例关闭,启动一个节点到mount 修改
将所有数据库open.
建立服务.
题不知,
估计是建立一个节点优先,另一个备选
备选是预链接还是基本链接要看题要求.
备份OCR.
ocrdump
适合导出查看不能被导入
ocrconfig -export
适合备份支持 -import 导入
-resotre 是对物理备份恢复(自动备份) -showbackup
section 8
搭建DC
第一天上午
机器检查
CPU
2.内存
3.交换分区
4.磁盘空间
5.selinux
6.iptables
7.网卡IP地址
8.主机名
9.ssh连通性

[root@ora31 ~]# grep ‘physical|core id|siblings|model name’ /proc/cpuinfo
model name : Intel® Core™ i7-2860QM CPU @ 2.50GHz
physical id : 0
siblings : 2
core id : 0
model name : Intel® Core™ i7-2860QM CPU @ 2.50GHz
physical id : 0
siblings : 2
core id : 1
[root@ora31 ~]#

[root@ora31 ~]# free -m
total used free shared buffers cached
Mem: 2026 470 1556 0 29 316
-/+ buffers/cache: 124 1902
Swap: 4094 0 4094
[root@ora31 ~]#

[root@ora31 ~]# fdisk -l

Disk /dev/sda: 68.7 GB, 68719476736 bytes
255 heads, 63 sectors/track, 8354 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 535 4192965 82 Linux swap / Solaris
/dev/sda3 536 8354 62806117+ 83 Linux
[root@ora31 ~]#

[root@ora31 ~]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
/dev/sda3 ext3 59G 2.4G 53G 5% /
/dev/sda1 ext3 99M 12M 83M 12% /boot
tmpfs tmpfs 1014M 0 1014M 0% /dev/shm
[root@ora31 ~]#
section 0 手动建库

Database Setup and Undo Management
1.1 Run the minimum required scripts to complete the basic configuration of the PROD database.
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 minues on average.
1.2.2 The number or concurrent OLTP users will be approximately 120 during normal business hours.
1.2.3 The number or concurrent batch processes that will run in the evenings will approximately 12 to 15

注意不要跑建库后的脚本

建立参数文件
doc–>
Reference -->
1 Initialization Parameters -->
Basic Initialization Parameters -->

复制后修改

[oracle@ora31 dbs]$ cat initPROD.ora
CONTROL_FILES=’/u01/app/PROD/Disk1/control01.ctl’
DB_BLOCK_SIZE=8192
DB_NAME=PROD
DB_UNIQUE_NAME=PROD
JOB_QUEUE_PROCESSES=10
LOG_ARCHIVE_DEST_1=‘LOCATION=/u01/app/PROD/Disk1/arc1/’
PROCESSES=200
SGA_TARGET=400M
UNDO_MANAGEMENT=auto
UNDO_TABLESPACE=undotbs1
UNDO_RETENTION=5400
[oracle@ora31 dbs]$

建库脚本

doc–>
Administrator’s Guide -->
2 Creating an Oracle Database -->
Step 7: Issue the CREATE DATABASE Statement -->

To create the new database, use the CREATE DATABASE statement. The following statement creates database mynewdb:
CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 (’/u01/oracle/oradata/mynewdb/redo01.log’) SIZE 100M,
GROUP 2 (’/u01/oracle/oradata/mynewdb/redo02.log’) SIZE 100M,
GROUP 3 (’/u01/oracle/oradata/mynewdb/redo03.log’) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/u01/oracle/oradata/mynewdb/system01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/u01/oracle/oradata/mynewdb/sysaux01.dbf’ SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE ‘/u01/oracle/oradata/mynewdb/temp01.dbf’
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE ‘/u01/oracle/oradata/mynewdb/undotbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

保存到一个文件,并修改

[oracle@ora31 ~]$ cat createdb.sql
CREATE DATABASE PROD
USER SYS IDENTIFIED BY oracle31
USER SYSTEM IDENTIFIED BY oracle31
LOGFILE GROUP 1 (’/u01/app/PROD/Disk1/redo01.log’) SIZE 100M,
GROUP 2 (’/u01/app/PROD/Disk1/redo02.log’) SIZE 100M,
GROUP 3 (’/u01/app/PROD/Disk1/redo03.log’) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/u01/app/PROD/Disk1/system01.dbf’ SIZE 325M REUSE AUTOEXTEND ON next 10m MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/u01/app/PROD/Disk1/sysaux01.dbf’ SIZE 125M REUSE AUTOEXTEND ON next 10M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE ‘/u01/app/PROD/Disk1/temp01.dbf’
SIZE 20M REUSE AUTOEXTEND ON next 10M MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE ‘/u01/app/PROD/Disk1/undotbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON NEXT 10m MAXSIZE UNLIMITED;

[oracle@ora31 ~]$

sqlplus as sysdba

startup nomount
@createdb.sql

建立spfile文件
SQL> create spfile from pfile ;
配置专属监听

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 listen on the default port.
2.1.3 Databases: PROD and EMREP(created later) will be serviced by this listener.
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.
2.3 Start both listeners.

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

conn system/oracle31
@?/sqlplus/admin/pupbld.sql

如果没有运行第二个脚本,重启数据库后出现错误
[oracle@ora31 ~]$ oerr ora 704
00704, 00000, “bootstrap process failure”
// *Cause: Failure in processing bootstrap data - see accompanying error.
// *Action: Contact your customer support representative.
[oracle@ora31 ~]$ oerr ora 39700
39700, 00000, “database must be opened with UPGRADE option”
// *Cause: A normal database open was attempted, but the database has not
// been upgraded to the current server version.
// *Action: Use the UPGRADE option when opening the database to run
// catupgrd.sql (for database upgrade), or to run catalog.sql
// and catproc.sql (after initial database creation).
[oracle@ora31 ~]$

sqlplus / as sysdba

startup mount
alter database open upgrade;
@?/rdbms/admin/catproc.sql
conn system/oracle31
@?/sqlplus/admin/pupbld.sql

[oracle@ora31 admin]$ cat listener.ora
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ora31.oracle.com)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0)
(SID_NAME=PROD))
(SID_DESC=
(GLOBAL_DBNAME=EMREP)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0)
(SID_NAME=EMREP))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0)
(PROGRAM=extproc)))

[oracle@ora31 admin]$

配置监听

建立listener.ora

doc -->
Net Services Reference -->
7 Listener Parameters (listener.ora) -->

Example 7-1 Example listener.ora File
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sales.us.acme.com)
(ORACLE_HOME=/oracle10g)
(SID_NAME=sales))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/oracle10g)
(PROGRAM=extproc)))

复制到$ORACLE_HOME/network/admin/listener.ora中修改

[oracle@ora31 admin]$

启动监听测试
lsnrctl start

添加第二监听LSNR2

LSNR2=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ora31.oracle.com)(PORT=1526))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc1))))

[oracle@ora31 admin]$ lsnrctl start LSNR2

添加PROD对 LSNR2和LISTENER的动态注册

doc -->
Net Services Reference -->
6 Local Naming Parameters (tnsnames.ora) -->

ADDRESS_LIST

Purpose

Use the parameter ADDRESS_LIST to define a list of protocol addresses. If there is only address list, ADDRESS_LIST is not necessary.

Embed this parameter under either the DESCRIPTION parameter or the DESCRIPTION_LIST parameter.

Example
net_service_name=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
(ADDRESS_LIST=
(FAILOVER=on)
(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)))

复制到$ORACLE_HOME/network/admin/tnsnames.ora 配置

zc_port=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ora31.oracle.com)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=ora31.oracle.com)(PORT=1526))
)
)

SQL> alter system set local_listener=zc_port;

System altered.

SQL>

检查两个监听器的注册情况

lsnrctl status
lsnrctl status lsnr2

链接测试
权限不足说明没密码文件.
sqlplus system/oracle31@ora31.oracle.com:1521/PROD
sqlplus system/oracle31@ora31.oracle.com:1526/PROD

配置共享监听
3. Shared Server Configuration
3.1 Configure the PROD database to support up to 300 sessions, reserving 100 for dedicated connections.
shared_server_sessions=200
sessions=330
3.2 Configure the PROD database to support.
3.2.1 Default of 3 TCP dispatchers and 2 TCPS dispatchers
3.2.2 Maximum of 10 dispatchers
3.3 Configure the PROD database to support:
3.3.1 Minimum of 10 shared server processes
3.3.2 Maximum of 30 shared server processes

配置调度器数量

SQL> show parameter disp

NAME TYPE VALUE

dispatchers string
max_dispatchers integer
SQL>

doc -->
Reference -->
DISPATCHERS

SQL> ALTER SYSTEM SET dispatchers=’(PROTOCOL=TCP)(DISPATCHERS=3)’;

System altered.

SYS@PROD> alter system set dispatchers=’(PROTOCOL=TCP)(DISPATCHERS=3)’,’(PROTOCOL=TCPS)(DISPATCHERS=3)’;

System altered.

SYS@PROD> alter system set DISPATCHERS = ‘(PROTOCOL=TCP)(DISPATCHERS=3)’;

SYS@PROD> alter system set DISPATCHERS = ‘(PROTOCOL=TCPS)(DISPATCHERS=2)’;

SYS@PROD>

SYS@PROD> show parameter dispa

NAME TYPE VALUE

dispatchers string (PROTOCOL=TCP)(DISPATCHERS=3),
(PROTOCOL=TCPS)(DISPATCHERS=2)
max_dispatchers integer 10
SYS@PROD>

SQL> ALTER SYSTEM SET max_dispatchers=10;

System altered.

SQL>

配置服务进程数量
SQL> show parameter shared_servers

NAME TYPE VALUE

max_shared_servers integer
shared_servers integer 0
SQL>

SQL> alter system set shared_servers=10;

System altered.

SQL> alter system set max_shared_servers=30;

System altered.

SQL>

[oracle@ora31 admin]$ ps aux | awk '$NF ~ /s0.*PROD/ {print NF}' ora_s000_PROD ora_s001_PROD ora_s002_PROD ora_s003_PROD ora_s004_PROD ora_s005_PROD ora_s006_PROD ora_s007_PROD ora_s008_PROD ora_s009_PROD [oracle@ora31 admin] ps aux | awk '$NF ~ /d0.*PROD/ {print NF}' ora_d000_PROD ora_d001_PROD ora_d002_PROD [oracle@ora31 admin]

配置数据库链接会话数量
3.1 Configure the PROD database to support up to 300 sessions,
reserving 100 for dedicated connections.

SQL> show parameter sessions

NAME TYPE VALUE

sessions integer 225
shared_server_sessions integer
SQL>

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

System altered.

SQL> alter system set shared_server_sessions=200;

System altered.

SQL> startup force

SQL> show parameter sessions

NAME TYPE VALUE

sessions integer 300
shared_server_sessions integer 200
SQL>

配置网络客户端
4. Client-side Network configuration
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.
4.1.2 The prod_s alias should connect to the PROD instance using LSNR2 and use a shared server connection.
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
4.3 The emrep alias should connect to the EMREP instance (created later) wiht a dedicated server connection.

doc -->
Net Services Reference -->
6 Local Naming Parameters (tnsnames.ora) -->

ADDRESS_LIST

Purpose

Use the parameter ADDRESS_LIST to define a list of protocol addresses. If there is only address list, ADDRESS_LIST is not necessary.

Embed this parameter under either the DESCRIPTION parameter or the DESCRIPTION_LIST parameter.

添加tnsnames.ora对PROD EMREP支持
PROD=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ora31.oracle.com)(PORT=1521))
)
(CONNECT_DATA=
(server=dedicated)
(SERVICE_NAME=PROD)
)
)

SQL> select username,sid,server from v$session where username is not null;

USERNAME SID SERVER

SYS 289 DEDICATED

开启新链接prod测试
[oracle@ora31 admin]$ sqlplus system/oracle31@prod

SQL> select username,sid,server from v$session where username is not null;

USERNAME SID SERVER

SYSTEM 277 DEDICATED
SYS 289 DEDICATED

SQL>

添加prod_s链接名

PROD_S=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ora31.oracle.com)(PORT=1526))
)
(CONNECT_DATA=
(SERVER=shared)
(SERVICE_NAME=PROD)
)
)

[oracle@ora31 admin]$ sqlplus system/oracle31@prod_s
[oracle@ora31 admin]$ sqlplus system/oracle31@prod_s

SQL> select username,sid,server from v$session where username is not null;

USERNAME SID SERVER

SYSTEM 277 NONE
SYS 289 DEDICATED

SQL> select username,sid,server from v$session where username is not null;

USERNAME SID SERVER

SYS 277 SHARED
SYS 289 DEDICATED

SQL>

建立 EMREP RACDB 链接名称

RACDB=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ora31.oracle.com)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=RACDB)
)
)

EMREP=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ora31.oracle.com)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=EMREP)
)
)

验证因为没有DB(后期创建) 所以只能用tnsping测试
[oracle@ora31 admin]$ tnsping emrep

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 24-DEC-2013 12:11:29

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=ora31.oracle.com)(PORT=1521))) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=EMREP)))
OK (0 msec)
[oracle@ora31 admin]$ tnsping racdb

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 24-DEC-2013 12:11:33

Used parameter files:

添加ezconnect和tnsnames方法链接允许

doc -->
Net Services Reference -->
5 Profile Parameters (sqlnet.ora) -->
NAMES.DIRECTORY_PATH

建立 sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

逗号前不要有空格

测试链接方法

[oracle@ora31 admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 24 12:45:00 2013

SQL> conn system/oracle31@ora31.oracle.com/prod
Connected.
SQL> conn system/oracle31@ora31.oracle.com:1521/prod
Connected.
SQL> conn system/oracle31@ora31.oracle.com:1526/prod
Connected.
SQL> conn system/oracle31@prod
Connected.
SQL> conn system/oracle31@202.0.0.31/prod
Connected.
SQL>

新增traceTNSR2

[oracle@ocm1 ~]$ rlwrap lsnrctl

LSNRCTL for Linux: Version 10.2.0.2.0 - Production on 13-JAN-2014 23:24:38

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>
LSNRCTL>
LSNRCTL> help trace
trace OFF | USER | ADMIN | SUPPORT [<listener_name>] : set tracing to the specified level

LSNRCTL>
LSNRCTL> trace SUPPORT LSNR2
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.localdomain)(PORT=1526)))
Opened trace file: /u01/app/oracle/product/10.2.0/network/trace/lsnr2.trc
The command completed successfully
LSNRCTL>

[oracle@ocm1 dbs]$ tail -f /u01/app/oracle/product/10.2.0/network/trace/lsnr2.trc
观察监控文件
通过网络连接测试是否生成trace信息
[oracle@ocm1 dbs]$ sqlplus sys/oracle@ocm1.localdomain:1526/PROD as sysdba
查看追踪文件
[oracle@ocm1 ~]$ trcasst /u01/app/oracle/product/10.2.0/network/trace/lsnr2.tr

关闭追踪.
LSNRCTL> trace OFF
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.localdomain)(PORT=1521)))
The command completed successfully
LSNRCTL>

或者设置到listener.ora文件

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0)
(SID_NAME=PROD)
)
(SID_DESC=
(GLOBAL_DBNAME=EMREP)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0)
(SID_NAME=EMREP)
)
)

LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.localdomain)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc1))))
LSNR2=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.localdomain)(PORT=1526))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc2))))

#TRACE_LEVEL_LSNR2=off
TRACE_LEVEL_LSNR2=support
TRACE_DIRECTORY_LSNR2=/u01/app/oracle/product/10.2.0/network/trace
TRACE_FILE_LSNR2=listener2.trc

修改后 lsnrctl reload lsnr2 立即生效
或者重启动监听lsnr2
新增retries选项

建立连接串EMRDEV
描述部分和EMREP一致.但后续要求连接偶数机(EVEN)[要求连接哪台主机看题要求]失败时有重试选项
题:the number of times to attempt to connect 50,
the amount of time in seconds to wait between connect attempts 5.
尝试连接50次,间隔5秒. 数值也要根据题意自行处理一下.

参考文档
Net Services Administrator’s Guide -->
13 Enabling Advanced Features of Oracle Net Services -->
或者搜索 TAF 关键字
FAILOVER_MODE Parameters -->
Example: TAF Retrying a Connection -->

得到下面例子
sales.us.acme.com=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=sales1-server)
(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic)
(RETRIES=20)
(DELAY=15))))

改为:

EMRDEV=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=ocm2.localdomain)
(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=EMRDEV)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic)
(RETRIES=50)
(DELAY=5))))
建立临时表空间
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.1 Temporary tablespace group named TEMP_GRP containing temporary tablespaces TEMP1 and TEMP2.
5.1.2 Make TEMP_GRP the default temporary tablespace for all new users.

SQL> select * from dba_tablespace_groups;

no rows selected

SQL> alter tablespace temp1 tablespace group temp_grp;

Tablespace altered.

SQL> alter tablespace temp2 tablespace group temp_grp;

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

TEMP_GRP TEMP1
TEMP_GRP TEMP2

SQL> alter database default temporary tablespace TEMP_GRP;

Database altered.

SQL> database_properties

doc -->
SQL reference -->
create tablespace

SQL> select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME CONTENTS

SYSTEM PERMANENT
UNDOTBS1 UNDO
SYSAUX PERMANENT
TEMPTS1 TEMPORARY

4 rows selected.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME

FILE_NAME

SYSTEM
/u01/app/PROD/Disk1/system01.dbf

UNDOTBS1
/u01/app/PROD/Disk1/undotbs01.dbf

SYSAUX
/u01/app/PROD/Disk1/sysaux01.dbf

SQL> select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME

FILE_NAME

TEMPTS1
/u01/app/PROD/Disk1/temp01.dbf

SQL>

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where rownum <=6;

PROPERTY_NAME PROPERTY_VALUE

DICT.BASE 2
DEFAULT_TEMP_TABLESPACE TEMPTS1
DEFAULT_PERMANENT_TABLESPACE SYSTEM
DBTIMEZONE +08:00
DEFAULT_TBS_TYPE SMALLFILE
NLS_LANGUAGE AMERICAN

SQL> create temporary tablespace temp1 tempfile ‘/u01/app/PROD/Disk1/temp101.dbf’ size 30M autoextend on;

Tablespace created.

SQL> create temporary tablespace temp2 tempfile ‘/u01/app/PROD/Disk1/temp201.dbf’ size 30M autoextend on;

Tablespace created.

SQL>

SQL> select * from dba_tablespace_groups;

no rows selected

SQL> alter tablespace temp1 tablespace group temp_grp;

Tablespace altered.

SQL> alter tablespace temp2 tablespace group temp_grp;

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

TEMP_GRP TEMP1
TEMP_GRP TEMP2

SQL> alter database default temporary tablespace TEMP_GRP;

Database altered.

SQL>

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where rownum <=6;

PROPERTY_NAME PROPERTY_VALUE

DICT.BASE 2
DEFAULT_TEMP_TABLESPACE TEMP_GRP
DEFAULT_PERMANENT_TABLESPACE SYSTEM
DBTIMEZONE +08:00
DEFAULT_TBS_TYPE SMALLFILE
NLS_LANGUAGE AMERICAN

6 rows selected.

SQL>
建立永久表空间
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 Initial datafile size of 400MB with the file expectd to grow to 4TB.
5.2.3 Initial extent size of 1MB
5.2.4 next extent size of 1MB
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
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
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
5.6 Create a permanent tablespace for storing segments associated with online transaction processing with 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

create bigfile tablespace EXAMPLE datafile ‘/u01/app/oracle/PROD/disk3/EXAMPLE01.dbf’ size 400M
autoextend on next 1M maxsize 4T uniform size 1M;

alter database default tablespace users;
select * from database_properties where rownum <=6;

第一题
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 Initial datafile size of 400MB with the file expectd to grow to 4TB.
5.2.3 Initial extent size of 1MB
5.2.4 next extent size of 1MB

doc -->
sql reference -->
create tablespace

create bigfile tablespace example
datafile ‘/u01/app/PROD/Disk1/example01.dbf’ size 400M
autoextend on next 1m maxsize 4T extent management local uniform size 1M;

验证

SQL> conn system/oracle31
Connected.
SQL> create table t1(x number) tablespace example;

Table created.

SQL>
SQL> select tablespace_name,file_id,blocks,bytes from dba_extents where segment_name=‘T1’;

TABLESPACE_NAME FILE_ID BLOCKS BYTES

EXAMPLE 4 128 1048576

SQL>

第二题
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

SQL> create tablespace indx datafile ‘/u01/app/oracle/PROD/Disk1/indx01.dbf’ size 40M autoextend on;

Tablespace created.

SQL>

第三题
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

SQL> create tablespace tools datafile ‘/u01/app/oracle/PROD/disk1/tools01.dbf’ size 10M autoextend on;

Tablespace created.

SQL>

第四题
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

SQL> create tablespace users datafile ‘/u01/app/PROD/Disk1/users01.dbf’ size 48M autoextend on uniform size 4m;

Tablespace created.

SQL> alter database default tablespace users;

Database altered.

SQL>

第五题
5.6 Create a permanent tablespace for storing segments associated with online transaction processing with 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

SQL> create tablespace oltp datafile ‘/u01/app/oracle/PROD/disk2/oltp01.dbf’ size 48M autoextend on uniform size 2M segment space management auto
SQL>
重做和控制文件
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 driver failure
6.2 Triplex the controlfile to minimize recovery in case of disk drive failure.

alter database add logfile group 4 ‘/u01/oracle/PROD/disk1/redo04a.log’ size 100M reuse;
alter database add logfile member ‘/u01/oracle/PROD/disk2/redo04b.log’ to group 4 reuse;
alter database add logfile group 5 (’/u01/oracle/PROD/disk1/redo05a.log’,’/u01/oracle/PROD/disk2/redo05b.log’) size 100M ;
添加的文件,如果磁盘存在加 reuse 参数.
! mv /u01/oracle/PROD/disk1/redo01.log /u01/oracle/PROD/disk1/redo01a.log
open模式下,不可以移动v$log.status=current 当前组.
alter database rename file ‘/u01/oracle/PROD/disk1/redo01.log’ to ‘/u01/oracle/PROD/disk1/redo01a.log’;

show parameter control_files;z
startup force nomount;
SYS@PROD> ! cp /u01/oracle/PROD/disk1/control01.ctl /u01/oracle/PROD/disk2/control02.ctl

SYS@PROD> ! cp /u01/oracle/PROD/disk1/control01.ctl /u01/oracle/PROD/disk3/control03.ctl

SYS@PROD> alter system set control_files
=’/u01/oracle/PROD/disk1/control01.ctl’,’/u01/oracle/PROD/disk2/control02.ctl’,’/u01/oracle/PROD/disk3/control03.ctl’ scope=spfile;

System altered.

SYS@PROD> startup force

alter database drop logfile group N; current和active不能删.alter system switch logfile; alter system checkpoint;之后再删
手动删除redo文件;

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

FIRST_CHANGE# FIRST_TIM

1 1 10 104857600 1 NO CURRENT
300866 24-DEC-13

2 1 8 104857600 1 NO INACTIVE
255859 24-DEC-13

3 1 9 104857600 1 NO INACTIVE
278281 24-DEC-13

SQL>

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

1 ONLINE /u01/app/PROD/Disk1/redo01.log NO
2 STALE ONLINE /u01/app/PROD/Disk1/redo02.log NO
3 STALE ONLINE /u01/app/PROD/Disk1/redo03.log NO

SQL>

SQL> ! ls /u01/app/PROD/Disk1/
arc1 oltp01.dbf sysaux01.dbf temp201.dbf
control01.ctl redo01.log system01.dbf tools01.dbf
example01.dbf redo02.log temp01.dbf undotbs01.dbf
indx01.dbf redo03.log temp101.dbf users01.dbf

SQL> ! ls /u01/app/PROD/Disk2/

SQL> ! ls /u01/app/PROD/Disk3/

SQL>
SQL> alter database add logfile member ‘/u01/app/PROD/Disk2/redo01_1.log’ to group 1;

Database altered.

SQL> alter database add logfile member ‘/u01/app/PROD/Disk2/redo02_1.log’ to group 2;

Database altered.

SQL> alter database add logfile member ‘/u01/app/PROD/Disk2/redo03_1.log’ to group 3;

Database altered.

SQL> select group#,member from v$logfile;

GROUP# MEMBER

1 /u01/app/PROD/Disk1/redo01.log
2 /u01/app/PROD/Disk1/redo02.log
3 /u01/app/PROD/Disk1/redo03.log
1 /u01/app/PROD/Disk2/redo01_1.log
2 /u01/app/PROD/Disk2/redo02_1.log
3 /u01/app/PROD/Disk2/redo03_1.log

6 rows selected.

SQL> alter database add logfile group 4 (’/u01/app/PROD/Disk1/redo04.log’,’/u01/app/PROD/Disk2/redo04_1.log’) size 100m;

Database altered.

SQL> alter database add logfile group 5(’/u01/app/PROD/Disk1/redo05.log’,’/u01/app/PROD/Disk2/redo05_1.log’) size 100m;

Database altered.

SQL>

SQL> select a.group#,members,bytes,a.status,b.member
from v $log a,v$ logfile b where a.group#=b.group#
order by 1
SQL> /

GROUP# MEMBERS BYTES STATUS

MEMBER

1 2 104857600 CURRENT
/u01/app/PROD/Disk2/redo01_1.log

1 2 104857600 CURRENT
/u01/app/PROD/Disk1/redo01.log

2 2 104857600 INACTIVE
/u01/app/PROD/Disk1/redo02.log

2 2 104857600 INACTIVE
/u01/app/PROD/Disk2/redo02_1.log

3 2 104857600 INACTIVE
/u01/app/PROD/Disk2/redo03_1.log

3 2 104857600 INACTIVE
/u01/app/PROD/Disk1/redo03.log

4 2 104857600 INACTIVE
/u01/app/PROD/Disk1/redo04.log

4 2 104857600 INACTIVE
/u01/app/PROD/Disk2/redo04_1.log

5 2 104857600 INACTIVE
/u01/app/PROD/Disk2/redo05_1.log

5 2 104857600 INACTIVE
/u01/app/PROD/Disk1/redo05.log

10 rows selected.

SQL>

SQL> select name from v$controlfile;

NAME

/u01/app/PROD/Disk1/control01.ctl

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 1219736 bytes
Variable Size 121635688 bytes
Database Buffers 289406976 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL>

SQL> ! cp /u01/app/PROD/Disk1/control01.ctl /u01/app/PROD/Disk2/control02.ctl

SQL> ! cp /u01/app/PROD/Disk1/control01.ctl /u01/app/PROD/Disk3/control03.ctl

SQL> alter system set control_files=’/u01/app/PROD/Disk1/control01.ctl’,’/u01/app/PROD/Disk2/control02.ctl’,’/u01/app/PROD/Disk3/control03.ctl’ scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 1219736 bytes
Variable Size 121635688 bytes
Database Buffers 289406976 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;

NAME

/u01/app/PROD/Disk1/control01.ctl
/u01/app/PROD/Disk2/control02.ctl
/u01/app/PROD/Disk3/control03.ctl

SQL>
建立schema
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

SH@test1> SELECT * FROM SESSION_ROLES;

ROLE

RESOURCE
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE

SH@test1>

SH@test1> SELECT * FROM SESSION_PRIVS;

PRIVILEGE

CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE MATERIALIZED VIEW
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
QUERY REWRITE
CREATE DIMENSION

17 rows selected.

SH@test1>

BI@test1> SELECT * FROM SESSION_ROLES;

ROLE

RESOURCE

BI@test1>

BI@test1> SELECT * FROM SESSION_PRIVS;

PRIVILEGE

14 rows selected.

BI@test1>

HR@test1> SELECT * FROM SESSION_ROLES;

ROLE

RESOURCE

HR@test1>

HR@test1> SELECT * FROM SESSION_PRIVS;

PRIVILEGE

14 rows selected.

HR@test1>

收集信息

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: Applications that use Oracle 10g features will be running therefore,
ensure the database and instance are appropriately configured.

doc -->
PL/SQL Packages and Types Reference -->
103 DBMS_STATS

GATHER_DATABASE_STATS Procedures

This procedure gathers statistics for all objects in the database.

Syntax
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param(‘ESTIMATE_PERCENT’)),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param(‘METHOD_OPT’),
degree NUMBER DEFAULT to_degree_type(get_param(‘DEGREE’)),
granularity VARCHAR2 DEFAULT GET_PARAM(‘GRANULARITY’),
cascade BOOLEAN DEFAULT to_cascade_type(get_param(‘CASCADE’)),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT ‘GATHER’,
objlist OUT ObjectTab,
statown VARCHAR2 DEFAULT NULL,
gather_sys BOOLEAN DEFAULT TRUE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param(‘NO_INVALIDATE’)));

DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param(‘ESTIMATE_PERCENT’)),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param(‘METHOD_OPT’),
degree NUMBER DEFAULT to_degree_type(get_param(‘DEGREE’)),
granularity VARCHAR2 DEFAULT GET_PARAM(‘GRANULARITY’),
cascade BOOLEAN DEFAULT to_cascade_type(get_param(‘CASCADE’)),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
options VARCHAR2 DEFAULT ‘GATHER’,
statown VARCHAR2 DEFAULT NULL,
gather_sys BOOLEAN DEFAULT TRUE,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param(‘NO_INVALIDATE’)));

exec dbms_stats.gather_database_stats(degree=>2);

SQL> sho parameter utl_fi

NAME TYPE VALUE

utl_file_dir string
SQL> ! ls /home/oracle/ /home/oracle/temp /home/oracle/scripts
ls: /home/oracle/temp: No such file or directory
ls: /home/oracle/scripts: No such file or directory
/home/oracle/:
afiedt.buf createdb.sql Desktop log.sql

SQL> ! mkdir /home/oracle/ /home/oracle/temp /home/oracle/scripts -p

SQL> ! ls /home/oracle/ /home/oracle/temp /home/oracle/scripts
/home/oracle/:
afiedt.buf createdb.sql Desktop log.sql scripts temp

/home/oracle/scripts:

/home/oracle/temp:

SQL> alter system set utl_file_dir=’/home/oracle/’,’/home/oracle/temp’,’/home/oracle/scripts’ scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 1219736 bytes
Variable Size 125829992 bytes
Database Buffers 285212672 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL>
备份数据库
9. Database Backup and Availability
9.1 Backup the database to prepare for complete recovery under all circumstances.
9.2 Open the database.

要求归档

SQL> show parameter dest_1

NAME TYPE VALUE

log_archive_dest_1 string LOCATION=/u01/app/PROD/Disk1/arc1/

SQL>

SQL> alter system set log_archive_format=’%s_%t_%r.arc’ scope=spfile;

System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
aORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 1219736 bytes
Variable Size 125829992 bytes
Database Buffers 285212672 bytes
Redo Buffers 7168000 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>

SQL> ! ls /u01/app/PROD/Disk1/arc1

SQL> alter system switch logfile;

System altered.

SQL> ! ls /u01/app/PROD/Disk1/arc1
51_1_834710148.arc

SQL>

SQL> ! ls /u01/app/PROD/Disk5
full_bak rman_bak

SQL> ! ls /u01/app/PROD/Disk5/rman_bak

SQL>

备份位置制定
doc -->
Backup and Recovery Reference -->
2 RMAN Commands -->
CONFIGURE -->
deviceConf

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘+dgroup1’;

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/u01/app/PROD/Disk5/rman_bak/%d_%I_%s_%p_%T.rbak’;

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/u01/app/PROD/Disk5/rman_bak/%d_%I_%s_%p_%T.rbak’;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u01/app/PROD/Disk5/rman_bak/ctl_%F.cbak’;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u01/app/PROD/Disk5/rman_bak/ctl_%F.cbak’;
new RMAN configuration parameters are successfully stored

RMAN>

RMAN> backup as compressed backupset database plus archivelog;

第一天下午
section 2 偶数机建立数据库
软件已经为我们安装好可以使用图形工具

查看SID的设置
export ORACLE_SID=EMREP

dbca

建立数据库时因为此版本BUG问题.要选择自定义

一定要把EM去掉否则不能安装GC

去掉所有组件.包括Standard Database Components按钮中的组件
为了加速建库过程.题中要求打开Oracle Label Security.
要使用Lable Security 要运行
/opt/oracle/product/10.2.0/oui/bin/runInstaller
选择安装软件位置.选自定义.添加Lable Security 组件.

内存给400M左右.

字符集一定要和PROD数据库一致.因为后面会有imp/exp题

查看PROD的字符集是在手动建库时定制的.
SYS@PROD> select * from nls_database_parameters where rownum <=7;

PARAMETER VALUE

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN

7 rows selected.

SYS@PROD>

迅速安装完成.

安装GC

安装前的准备
调整参数:

开启监听
测试OK.

SQL> alter system set session_cached_cursors = 201 scope=spfile;
SQL> alter system set job_queue_processes = 11 scope=spfile;
SQL> alter system set open_cursors = 301 scope=spfile;
SQL> alter system set dispatchers=’’;
SQL> alter system set aq_tm_processes=2;

SQL> alter user DBSNMP account unlock identified by oracle1;

User altered.

SQL>

SQL> @?/rdbms/admin/dbmspool

Package created.

Grant succeeded.

View created.

Package body created.

SQL> startup force

运行安装程序前的注意事项:

1.EMREP数据库OPEN
2.监听开启 EMREP已经注册到监听
3.把系统时间调整为2010-12-31以前
4.主机名和IP地址的解析关系
/etc/hosts /etc/sysconfig/network

[oracle@ocm2 ~]$ date
Fri Jan 1 00:28:02 CST 2010
[oracle@ocm2 ~] $[oracle@ocm2 ~]$ date “+%F %T”
2010-02-01 00:30:06
[oracle@ocm2 ~]$

安装过程

选择使用已存在中的数据库

软件包检查失败这个是在RHEL5上安装,要求的软件包已经有高版本所以下面的可以忽略
点上勾,用户验证通过

Checking operating system package requirements …
Checking for make-3.79; found make-1:3.81-3.el5. Passed
Checking for binutils-2.15.92.0.2-13; found binutils-2.17.50.0.6-14.el5. Passed
Checking for gcc-3.4.3-22.1; found gcc-4.1.2-48.el5. Passed
Checking for libaio-0.3.96; found libaio-0.3.106-5. Passed
Checking for glibc-common-2.3.4-2.9; found glibc-common-2.5-49. Passed
Checking for compat-libstdc+±296-2.96-132.7.2; found Not found. Failed <<<<
Checking for libstdc+±3.4.3-22.1; found libstdc+±4.1.2-48.el5. Passed
Checking for libstdc++devel-3.4.3-22.1; found Not found. Failed <<<<
Checking for openmotif-21-2.1.30-11; found openmotif-2.3.1-2.el5_4.1. Failed <<<<
Checking for pdksh-5.2.14-30; found pdksh-5.2.14-36.el5. Passed
Checking for setarch-1.6-1; found setarch-2.0-1.1. Passed
Checking for gnome-libs-1.4.1.2.90-44.1; found gnome-libs-1:1.4.2-7.fc6. Passed
Checking for sysstat-5.0.5-1; found sysstat-7.0.2-3.el5. Passed
Checking for compat-db-4.1.25-9; found compat-db-4.2.52-5.1. Passed
Checking for control-center-2.8.0-12; found control-center-1:2.16.0-16.el5. Passed
Checking for xscreensaver-4.18-5.rhel4.2; found Not found. Failed <<<<
Check complete. The overall result of this check is: Failed <<<<
Problem: Some packages required for the Oracle EnterPrise Manager 10gR2 to function properly are missing (see above).
Recommendation: Install the required packages before continuing with the installation.

如实填写EMREP的链接信息

表空间位置.默认路径如下:
/opt/oracle/OracleHomes/oms10g/oradata/mgmt.dbf
要变为图上的位置需要点一下 Prefill Tablespace Locations 按钮
点完自动改变.对弹出窗口点确定

点击下一步会报错.因为我们前面没有设置参数
根据错误提示修改EMREP数据库的参数

错误窗口放一边别管它.改完参数重启数据库后再来点OK

SQL> show parameter session_cached_cursors

NAME TYPE VALUE

session_cached_cursors integer 20

SQL> alter system set session_cached_cursors=220 scope=spfile;

System altered.

SQL> show parameter aq

NAME TYPE VALUE

aq_tm_processes integer 0
SQL> alter system set aq_tm_processes=5;

System altered.

SQL>

SQL> startup force
ORACLE instance started.

Total System Global Area 369098752 bytes
Fixed Size 1260876 bytes
Variable Size 109052596 bytes
Database Buffers 251658240 bytes
Redo Buffers 7127040 bytes
Database mounted.
Database opened.
SQL>

下一步又会出错

回数据库加载这个包的建立脚本

SQL> @?/rdbms/admin/dbmspool

Package created.

Grant succeeded.

View created.

Package body created.

SQL>

再回错误窗口点确定,再点下一步,通过了.

配置GC选项.什么都不选,保持默认.下一步

密码安全设定，设置完成后，点击“Next”
注意：给定的密码要求至少是5位，并且需要包含数字，我们这里统一设置为“oracle1”！
sysman的密码也是如上要求

安装概览.直接安装.

注意开启窗口监控日志,第一时间发现问题

96%时的OPMN的BUG解决[考试时不会发生]

[oracle@ocm2 OracleHomes]$ cd $ORACLE_BASE/OracleHomes/oms10g/opmn/bin/ [oracle@ocm2 bin]$ ls
argus opmn opmnconfig opmnctl opmnctl.tmp opmndep
[oracle@ocm2 bin]$ ./opmnctl stopall
opmnctl: stopping opmn and all managed processes…
[oracle@ocm2 bin]$ ./opmnctl status
Unable to connect to opmn.
Opmn may not be up.
[oracle@ocm2 bin]$ cd …/…/bin
[oracle@ocm2 bin]$ pwd
/opt/oracle/OracleHomes/oms10g/bin
[oracle@ocm2 bin]$ ./emctl start oms
Oracle Enterprise Manager 10g Release 10.2.0.1.0
Copyright © 1996, 2005 Oracle Corporation. All rights reserved.
opmnctl: opmn started
Starting HTTP Server …
Starting Oracle Management Server …
Checking Oracle Management Server Status …
Oracle Management Server is not functioning because of the following reason:
Unexpected error occurred. Check error and log files.
[oracle@ocm2 bin]$ cd …/opmn/bin
[oracle@ocm2 bin]$ pwd
/opt/oracle/OracleHomes/oms10g/opmn/bin
[oracle@ocm2 bin]$ ./opmnctl status

[oracle@ocm2 bin]$

[oracle@ocm2 bin]$ ./opmnctl -h

usage: ./opmnctl [verbose] [] []

verbose: print detailed execution message if available

Permitted // combinations are:

scope command options

start - Start opmn
startall - Start opmn & all managed processes
stopall - Stop opmn & all managed processes
shutdown - Shutdown opmn & all managed processes
[] startproc [= …] - Start opmn managed processes
[] restartproc [= …] - Restart opmn managed processes
[] stopproc [= …] - Stop opmn managed processes
[] reload - Trigger opmn to reread opmn.xml
[] status [] - Get managed process status
[] dmsdump [=&…] - Get DMS stats
ping [<max_retry>] - Ping local opmn
validate [] - Validate the given xml file
help - Print brief usage description
usage [] - Print detailed usage description

[oracle@ocm2 bin]$

/opt/oracle/OracleHomes/oms10g/Apache/Apache/bin/apachectl start: execing httpd

/opt/oracle/OracleHomes/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or direct

在安装过程中还会报另外找不到libdb.so.2 类此的一个错误，需要做如下连接

[root@dbserv test]# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2

[oracle@ocm2 bin]$ ./opmnctl startproc ias-component=‘DSA’
opmnctl: starting opmn managed processes…
[oracle@ocm2 bin]$ ./opmnctl startproc ias-component=‘LogLoader’
opmnctl: starting opmn managed processes…
[oracle@ocm2 bin]$ ./opmnctl startproc ias-component=‘OC4J’
opmnctl: starting opmn managed processes…
[oracle@ocm2 bin]$ ./opmnctl startproc ias-component=‘WebCache’
opmnctl: starting opmn managed processes…
[oracle@ocm2 bin]$ ./opmnctl status

[oracle@ocm2 bin]$

回到报错窗口点continue.

弹出新安装界面继续监控日志

client_sharedlib BUG 考试时没有

点 Cancel

点 Yes

重新运行 GC的 runInstaller …
一定要选 Resume…
点OK.

继续原来的安装就跳过了这个BUG

弹出运行root.sh 惯例

[root@ocm2 ~]# /opt/oracle/OracleHomes/oms10g/allroot.sh

Starting to execute allroot.sh …

Starting to execute /opt/oracle/OracleHomes/oms10g/root.sh …
Running Oracle10 root.sh script…
\nThe following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /opt/oracle/OracleHomes/oms10g

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file “dbhome” already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin …
The file “oraenv” already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin …
The file “coraenv” already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin …

Adding entry to /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished execution of /opt/oracle/OracleHomes/oms10g/root.sh …

Starting to execute /opt/oracle/OracleHomes/agent10g/root.sh …
Running Oracle10 root.sh script…

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /opt/oracle/OracleHomes/agent10g

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

Finished execution of /opt/oracle/OracleHomes/agent10g/root.sh …
[root@ocm2 ~]#

新窗口继续监控日志
这步OMS Configuration很漫长盯住日志.出问题立即解决
只要日志在滚动就可以了. 如果此步失败后,则没有时间完成再一次的GC配置.

日志内容:
Operation Stopping OPMN Processes is in progress.
Operation EM Deploying is in progress.

Operation Creating OMS Respository is in progress.

日志到这里时会等很久大概10多分钟

Operation Configuring OMS is in progress.

OMS is being Secured and Lock is set to true.

小等一会儿

Precompiling JSPs.
Performing installation of CLI services for client.

Operation Restarting OPMN Processes is in progress.

INFO: Configuration assistant “OMS Configuration” succeeded
INFO: Command = oracle.sysman.emcp.agent.AgentPlugIn

Performing free port detection on host=ocm2.localdomain
Securing the agent

Command = oracle.sysman.emcp.oms.OmsPlugIn has failed

INFO: Configuration assistant “OMS Configuration” failed

如果见到这个错误是你OS事件没有修改.

OPMN错误

[oracle@ocm2 bin]$ pwd
/u01/oracle/OracleHomes/oms10g/opmn/bin
[oracle@ocm2 bin] $[oracle@ocm2 bin]$ ./opmnctl startall
opmnctl: starting opmn and all managed processes…

opmn id=ocm2.oracle.com:6200
3 of 4 processes started.

ias-instance id=EnterpriseManager0.ocm2.oracle.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
HTTP_Server/HTTP_Server/HTTP_Server

Error
–> Process (pid=6334)
failed to start a managed process after the maximum retry limit
Log:
/u01/oracle/OracleHomes/oms10g/opmn/logs/HTTP_Server~1

[oracle@ocm2 bin]$

[oracle@ocm2 bin]$ cat /u01/oracle/OracleHomes/oms10g/opmn/logs/HTTP_Server~1

…
/u01/oracle/OracleHomes/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2:
cannot open shared object file: No such file or directory

在安装过程中还会报另外找不到libdb.so.2 类此的一个错误，需要做如下连接

[root@dbserv test]# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2

[oracle@ocm2 bin]$ ./opmnctl startall
opmnctl: starting opmn and all managed processes…
[oracle@ocm2 bin]$ ./opmnctl status

[oracle@ocm2 bin]$

回到安装报错界面点 continue

client_sharedlib错误

点击 cancel 关闭安装程序

重新运行安装程序
选择继续上一次的安装
安装agent
将PROD库的dbsnmp解锁并设置密码为dbsnmp

[oracle@ora32 linux]$ pwd
/u01/app/oracle/oms_home/oms10g/sysman/agent_download/10.2.0.1.0/linux
[oracle@ora32 linux]$ ls
addons agentDownload.linux oui response
agent agent_scripts.jar prereqs
[oracle@ora32 linux]$

[oracle@ora32 linux]$ scp agentDownload.linux ora31:/home/oracle/
The authenticity of host ‘ora31 (202.0.0.31)’ can’t be established.
RSA key fingerprint is 13:37:a5:85:e9:6a:6b:c1:7e:55:d5:65:16:af:e1:42.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘ora31,202.0.0.31’ (RSA) to the list of known hosts.
oracle@ora31’s password:
agentDownload.linux 100% 22KB 22.3KB/s 00:00
[oracle@ora32 linux]$

[oracle@ora31 oracle]$ cd $ORACLE_BASE [oracle@ora31 oracle]$ mkdir agent -p
[oracle@ora31 oracle]$ cd agent/
[oracle@ora31 agent]$ pwd
/u01/app/oracle/agent
[oracle@ora31 agent]$

[oracle@ora31 agent_dir]$ chmod +x agentDownload.linux
[oracle@ora31 agent_dir]$ ll
total 24
-rwx–x--x 1 oracle oinstall 22783 Dec 25 18:46 agentDownload.linux
[oracle@ora31 agent_dir]$

[oracle@ora31 agent_dir]$ which jar
/usr/bin/which: no jar in (/usr/kerberos/bin:/usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin:/home/oracle/bin:/u01/app/oracle/product/10.2.0/bin)
[oracle@ora31 agent_dir]$ ls -l $ORACLE_HOME/jdk/bin/jar -rwxr-xr-x 1 oracle oinstall 66156 Apr 20 2005 /u01/app/oracle/product/10.2.0/jdk/bin/jar [oracle@ora31 agent_dir]$ export PATH= $ORACLE_HOME/jdk/bin:$ PATH
[oracle@ora31 agent_dir]$ which java
/u01/app/oracle/product/10.2.0/jdk/bin/java
[oracle@ora31 agent_dir]$ which jar
/u01/app/oracle/product/10.2.0/jdk/bin/jar
[oracle@ora31 agent_dir]$

[oracle@ora31 agent_dir]$ ./agentDownload.linux -b /u01/app/oracle/agent

…
The following configuration scripts need to be executed as the “root” user.
#!/bin/sh
#Root script to run
/u01/app/oracle/agent/agent10g/root.sh
To execute the configuration scripts:

Open a terminal window
Log in as “root”
Run the scripts

Starting to execute configuration assistants
…
使用root运行脚本

访问 http://ocm2.localdomain:4889/em

添加数据库PROD到OMS

步骤1:

步骤2:

步骤3:

步骤4:

步骤5:

步骤6:

关机启动方法

1.启动监听
odd:
lsnrctl start
lsnrctl start lsnr2
even:
lsnrctl start

2.启动DB
odd+even:
sqlplus / as sysdba
startup

3.启动agent
odd+even:
[oracle@ocm1 bin]$ pwd
/u01/app/oracle/agent10g/agent10g/bin
[oracle@ocm1 bin]$ ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright © 1996, 2005 Oracle Corporation. All rights reserved.

Agent is Not Running
[oracle@ocm1 bin]$ ./emctl start agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright © 1996, 2005 Oracle Corporation. All rights reserved.
Starting agent … started.
[oracle@ocm1 bin]$

[oracle@ocm2 bin]$ pwd
/u01/app/oracle/OracleHomes/agent10g/bin
[oracle@ocm2 bin]$ ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright © 1996, 2005 Oracle Corporation. All rights reserved.

Agent is Not Running
[oracle@ocm2 bin] $[oracle@ocm2 bin]$ pwd
/u01/app/oracle/OracleHomes/agent10g/bin
[oracle@ocm2 bin]$ ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright © 1996, 2005 Oracle Corporation. All rights reserved.

Agent is Not Running
[oracle@ocm2 bin]$ ./emctl start agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright © 1996, 2005 Oracle Corporation. All rights reserved.
Starting agent … started.

[oracle@ocm2 bin]$

4.启动OMS
even:

[oracle@ocm2 bin]$ pwd
/u01/app/oracle/OracleHomes/oms10g/bin
[oracle@ocm2 bin]$ ./emctl status oms
Oracle Enterprise Manager 10g Release 10.2.0.1.0
Copyright © 1996, 2005 Oracle Corporation. All rights reserved.
Oracle Management Server is Down.
[oracle@ocm2 bin] $[oracle@ocm2 bin]$ ./emctl start oms
Oracle Enterprise Manager 10g Release 10.2.0.1.0
Copyright © 1996, 2005 Oracle Corporation. All rights reserved.
opmnctl: opmn is already running
Starting HTTP Server …
Starting Oracle Management Server …
Checking Oracle Management Server Status …
Oracle Management Server is not functioning because of the following reason:
Connection to the repository failed. Verify that the repository connection information provided is correct.
[oracle@ocm2 bin]$
上面错误一般是OMS主机的DB和监听未启动

GC 题

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

=================================================================================

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.
*** 就是scope=memory;

=================================================================================

验证结果.
SYS@PROD> select name,value from v$spparameter where name like ‘pga%’;

NAME VALUE

pga_aggregate_target

SYS@PROD> select name,value from v$parameter where name like ‘pga%’;

NAME VALUE

pga_aggregate_target 524288000

SYS@PROD>

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.

和上题一样.但要使用scope=both;
修改参数为 fast_start_mttr_target 单位是秒

=================================================================================

验证:

SYS@PROD> select name,value from v$parameter where name like ‘%mttr%’;

NAME VALUE

fast_start_mttr_target 300

SYS@PROD> select name,value from v$spparameter where name like ‘%mttr%’;

NAME VALUE

fast_start_mttr_target 300

SYS@PROD>

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

=================================================================================

验证修改:

SYS@PROD> select dbms_server_alert.VIEW_THRESHOLDS from dual;

VIEW_THRESHOLDS(OBJECT_TYPE, OBJECT_NAME, METRICS_ID, INSTANCE_NAME, FLAGS, WARNING_OPERATOR, WARNING_VALUE, CRITICAL_OP

…省略…
THRESHOLD_TYPE(5, ‘SYSTEM’, 9000, ‘database_wide’, 1, 4, ‘87’, 4, ‘95’, 1, 1, 0))

SYS@PROD>

2.4 Setup notifications to be sent to the email address ‘dba@ocm.com’ Notification messages should be sent to this address at anytime.

进去后点 create 按钮

=================================================================================

验证:

SYS@PROD> select TABLESPACE_NAME,FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_data_files;

TABLESPACE_NAME FILE_NAME AUT MAXBYTES

SYSTEM /u01/app/oracle/PROD/Disk1/system01.dbf YES 3.4360E+10
UNDOTBS /u01/app/oracle/PROD/Disk1/undotbs01.dbf YES 3.4360E+10
SYSAUX /u01/app/oracle/PROD/Disk1/sysaux01.dbf YES 3.4360E+10
EXAMPLE /u01/app/oracle/PROD/Disk1/example01.dbf YES 4.3980E+12
INDX /u01/app/oracle/PROD/Disk1/indx01.dbf NO 0
TOOLS /u01/app/oracle/PROD/Disk1/tools01.dbf NO 0
USERS /u01/app/oracle/PROD/Disk1/users01.dbf NO 0
OLTP /u01/app/oracle/PROD/Disk1/oltp01.dbf NO 0
REGISTRATION /u01/app/oracle/PROD/Disk1/REGISTRATION01.dbf YES 125829120

9 rows selected.

SYS@PROD> select dbms_metadata.get_ddl(‘TABLESPACE’,‘REGISTRATION’) FROM DUAL;

DBMS_METADATA.GET_DDL(‘TABLESPACE’,‘REGISTRATION’)

CREATE TABLESPACE “REGISTRATION” DATAFILE
‘/u01/app/oracle/PROD/Disk1/REGISTRATION01.dbf’ SIZE 94371840
AUTOEXTEND ON NEXT 2097152 MAXSIZE 125829120
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

SYS@PROD>

Implementing Schedules and Jobs
3.1 Using Grid Control, create a schedule for the PROD database.
3.1.1 Call this schedule DAILYREBUILD
3.1.2 Configure it to run at 2PM every day

=================================================================================

进入后点击 create按钮

=================================================================================

对应SQL:
手动做时文档位置:
BOOKS ==>
PL/SQL Packages and Types Reference ==> 搜索dbms_sche
CREATE_SCHEDULE Procedure ==>

PROCEDURE CREATE_SCHEDULE
Argument Name Type In/Out Default?

SCHEDULE_NAME VARCHAR2 IN
START_DATE TIMESTAMP WITH TIME ZONE IN DEFAULT
REPEAT_INTERVAL VARCHAR2 IN
END_DATE TIMESTAMP WITH TIME ZONE IN DEFAULT
COMMENTS VARCHAR2 IN DEFAULT

DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name IN VARCHAR2,
start_date IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
repeat_interval IN VARCHAR2,
end_date IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
comments IN VARCHAR2 DEFAULT NULL);

begin
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name=>‘SYSTEM.DAILYREBUILD’,
start_date => systimestamp,
repeat_interval=>‘FREQ=DAILY;BYHOUR=14;BYMINUTE=0;BYSECOND=0’
);
end;
/

SYS@PROD> select OWNER,SCHEDULE_NAME,START_DATE,REPEAT_INTERVAL,END_DATE from DBA_SCHEDULER_SCHEDULES;

OWNER SCHEDULE_NAME START_DATE

REPEAT_INTERVAL END_DATE

SYS DAILY_PURGE_SCHEDULE
freq=daily;byhour=3;byminute=0;bysecond=0

SYSTEM DAILYREBUILD 16-JAN-14 12.56.47.665805 AM +08:00
FREQ=DAILY;BYHOUR=14;BYMINUTE=0;BYSECOND=0

SYS@PROD>

SYS@PROD> exec dbms_scheduler.drop_schedule(‘system.DAILYREBUILD1’);

PL/SQL procedure successfully completed.

SYS@PROD>

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

SYS@PROD> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME from dba_indexes where TABLE_OWNER=‘HR’ and TABLE_NAME=‘EMPLOYEES’;

OWNER INDEX_NAME TABLE_OWNER TABLE_NAME

HR EMP_MANAGER_IX HR EMPLOYEES
HR EMP_NAME_IX HR EMPLOYEES
HR EMP_EMAIL_UK HR EMPLOYEES
HR EMP_EMP_ID_PK HR EMPLOYEES
HR EMP_DEPARTMENT_IX HR EMPLOYEES
HR EMP_JOB_IX HR EMPLOYEES

6 rows selected.

SYS@PROD>

SYS@PROD> select ‘ALTER INDEX ‘||OWNER||’.’||INDEX_NAME||’ rebuild’ from
dba_indexes where TABLE_OWNER=‘HR’ and TABLE_NAME=‘EMPLOYEES’;

‘ALTERINDEX’||OWNER||’.’||INDEX_NAME||‘REBUILD;’

ALTER INDEX HR.EMP_MANAGER_IX rebuild
ALTER INDEX HR.EMP_NAME_IX rebuild
ALTER INDEX HR.EMP_EMAIL_UK rebuild
ALTER INDEX HR.EMP_EMP_ID_PK rebuild
ALTER INDEX HR.EMP_DEPARTMENT_IX rebuild
ALTER INDEX HR.EMP_JOB_IX rebuild

6 rows selected.

SYS@PROD> 不要在rebuild后面加分号.

begin
FOR I IN (select ‘ALTER INDEX ‘||OWNER||’.’||INDEX_NAME||’ rebuild’ run_sql
from dba_indexes where TABLE_OWNER=‘HR’ and TABLE_NAME=‘EMPLOYEES’)
loop
execute immediate i.run_sql;
end loop;
end;
/

BEGIN

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>’“SYSTEM”.“EMP_IND_REBUILD”’,
program_action=>‘begin
FOR I IN (select ‘‘ALTER INDEX ‘’||OWNER||’’.’’||INDEX_NAME||’’ rebuild’’ run_sql
from dba_indexes where TABLE_OWNER=’‘HR’’ and TABLE_NAME=’‘EMPLOYEES’’)
loop
execute immediate i.run_sql;
end loop;
end;’,
program_type=>‘PLSQL_BLOCK’,
number_of_arguments=>0,
comments=>’’,
enabled=>TRUE);
END;

3.3 Create a window that utilizes the DAILYREBUILD schedule and SYSTEM_PLAN resource manager plan.

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

GC新增题
新增题

做一个job ,实例启动7天后关闭实例.

v$instance.startup_time

这个JOB不属于DB里的JOB.是GC的JOB

步骤1:

==========================================================================================
步骤2:

==========================================================================================
步骤3:

==========================================================================================
步骤4:

==========================================================================================
步骤5:

==========================================================================================
步骤6:

GC新增题2:
scripts目录提供两个脚本
脚本1
[oracle@ocm1 scripts]$ cat mk_dir.sh
#!/bin/bash

mkdir -p /home/oracle/backup
[oracle@ocm1 scripts]$

脚本2
[oracle@ocm1 scripts]$ cat bak_tbs.rev
run
{
backup tablespace users format ‘/home/oracle/backup/%U’;
}
[oracle@ocm1 scripts]$

要求脚本1执行成功后,执行脚本2
虚拟机里的 mk_dir.sh 没有x权限
chmod +x mk_dir.sh

这个题不是用DB中的job-chain实现的.也是用GC-job的Multi-task实现的.

步骤1:

=======================================================================================

步骤2:

=======================================================================================

步骤3:

=======================================================================================

步骤4:

=======================================================================================

步骤5:

=======================================================================================

步骤6:

=======================================================================================

步骤7:

=======================================================================================

步骤8:

=======================================================================================

步骤9:

=======================================================================================

步骤10:

=======================================================================================

步骤11:

=======================================================================================

步骤12:

section 3 备份恢复

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.

文档位置
BOOKS ==> bac 关键字
Backup and Recovery Advanced User’s Guide ==>
10 Managing the Recovery Catalog ==>

SQL> create tablespace rc_data datafile ‘/opt/oracle/oradata/EMREP/rc_data01.dbf’ size 100M;

Tablespace created.

SQL>
SQL> grant connect,resource,recoverY_catalog_owner to rc_admin;

Grant succeeded.

SQL>

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 RC_DATA.
1.2.2 Give the user the ability to manage a Recovery Catalog.

SQL> create user rc_admin identified by rc_admin default tablespace rc_data;

User created.

SQL>
RMAN> create catalog;

recovery catalog created

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

[oracle@ocm1 ~]$ rman target / catalog rc_admin/rc_admin@emrep

Recovery Manager: Release 10.2.0.2.0 - Production on Thu Jan 16 21:32:30 2014

connected to target database: PROD (DBID=248617215)
connected to recovery catalog database

RMAN>

1.3 Create a Recovery Catalog
1.3.1 Create the catalog in the EMREP database owned by RC_ADMIN
1.3.2 Register the PROD database with the catalog

Using RMAN
2.1 Configure RMAN options for the PROD database
2.1.1 Turn backup optimization on
2.1.2 Set your default channel to write to /home/oracle/backup (you may have to create this directory )
2.1.3 Turn on controlfile autobackups to write to /home/oracle/backup/control (you may have to create this directory)
2.1.4 Configure a retention window of 7 days.

RMAN> CONFIGURE BACKUP OPTIMIZATION On;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/home/oracle/backup/%d_%I_%T_%s_%p_%u.rbak’;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/home/oracle/backup/control/ctl_%F.bak’;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP On;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

RMAN> backup as compressed backupset database include current controlfile plus archivelog delete all input;

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 diretory)
3.2 Leave your databases open for review.

SYS@PROD> alter system set db_recovery_file_dest=’/home/oracle/flash’ scope=spfile;

System altered.

SYS@PROD>

SYS@PROD> alter system set db_recovery_file_dest_size=4G scope=spfile;

System altered.

SYS@PROD> startup force
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 1261164 bytes
Variable Size 125829524 bytes
Database Buffers 285212672 bytes
Redo Buffers 7127040 bytes
Database mounted.
Database opened.
SYS@PROD> show parameter recover

NAME TYPE VALUE

db_recovery_file_dest string /home/oracle/flash
db_recovery_file_dest_size big integer 4G
recovery_parallelism integer 0
SYS@PROD>

新增题
题的意思:三种可能.
1.要求日志组每5分钟切换一次.
2.归档5分钟产生一次
3.实例失败,redo丢失,最大容忍数据丢失5分钟.

SYS@PROD> show parameter archive%target

NAME TYPE VALUE

archive_lag_target integer 0
SYS@PROD> alter system set archive_lag_target=300;

System altered.

SYS@PROD>

中间休息时.考官会删除数据库中的某个文件(一般是1号文件).所以下一节上来要做恢复.

增加copy image归档

RMAN> backup as copy archivelog all;

RMAN> backup as copy archivelog logseq 50;

RMAN> backup as copy archivelog from logseq 50;

RMAN> backup as copy archivelog until logseq 50;

RMAN> backup as copy current controlfile;

RMAN> backup as copy datafile 7;

RMAN> backup as copy spfile;

list copy of …

备份 catalogDB
恢复题

1.system表空间文件

SYS@PROD> startup
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1261764 bytes
Variable Size 146804540 bytes
Database Buffers 369098752 bytes
Redo Buffers 7122944 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/u01/oracle/PROD/disk1/system01.dbf’
ORA-01251: Unknown File Header Version read for file number 1

SYS@PROD>

rman target /

report schema;
list backup of database;
list backup of datafile 1;

startup mount

restore datafile 1;

recover database;

alter database open;

2.ctl损坏其中之一

SYS@PROD> conn / as sysdba
Connected to an idle instance.
SYS@PROD> startup
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1261764 bytes
Variable Size 146804540 bytes
Database Buffers 369098752 bytes
Redo Buffers 7122944 bytes
ORA-00205: error in identifying control file, check alert log for more info

SYS@PROD>

SYS@PROD> select value from v$Parameter where name like ‘contro%’;

SYS@PROD> show parameter control

NAME TYPE VALUE

control_file_record_keep_time integer 7
control_files string /u01/oracle/PROD/disk1/control
01.ctl, /u01/oracle/PROD/disk2
/control02.ctl, /u01/oracle/PR
OD/disk3/control03.ctl, /u01/o
racle/PROD/disk4/control04.ctl

手动cp好的覆盖坏的

SYS@PROD> ! cp /u01/oracle/PROD/disk1/control01.ctl /u01/oracle/PROD/disk3/control03.ctl

SYS@PROD> alter database mount;

SYS@PROD> alter database open;
section 4 数据仓库
物化视图
预备知识

物化视图一个重要的特性为查询重写。
查询重写的对象为普通查询语句。
在查询语句中，Oracle总是搜寻from子句中所指定的数据源（表或视图）。
查询重写是指，当进行查询时，Oracle改写查询语句，搜寻其他数据源，
以在保证相同结果的情况下提高执行效率。
而这个新的数据源，则往往是物化视图。

就是将结果一致的查询对象由表重定向到MV.
此选项默认是关闭的.

物化视图另一个重要的特性为刷新.
物化视图有三种刷新方式：COMPLETE、FAST和FORCE。
完全刷新（COMPLETE）
会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），
然后根据物化视图中查询语句的定义重新生成物化视图。
快速刷新（FAST）
采用增量刷新的机制，
需要日志来记录增量信息,
只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。
采用FORCE方式，
Oracle会自动判断是否满足快速刷新的条件，
如果满足则进行快速刷新，否则进行完全刷新。

物化视图另一个重要的特性为可更新.
可以update MV
但数据只针对MV,不能将变更数据库写到基表.

物化视图的快速刷新要求基本必须建立物化视图日志
每个基础表要有独立的物化视图日志
物化视图日志在建立时有多种选项：
1.ROWID
2.PRIMARY KEY
3.OBJECT ID
4.SEQUENCE
5.明确指定列名
上面这些情况产生的物化视图日志的结构都不相同。

任何物化视图都会包括的4列：
SNAPTIME$$：用于表示刷新时间。
DMLTYPE$$：用于表示DML操作类型
I表示INSERT
D表示DELETE
U表示UPDATE。
OLD_NEW$$：用于表示这个值是新值还是旧值
N（EW）表示新值
O（LD）表示旧值
U表示UPDATE操作
CHANGE_VECTOR$$：表示修改矢量
用来表示被修改的是哪个或哪几个字段。

如果WITH后面跟了ROWID，
则物化视图日志中会包含M_ROW$$:
用来存储发生变化的记录的ROWID。
如果WITH后面跟了PRIMARY KEY，
则物化视图日志中会包含主键列。
如果WITH后面跟了OBJECT ID，
则物化视图日志中会包含SYS_NC_OID：用来记录每个变化对象的对象ID。如果WITH后面跟了SEQUENCE，则物化视图日子中会包含SEQUENCE$：
给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。
如果WITH后面跟了一个或多个COLUMN名称，
则物化视图日志中会包含这些列。

更多参见
http://otn.itpub.net/22308399/viewspace-750552/
http://www.blogjava.net/allan-oy/articles/57769.html

手动快速刷新考题

section 4 :

Fast Refreshable Materialized View
1.1 Using the query fo und in the mviewl.txt text file,
create a fast refreshable materialized view named PROD_MV in the SH schema.

/* query sql
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;
*/

每一from的表一个日志
日志包含的列是这条sql涉及的所有列

costs (TIME_ID,UNIT_COST,prod_id)
products (PROD_SUBCATEGORY,prod_id)

SH@PROD> select table_name,constraint_name,constraint_type
from user_constraints where table_name in (‘PRODUCTS’,‘COSTS’) and constraint_type=‘P’;

TABLE_NAME CONSTRAINT_NAME C

PRODUCTS PRODUCTS_PK P

SH@PROD>
SH@PROD> select column_name,CONSTRAINT_NAME from user_cons_columns where table_name=‘PRODUCTS’;

COLUMN_NAME CONSTRAINT_NAME

PROD_ID PRODUCTS_PK

CREATE MATERIALIZED VIEW LOG ON costs
WITH ROWID, SEQUENCE (TIME_ID,UNIT_COST,prod_id)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON products
WITH ROWID, SEQUENCE (PROD_SUBCATEGORY), PRIMARY KEY
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW sh.PROD_MV
REFRESH FAST ON COMMIT
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;

SH@PROD> exec DBMS_MVIEW.REFRESH(‘PROD_MV’,‘F’);

PL/SQL procedure successfully completed.

SH@PROD>

文档位置
books ==>
SQL Reference ==> 搜 create mat

CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG

分析每个列的对应归属表
SELECT c.time_id, p.prod_subcategory, SUM( c.unit_cost),
COUNT(c.unit_cost), COUNT(*)
FROM costs c, products p
WHERE c.prod_id = p.prod_id
GROUP BY c.time_id, p.prod_subcategory;

查询得出: 只有PRODUCTS.PROD_ID列有主键.
于是我们对PRODUCTS表建立基于主键的物化视图日志
对COSTS 表建立基于ROWID的物化视图日志
SH@PROD> select column_name,a.CONSTRAINT_NAME,CONSTRAINT_TYPE
from user_constraints A,USER_cons_columns b
where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
and a.table_name in (‘PRODUCTS’,‘COSTS’)
and CONSTRAINT_TYPE=‘P’;

COLUMN_NAME CONSTRAINT_NAME C

PROD_ID PRODUCTS_PK P

SH@PROD>

从文档中复制语句使用rowid方式建立语法(INCLUDING语法写的少了个i),

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE(amount_sold, time_id, prod_id)
INCLUDNG NEW VALUES;

改为COSTS表的MVLOG(别忘记补充文档中缺少的i):
CREATE MATERIALIZED VIEW LOG ON costs
WITH ROWID, SEQUENCE(time_id,unit_cost,prod_id)
INCLUDiNG NEW VALUES;

从文档中复制语句使用primary方式建立语法

CREATE MATERIALIZED VIEW LOG ON product_information
WITH ROWID, SEQUENCE (list_price, min_price, category_id), PRIMARY KEY
INCLUDING NEW VALUES;

改为PRODUCTS表的MVLOG(建立主键MVLOG时,就不用再sequence列表里写明主键列了)
CREATE MATERIALIZED VIEW LOG ON products
WITH ROWID, SEQUENCE (prod_subcategory), PRIMARY KEY
INCLUDING NEW VALUES;

建立物化视图
从文档中复制建立FAST MV语法,AS以后的就不用了,考题中提供了SQL

CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS

改为:
CREATE MATERIALIZED VIEW PROD_MV
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
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;

上述关键词,立即构建,快速刷新,查询重写语法上是有先后顺序的.

使用dbms_mview包来测试:

文档位置
BOOKS==>
PL/SQL Packages and Types Reference==> 搜 DBMS_MVIEW
REFRESH Procedures ==>

SH@PROD> exec DBMS_MVIEW.REFRESH(‘PROD_MV’,‘F’);

PL/SQL procedure successfully completed.

SH@PROD>

或者通过DBMS_MVIEW.EXPLAIN_MVIEW分析后的结果来判断
分析的结果存储在一张未建立的表里
SH@PROD> exec DBMS_MVIEW.EXPLAIN_MVIEW(‘PROD_MV’);
BEGIN DBMS_MVIEW.EXPLAIN_MVIEW(‘PROD_MV’); END;

ERROR at line 1:
ORA-30377: table SH.MV_CAPABILITIES_TABLE not found
ORA-00942: table or view does not exist
ORA-06512: at “SYS.DBMS_XRWMV”, line 22
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 3008
ORA-06512: at line 1

SH@PROD> 在EXPLAIN_MVIEW文档中搜索这个表名得出需要加载 utlxmv.sql

Usage Notes

You must run the utlxmv.sql script to create MV_CAPABILITIES_TABLE in the current schema

SH@PROD> @?/rdbms/admin/utlxmv.sql

Table created.

SH@PROD> exec DBMS_MVIEW.EXPLAIN_MVIEW(‘PROD_MV’);

PL/SQL procedure successfully completed.

SH@PROD>

SH@PROD> select CAPABILITY_NAME,POSSIBLE,MSGTXT from MV_CAPABILITIES_TABLE;

CAPABILITY_NAME P MSGTXT

PCT Y
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE Y
PCT_TABLE Y
PCT_TABLE N relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT Y
PCT_TABLE_REWRITE Y
PCT_TABLE_REWRITE N relation is not a partitioned table

16 rows selected.

SH@PROD>

GC快速刷新考题

section 4 :

Fast Refreshable Materialized View
1.1 Using the query fo und in the mviewl.txt text file,
create a fast refreshable materialized view named PROD_MV in the SH schema.

/* query sql
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;
*/

使用GC完成这个题:

删除之前手动建立的MV,MVLOG

SH@PROD> DROP MATERIALIZED VIEW “SH”.“PROD_MV”;

Materialized view dropped.

SH@PROD> DROP MATERIALIZED VIEW log on “SH”.PRODUCTS;

Materialized view log dropped.

SH@PROD> DROP MATERIALIZED VIEW log on “SH”.COSTS;

建立MV日志

建立MV

可更新物化视图考题

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.

表在PROD数据库里
物化视图建立在EMREP数据库里
这说明要在EMREP数据库里先建立一个DB-link

建立EMREP的 TNSNAMES.ORA

手动或netmgr都可以
可以从PROD中复制.

PROD=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.localdomain)(PORT=1521))
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=PROD)
)
)

EMREP=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm2.localdomain)(PORT=1521))
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=EMREP)
)
)

存盘测试
[oracle@ocm2 ~]$ sqlplus hr/hr@prod

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Jan 19 01:23:45 2014

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

SQL>
SQL>
SQL> show user
USER is “HR”
SQL> exit

在EMREP库建立DBLINK,并测试

DBlink语法文档位置
books ==>
==> SQL Reference 搜 database link

SQL> conn / as sysdba
Connected.
SQL> create public database link prod_hr_dblk connect to hr identified by hr using ‘PROD’;

Database link created.

SQL> select count(*) from tab;

COUNT(*)

3518

SQL> select count(*) from tab@prod_hr_dblk;

COUNT(*)

SQL>

在EMREP数据库建立MV, schema没要求,建议system里

从create MV文档中复制 for update的mv语法

CREATE MATERIALIZED VIEW foreign_customers FOR UPDATE
AS

改为:

CREATE MATERIALIZED VIEW EMP_UPD_MV FOR UPDATE
AS
select EMPLOYEE_ID , FIRST_NAME , LAST_NAME , PHONE_NUMBER , SALARY
from HR.EMPLOYEES@prod_hr_dblk;

SQL> conn system/oracle
Connected.
CREATE MATERIALIZED VIEW EMP_UPD_MV FOR UPDATE
AS
select EMPLOYEE_ID , FIRST_NAME , LAST_NAME , PHONE_NUMBER , SALARY
from HR.EMPLOYEES@prod_hr_dblk;

Materialized view created.

SQL>

GC 做法:

增加聚集类物化视图

题中给了一条SQL:
select distinct COLUMN_NAME from TABLE_NAME;
对这条sql做出一个物化视图,要求快速刷新,并查询重写

这条sql要改写为:
select COLUMN_NAME,count(*),count(COLUMN_NAME) from TABLE_NAME group by COLUMN_name;
才可以作为MV的子句使用

拿这条SQL为例
select distinct SALARY from employees;

表中数据结果
HR@PROD> select count(*),count(distinct SALARY) from employees;

COUNT(*) COUNT(DISTINCTSALARY)

107 57

HR@PROD>

先建立MV-log

CREATE MATERIALIZED VIEW LOG ON employees
WITH ROWID, SEQUENCE (SALARY)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW MY_EMP_MV
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS
select distinct SALARY from employees;

HR@PROD> CREATE MATERIALIZED VIEW MY_EMP_MV
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS
select distinct SALARY from employees;
HR@PROD>
select distinct SALARY from employees
*
ERROR at line 6:
ORA-12015: cannot create a fast refresh materialized view from a complex query

HR@PROD>

HR@PROD> CREATE MATERIALIZED VIEW MY_EMP_MV
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS AS
select SALARY from employees group by SALARY;

Materialized view created.

HR@PROD>

外部表

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 an external table names PROD_MASTER in the SH schema of the PROD database.

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.

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 setp.

oracle loader

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 an external table names PROD_MASTER in the SH schema of the PROD database.

外部表建立语法十分复杂.
参照文档也耗时比较长,
建议使用sqlldr构建外部表语法速度比较快.

我们没有prod_master.dat文件中的数据.所以自己伪造一个

[oracle@ocm1 scripts]$ awk -F: ‘{print $3,$1,$7}’ /etc/passwd > prod_master.dat

考试的数据文件中可能出现空列,这个伪造文件中的第三列也存在null列

[oracle@ocm1 scripts]$ awk ‘NF<3{print}’ prod_master.dat
9 news
[oracle@ocm1 scripts]$

看prod_master.ctl参数中是否存在对null列的处理.如果没有需要添加.

[oracle@ocm1 scripts]$ cat prod_master.ctl
LOAD DATA
INFILE ‘/home/oracle/scripts/prod_master.dat’
INTO TABLE sh.exm
FIELDS TERMINATED BY whitespace
(id,ename,shell)
[oracle@ocm1 scripts]$

文档 BOOS==>
Utilities ==> 搜 SQLLoader Con
8 SQLLoader Control File Reference ==>

Example 8-1 Sample Control File ==> 包含: TRAILING NULLCOLS

[oracle@ocm1 scripts]$ cat prod_master.ctl
LOAD DATA
INFILE ‘/home/oracle/scripts/prod_master.dat’
INTO TABLE sh.exm
FIELDS TERMINATED BY whitespace
TRAILING NULLCOLS
(id,ename,shell)
[oracle@ocm1 scripts]$

[oracle@ocm1 scripts]$ sqlldr 2>&1 | grep external_table
external_table – use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)
[oracle@ocm1 scripts]$

[oracle@ocm1 scripts]$ sqlldr userid=sh/sh control=prod_master.ctl external_table=GENERATE_ONLY

SQL*Loader: Release 10.2.0.2.0 - Production on Sun Jan 19 16:00:27 2014

SQL*Loader-941: Error during describe of table SH.EXM
ORA-04043: object SH.EXM does not exist
[oracle@ocm1 scripts]$

SH@PROD> create table exm(id number,ename varchar2(50),shell varchar2(50));

Table created.

SH@PROD>

[oracle@ocm1 scripts]$ sqlldr userid=sh/sh control=prod_master.ctl external_table=GENERATE_ONLY

SQL*Loader: Release 10.2.0.2.0 - Production on Sun Jan 19 16:01:47 2014

[oracle@ocm1 scripts]$ ls 如果看到有bad的文件需要处理.
afiedt.buf prod_master.ctl prod_master.log
ex.sql prod_master.dat prod_master.log_xt
[oracle@ocm1 scripts]$

看prod_master.log文件内容
…
CREATE DIRECTORY statements needed for files

CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS ‘/home/oracle/scripts’
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS ‘/home/oracle/scripts/’
…

建立数据库目录使用scripts/ 结尾带路径的. 否则可能有bug

名称用系统取得就可以

SYS@PROD> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS ‘/home/oracle/scripts/’;

Directory created.

SYS@PROD> grant read,write on DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 to sh;

Grant succeeded.

SYS@PROD>

看prod_master.log文件内容
…
CREATE TABLE statement for external table:

CREATE TABLE “PROD_MASTER”
(
“ID” NUMBER,
“ENAME” VARCHAR2(50),
“SHELL” VARCHAR2(50)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE ‘SYS_SQLLDR_XT_TMPDIR_00000’:‘prod_master.bad’
LOGFILE ‘prod_master.log_xt’
READSIZE 1048576
FIELDS TERMINATED BY WHITESPACE LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
“ID” CHAR(255)
TERMINATED BY WHITESPACE,
“ENAME” CHAR(255)
TERMINATED BY WHITESPACE,
“SHELL” CHAR(255)
TERMINATED BY WHITESPACE
)
)
location
(
‘prod_master.dat’
)
)REJECT LIMIT UNLIMITED

…

将表名修改为题中要求的 PROD_MASTER 即可.

验证:
SH@PROD> select count(*) from prod_master;

COUNT(*)

SH@PROD> ! wc -l /home/oracle/scripts/prod_master.dat
36 /home/oracle/scripts/prod_master.dat

SH@PROD>
data pump

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.

这个是使用datapump导出

将上一题中建立外部表语句拿来修改

CREATE TABLE “PROD_MASTER”
(
“ID” NUMBER,
“ENAME” VARCHAR2(50),
“SHELL” VARCHAR2(50)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY PROD_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE ‘SYS_SQLLDR_XT_TMPDIR_00000’:‘prod_master.bad’
LOGFILE ‘prod_master.log_xt’
READSIZE 1048576
FIELDS TERMINATED BY WHITESPACE LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
“ID” CHAR(255)
TERMINATED BY WHITESPACE,
“ENAME” CHAR(255)
TERMINATED BY WHITESPACE,
“SHELL” CHAR(255)
TERMINATED BY WHITESPACE
)
)
location
(
‘prod_master.dat’
)
)REJECT LIMIT UNLIMITED

修改为:
1.去掉列部分
2.去掉访问参数部分
3.修改导出文件名
4.添加查询语句

CREATE TABLE SH.“COUNTRIES_EXT”
ORGANIZATION external
(
TYPE oracle_datapump
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
location
(
‘prod_master.dp’
)
) as
select COUNTRY_ID , COUNTRY_NAME,COUNTRY_REGION from SH.COUNTRIES;

SH@PROD> CREATE TABLE SH.“COUNTRIES_EXT”
ORGANIZATION external
(
TYPE oracle_datapump
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
location
(
‘prod_master.dp’
)
) as
select COUNTRY_ID , COUNTRY_NAME,COUNTRY_REGION from SH.COUNTRIES;

Table created.

SH@PROD>
SH@PROD> select count(*) from COUNTRIES_EXT;

COUNT(*)

SH@PROD>
[oracle@ocm1 scripts]$ file prod_master.dp
prod_master.dp: PDP-11 UNIX/RT ldp
[oracle@ocm1 scripts]$

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 setp.

将4.1中的导出文件,传到ocm2机器,目录随意.导入到EMREP数据库的system用户中.
例子中的数据库目录 /home/oracle/dp_dir/

[oracle@ocm1 scripts]$ pwd
/home/oracle/scripts
[oracle@ocm1 scripts]$ ls prod_master.dp
prod_master.dp
[oracle@ocm1 scripts] $[oracle@ocm1 scripts]$ scp prod_master.dp ocm2:/home/oracle/dp_dir/
oracle@ocm2’s password:
prod_master.dp 100% 12KB 12.0KB/s 00:00
[oracle@ocm1 scripts]$

[oracle@ocm2 dp_dir]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Jan 19 16:57:53 2014

SQL> conn system/oracle
Connected.

SQL> create directory dp_dir as ‘/home/oracle/dp_dir/’;

Directory created.

SQL>

从3.1题复制建立外部表语句,或者获取元数据:

改为:

CREATE TABLE system.“COUNTRIES_EXT”
(
COUNTRY_ID NUMBER,
COUNTRY_NAME VARCHAR2(50),
COUNTRY_REGION VARCHAR2(50)
)
ORGANIZATION external
(
TYPE oracle_datapump
DEFAULT DIRECTORY DB_DIR
location
(
‘prod_master.dp’
)
)REJECT LIMIT UNLIMITED
第二天上午
section 5 数据管理

section 5 :
/*
export ORACLE_SID=PROD
SQL> create user sst identified by sst default tablespace oltp;
SQL> grant dba to sst;
SQL> conn sst/sst
SQL> create table t as select * from all_objects;
[oracle@vmrac1 ~]$exp sst/sst@prod file=sst.dmp
SQL> drop user sst cascade;
./import_oltp_batch.sh
*/

Transportable Tablespace
1.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 ).
1.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.

Create Additional Buffer Cache
2.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.

Working with LOB Data
3.1 Create a new tablespace named LOB_DATA in the PROD database to store lob data
and lob indexes with the following specifications:
3.1.1 Create 2 datafiles each in different location.
3.1.2 Each file should be 64MB in size
3.1.3 Block size 16KB
3.1.4 Determine which type of extent management would be best for lob data and configure the extents appropriately.

Manage Schema Data
4.1 Create a new table in the HR schema in the PROD database with the following specifications:
4.1.1 Table name MAGAZINE_ARTICLES
4.1.2 Tablespace USERS
4.1.3 Column names
4.1.3.1 AUTHOR VARCHAR2(30)
4.1.3.2 ARTICLE_NAME VARCHAR2(50)
4.1.3.3 ARTICLE_DATE DATE
4.1.3.4 ARTICLE_DATA CLOB
4.1.3.4.1 Tablespace LOB_DATA with a 16KB chunk size, initial and next extents each with a size of 2MB
4.1.3.4.2 Use the nocache option and disable storage in row.
4.1.4 Use import to populate the HR.MAGAZINE_ARTICLES table with data from the exp_mag.dmp file.

从HR.MAGAZINE_ARTICLES表的ARTICLE_DATA(LOB列)里,过滤出带有Oracle9i字符串出现大于等于3次的行
把符合条件的行的rowid插入到ORACLE9I_REFERENCES.ORACLE9I_ARTICLE列,并且记录下插入时的timestamp.

Partitioning
5.1 Create 5 new tablespaces in the PROD database as follows:
5.1.1 Use the name DATA01, DATA02, DATA03, DATA04 and DATA05.
5.1.2 Spread the datafiles across different disk directories.
5.1.3 Each file should be 250MB in size.
5.1.4 Use uniform extents of 4MB.
5.1.5 Block size should be 16KB.

5.3 Run the populate_sales_hist.sql script located in the /home/oracle/scripts directory
to populate the SALES_HISTORY table with data.

SH@PROD> insert into sh.SALES_HISTORY select * from oltp_user.sales;

sh> @ /home/oracle/scripts/populate_sales_hist.sql

create index SALES_HISTORY_DATE_IDX on SALES_HISTORY(SDATE) local

5.6 Query the data in the SALES_HISTORY table to validate the use of each of the indexes.

SH@PROD> alter index SALES_HISTORY_PK noMONITORING usage;

Index altered.

SH@PROD> alter index SALES_HISTORY_DATE_IDX noMONITORING usage;

Index altered.

SH@PROD> select INDEX_NAME,TABLE_NAME,MONITORING,USED from v$object_usage;

5.7 Truncate partition P1 of the SALES_HISTORY and ensure all indexes are availble
for use both during and after the truncate command complete

SH@PROD> alter table SALES_HISTORY truncate partition q2 update indexes;

dba_ind_partitions.status
alter index SALES_HISTORY_pk rebuild partition SYS_P24;

Fine-Grained Auditing
6.1 Set up FGA on the SALARY and COMMISSION_PCT columns of the EMPLOYEES table in the HR schema of the PROD database.
An audit record should be created if either of these two columns are
selected as part of the output of a query or are used in the where condition of
a select statement and their values are not null.
6.2 Validate that the FGA is taking place by executing statements that should
result in auditing records being created and by executing statements
where no auditing records will be generated. Do not delete your audit records.

1.sys
2.用户 (表级别)
3.用操作的列级 + 列条件

Flashback
7.1 Create a table named ORIGINAL_SALARY in the HR schema in the PROD database
that includes the employee_id, commission_pct and salaries of all records in the HR.EMPLOYEES table.
Note the date and time and then commit the changes.
7.2 Delete all employees in the HR.ORIGINAL_SALARY table whose HIRE_DATE is before 1994.
Commit the changes.
Create a view HR.SALARY_VIEW that will show all the original rows before the deletion.

trans TBS
section 5 :
/*
export ORACLE_SID=PROD
SQL> create user sst identified by sst default tablespace oltp;
SQL> grant dba to sst;
SQL> conn sst/sst
SQL> create table t as select * from all_objects;
[oracle@vmrac1 ~]$imp sst/sst@prod file=sst.dmp
SQL> drop user sst cascade;
./import_oltp_batch.sh
*/

Transportable Tablespace
1.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 ).
1.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.

[oracle@pdg1 dp_dir]$ expdp userid=scott/tiger directory=dp_dir dumpfile=scott_all_notab.dmp ;
[oracle@pdg1 dp_dir] $[oracle@pdg1 dp_dir]$
[oracle@pdg1 dp_dir]$ impdp userid=scott/tiger directory=dp_dir dumpfile=scott_all.dmp exclude=table,index,constraint,TRIGGER
[oracle@pdg1 dp_dir] $[oracle@pdg1 dp_dir]$

解答:

1.1:
执行脚本将sst用户的对象导出到 sst.dmp,
再将sst.dmp导入到OLTP_USER用户.
就是将一个schema的对象通过imp/exp传递到另一个schema.

SH@PROD> conn / as sysdba
Connected.
SYS@PROD> create user sst identified by sst default tablespace oltp;

User created.

SYS@PROD> grant dba to sst;

Grant succeeded.

SYS@PROD> conn sst/sst
Connected.
SST@PROD> create table t as select * from all_objects;

Table created. 我们没有那个建立对象的脚本,自己制造点数据在SST中

SST@PROD> exit
[oracle@ocm1 scripts]$ exp sst/sst@prod file=sst.dmp

导入到oltp_user用户.

SYS@PROD> grant connect,resource to oltp_user identified by oltp_user ;

Grant succeeded.

SYS@PROD>

SYS@PROD> select tablespace_name from dba_tablespaces where tablespace_name=‘OLTP’;

TABLESPACE_NAME

OLTP

SYS@PROD> alter user oltp_user default tablespace oltp;

User altered.

SYS@PROD>

[oracle@ocm1 scripts]$ imp userid=’/ as sysdba’ file=sst.dmp fromuser=sst touser=oltp_user

Import: Release 10.2.0.2.0 - Production on Sun Jan 19 18:01:17 2014

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.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 US7ASCII 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 “T” 9618 rows imported
Import terminated successfully without warnings.
[oracle@ocm1 scripts]$

SYS@PROD> select tablespace_name,segment_name,segment_type from dba_segments where owner=‘OLTP_USER’;

TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE

OLTP T TABLE

SYS@PROD>

1.2:
将PROD数据库的OLTP表空间传输到EMREP数据库
1.获取OLTP表空间的数据文件.检查子包含关系.字符集.对象的schema.平台
2.将表空间设置为RO
3.导出元数据
4.将导出元数据文件和DBF一同传输到EMREP主机,检测OLTP_USER是否存在
5.安置EMREP的数据文件位置.
6.将元数据导入并连接DBF
7.将两端表空间都改为RW

SYS@PROD> select tablespace_name,file_name from dba_data_files where tablespace_name=‘OLTP’;

TABLESPACE_NAME FILE_NAME

OLTP /u01/app/oracle/PROD/Disk1/oltp01.dbf

SYS@PROD>
SYS@PROD> select owner,tablespace_name,segment_name,segment_type from dba_segments where tablespace_name=‘OLTP’;

OWNER TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE

OLTP_USER OLTP T TABLE

SYS@PROD>
检查自包含关系
文档位置
BOOKS >
PL/SQL Packages and Types Reference> 搜 dbms_tts

SYS@PROD> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘OLTP’, TRUE);

PL/SQL procedure successfully completed.

SYS@PROD> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SYS@PROD>
SYS@PROD> ALTER TABLESPACE OLTP read only;

Tablespace altered.

SYS@PROD>

[oracle@ocm1 scripts]$ exp userid=’/ as sysdba’ file=transoltp.dmp TRANSPORT_TABLESPACE=y TABLESPACES=oltp

[oracle@ocm1 scripts]$ scp /u01/app/oracle/PROD/Disk1/oltp01.dbf transoltp.dmp ocm2:/home/oracle/dp_dir/
oracle@ocm2’s password:
oltp01.dbf 100% 48MB 48.0MB/s 00:01
transoltp.dmp 100% 16KB 16.0KB/s 00:00
[oracle@ocm1 scripts]$

在 EMREP导入

SQL> conn / as sysdba
Connected.

SQL> select file_name from dba_data_files;

FILE_NAME

/opt/oracle/oradata/EMREP/system01.dbf
/opt/oracle/oradata/EMREP/undotbs01.dbf
/opt/oracle/oradata/EMREP/sysaux01.dbf
/opt/oracle/oradata/EMREP/users01.dbf
/opt/oracle/oradata/EMREP/mgmt.dbf
/opt/oracle/oradata/EMREP/mgmt_ecm_depot1.dbf
/opt/oracle/oradata/EMREP/rc_data01.dbf

7 rows selected.

SQL>

将数据文件放在一起

[oracle@ocm2 dp_dir]$ ls
oltp01.dbf prod_master.dp transoltp.dmp
[oracle@ocm2 dp_dir]$ mv oltp01.dbf /opt/oracle/oradata/EMREP/
[oracle@ocm2 dp_dir]$

[oracle@ocm2 dp_dir]$ imp userid=’/ as sysdba’ file=transoltp.dmp TRANSPORT_TABLESPACE=y TABLESPACES=oltp DATAFILES=’/opt/oracle/oradata/EMREP/oltp01.dbf’

Import: Release 10.2.0.2.0 - Production on Sun Jan 19 18:18:50 2014

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

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata…
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS’s objects into SYS
. importing SYS’s objects into SYS
IMP-00017: following statement failed with ORACLE error 29342:
“BEGIN sys.dbms_plugts.checkUser(‘OLTP_USER’); END;”
IMP-00003: ORACLE error 29342 encountered
ORA-29342: user OLTP_USER does not exist in the database
ORA-06512: at “SYS.DBMS_PLUGTS”, line 1895
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
[oracle@ocm2 dp_dir] $[oracle@ocm2 dp_dir]$

SQL> grant connect,resource to oltp_user identified by oltp_user;

Grant succeeded.

SQL> 如果考试环境涉及多个用户,需要建立每个用户

[oracle@ocm2 dp_dir]$ imp userid=’/ as sysdba’ file=transoltp.dmp TRANSPORT_TABLESPACE=y TABLESPACES=oltp DATAFILES=’/opt/oracle/oradata/EMREP/oltp01.dbf’

Import: Release 10.2.0.2.0 - Production on Sun Jan 19 18:24:15 2014

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

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata…
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS’s objects into SYS
. importing SYS’s objects into SYS
. importing OLTP_USER’s objects into OLTP_USER
. . importing table “T”
. importing SYS’s objects into SYS
Import terminated successfully without warnings.
[oracle@ocm2 dp_dir]$

将两端表空间设置为RW

SYS@PROD> alter tablespace oltp read write;

Tablespace altered.

SYS@PROD>
SYS@EMREP> alter tablespace oltp read write;

Tablespace altered.

SYS@EMREP> exit

非标准块

Create Additional Buffer Cache
2.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.

@> conn / as sysdba
Connected.
SYS@PROD> show parameter 16k

NAME TYPE VALUE

db_16k_cache_size big integer 0
SYS@PROD> alter system set db_16k_cache_size=20M;

System altered.

SYS@PROD>

LOB TBS

Working with LOB Data
3.1 Create a new tablespace named LOB_DATA in the PROD database to store lob data
and lob indexes with the following specifications:
3.1.1 Create 2 datafiles each in different location.
3.1.2 Each file should be 64MB in size
3.1.3 Block size 16KB
3.1.4 Determine which type of extent management would be best for lob data and configure the extents appropriately.

自动分配.段越大,一次分配的区大小越大.

SYS@PROD>
SYS@PROD> select tablespace_name,file_name,bytes from dba_data_files;

TABLESPACE_NAME FILE_NAME BYTES

SYSTEM /u01/app/oracle/PROD/Disk1/system01.dbf 340787200
UNDOTBS /u01/app/oracle/PROD/Disk1/undotbs01.dbf 209715200
SYSAUX /u01/app/oracle/PROD/Disk1/sysaux01.dbf 340787200
EXAMPLE /u01/app/oracle/PROD/Disk1/example01.dbf 419430400
INDX /u01/app/oracle/PROD/Disk1/indx01.dbf 41943040
TOOLS /u01/app/oracle/PROD/Disk1/tools01.dbf 10485760
USERS /u01/app/oracle/PROD/Disk1/users01.dbf 50331648
OLTP /u01/app/oracle/PROD/Disk1/oltp01.dbf 50331648
REGISTRATION /u01/app/oracle/PROD/Disk1/REGISTRATION01.dbf 94371840

9 rows selected.

SYS@PROD> create tablespace lob_data datafile ‘/u01/app/oracle/PROD/Disk1/lob_data01.dbf’ size 64M,
‘/u01/app/oracle/PROD/Disk1/lob_data02.dbf’ size 64M
blocksize 16K ;

Tablespace created.

SYS@PROD>

因为下一个题就要求区大小是2M; 为了避免oracle说我们知道题.先自动建出来.
下一题先删除.再建立.
虽然我们可以在表建立时制定表的初始和下一次大小,但因为表空间设置好了,会忽略表的这些参数的.
时间紧张的就直接设置2M,下一个题就不用动了.

Manage Schema Data
4.1 Create a new table in the HR schema in the PROD database with the following specifications:
4.1.1 Table name MAGAZINE_ARTICLES
4.1.2 Tablespace USERS
4.1.3 Column names
4.1.3.1 AUTHOR VARCHAR2(30)
4.1.3.2 ARTICLE_NAME VARCHAR2(50)
4.1.3.3 ARTICLE_DATE DATE
4.1.3.4 ARTICLE_DATA CLOB
4.1.3.4.1 Tablespace LOB_DATA with a 16KB chunk size, initial and next extents each with a size of 2MB
4.1.3.4.2 Use the nocache option and disable storage in row.
4.1.4 Use import to populate the HR.MAGAZINE_ARTICLES table with data from the exp_mag.dmp file.

SYS@PROD> drop tablespace lob_data including contents and datafiles;

Tablespace dropped.

SYS@PROD> create tablespace lob_data datafile ‘/u01/app/oracle/PROD/Disk1/lob_data01.dbf’ size 64M,
‘/u01/app/oracle/PROD/Disk1/lob_data02.dbf’ size 64M
blocksize 16K
uniform size 2M;

Tablespace created.

SYS@PROD>

SYS@PROD> conn hr/hr
Connected.
HR@PROD> create table MAGAZINE_ARTICLES (AUTHOR VARCHAR2(30),
ARTICLE_NAME VARCHAR2(50),
ARTICLE_DATE DATE,
ARTICLE_DATA CLOB)
tablespace users;

Table created.

HR@PROD> select dbms_metadata.get_ddl(‘TABLE’,‘MAGAZINE_ARTICLES’) FROM DUAL;

DBMS_METADATA.GET_DDL(‘TABLE’,‘MAGAZINE_ARTICLES’)

CREATE TABLE “HR”.“MAGAZINE_ARTICLES”
( “AUTHOR” VARCHAR2(30),
“ARTICLE_NAME” VARCHAR2(50),
“ARTICLE_DATE” DATE,
“ARTICLE_DATA” CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USERS”
LOB (“ARTICLE_DATA”) STORE AS (
TABLESPACE “USERS” ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

HR@PROD>

HR@PROD> DROP TABLE “HR”.“MAGAZINE_ARTICLES” PURGE;

Table dropped.

HR@PROD>

改元数据语句:
CREATE TABLE “HR”.“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”
disABLE STORAGE IN ROW
CHUNK 16384
NOCACHE
STORAGE(INITIAL 2097152 NEXT 2097152))

再去建立

验证

HR@PROD> select TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE,EXTENT_ID,BYTES
from user_extents where tablespace_name in (‘USERS’,‘LOB_DATA’);

TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BYTES

USERS MLOG $_EMPLOYEES TABLE 0 4194304 USERS MY_EMP_MV TABLE 0 4194304 USERS MAGAZINE_ARTICLES TABLE 0 4194304 USERS I_SNAP$ _MY_EMP_MV INDEX 0 4194304
LOB_DATA SYS_IL0000010941C00004$$ LOBINDEX 0 2097152
LOB_DATA SYS_LOB0000010941C00004$$ LOBSEGMENT 0 2097152

6 rows selected.

HR@PROD>

还要向这张表加载数据 .

New node

SYS@PROD> conn hr/hr
Connected.
HR@PROD> create table ORACLE9I_REFERENCES (ORACLE9I_ARTICLE ROWID,
INSERT_TIME TIMESTAMP WITH LOCAL TIME ZONE)
tablespace users;

Table created.

HR@PROD>

分区表

Partitioning
5.1 Create 5 new tablespaces in the PROD database as follows:
5.1.1 Use the name DATA01, DATA02, DATA03, DATA04 and DATA05.
5.1.2 Spread the datafiles across different disk directories.
5.1.3 Each file should be 250MB in size.
5.1.4 Use uniform extents of 4MB.
5.1.5 Block size should be 16KB.

HR@PROD> conn / as sysdba
Connected.
SYS@PROD> select name from v$datafile where rownum=1;

NAME

/u01/app/oracle/PROD/Disk1/system01.dbf

SYS@PROD>

SYS@PROD> ! ls /u01/app/oracle/PROD/
Disk1 Disk2 Disk3 Disk4 Disk5

SYS@PROD>

create tablespace data01 datafile ‘/u01/app/oracle/PROD/Disk1/data01_1.dbf’ size 250M uniform size 4M blocksize 16K;
create tablespace data02 datafile ‘/u01/app/oracle/PROD/Disk2/data02_1.dbf’ size 250M uniform size 4M blocksize 16K;
create tablespace data03 datafile ‘/u01/app/oracle/PROD/Disk3/data03_1.dbf’ size 250M uniform size 4M blocksize 16K;
create tablespace data04 datafile ‘/u01/app/oracle/PROD/Disk4/data04_1.dbf’ size 250M uniform size 4M blocksize 16K;
create tablespace data05 datafile ‘/u01/app/oracle/PROD/Disk5/data05_1.dbf’ size 250M uniform size 4M blocksize 16K;

SYS@PROD> select tablespace_name,file_name,bytes from dba_data_files where tablespace_name like ‘DATA%’;

TABLESPACE_NAME FILE_NAME BYTES

DATA01 /u01/app/oracle/PROD/Disk1/data01_1.dbf 262144000
DATA02 /u01/app/oracle/PROD/Disk2/data02_1.dbf 262144000
DATA03 /u01/app/oracle/PROD/Disk3/data03_1.dbf 262144000
DATA04 /u01/app/oracle/PROD/Disk4/data04_1.dbf 262144000
DATA05 /u01/app/oracle/PROD/Disk5/data05_1.dbf 262144000

SYS@PROD>

5.2 Create a partitioned table named SALES_HISTORY in the SH schema in the PROD database.
Use the following specifications:
5.2.1 The column names and definitions will be the same as the OLTP_USER.SALES table
5.2.2 Partition the table into 5 different partitions on the SDATE column using the following specifications:
5.2.2.1 Partition P1 will contain data for 1998 and should be placed in the DATA01 tablespace.
5.2.2.2 Partition P2 will contain data for 1999 and should be placed in the DATA02 tablespace.
5.2.2.3 Partition P3 will contain data for 2000 and should be placed in the DATA03 tablespace.
5.2.2.4 Partition P4 will contain data for 2001 and should be placed in the DATA04 tablespace.
5.2.2.5 Partition P5 will contain data for 2002 and should be placed in the DATA05 tablespace.

我们没有OLTP_USER.SALES表自己构建一下.
SH@PROD> grant select on SALES to oltp_user;

Grant succeeded.

SH@PROD>
OLTP_USER@PROD> create table sales as select rownum orderid,s.*,TIME_ID sdate from sh.sales s;

Table created.

OLTP_USER@PROD> 为了满足以后的题目要求,构建了ORDERID,sdate列
OLTP_USER@PROD> grant select on oltp_user.sales to sh;

Grant succeeded.

OLTP_USER@PROD>

文档位置
BOOKS ==>
SQL Reference ==> 搜create table
CREATE TABLE ==> 最下面或者搜 Range Partitioning

create table SALES_HISTORY
PARTITION BY RANGE (time_id)
(PARTITION P1 VALUES LESS THAN (TO_DATE(‘01-01-1999’,‘DD-MM-YYYY’)) TABLESPACE DATA01,
PARTITION P2 VALUES LESS THAN (TO_DATE(‘01-01-2000’,‘DD-MM-YYYY’)) TABLESPACE DATA02,
PARTITION P3 VALUES LESS THAN (TO_DATE(‘01-01-2001’,‘DD-MM-YYYY’)) TABLESPACE DATA03,
PARTITION P4 VALUES LESS THAN (TO_DATE(‘01-01-2002’,‘DD-MM-YYYY’)) TABLESPACE DATA04,
PARTITION P5 VALUES LESS THAN (maxvalue) TABLESPACE DATA05
)
as select * from oltp_user.sales s where 0=9;

SH@PROD> select tablespace_name,PARTITION_NAME
from user_tab_partitions
where TABLE_NAME=‘SALES_HISTORY’;

TABLESPACE_NAME PARTITION_NAME

DATA01 P1
DATA02 P2
DATA03 P3
DATA04 P4
DATA05 P5

SH@PROD>

5.3 Run the populate_sales_hist.sql script located in the /home/oracle/scripts directory
to populate the SALES_HISTORY table with data.

没这个脚本随意插入点数据

SH@PROD> insert into sales_history select * from oltp_user.sales s;

918843 rows created.

SH@PROD> commit;

Commit complete.

SH@PROD>

SH@PROD> select count(*) from SALES_HISTORY;

COUNT(*)

918843

SH@PROD>

SH@PROD> select count(*) from SALES_HISTORY partition (P1);

COUNT(*)

178834

SH@PROD> select count(*) from SALES_HISTORY partition (P2);

COUNT(*)

247945

SH@PROD> select count(*) from SALES_HISTORY partition (P3);

COUNT(*)

232646

SH@PROD> select count(*) from SALES_HISTORY partition (P4);

COUNT(*)

259418

SH@PROD> select count(*) from SALES_HISTORY partition (P5);

COUNT(*)

SH@PROD>

5.4 Create a unique index named SALES_HISTORY_PK
in the SH schema of PROD database on the SALES_HISTORY table.
Partition the index into 4 partitions with each partition containing approximately
the same amount of entries following specification:
5.4.1 Create the index to include the ORDERID column.
5.4.2 Create the index in the INDX tablespace.
5.4.3 Create the index with parallelism degree 4.

文档位置
BOOKS==>
SQL Reference => 搜 create index
==> global_partitioned_index

SH@PROD> CREATE UNIQUE INDEX SALES_HISTORY_PK on SALES_HISTORY(ORDERID)
parallel 4
tablespace indx
global
partition by hash(ORDERID)
partitions 4;

Index created.

SH@PROD>

文档位置
BOOKS==>
SQL Reference => 搜 create index
==> local_partitioned_index

SH@PROD> CREATE INDEX SALES_HISTORY_DATE_IDX on SALES_HISTORY(SDATE) local;

Index created.

SH@PROD>

验证
SH@PROD> select INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME
from user_ind_partitions
where index_name in (‘SALES_HISTORY_DATE_IDX’,‘SALES_HISTORY_PK’);

INDEX_NAME PARTITION_NAME TABLESPACE_NAME

SALES_HISTORY_DATE_IDX P1 DATA01
SALES_HISTORY_DATE_IDX P2 DATA02
SALES_HISTORY_DATE_IDX P3 DATA03
SALES_HISTORY_DATE_IDX P4 DATA04
SALES_HISTORY_DATE_IDX P5 DATA05
SALES_HISTORY_PK SYS_P21 INDX
SALES_HISTORY_PK SYS_P22 INDX
SALES_HISTORY_PK SYS_P23 INDX
SALES_HISTORY_PK SYS_P24 INDX

9 rows selected.

SH@PROD>

5.6 Query the data in the SALES_HISTORY table to validate the use of each of the indexes.

5.7 Truncate partition P1 of the SALES_HISTORY and ensure all indexes are availble
for use both during and after the truncate command complete

文档位置:
BOOKS==>
SQL Reference => 搜 ALTER TABLE
ALTER TABLE ==> 搜 truncate partition

SH@PROD>select INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME,status from user_ind_partitions where index_name in (UPPER(‘ind_range_ext_onm’),UPPER(‘ind_range_ext_crtd’))

SH@PROD> /

INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS

SALES_HISTORY_DATE_IDX P1 DATA01 USABLE
SALES_HISTORY_DATE_IDX P2 DATA02 USABLE
SALES_HISTORY_DATE_IDX P3 DATA03 USABLE
SALES_HISTORY_DATE_IDX P4 DATA04 USABLE
SALES_HISTORY_DATE_IDX P5 DATA05 USABLE
SALES_HISTORY_PK SYS_P21 INDX USABLE
SALES_HISTORY_PK SYS_P22 INDX USABLE
SALES_HISTORY_PK SYS_P23 INDX USABLE
SALES_HISTORY_PK SYS_P24 INDX USABLE

9 rows selected.

SH@PROD>

SH@PROD> alter table SALES_HISTORY truncate partition p1 update indexes;

Table truncated.

SH@PROD>

如果忘记加这个update indexes选项.即使再truncate补上选项也没用.只能对每个分区索引逐个rebuild
所以考试时一定要加,不要把时间白白浪费.
下面演示这个过程:

SH@PROD> alter table SALES_HISTORY truncate partition p2;

Table truncated.

select INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME,status
from user_ind_partitions
where index_name in (‘SALES_HISTORY_DATE_IDX’,‘SALES_HISTORY_PK’);

INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS

SALES_HISTORY_DATE_IDX P1 DATA01 USABLE
SALES_HISTORY_DATE_IDX P2 DATA02 USABLE
SALES_HISTORY_DATE_IDX P3 DATA03 USABLE
SALES_HISTORY_DATE_IDX P4 DATA04 USABLE
SALES_HISTORY_DATE_IDX P5 DATA05 USABLE
SALES_HISTORY_PK SYS_P21 INDX UNUSABLE
SALES_HISTORY_PK SYS_P22 INDX UNUSABLE
SALES_HISTORY_PK SYS_P23 INDX UNUSABLE
SALES_HISTORY_PK SYS_P24 INDX UNUSABLE

9 rows selected.

SH@PROD>

SH@PROD> alter table SALES_HISTORY truncate partition p2 update indexes;

Table truncated.

select INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME,status
from user_ind_partitions
where index_name in (‘SALES_HISTORY_DATE_IDX’,‘SALES_HISTORY_PK’);

INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS

SALES_HISTORY_DATE_IDX P1 DATA01 USABLE
SALES_HISTORY_DATE_IDX P2 DATA02 USABLE
SALES_HISTORY_DATE_IDX P3 DATA03 USABLE
SALES_HISTORY_DATE_IDX P4 DATA04 USABLE
SALES_HISTORY_DATE_IDX P5 DATA05 USABLE
SALES_HISTORY_PK SYS_P21 INDX UNUSABLE
SALES_HISTORY_PK SYS_P22 INDX UNUSABLE
SALES_HISTORY_PK SYS_P23 INDX UNUSABLE
SALES_HISTORY_PK SYS_P24 INDX UNUSABLE

9 rows selected.

SH@PROD>

SH@PROD> alter index SALES_HISTORY_PK rebuild partition SYS_P21;

Index altered.

SH@PROD> select INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME,status
from user_ind_partitions
where index_name in (‘SALES_HISTORY_DATE_IDX’,‘SALES_HISTORY_PK’);

INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS

SALES_HISTORY_DATE_IDX P1 DATA01 USABLE
SALES_HISTORY_DATE_IDX P2 DATA02 USABLE
SALES_HISTORY_DATE_IDX P3 DATA03 USABLE
SALES_HISTORY_DATE_IDX P4 DATA04 USABLE
SALES_HISTORY_DATE_IDX P5 DATA05 USABLE
SALES_HISTORY_PK SYS_P21 INDX USABLE
SALES_HISTORY_PK SYS_P22 INDX UNUSABLE
SALES_HISTORY_PK SYS_P23 INDX UNUSABLE
SALES_HISTORY_PK SYS_P24 INDX UNUSABLE

9 rows selected.

SH@PROD>

SH@PROD> alter index SALES_HISTORY_PK rebuild partition SYS_P22;

Index altered.

SH@PROD> alter index SALES_HISTORY_PK rebuild partition SYS_P23;

Index altered.

SH@PROD> alter index SALES_HISTORY_PK rebuild partition SYS_P24;

Index altered.

SH@PROD>select INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME,status
from user_ind_partitions
where index_name in (‘SALES_HISTORY_DATE_IDX’,‘SALES_HISTORY_PK’);

INDEX_NAME PARTITION_NAME TABLESPACE_NAME STATUS

9 rows selected.

SH@PROD>
细粒度审计

Fine-Grained Auditing
6.1 Set up FGA on the SALARY and COMMISSION_PCT columns of the EMPLOYEES table
in the HR schema of the PROD database.
An audit record should be created if either of these two columns are
selected as part of the output of a query or are used in the where condition of
a select statement and their values are not null.
6.2 Validate that the FGA is taking place by executing statements that should
result in auditing records being created and by executing statements
where no auditing records will be generated.
Do not delete your audit records.

文档位置
BOOKS==>
PL/SQL Packages and Types Reference ==> 搜 dbms_fga

找到例子
DBMS_FGA.ADD_POLICY (
object_schema => ‘scott’,
object_name => ‘emp’,
policy_name => ‘mypolicy1’,
audit_condition => ‘sal < 100’,
audit_column => ‘comm,sal’,
handler_schema => NULL,
handler_module => NULL,
enable => TRUE,
statement_types => ‘INSERT, UPDATE’,
audit_trail => DBMS_FGA.XML + DBMS_FGA.EXTENDED,
audit_column_opts => DBMS_FGA.ANY_COLUMNS);

改为:
begin
DBMS_FGA.ADD_POLICY (
object_schema => ‘HR’,
object_name => ‘EMPLOYEES’,
policy_name => ‘mypolicy1’,
audit_condition => ‘SALARY IS NOT NULL and COMMISSION_PCT IS NOT NULL’,
audit_column => ‘SALARY,COMMISSION_PCT’,
handler_schema => NULL,
handler_module => NULL,
enable => TRUE,
statement_types => ‘SELECT’,
audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED,
audit_column_opts => DBMS_FGA.ANY_COLUMNS);
end;
/

SYS@PROD> grant execute on dbms_fga to hr;

审计信息存储位置
SYS@PROD> select table_name from dict where table_name like ‘%FGA%’;

TABLE_NAME

DBA_FGA_AUDIT_TRAIL

SYS@PROD>

HR@PROD> conn hr/hr
Connected.
HR@PROD>

select * from EMPLOYEES;
select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES;
select EMPLOYEE_ID,SALARY from EMPLOYEES;
select EMPLOYEE_ID,COMMISSION_PCT from EMPLOYEES;
select EMPLOYEE_ID,SALARY,COMMISSION_PCT from EMPLOYEES;
select EMPLOYEE_ID from EMPLOYEES where SALARY is not null or COMMISSION_PCT is not null;
select EMPLOYEE_ID from EMPLOYEES where SALARY is not null and COMMISSION_PCT is not null;
select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES where COMMISSION_PCT is not null;

SYS@PROD> select TIMESTAMP,DB_USER,OBJECT_NAME,SQL_TEXT from DBA_FGA_AUDIT_TRAIL;

TIMESTAMP DB_USER OBJECT_NAME

SQL_TEXT

20-JAN-14 HR EMPLOYEES
select * from EMPLOYEES

20-JAN-14 HR EMPLOYEES
select EMPLOYEE_ID,SALARY from EMPLOYEES

20-JAN-14 HR EMPLOYEES
select EMPLOYEE_ID,SALARY,COMMISSION_PCT from EMPLOYEES

20-JAN-14 HR EMPLOYEES
select EMPLOYEE_ID from EMPLOYEES where SALARY is not null or COMMISSION_PCT is not null

20-JAN-14 HR EMPLOYEES
select EMPLOYEE_ID from EMPLOYEES where SALARY is not null and COMMISSION_PCT is not null

20-JAN-14 HR EMPLOYEES
select EMPLOYEE_ID,FIRST_NAME from EMPLOYEES where COMMISSION_PCT is not null

6 rows selected.

SYS@PROD>

begin
DBMS_FGA.ADD_POLICY (
object_schema => ‘HR’,
object_name => ‘EMPLOYEES’,
policy_name => ‘mypolicy1’,
audit_condition => ‘SALARY IS NOT NULL and COMMISSION_PCT IS NOT NULL’,
audit_column => ‘SALARY,COMMISSION_PCT’,
handler_schema => NULL,
handler_module => NULL,
enable => TRUE,
statement_types => ‘SELECT’,
audit_trail => DBMS_FGA.DB_EXTENDED,
audit_column_opts => DBMS_FGA.ANY_COLUMNS);
end;
/

闪回

Flashback
7.1 Create a table named ORIGINAL_SALARY in the HR schema in the PROD database
that includes the employee_id, commission_pct and salaries
of all records in the HR.EMPLOYEES table.
Note the date and time and then commit the changes.
7.2 Delete all employees in the HR.ORIGINAL_SALARY table whose HIRE_DATE is before 1994.
Commit the changes.
Create a view HR.SALARY_VIEW that will show all the original rows before the deletion.

HR@PROD> create table ORIGINAL_SALARY as select employee_id, commission_pct,salary from HR.EMPLOYEES ;

Table created.

HR@PROD>

SYS@PROD> grant select on v_$database to hr;

Grant succeeded.

SYS@PROD>
HR@PROD> select count(*) from ORIGINAL_SALARY;

COUNT(*)

107

HR@PROD> select current_scn from v$database;

CURRENT_SCN

14252020

HR@PROD> delete ORIGINAL_SALARY
where EMPLOYEE_ID in (select EMPLOYEE_ID
from EMPLOYEES
where HIRE_DATE < to_date(‘1994-01-01’,‘yyyy-mm-dd’));

6 rows deleted.

HR@PROD>

HR@PROD> commit;

Commit complete.

HR@PROD> select count(*) from ORIGINAL_SALARY;

COUNT(*)

101

HR@PROD> select count(*) from ORIGINAL_SALARY as of scn 14252020;

COUNT(*)

107

HR@PROD> create view HR.SALARY_VIEW as select * from ORIGINAL_SALARY as of scn 14252020;

View created.

HR@PROD> select count(*) from HR.SALARY_VIEW;

COUNT(*)

107

HR@PROD>

闪回回收站

新增题1
从回收站中闪回一个包含某个字段的表. 并改名
某个用户下的回收站里会有几张同名的表
通过 desc “BINxxxx” 描述表获取列的信息回收站中的表名区分大小写所以必须用双引号
将包含某个列的表闪回并改名

Flashback table “BINxxx” to before drop rename to NEW_NAME;

desc “BIN…”

HR@PROD> flashback table “BIN$Bow91PEShQLgUADKHwBPHw==$0” to before drop rename to mytab;

Flashback complete.

HR@PROD> show recyc;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

E2 BIN$BoxGudY9zT/gUADKHwBRTA== $0 TABLE 2014-10-29:16:09:11 E3 BIN$ BoxGudY4zT/gUADKHwBRTA==$0 TABLE 2014-10-29:16:09:00
HR@PROD>
section 6 性能调整

section6

Instance Configuration
1.1 Configure your database to record checkpoints in the alert.log file.
1.2 Ensure all user trace files are placed in the USER_DUMP_DEST location.

SYS@PROD> show parameter alert

NAME TYPE VALUE

log_checkpoints_to_alert boolean FALSE
SYS@PROD> alter system set log_checkpoints_to_alert=true;

System altered.

SYS@PROD>

SYS@PROD> show parameter user_dump

NAME TYPE VALUE

user_dump_dest string /u01/app/oracle/admin/PROD/udump
SYS@PROD>

SYS@PROD> alter system checkpoint;

System altered.

SYS@PROD> ! tail $ORACLE_BASE/admin/$ ORACLE_SID/bdump/alert_$ORACLE_SID.log
Current log# 5 seq# 288 mem# 1: /u01/app/oracle/PROD/Disk2/redo05b.log
Mon Jan 20 04:15:06 2014
ALTER SYSTEM SET log_checkpoints_to_alert=TRUE SCOPE=BOTH;
Mon Jan 20 04:15:47 2014
Completed checkpoint up to RBA [0x11f.2.10], SCN: 14252443
Mon Jan 20 04:16:47 2014
Completed checkpoint up to RBA [0x120.2.10], SCN: 14252548
Mon Jan 20 04:17:03 2014
Beginning global checkpoint up to RBA [0x120.4.10], SCN: 14252587
Completed checkpoint up to RBA [0x120.4.10], SCN: 14252587

SYS@PROD>

资源管理器

Set Up and Configure Resource Manager
2.1 Set up and configure Resource Manager using the following specifications:
2.1.1 Assign the user SH as the resource administrator.
2.1.2 Create two resource manager consumer groups, OLTP and DSS.
(Use comments with each to denote what the objects will be used for.)

文档位置
BOOKS ==>
Administrator’s Guide ==> 搜 resource
24 Using the Database Resource Manager

PL/SQL Packages and Types Reference ==> 搜 dbms_resource
85 DBMS_RESOURCE_MANAGER

使用方法:
1.授权
2.建立资源组
3.建立计划 (资源组隶属于计划)
4.建立资源指标 (用于指派资源限制)
5.

先对SH用户授权:

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE (
grantee_name => ‘SH’,
privilege_name => ‘ADMINISTER_RESOURCE_MANAGER’,
admin_option => FALSE);
END;
/

exec DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE (‘SH’);

建立错误后删除

DELETE_CONSUMER_GROUP Procedure
DELETE_PLAN Procedure
DELETE_PLAN_CASCADE Procedure
DELETE_PLAN_DIRECTIVE Procedure

conn sh/sh

需要先开放PENDING区类似缓存编辑区校验后才可以整段代码提交.

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => ‘OLTP’,
COMMENT => ‘Resource consumer OLTP group’);
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => ‘DSS’,
COMMENT => ‘Resource consumer DSS group’);

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;
/

设置错了后,要清除PENDING区,才可以重新建立

exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

资源限制参数

PLAN
Name of the resource plan.

GROUP_OR_SUBPLAN
Name of the consumer group or subplan.

COMMENT
Any comment.

CPU_P1
For EMPHASIS, specifies the CPU percentage at the first level.
For RATIO, specifies the weight of CPU usage.
Default is NULL for all CPU parameters.

CPU_P2
For EMPHASIS, specifies CPU percentage at the second level.
Not applicable for RATIO.

ACTIVE_SESS_POOL_P1
Specifies maximum number of concurrently active sessions for
a consumer group. Default is UNLIMITED.

QUEUEING_P1
Specified time (in seconds) after which a job in
an inactive session queue (waiting for execution) will time out.
Default is UNLIMITED.

PARALLEL_DEGREE_LIMIT_P1
Specifies a limit on the degree of parallelism for any operation.
Default is UNLIMITED.

SWITCH_GROUP
Specifies consumer group to which this session is switched if other switch criteria are met.
If the group name is ‘CANCEL_SQL’,
then the current call will be canceled when other switch criteria are met.
If the group name is ‘KILL_SESSION’,
then the session will be killed when other switch criteria are met.
Default is NULL.

SWITCH_TIME 一个语句的切换时间.切了就不返回
Specifies time (in seconds) that a session can execute before an action is taken.
Default in UNLIMITED.
You cannot specify both SWITCH_TIME and SWITCH_TIME_IN_CALL.

SWITCH_ESTIMATE 评估切换.如果要设置切换评估,则需要打开此项
If TRUE, tells the database to use its execution time estimate
to automatically switch the consumer group of an operation prior to beginning its execution.
Default is FALSE.

MAX_EST_EXEC_TIME 累积执行时间
Specifies the maximum execution time (in seconds) allowed for a session.
If the optimizer estimates that an operation will take longer than MAX_EST_EXEC_TIME,
the operation is not started and ORA-07455 is issued.
If the optimizer does not provide an estimate,
this directive has no effect. Default is UNLIMITED.

UNDO_POOL
Sets a maximum in kilobytes (K) on the total amount of undo generated by a consumer group.
Default is UNLIMITED

MAX_IDLE_TIME
Indicates the maximum session idle time.
Default is NULL, which implies unlimited.

MAX_IDLE_BLOCKER_TIME
Indicates the maximum session idle time of a blocking session.
Default is NULL, which implies unlimited.

SWITCH_TIME_IN_CALL 一个语句的切换时间.切了后,执行完返回

Specifies the time (in seconds) that a session can execute before an action is taken.
At the end of the call, the consumer group of the session is restored to its original
consumer group. Default is UNLIMITED.
You cannot specify both SWITCH_TIME_IN_CALL and SWITCH_TIME.

2.1.3 Create a plan named WEEKDAYS with the following directives only;
2.1.3.1 For OLTP group, we cannot allow more than 20 active sessions.
If the 21st user attempts an activity,
the request should be aborted if the wait exceeds 60 seconds.
2.1.3.2 The maximum number of active sessions for the DSS group to 5.
If more than 5 sessions are requested,
then the request should abort at 120 seconds.
2.1.3.3 The maximum execution time for a query for a session
in the OLTP group should be set to 5 seconds.
If the query is estimated to take longer than 5 seconds
the session should be automatically switched to the DSS group.
2.1.3.4 The maximum amount of undo that the OLTP group can use should be set to 200MB.
2.1.3.5 Set CPU ratios for OLTP,DSS and OTHER_GROUPS as 50,30 and 20 respectively.
2.1.3.6 DSS group has parallel degree limit of 20.
2.1.3.7 Make sure that an idle OLTP sessions cannot block a DML statement for more than 60 seconds.

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => ‘WEEKDAYS’,
COMMENT => ‘Resource plan WEEKDAYS’);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘WEEKDAYS’,
GROUP_OR_SUBPLAN => ‘OLTP’, COMMENT => ‘WEEKDAYS OLTP’,
ACTIVE_SESS_POOL_P1 => 20, QUEUEING_P1=> 60,
SWITCH_ESTIMATE=>true,SWITCH_TIME_IN_CALL=> 5,SWITCH_GROUP=>‘DSS’,
UNDO_POOL=>204800,CPU_P1=>50,MAX_IDLE_BLOCKER_TIME=>60
);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘WEEKDAYS’,
GROUP_OR_SUBPLAN => ‘DSS’, COMMENT => ‘WEEKDAYS DSS’,
ACTIVE_SESS_POOL_P1 => 5, QUEUEING_P1=> 120,CPU_P1=>30,
PARALLEL_DEGREE_LIMIT_P1=>20
);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => ‘WEEKDAYS’,
GROUP_OR_SUBPLAN => ‘OTHER_GROUPS’, COMMENT => ‘WEEKDAYS OTHER_GROUP’,
CPU_P1 => 20 );

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;
/

配置错误,重新配置,需要删除.

级联删除:
SH@PROD>

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.delete_PLAN_cascade(PLAN => ‘WEEKDAYS’);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

PL/SQL procedure successfully completed.

SH@PROD>

逐个删除:
exec DBMS_RESOURCE_MANAGER.delete_PLAN(PLAN => ‘WEEKDAYS’);
exec DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (plan=> ‘WEEKDAYS’,GROUP_OR_SUBPLAN => ‘OLTP’);
exec DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (plan=> ‘WEEKDAYS’,GROUP_OR_SUBPLAN => ‘DSS’);
exec DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (plan=> ‘WEEKDAYS’,GROUP_OR_SUBPLAN => ‘OTHER_GROUPS’);

2.1.4 Assign the default consumer group for the OLTP_USER to OLTP group.

2.1.5 Assign the default consumer group for the SH user to DSS group.

文档位置
DBMS_RESOURCE_MANAGER_PRIVS
有模板.复制修改.

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
‘OLTP_USER’, ‘OLTP’, false);
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
‘SH’, ‘DSS’, false);

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.set_consumer_group_mapping(
dbms_resource_manager.oracle_user, ‘OLTP_USER’,‘OLTP’);
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping(
dbms_resource_manager.oracle_user, ‘SH’,‘DSS’);

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

2.1.6 Specify that the WEEKDAYS plan the used by the instance as default.

SYS@PROD> show parameter resource

NAME TYPE VALUE

resource_limit boolean FALSE
resource_manager_plan string
SYS@PROD> alter system set resource_manager_plan=‘WEEKDAYS’;

System altered.

SYS@PROD>

测试:

会话1:
OLTP_USER@PROD> delete t where rownum=1;

1 row deleted.

OLTP_USER@PROD>

会话2:
OLTP_USER@PROD> delete t where rownum=1;
被阻塞

60秒后.会话1被踢出.

OLTP_USER@PROD> select 8+9 from dual;
select 8+9 from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again

OLTP_USER@PROD> select 8+9 from dual;
select 8+9 from dual
*
ERROR at line 1:
ORA-01012: not logged on

OLTP_USER@PROD>

GC完成:
先以sys登录为SH用户授予资源管理权限

============================================================================================
授权使用GC做无效.平时手动grante这个权限也无效,必须调用过程

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE (
grantee_name => ‘SH’,
privilege_name => ‘ADMINISTER_RESOURCE_MANAGER’,
admin_option => FALSE);
END;
/

============================================================================================

使用SH用户登录PROD数据库.

============================================================================================

建 Resource Consumer GroupS 并映射用户到组中.

选上第4步就是在做:GRANT_SWITCH_CONSUMER_GROUP.

下面是映射做法:

============================================================================================

如果 Active this plan被选,SH用户又无修改参数权限.
则使用sysdba手动完成.即使选了报错,前面的plan也能建立.

SYS@PROD> alter system set resource_manager_plan=‘WEEKDAYS’;

System altered.

SYS@PROD>

RM新增

新增题1
Resource Manager 添加一个Resource Manager 名字根据题意获得
将这个组设置为HR用户的初始资源组

建立资源组

步骤1:

====================================================================================

步骤2:

====================================================================================

步骤3:

=====================================================================================

步骤4:

======================================================================================

步骤5:

验证:

SYS@PROD> select username,INITIAL_RSRC_CONSUMER_GROUP from dba_users;

USERNAME INITIAL_RSRC_CONSUMER_GROUP

PERFSTAT DEFAULT_CONSUMER_GROUP
OLTP_USER DEFAULT_CONSUMER_GROUP
SH DEFAULT_CONSUMER_GROUP
OUTLN DEFAULT_CONSUMER_GROUP
DBSNMP DEFAULT_CONSUMER_GROUP
HR TEST1
SYS SYS_GROUP
SYSTEM SYS_GROUP
TSMSYS DEFAULT_CONSUMER_GROUP
DIP DEFAULT_CONSUMER_GROUP

10 rows selected.

SYS@PROD>

使用命令建立:

BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => ‘HR’,
consumer_group => ‘test1’,
grant_option => FALSE
);
END;
BEGIN
dbms_resource_manager.set_initial_consumer_group(
user => ‘HR’,
consumer_group => ‘test1’
);
END;

内存相关

Manage Instance Memory Structures
3.1 Create a view owned by user SYS that lists the packages,procedures,
triggers and functions that are in memory and occupy more than 50KB.
The view should be named LARGE_PROC
and visible to all users through a public synonym named LARGE_PROC.

SYS@PROD> select distinct type from v$db_object_cache
where type in (‘PACKAGE’,‘PACKAGE BODY’,‘TRIGGER’,‘FUNCTION’,‘PROCEDURE’);

TYPE

PROCEDURE
PACKAGE
PACKAGE BODY
TRIGGER
FUNCTION

SYS@PROD>

SYS@PROD> select owner,name,type,SHARABLE_MEM
from v$db_object_cache
where type in (‘PACKAGE’,‘PACKAGE BODY’,‘TRIGGER’,‘FUNCTION’,‘PROCEDURE’)
and SHARABLE_MEM>50*1024
order by type,SHARABLE_MEM;

OWNER NAME TYPE SHARABLE_MEM

SYS DBMS_SPACE PACKAGE 54670
SYS DBMS_ADVISOR PACKAGE 58768
SYS PRVT_ADVISOR PACKAGE 75152
SYS DBMS_STATS PACKAGE 132494
SYS DBMS_RCVMAN PACKAGE 245031
SYS DBMS_STATS_INTERNAL PACKAGE 246179
SYS DBMS_BACKUP_RESTORE PACKAGE 259567
SYS STANDARD PACKAGE 439692
SYS DBMS_SYS_SQL PACKAGE BODY 67196
SYS PRVT_ADVISOR PACKAGE BODY 75212
SYS DBMS_PRVTAQIP PACKAGE BODY 79877
SYS DBMS_STATS_INTERNAL PACKAGE BODY 87135
SYS DBMS_BACKUP_RESTORE PACKAGE BODY 97671
SYS DBMS_SPACE PACKAGE BODY 107358
SYS DBMS_RCVMAN PACKAGE BODY 383747
SYS DBMS_STATS PACKAGE BODY 463694

16 rows selected.

SYS@PROD>

SYS@PROD> create or replace view LARGE_PROC
as select owner,name,type,SHARABLE_MEM
from v$db_object_cache
where type in (‘PACKAGE’,‘PACKAGE BODY’,‘TRIGGER’,‘FUNCTION’,‘PROCEDURE’)
and SHARABLE_MEM>50*1024
order by type,SHARABLE_MEM;

View created.

SYS@PROD>
SYS@PROD> create public synonym LARGE_PROC for sys.LARGE_PROC;

Synonym created.

SYS@PROD> grant select on sys.LARGE_PROC to public;

Grant succeeded.

SYS@PROD>

测试

HR@PROD> select count(*) from LARGE_PROC;

COUNT(*)

HR@PROD>

3.2 Set your maximum SGA to 512MB.
Turn on Automatic Shared Memory Management.
Restart the instance after specifying.
3.3 Your developers notify you that they will need the Java Pool set to a minimum of 200MB.
3.4 Limit the total amount of PGA that can be used on an instance-wide basis to 150MB.

SYS@PROD> show parameter sga

NAME TYPE VALUE

lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 400M
sga_target big integer 400M
SYS@PROD> alter system set sga_max_size=512M scope=spfile;

System altered.

SYS@PROD> alter system set sga_target=512M scope=spfile;

System altered.

SYS@PROD> show parameter java_pool

NAME TYPE VALUE

java_pool_size big integer 0
SYS@PROD> alter system set java_pool_size=200M scope=spfile;

System altered.

SYS@PROD> show parameter work

NAME TYPE VALUE

fileio_network_adapters string
workarea_size_policy string AUTO
SYS@PROD>
SYS@PROD> show parameter pga

NAME TYPE VALUE

pga_aggregate_target big integer 80M
SYS@PROD> alter system set pga_aggregate_target=150M scope=spfile;

System altered.

SYS@PROD>

SYS@PROD> startup force
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1261888 bytes
Variable Size 301993664 bytes
Database Buffers 226492416 bytes
Redo Buffers 7122944 bytes
Database mounted.
Database opened.
SYS@PROD>

SYS@PROD> select name,value
from v$parameter
where name in (‘sga_max_size’,‘sga_target’,‘java_pool_size’,‘pga_aggregate_target’);

NAME VALUE

sga_max_size 536870912
java_pool_size 209715200
sga_target 536870912
pga_aggregate_target 157286400

SYS@PROD>

索引相关
4. Manage Objects for Performance
4.1 Our application needs to access the CUST_LAST_NAME column
in the CUSTOMERS table in the SH schema.
The problem is that the users can supply names without regard to case.
The application changes all the user-supplied names to uppercase.
Analysis reveals that a normal index we placed on the column is not used.
Create an index on the aforementioned column that will be usable by the application.

SH@PROD> desc customers
Name Null? Type

CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_CITY_ID NOT NULL NUMBER
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
CUST_STATE_PROVINCE_ID NOT NULL NUMBER
COUNTRY_ID NOT NULL NUMBER
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
CUST_TOTAL NOT NULL VARCHAR2(14)
CUST_TOTAL_ID NOT NULL NUMBER
CUST_SRC_ID NUMBER
CUST_EFF_FROM DATE
CUST_EFF_TO DATE
CUST_VALID VARCHAR2(1)

SH@PROD>

SH@PROD> create index ind_CUST_LAST_NAME_up on CUSTOMERS(upper(CUST_LAST_NAME));

Index created.

SH@PROD>

4.2 Turn on monitoring for all of the indexes on the SALES table in the OLTP_USER schema.

别用sysdba做.没监控信息.

OLTP_USER@PROD> select column_name,index_name from user_ind_columns where table_name=‘SALES’;

COLUMN_NAME INDEX_NAME

PROMO_ID SALES_PROMO_BIX
PROD_ID SALES_PROD_BIX
CUST_ID SALES_CUST_BIX
TIME_ID SALES_TIME_BIX
CHANNEL_ID SALES_CHANNEL_BIX

OLTP_USER@PROD>

OLTP_USER@PROD> desc v$object_usage;
Name Null? Type

INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)

OLTP_USER@PROD>

OLTP_USER@PROD> select ‘alter index ‘||index_name||’ MONITORING usage;’
from user_indexes where table_name=‘SALES’;

‘ALTERINDEX’||INDEX_NAME||‘MONITORINGUSAGE;’

alter index SALES_TIME_BIX MONITORING usage;
alter index SALES_CUST_BIX MONITORING usage;
alter index SALES_PROD_BIX MONITORING usage;
alter index SALES_PROMO_BIX MONITORING usage;
alter index SALES_CHANNEL_BIX MONITORING usage;

OLTP_USER@PROD>

OLTP_USER@PROD> select INDEX_NAME,MONITORING, USED from v$object_usage;

INDEX_NAME MON USE

SALES_TIME_BIX YES NO
SALES_CUST_BIX YES NO
SALES_PROD_BIX YES NO
SALES_PROMO_BIX YES NO
SALES_CHANNEL_BIX YES NO

OLTP_USER@PROD>

OLTP_USER@PROD> select count(*) from sales where PROMO_ID=1;

COUNT(*)

OLTP_USER@PROD> select count(*) from sales where PROD_ID=1;

COUNT(*)

OLTP_USER@PROD> select count(*) from sales where CUST_ID=1;

COUNT(*)

OLTP_USER@PROD> select count(*) from sales where TIME_ID=SYSDATE;

COUNT(*)

OLTP_USER@PROD> select count(*) from sales where CHANNEL_ID=1;

COUNT(*)

OLTP_USER@PROD> select INDEX_NAME,MONITORING, USED from v$object_usage;

INDEX_NAME MON USE

SALES_TIME_BIX YES YES
SALES_CUST_BIX YES YES
SALES_PROD_BIX YES YES
SALES_PROMO_BIX YES YES
SALES_CHANNEL_BIX YES YES

OLTP_USER@PROD>

OLTP_USER@PROD> select ‘alter index ‘||index_name||’ noMONITORING usage;’
from user_indexes where table_name=‘SALES’;

‘ALTERINDEX’||INDEX_NAME||‘NOMONITORINGUSAGE;’

alter index SALES_TIME_BIX noMONITORING usage;
alter index SALES_CUST_BIX noMONITORING usage;
alter index SALES_PROD_BIX noMONITORING usage;
alter index SALES_PROMO_BIX noMONITORING usage;
alter index SALES_CHANNEL_BIX noMONITORING usage;

OLTP_USER@PROD> select INDEX_NAME,MONITORING, USED from v$object_usage;

INDEX_NAME MON USE

SALES_TIME_BIX NO YES
SALES_CUST_BIX NO YES
SALES_PROD_BIX NO YES
SALES_PROMO_BIX NO YES
SALES_CHANNEL_BIX NO YES

OLTP_USER@PROD>

索引组织表

4.3 Create two new tables named STUDENTS and ATTENDEES in the OLTP_USER schema.
This STUDENTS table will contain three columns.
STUD_ID will be a number and primary key.
FNAME and LNAME will be the other two columns and may vary
in length with a maximum of 20 characters.
ATTENDEES will be an intersection table in a many-to-many relationship
between the STUDENTS and CLASSES tables also in the OLTP_USER schema.
The ATTENDEES table will contain the primary keys of each of
the other tables as its primary key.
Create the ATTENDEES table so what the primary key index and
the table itself are the same object.

/*
create table OLTP_USeR.CLASS (CLASS_ID number primary key,CLASS_NAME VARCHAR2(20));
*/

OLTP_USER@PROD> create table STUDENTS (
STUD_ID number constraint cons_stuID_pk primary key,
FNAME varchar2(20),
LNAME varchar2(20));

Table created.

OLTP_USER@PROD>

OLTP_USER@PROD> create table CLASSES(
class_id number constraint cons_clasID_pk primary key,
class_name varchar2(20));

Table created.

OLTP_USER@PROD>
OLTP_USER@PROD> create table ATTENDEES(
STUD_ID number constraint cons_stud_id_fk references STUDENTS(STUD_ID),
class_id number constraint cons_class_id_FK references CLASSES(class_id),
constraint cons_atte_stud_class_id_PK primary key(STUD_ID,class_id)
) organization index;

Table created.

OLTP_USER@PROD>

分析表和列

4.4 Because of the unevenly distributed data in the DEPARTMENT_ID column of the EMPLOYEES table
of the HR schema,
you need to supply more information to the optimizer to allow for more efficient use of indexes.
Regenerate statistics on the EMPLOYEES table to solve this problem.

文档位置
BOOKS==>
PL/SQL Packages and Types Reference ==> 搜 dbms_stats
GATHER_TABLE_STATS Procedure
conn hr/hr
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname=>‘HR’,
tabname=>‘EMPLOYEES’,
estimate_percent=>100,
method_opt=>‘FOR COLUMNS DEPARTMENT_ID SIZE 254’) ;
end;
/

验证
文档位置

dba_tab_columns

HR@PROD> select COLUMN_NAME,HISTOGRAM,NUM_DISTINCT,DENSITY,NUM_BUCKETS
from all_tab_columns
where table_name=‘EMPLOYEES’ and column_name=‘DEPARTMENT_ID’;

COLUMN_NAME HISTOGRAM NUM_DISTINCT DENSITY NUM_BUCKETS

DEPARTMENT_ID FREQUENCY 11 .004716981 11

HR@PROD>

位图和压缩索引

4.5 Analysis has revealed that the COUNTRY_ID column of the CUSTOMERS table
of the SH schema has very low cardinality.
This column is never updated.
Create an index that can take advantage of the above attributes of this column.

conn sh/sh
create bitmap index ind_cost_couID on CUSTOMERS(COUNTRY_ID);

4.6 Create an index on the COUNTRY_ID and CUST_CITY column in the CUSTOMERS table of the SH schema.
The application requires that leading column of this index must be the COUNTRY_ID column.
Take advantage of the Oracle feature of indexes
that allows the creation of the index to use less space when the leading column
is not the most unique.

conn sh/sh
create index ind_cust_couID_cyID on CUSTOMERS(COUNTRY_ID,CUST_CITY) compress 1;

4.7 Make certain that the package named STANDARD is always kept in memory.

SYS@PROD> @?/rdbms/admin/dbmspool

Package created.

Grant succeeded.

View created.

Package body created.

SYS@PROD> exec dbms_shared_pool.keep(‘SYS.STANDARD’,‘P’);

PL/SQL procedure successfully completed.

SYS@PROD>
SYS@PROD> select owner,name,type ,kept from v$db_object_cache where name=‘STANDARD’;

OWNER NAME TYPE KEP

SYS STANDARD PACKAGE BODY YES
SYS STANDARD PACKAGE YES

SYS@PROD>
杂类

SYS@PROD> show parameter cursor_sha

NAME TYPE VALUE

cursor_sharing string EXACT
SYS@PROD> alter system set cursor_sharing=‘A’;
alter system set cursor_sharing=‘A’
*
ERROR at line 1:
ORA-00096: invalid value A for parameter cursor_sharing, must be from among SIMILAR, EXACT, FORCE

SYS@PROD>

SYS@PROD> alter system set cursor_sharing=‘SIMILAR’;

System altered.

SYS@PROD>

4.9 对cust_tbs表空间中的对象rebuild到其他表空间。重建该表空间为assm。然后再将对象rebuild 回来。

原题不知.

SYS@PROD> create tablespace xyz datafile ‘/u01/app/oracle/PROD/Disk5/xyz01.dbf’ size 30M segment space MANAGEMENT manual;

Tablespace created.

SYS@PROD>

HR@PROD> create table myemp tablespace xyz as select * from employees;

Table created.

HR@PROD>
HR@PROD> create index ind_myemp_eid on myemp(EMPLOYEE_ID) tablespace xyz;

Index created.

HR@PROD> create index ind_myemp_lname on myemp(LAST_NAME) tablespace xyz;

Index created.

HR@PROD> create table myJOBS tablespace xyz as select * from JOBS;

Table created.

HR@PROD>

HR@PROD> create index ind_myjob_jobid on myemp(job_ID) tablespace xyz;

Index created.

HR@PROD>

SYS@PROD> select owner,segment_name,segment_type from dba_segments where tablespace_name=‘XYZ’;

OWNER SEGMENT_NAME SEGMENT_TYPE

HR IND_MYJOB_JOBID INDEX
HR MYJOBS TABLE
HR IND_MYEMP_LNAME INDEX
HR IND_MYEMP_EID INDEX
HR MYEMP TABLE

SYS@PROD>

HR@PROD> create table mylob tablespace xyz as select * from employees;

Table created.

HR@PROD>
HR@PROD> alter table mylob add (lobcol clob) LOB (“LOBCOL”) STORE AS (disABLE STORAGE IN ROW);

Table altered.

HR@PROD>

HR@PROD> update mylob set lobcol=lpad(‘x’,4000,‘x’);

107 rows updated.

HR@PROD> commit;

Commit complete.

HR@PROD>

SYS@PROD> select owner,segment_name,segment_type from dba_segments where tablespace_name=‘XYZ’;

OWNER SEGMENT_NAME SEGMENT_TYPE

HR MYLOB TABLE
HR SYS_LOB0000011352C00012$$ LOBSEGMENT
HR SYS_IL0000011352C00012$$ LOBINDEX
HR IND_MYJOB_JOBID INDEX
HR MYJOBS TABLE
HR IND_MYEMP_LNAME INDEX
HR IND_MYEMP_EID INDEX
HR MYEMP TABLE

8 rows selected.

SYS@PROD>

获取索引元数据

SYS@PROD> select owner,segment_name,segment_type from dba_segments where tablespace_name=‘XYZ’ and segment_type=‘INDEX’;

OWNER SEGMENT_NAME SEGMENT_TYPE

HR IND_MYJOB_JOBID INDEX
HR IND_MYEMP_LNAME INDEX
HR IND_MYEMP_EID INDEX

SYS@PROD>
SYS@PROD> select ‘select dbms_metadata.get_ddl(’
||chr(39)||segment_type||chr(39)||’,’
||chr(39)||segment_name||chr(39)||’,’
||chr(39)||owner||chr(39)||’) from dual;’
from dba_segments
where tablespace_name=‘XYZ’ and segment_type=‘INDEX’;

'SELECTDBMS_METADATA.GET_DDL(

select dbms_metadata.get_ddl(‘INDEX’,‘IND_MYJOB_JOBID’,‘HR’) from dual;
select dbms_metadata.get_ddl(‘INDEX’,‘IND_MYEMP_LNAME’,‘HR’) from dual;
select dbms_metadata.get_ddl(‘INDEX’,‘IND_MYEMP_EID’,‘HR’) from dual;

SYS@PROD>

SYS@PROD> select dbms_metadata.get_ddl(‘INDEX’,‘IND_MYJOB_JOBID’,‘HR’) from dual;

DBMS_METADATA.GET_DDL(‘INDEX’,

CREATE INDEX “HR”.“IND_MYJOB_JOBID” ON “HR”.“MYEMP” (“JOB_ID”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “XYZ”

SYS@PROD> select dbms_metadata.get_ddl(‘INDEX’,‘IND_MYEMP_LNAME’,‘HR’) from dual;

DBMS_METADATA.GET_DDL(‘INDEX’,

CREATE INDEX “HR”.“IND_MYEMP_LNAME” ON “HR”.“MYEMP” (“LAST_NAME”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “XYZ”

SYS@PROD> select dbms_metadata.get_ddl(‘INDEX’,‘IND_MYEMP_EID’,‘HR’) from dual;

DBMS_METADATA.GET_DDL(‘INDEX’,

CREATE INDEX “HR”.“IND_MYEMP_EID” ON “HR”.“MYEMP” (“EMPLOYEE_ID”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “XYZ”

SYS@PROD>

获取LOB列的表的元数据
SYS@PROD> select table_name,column_name,data_type
from dba_tab_columns where owner=‘HR’ and data_type like ‘%LOB%’ ;

TABLE_NAME COLUMN_NAME DATA_TYPE

MAGAZINE_ARTICLES ARTICLE_DATA CLOB
MYLOB LOBCOL CLOB

SYS@PROD>
SYS@PROD> select dbms_metadata.get_ddl(‘TABLE’,‘MYLOB’,‘HR’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,

CREATE TABLE “HR”.“MYLOB”
( “EMPLOYEE_ID” NUMBER(6,0),
“FIRST_NAME” VARCHAR2(20),
“LAST_NAME” VARCHAR2(25) NOT NULL ENABLE,
“EMAIL” VARCHAR2(25) NOT NULL ENABLE,
“PHONE_NUMBER” VARCHAR2(20),
“HIRE_DATE” DATE NOT NULL ENABLE,
“JOB_ID” VARCHAR2(10) NOT NULL ENABLE,
“SALARY” NUMBER(8,2),
“COMMISSION_PCT” NUMBER(2,2),
“MANAGER_ID” NUMBER(6,0),
“DEPARTMENT_ID” NUMBER(4,0),
“LOBCOL” CLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “XYZ”
LOB (“LOBCOL”) STORE AS (
TABLESPACE “XYZ” DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

SYS@PROD>

获取XYZ表空间元数据
SYS@PROD> select dbms_metadata.get_ddl(‘TABLESPACE’,‘XYZ’) from dual;

DBMS_METADATA.GET_DDL('TABLESP

CREATE TABLESPACE “XYZ” DATAFILE
‘/u01/app/oracle/PROD/Disk5/xyz01.dbf’ SIZE 31457280
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL

SYS@PROD>

建立个新表空间
SYS@PROD> create tablespace abc datafile ‘/u01/app/oracle/PROD/Disk5/abc01.dbf’ size 30M;

Tablespace created.

SYS@PROD>

移动表
SYS@PROD> select owner,segment_name,segment_type from dba_segments where tablespace_name=‘XYZ’ and segment_TYPE=‘TABLE’;

OWNER SEGMENT_NAME SEGMENT_TYPE

HR MYLOB TABLE
HR MYJOBS TABLE
HR MYEMP TABLE

SYS@PROD>

alter table hr.myemp move tablespace abc;
alter table hr.jobs move tablespace abc;

alter table hr.mylob move tablespace abc
lob(lobcol) store as (
TABLESPACE “ABC”
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
/

删除原表空间
SYS@PROD> drop tablespace xyz including contents and datafiles;

Tablespace dropped.

SYS@PROD>

建立新表空间

移动回来原来的对象
alter table hr.myemp move tablespace xyz;
alter table hr.jobs move tablespace xyz;

alter table hr.mylob move tablespace xyz
lob(lobcol) store as (
TABLESPACE “XYZ”
DISABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
/

建立索引

恢复和原来一样变空间变为 ASSM

SYS@PROD> select owner,segment_name,segment_type from dba_segments where tablespace_name=‘XYZ’;

OWNER SEGMENT_NAME SEGMENT_TYPE

HR MYLOB TABLE
HR SYS_LOB0000011352C00012$$ LOBSEGMENT
HR SYS_IL0000011352C00012$$ LOBINDEX
HR IND_MYEMP_EID INDEX
HR IND_MYEMP_LNAME INDEX
HR IND_MYJOB_JOBID INDEX
HR MYEMP TABLE
HR JOBS TABLE

8 rows selected.

SYS@PROD>

4.10 对提供的一条select语句固定执行计划

原题不知.会提供一条sql.

SYS@PROD>

SYS@PROD> create outline hr_outline on select * from hr.employees where employee_id=100;
Outline created.

SYS@PROD> alter system set use_stored_outlines=hr_outline;

System altered.

SYS@PROD> conn hr/hr
Connected.
HR@PROD> set autot trace exp

HR@PROD> select * from hr.employees where employee_id=100;

Execution Plan

Plan hash value: 1445712933

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 68 | 1 (0)| 00:00:01 |
| 1 | VIEW | EMPLOYEES | 1 | 68 | 1 (0)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 68 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - filter(CASE WHEN (“SALARY” IS NOT NULL AND “COMMISSION_PCT” IS NOT NULL) THEN
SYS_AUDIT(‘HR’,‘EMPLOYEES’,‘MYPOLICY1’,3) ELSE NULL END IS NULL)
4 - access(“EMPLOYEE_ID”=100)

Note

outline “HR_OUTLINE” used for this statement

HR@PROD>

注意.重启实例会失效.如果重启了.需要重新设置 alter system set use_stored_outlines=hr_outline;

如果是指定用户建立需要赋予create any outline权限.

1 create or replace trigger tri_keep_s1 after startup on database
2 begin
3 dbms_shared_pool.keep(‘SCOTT.S1’,‘Q’);
4 execute immediate ‘alter system set use_stored_outlines=hr_outline’;
5* END;

SP
5. Using STATSPACK
5.1 Install the STATSPACK package.
5.1.1 Assign the TOOLS tablespace as the default tablespace for the PERFSTAT user.
5.1.2 Assign the TEMP1 tablespace as the temporary tablespace for the PERFSTAT user.

SYS@PROD> select tablespace_name,file_name ,bytes
from dba_data_files where tablespace_name=‘TOOLS’;

TABLESPACE_NAME FILE_NAME BYTES

TOOLS /u01/app/oracle/PROD/Disk1/tools01.dbf 10485760

SYS@PROD> alter database datafile ‘/u01/app/oracle/PROD/Disk1/tools01.dbf’ autoextend on;

Database altered.

SYS@PROD>

SYS@PROD> select tablespace_name,file_name ,bytes
from dba_temp_files where tablespace_name=‘TEMP1’;

TABLESPACE_NAME FILE_NAME BYTES

TEMP1 /u01/app/oracle/PROD/Disk1/temp1_1.dbf 72351744

SYS@PROD>

SYS@PROD> select username from dba_users where username=‘PERFSTAT’;

no rows selected

SYS@PROD>

SYS@PROD> ! ls $ORACLE_HOME/rdbms/admin/sp*
/u01/app/oracle/product/10.2.0/rdbms/admin/spauto.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spcpkg.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spcreate.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spctab.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spcusr.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spdoc.txt
/u01/app/oracle/product/10.2.0/rdbms/admin/spdrop.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spdtab.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spdusr.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/sppurge.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/sprepcon.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/sprepins.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spreport.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/sprepsql.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/sprsqins.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/sptrunc.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spuexp.par
/u01/app/oracle/product/10.2.0/rdbms/admin/spup10.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spup816.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spup817.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spup90.sql
/u01/app/oracle/product/10.2.0/rdbms/admin/spup92.sql

SYS@PROD>

SYS@PROD> @?/rdbms/admin/spcreate

Choose the PERFSTAT user’s password

Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat
perfstat

Choose the Default tablespace for the PERFSTAT user

Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user’s
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users’s default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE

ABC PERMANENT
DATA01 PERMANENT
DATA02 PERMANENT
DATA03 PERMANENT
DATA04 PERMANENT
DATA05 PERMANENT
EXAMPLE PERMANENT
INDX PERMANENT
LOB_DATA PERMANENT
OLTP PERMANENT
REGISTRATION PERMANENT
SYSAUX PERMANENT *
TOOLS PERMANENT
USERS PERMANENT
XYZ PERMANENT

Pressing will result in STATSPACK’s recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: tools

Using tablespace TOOLS as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user

Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user’s temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user’s Temporary tablespace.

TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE

TEMP TEMPORARY
TEMP1 TEMPORARY
TEMP2 TEMPORARY

Pressing will result in the database’s default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: temp1

Using tablespace temp1 as PERFSTAT temporary tablespace.

…省略…

SYS@PROD> select username,default_tablespace,temporary_tablespace
from dba_users where username=‘PERFSTAT’;

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

PERFSTAT TOOLS TEMP1

SYS@PROD>

5.2 Generate the initial set of statistics using STATSPACK making
certain that timing data and segment level statics are included.
Specify a comment for the statistics MANUAL.

SYS@PROD> CONN PERFSTAT/PERFSTAT
Connected.
PERFSTAT@PROD>

PERFSTAT@PROD> select * from STATS$LEVEL_DESCRIPTION;

SNAP_LEVEL DESCRIPTION

0 This level captures general statistics, including
rollback segment, row cache, SGA, system events, b
ackground events, session events, system statistic
s, wait statistics, lock statistics, and Latch inf
ormation

5 This level includes capturing high resource usage
SQL Statements, along with all data captured by lo
wer levels

6 This level includes capturing SQL plan and SQL pla
n usage information for high resource usage SQL St
atements, along with all data captured by lower le
vels

7 This level captures segment level statistics, incl
uding logical and physical reads, row lock, itl an
d buffer busy waits, along with all data captured
by lower levels

10 This level includes capturing Child Latch statisti
cs, along with all data captured by lower levels

PERFSTAT@PROD>
PERFSTAT@PROD> DESC STATSPACK

PERFSTAT@PROD> SPOOL sp.txt

PERFSTAT@PROD> DESC STATSPACK

PERFSTAT@PROD> spool off

PROCEDURE SNAP
Argument Name Type In/Out Default?

I_SNAP_LEVEL NUMBER IN DEFAULT
I_SESSION_ID NUMBER IN DEFAULT
I_UCOMMENT VARCHAR2 IN DEFAULT
I_NUM_SQL NUMBER IN DEFAULT
I_EXECUTIONS_TH NUMBER IN DEFAULT
I_PARSE_CALLS_TH NUMBER IN DEFAULT
I_DISK_READS_TH NUMBER IN DEFAULT
I_BUFFER_GETS_TH NUMBER IN DEFAULT

execute statspack.modify_statspack_parameter(i_snap_level=>7);

execute STATSPACK.SNAP(I_SNAP_LEVEL=>7,I_UCOMMENT=>‘MANUAL’);

PERFSTAT@PROD> select SNAP_LEVEL,UCOMMENT from STATS$SNAPSHOT;

SNAP_LEVEL UCOMMENT

7 MANUAL

PERFSTAT@PROD>

5.3 Collect statistics using STATSPACK every five minutes for 15 minutes
and then remove the job. During this interval run the oltp_workload.sql script.

PERFSTAT@PROD> select SYSDATE,SYSDATE+15/24/60 from dual;

SYSDATE SYSDATE+15/24/60

2014-01-21 14:43:58 2014-01-21 14:58:58

PERFSTAT@PROD> 计算一下结束时间还够不够.

PERFSTAT@PROD> ! vim $ORACLE_HOME/rdbms/admin/spauto.sql

begin
select instance_number into :instno from v$instance;
– dbms_job.submit(:jobno, ‘statspack.snap;’, trunc(sysdate+1/24,‘HH’), ‘trunc(SYSDATE+
1/24,’‘HH’’)’, TRUE, :instno);
statspack.snap;
dbms_job.submit(:jobno, ‘statspack.snap;’, trunc(sysdate+5/24/60,‘MI’), ‘trunc(SYSDA
TE+5/24/60,’‘MI’’)’, TRUE, :instno);
commit;
end;
/

添加调用spauto就抓取一份.否则要等到执行脚本后的5分钟才抓取第一次.
修改间隔时间.将默认的行注释. 运行完脚本再改回来.

PERFSTAT@PROD> @?/rdbms/admin/spauto

PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance

Note that this job number is needed when modifying or removing
the job:

JOBNO
----------
3


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
job_queue_processes integer 20


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC
---------- ------------------- --------------------------------
3 2014-01-21 15:10:00 15:10:00

PERFSTAT@PROD> commit;

Commit complete.

PERFSTAT@PROD>


PERFSTAT@PROD> select SNAP_ID,SNAP_TIME,SNAP_LEVEL from STATS$SNAPSHOT;

SNAP_ID SNAP_TIME SNAP_LEVEL
---------- ------------------- ----------
1 2014-01-21 14:36:11 7
3 2014-01-21 14:38:56 5
4 2014-01-21 14:39:21 5

PERFSTAT@PROD>
PERFSTAT@PROD> select JOB,INTERVAL,NEXT_DATE,WHAT from user_jobs;

JOB INTERVAL NEXT_DATE WHAT
---------- ----------------------------------- ------------------- ---------------
3 trunc(SYSDATE+5/24/60,'MI') 2014-01-21 15:10:00 statspack.snap;

PERFSTAT@PROD> select sysdate from dual;

SYSDATE
-------------------
2014-01-21 15:08:44

PERFSTAT@PROD>
去运行脚本. 产生负载信息..




5.4 Generate a STATSPACK report using any two sets of stats.
Name the report statspack.ls and place it in the /home/oracle directory.
***


PERFSTAT@PROD> @?/rdbms/admin/spreport


Listing all Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
PROD PROD 6 21 Jan 2014 15:12 5
7 21 Jan 2014 15:17 5
8 21 Jan 2014 15:22 5



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 7
Begin Snapshot Id specified: 7

Enter value for end_snap: 8
End Snapshot Id specified: 8

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_7_8. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/statspack.ls

Using the report name /home/oracle/statspack.ls

STATSPACK report for




PERFSTAT@PROD> exec dbms_job.remove(3);

PL/SQL procedure successfully completed.

PERFSTAT@PROD> commit;

Commit complete.

PERFSTAT@PROD>

PERFSTAT@PROD> conn perfstat/perfstat
Connected.
PERFSTAT@PROD> select JOB,INTERVAL,NEXT_DATE,WHAT from user_jobs;

no rows selected

PERFSTAT@PROD>
DG
1.删除不必要的表空间(看题要求)
2.主库强制日志模式 归档模式
SYS@PROD> alter database force logging;
3.建立主库的RMAN压缩备份
backup as compressed backupset database format '/home/oracle/rman_bak/prod%U.bak';
备份期间 看题
4.在主库建立备库专用控制文件(这个控制文件存在备份信息 控制文件存在备份信息)
alter database create standby controlfile as '/home/oracle/rman_bak/sdb01.ctl';

5.在主库建立备库的文本参数文件 以及密码文件
create pfile='/home/oracle/rman/initprod.ora' from spfile;
cp $ORACLE_HOME/dbs/orapwPROD /home/oracle/rman/

6.传递备份集 密码文件 参数文件 standby控制文件到备库主机
备份集的位置路径和主库一致 避免注册
控制文件,密码文件,参数文件根据题中要求位置放置

备库操作
7.修改备库参数文件
controlfile
db_unique_name


session8 new
非最大保护的 DG，要用 standby log，延迟 apply，并行恢复
执行脚本
最后状态 read only
步骤


删除表空间
lob_data
data01-05

步骤:
主库:
1.改二进制的PROD参数
2.重启PROD,使所有修改静态参数生效.主要是文件路径转换的参数.
3.将spfile 转为 pfile;
3.使用rman删除所有备份文件,修改备份位置,执行包含standby CTL的全备份.
4.将奇数机上的RMAN备份文件+文本参数文件+密码文件 传输到偶数机
备库:
5.修改文本参数文件+密码文件 名字为SBDB
6.修改SBDB的文本参数文件
7.启动测试,测试ok,转换为SPFILE,并使用spfile启动实例到nomount
8.配置静态监听 使用netmgr,
9.在PROD的tnsnames.ora里建立SBDB的别名,并将PROD+SBDB别名复制到偶数机的tnsnames.ora中
10.在PROD数据库上,使用rman做duplicate 产生standby.
11.检查备库能接收日志并能应用日志.

改PROD参数

文档
Data Guard Concepts and Administration
3 Creating a Physical Standby Database

SYS@PROD> ALTER DATABASE FORCE LOGGING;

Database altered.

SYS@PROD>


SYS@PROD> alter system set db_unique_name=PROD scope=spfile;


SYS@PROD> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,SBDB)';

System altered.

SYS@PROD>


show parameter dest_1

SYS@PROD> alter system set log_archive_dest_1='location=/home/oracle/arc1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD';

System altered.

SYS@PROD>

SYS@PROD> alter system set LOG_ARCHIVE_DEST_2='SERVICE=SBDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB';

System altered.

SYS@PROD>

SYS@PROD> alter system set fal_server=SBDB;

System altered.

SYS@PROD>
SYS@PROD> alter system set fal_client=PROD;

System altered.

SYS@PROD>

SYS@PROD> alter system set db_file_name_convert='/u01/oracle/oradata/SBDB/','/u01/oracle/PROD/disk4/' scope=spfile;

System altered.

SYS@PROD> alter system set log_file_name_convert='/u01/oracle/oradata/SBDB/','/u01/oracle/PROD/disk4/' scope=spfile;

System altered.

SYS@PROD>



SYS@PROD> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SYS@PROD>


注意归档LOG_ARCHIVE_FORMAT 在2台库上一致.

重启主库,使所有修改的静态参数生效.

SYS@PROD> startup force
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1261888 bytes
Variable Size 218107584 bytes
Database Buffers 310378496 bytes
Redo Buffers 7122944 bytes
Database mounted.
Database opened.
SYS@PROD>



SYS@PROD> create pfile from spfile;

File created.

SYS@PROD>


rman
corsscheck copy;
delete expired copy;
delete copy;
delete backup;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/backup/%d_%I_%s_%p_%c_%T_%u.rbak';

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;


RMAN> backup as compressed backupset database include current controlfile for standby plus archivelog;

[oracle@ocm1 ~]$ ls /home/oracle/backup/
c-272681951-20141031-07 PROD_272681951_97_1_1_20141031_31pme8oc.rbak
PROD_272681951_95_1_1_20141031_2vpme8nq.rbak PROD_272681951_98_1_1_20141031_32pme8od.rbak
PROD_272681951_96_1_1_20141031_30pme8ns.rbak
[oracle@ocm1 ~]$


向备库传文件
备份文件要和主库相同路径
密码文件和pfile放到$ORACLE_HOME/dbs/中 并改名

[oracle@ocm1 ~]$ ls /home/oracle/backup/
c-272681951-20141031-07 PROD_272681951_97_1_1_20141031_31pme8oc.rbak
PROD_272681951_95_1_1_20141031_2vpme8nq.rbak PROD_272681951_98_1_1_20141031_32pme8od.rbak
PROD_272681951_96_1_1_20141031_30pme8ns.rbak
[oracle@ocm1 ~]$ scp /home/oracle/backup/* ocm2:/home/oracle/backup/
oracle@ocm2's password:

[oracle@ocm1 backup]$



[oracle@ocm1 backup]$ cd $ORACLE_HOME/dbs
[oracle@ocm1 dbs]$ ls
abc.log hc_PROD.dat init.ora orapwPROD snapcf_PROD.f
afiedt.buf initdw.ora initPROD.ora lkPROD reorg21.sql spfilePROD.ora
[oracle@ocm1 dbs]$
[oracle@ocm1 dbs]$ scp initPROD.ora orapwPROD ocm2:$ORACLE_HOME/dbs/
oracle@ocm2's password:
initPROD.ora 100% 2289 2.2KB/s 00:00
orapwPROD 100% 1536 1.5KB/s 00:00
[oracle@ocm1 dbs]$



去备库

修改文件名字
[oracle@ocm2 ~]$ cd $ORACLE_HOME/dbs
[oracle@ocm2 dbs]$ ls
hc_EMREP.dat initdw.ora init.ora initPROD.ora lkEMREP orapwEMREP orapwPROD spfileEMREP.ora
[oracle@ocm2 dbs]$ mv orapwPROD orapwSBDB
[oracle@ocm2 dbs]$

[oracle@ocm2 dbs]$ mv initPROD.ora initSBDB.ora
[oracle@ocm2 dbs]$


改参数
vi initSBDB.ora

开一个命令行 执行 mkdir -p 积累要建立的目录

把所有PROD.xxxx 删除

改控制文件位置
*.control_files='/u01/oracle/oradata/SBDB/control01.ctl','/u01/oracle/oradata/SBDB/control02.ctl'

改唯一名.不用改db_name.
*.db_unique_name='SBDB'


互换名字
*.fal_client='SBDB'
*.fal_server='PROD'



互换唯一名
*.log_archive_dest_1='location=/home/oracle/arc1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD'
改为:
*.log_archive_dest_1='location=/home/oracle/arc1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SBDB'


互换唯一名和服务名
*.log_archive_dest_2='SERVICE=SBDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB'
改为:
*.log_archive_dest_2='SERVICE=PROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD'


其他参数:
*.log_archive_format='%t_%s_%r.dbf' 一定要保证和主库一致

prod.resource_manager_plan='WEEKDAYS' 删除 或者去掉前缀

*.service_names='PROD','SRV_NM' 删除 会默认继承 db_unique_name
*.local_listener='PORT1526' 删除

目录转换:
db_file_name_convert='/u01/oracle/PROD/disk1/','/u01/oracle/oradata/SBDB/',
'/u01/oracle/PROD/disk2/','/u01/oracle/oradata/SBDB/',
'/u01/oracle/PROD/disk3/','/u01/oracle/oradata/SBDB/',
'/u01/oracle/PROD/disk4/','/u01/oracle/oradata/SBDB/',
'/u01/oracle/PROD/disk5/','/u01/oracle/oradata/SBDB/'

log_file_name_convert='/u01/oracle/PROD/disk1/','/u01/oracle/oradata/SBDB/',
'/u01/oracle/PROD/disk2/','/u01/oracle/oradata/SBDB/',
'/u01/oracle/PROD/disk3/','/u01/oracle/oradata/SBDB/',
'/u01/oracle/PROD/disk4/','/u01/oracle/oradata/SBDB/',
'/u01/oracle/PROD/disk5/','/u01/oracle/oradata/SBDB/'




建立积累出来的目录.
[oracle@ocm2 ~]$ mkdir -p /home/oracle/bdump /home/oracle/flash/ /home/oracle/arc1/ /home/oracle/udump /home/oracle /home/oracle/temp /home/oracle/scripts


备库能nomount后建立spfile
[oracle@ocm2 ~]$ export ORACLE_SID=SBDB
[oracle@ocm2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 31 10:26:56 2014

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to an idle instance.

SYS@SBDB> startup nomount
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1261888 bytes
Variable Size 188747456 bytes
Database Buffers 339738624 bytes
Redo Buffers 7122944 bytes
SYS@SBDB> create spfile from pfile;

File created.

SYS@SBDB> startup force nomount
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1261888 bytes
Variable Size 188747456 bytes
Database Buffers 339738624 bytes
Redo Buffers 7122944 bytes
SYS@SBDB>



配置备库的静态监听
使用netmgr

[oracle@ocm2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/oracle/product/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SBDB)
(ORACLE_HOME = /u01/oracle/product/10.2.0)
(SID_NAME = SBDB)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocm2.oracle.com)(PORT = 1521))
)

[oracle@ocm2 admin]$

添加主库和备份的 tnsnames.ora 解析别名

PROD=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1.oracle.com)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=prod)(SERVER=dedicated))
)

SBDB=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ocm2.oracle.com)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=SBDB)(SERVER=dedicated))
)


[oracle@ocm2 admin]$ lsnrctl reload

[oracle@ocm2 admin]$ lsnrctl status

Service "SBDB" has 2 instance(s).
Instance "SBDB", status UNKNOWN, has 1 handler(s) for this service...
Instance "SBDB", status BLOCKED, has 1 handler(s) for this service...

测试互相的连通性
两台都要测试.
[oracle@ocm1 ~]$ sql sys/oracle@prod as sysdba
[oracle@ocm1 ~]$ sql sys/oracle@sbdb as sysdba

[oracle@ocm2 ~]$ sql sys/oracle@prod as sysdba
[oracle@ocm2 ~]$ sql sys/oracle@sbdb as sysdba

在PROD数据库,使用rman
[oracle@ocm1 ~]$ rman help=y

[oracle@ocm1 ~]$ rman target / auxiliary sys/oracle@ocm2:1521/SBDB

Recovery Manager: Release 10.2.0.2.0 - Production on Fri Oct 31 10:34:10 2014

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

connected to target database: PROD (DBID=272681951)
connected to auxiliary database: PROD (not mounted)

RMAN>

RMAN> duplicate target database for standby;

Starting Duplicate Db at 31-OCT-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=285 devtype=DISK

contents of Memory Script:

....
....
....

Finished Duplicate Db at 31-OCT-14

RMAN>

检查备库角色:
SYS@SBDB> select NAME, DB_UNIQUE_NAME ,OPEN_MODE, DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------ -------------- ------------ ---------------- --------------------
PROD SBDB MOUNTED PHYSICAL STANDBY SESSIONS ACTIVE

SYS@SBDB>




测试日志传输

主库日志切换 备库能接收

SYS@PROD> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------
1 1 30 104857600 2 YES INACTIVE 843741 31-OCT-14
2 1 31 104857600 2 NO CURRENT 843800 31-OCT-14
3 1 25 104857600 2 YES INACTIVE 820011 31-OCT-14
4 1 26 104857600 2 YES INACTIVE 841359 31-OCT-14
5 1 29 104857600 2 YES INACTIVE 843672 31-OCT-14
6 1 27 104857600 2 YES INACTIVE 842685 31-OCT-14
7 1 28 104857600 2 YES INACTIVE 842700 31-OCT-14

7 rows selected.

SYS@PROD> alter system switch logfile;

System altered.

SYS@PROD>


备库的alert里
Fri Oct 31 11:17:05 2014
RFS[2]: Archived Log: '/home/oracle/arc1/1_31_862064786.dbf'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: No standby redo logfiles created

检查主库切日志,备库能应用.

SYS@SBDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SYS@SBDB>

备库alert日志显示:
Media Recovery Waiting for thread 1 sequence 32 (in transit)






DG题

考题:

1.主库建立表空间 备库一起建立

standby_file_management = AUTO

备库开启日志应用服务>:
文档: 6 Log Apply Services

SYS@SBDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SYS@SBDB>

主库切换日志后.备库日志:
缺少redo文件错误 忽略.
Fri Oct 31 11:26:02 2014
RFS[2]: Archived Log: '/home/oracle/arc1/1_32_862064786.dbf'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: No standby redo logfiles created
Fri Oct 31 11:26:05 2014
Media Recovery Log /home/oracle/arc1/1_32_862064786.dbf
Media Recovery Waiting for thread 1 sequence 33 (in transit)


2. 添加standby redo log

备库要取消日志应用后才能添加,主库也要添加.
SYS@SBDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

Database altered.

SYS@SBDB>


SYS@SBDB> alter database add standby logfile group 11 '/u01/oracle/oradata/SBDB/sdb_redo01.log' size 100M;

Database altered.

SYS@SBDB> alter database add standby logfile group 12 '/u01/oracle/oradata/SBDB/sdb_redo02.log' size 100M;

Database altered.

SYS@SBDB> alter database add standby logfile group 13 '/u01/oracle/oradata/SBDB/sdb_redo03.log' size 100M;

Database altered.

SYS@SBDB>


3. 修改日志传输方式 LGWR SYNC AFFIRM

主库:
SYS@PROD> select value from v$parameter where name='log_archive_dest_2';

VALUE
------------------------------------------------------------------------------------------------------------------------
SERVICE=SBDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB

SYS@PROD> alter system set log_archive_dest_2='SERVICE=SBDB LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB';

System altered.

SYS@PROD>

备库:
SYS@SBDB> select value from v$parameter where name='log_archive_dest_2';

VALUE
------------------------------------------------------------------------------------------------------------------------
SERVICE=PROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD

SYS@SBDB>

SYS@SBDB> alter system set log_archive_dest_2='SERVICE=PROD LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD';

System altered.

SYS@SBDB>


4. 切换.
把主库的 /home/oracle/scripts 目录 传到备库相同位置.

[oracle@ocm1 ~]$ scp -r scripts ocm2:/home/oracle/

文档 7 Role Transitions


备库先开启日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;


主切备:
切换时收集命令,留作后期再切换时使用:
SYS@PROD> select DB_UNIQUE_NAME,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------- -------------------- ---------------- --------------------
PROD MAXIMUM PERFORMANCE PRIMARY SESSIONS ACTIVE

SYS@PROD>


SYS@PROD> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Database altered.

SYS@PROD>
PROD库alert文件内容:
Switchover: Complete - Database shutdown required (PROD)
Fri Oct 31 11:51:37 2014
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN

关闭数据库,重启动到mount,开启日志应用.
SYS@PROD> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted


ORACLE instance shut down.
SYS@PROD> startup mount;
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1261888 bytes
Variable Size 218107584 bytes
Database Buffers 310378496 bytes
Redo Buffers 7122944 bytes
Database mounted.
SYS@PROD> select DB_UNIQUE_NAME,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------- -------------------- ---------------- --------------------
PROD MAXIMUM PERFORMANCE PHYSICAL STANDBY TO PRIMARY

SYS@PROD> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect;



备切主:

切换时收集命令,留作后期再切换时使用:
SYS@SBDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect;

Database altered.


SYS@SBDB> select DB_UNIQUE_NAME,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------- -------------------- ---------------- --------------------
SBDB MAXIMUM PERFORMANCE PHYSICAL STANDBY SESSIONS ACTIVE

SYS@SBDB> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SYS@SBDB> select DB_UNIQUE_NAME,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------- -------------------- ---------------- --------------------
SBDB MAXIMUM PERFORMANCE PRIMARY SESSIONS ACTIVE

SYS@SBDB> ALTER DATABASE OPEN;

Database altered.

SYS@SBDB>

测试 新主库SBDB切换日志 观察备库PROD是否能应用.

SYS@SBDB> SELECT * FROM V$LOG;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------
1 1 52 104857600 2 YES INACTIVE 845284 31-OCT-14
2 1 53 104857600 2 YES ACTIVE 845295 31-OCT-14
3 1 54 104857600 2 YES ACTIVE 845774 31-OCT-14
4 1 55 104857600 2 YES ACTIVE 845776 31-OCT-14
5 1 51 104857600 2 YES INACTIVE 845281 31-OCT-14
6 1 56 104857600 2 NO CURRENT 845778 31-OCT-14
7 1 50 104857600 2 YES INACTIVE 845279 31-OCT-14

7 rows selected.

SYS@SBDB> alter system switch logfile;


PROD的告警日志

Fri Oct 31 12:15:18 2014
Media Recovery Log /home/oracle/arc1/1_56_862064786.dbf
Media Recovery Waiting for thread 1 sequence 57 (in transit)





重复切换过程:

保证切换前 备库是开启日志应用的状态的.

主切备:
收集到的命令.全复制再执行:
select DB_UNIQUE_NAME,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SHUTDOWN IMMEDIATE;
startup mount;
select DB_UNIQUE_NAME,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect;


备切主:
收集到的命令.全复制再执行:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
select DB_UNIQUE_NAME,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
select DB_UNIQUE_NAME,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
alter database open;
select * from v$Log;
alter system switch logfile;




5.把其中一个做 open read only;

如果是主库
shut immediate
startup mount
alter database open read only;

如果是备库

SYS@SBDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

Database altered.

SYS@SBDB> alter database open ;

Database altered.

SYS@SBDB> select open_mode from v$database;

OPEN_MODE
------------
READ ONLY

SYS@SBDB>









RAC

安装
搭建RAC

1.关闭PROD,EMREP数据库 节省主机资源 尤其是OMS
2. 检查资源
1).裸设备(考试时已配置好)
到 /dev/raw/下能发现 raw1-4裸设备
分别用于OCR VOT asmDISK1 asmDISK2

[root@ocm2 ~]# raw -qa
/dev/raw/raw1: bound to major 8, minor 48
/dev/raw/raw2: bound to major 8, minor 16
/dev/raw/raw3: bound to major 8, minor 64
/dev/raw/raw4: bound to major 8, minor 32
[root@ocm2 ~]#

[root@ocm1 ~]# raw -qa
/dev/raw/raw1: bound to major 8, minor 112
/dev/raw/raw2: bound to major 8, minor 96
/dev/raw/raw3: bound to major 8, minor 80
/dev/raw/raw4: bound to major 8, minor 128
[root@ocm1 ~]#
2).主机名 IP 解析(考试时已配置好)
/sbin/ip a
/etc/sysconfig/network
hostname
/etc/hosts

node1:
202.0.0.31
192.168.10.31
HOSTNAME=rac1.localdomain
hostname rac1.localdomain
node2:
202.0.0.32
192.168.10.32
HOSTNAME=rac2.localdomain
hostname rac2.localdomain

/etc/hosts
202.0.0.31 rac1.localdomain rac1
202.0.0.32 rac2.localdomain rac2

202.0.0.35 rac1-vip.localdomain rac1-vip
202.0.0.36 rac2-vip.localdomain rac2-vip

192.168.10.31 rac1-priv.localdomain rac1-priv
192.168.10.32 rac2-priv.localdomain rac2-priv




3).每个节点的oracle用户和oinstal,dba组 需要时一致的UID GID


3.安装步骤
1).SSH对等信任(考试时是跑脚本建立,而且只需要再第一节点跑一次.
千万不要手动建立)
2).测试对等信任
3).安装CRS
======================================================
BUG一:
[root@rac1 crs]# ./root.sh
WARNING: directory '/u02/oracle' is not owned by root
WARNING: directory '/u02' is not owned by root
Checking to see if Oracle CRS stack is already configured

Setting the permissions on OCR backup directory
Setting up NS directories
Failed to upgrade Oracle Cluster Registry configuration
修复补丁:
[root@rac1 ~]# unzip p4679769_10201_LINUX.zip
[root@rac1 ~]# cd 4679769/
[root@rac1 4679769]# ls
clsfmt.bin README.txt
[root@rac1 4679769]#
[root@rac1 4679769]# cp clsfmt.bin /u02/oracle/crs/bin/
cp: overwrite `/u02/oracle/crs/bin/clsfmt.bin'? y
[root@rac1 4679769]# ll /u02/oracle/crs/bin/clsfmt.bin
-rwxr-xr-x 1 oracle oinstall 1043097 Jan 23 21:28 /u02/oracle/crs/bin/clsfmt.bin
[root@rac1 4679769]#
[root@rac1 crs]# dd if=/dev/zero of=/dev/raw/raw1 bs=1M
dd: writing `/dev/raw/raw1': No space left on device
201+0 records in
200+0 records out
209715200 bytes (210 MB) copied, 0.522845 seconds, 401 MB/s
[root@rac1 crs]#
[root@rac1 crs]# ./root.sh
WARNING: directory '/u02/oracle' is not owned by root
WARNING: directory '/u02' is not owned by root
Checking to see if Oracle CRS stack is already configured

Setting the permissions on OCR backup directory
Setting up NS directories
Failed to upgrade Oracle Cluster Registry configuration
[root@rac1 crs]# dd if=/dev/zero of=/dev/raw/raw1 bs=1M
dd: writing `/dev/raw/raw1': No space left on device
201+0 records in
200+0 records out
209715200 bytes (210 MB) copied, 0.522845 seconds, 401 MB/s
[root@rac1 crs]# ./root.sh
WARNING: directory '/u02/oracle' is not owned by root
WARNING: directory '/u02' is not owned by root
Checking to see if Oracle CRS stack is already configured

Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory '/u02/oracle' is not owned by root
WARNING: directory '/u02' is not owned by root
assigning default hostname rac1 for node 1.
assigning default hostname rac2 for node 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node <nodenumber>: <nodename> <private interconnect name> <hostname>
node 1: rac1 rac1-priv rac1
node 2: rac2 rac2-priv rac2
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Now formatting voting device: /dev/raw/raw2
Format of 1 voting devices complete.
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
rac1
CSS is inactive on these nodes.
rac2
Local node checking complete.
Run root.sh on remaining nodes to start CRS daemons.
[root@rac1 crs]#
=======================================================
再到node2运行root.sh之前,先修改文件,node1上不需要修改.

BUG二: 修改$CRS_HOME/bin/vipca和$CRS_HOME/bin/srvctl
找到 export LD_ASSUME_KERNEL
再其下
添加 unset LD_ASSUME_KERNE
如果不修改 将遇到:

Running vipca(silent) for configuring nodeapps

/u01/crs/oracle/product/10.2.0/crs/jdk/jre//bin/java:
error while loading shared libraries:
libpthread.so.0: cannot open shared object file:
No such file or directory

==========================================================
BUG四:

[root@rac2 bin]# ./vipca
Error 0(Native: listNetInterfaces:[3])
[Error 0(Native: listNetInterfaces:[3])]
[root@rac2 bin]# ./oifcfg getif
[root@rac2 bin]#
[root@rac2 bin]# ./oifcfg -h
PRIF-9: incorrect usage

Name:
oifcfg - Oracle Interface Configuration Tool.

Usage: oifcfg iflist [-p [-n]]
oifcfg setif {-node <nodename> | -global} {<if_name>/<subnet>:<if_type>}...
oifcfg getif [-node <nodename> | -global] [ -if <if_name>[/<subnet>] [-type <if_type>] ]
oifcfg delif [-node <nodename> | -global] [<if_name>[/<subnet>]]
oifcfg [-help]

<nodename> - name of the host, as known to a communications network
<if_name> - name by which the interface is configured in the system
<subnet> - subnet address of the interface
<if_type> - type of the interface { cluster_interconnect | public | storage }


[root@rac2 bin]# ./oifcfg iflist
eth0 202.0.0.0
eth1 61.0.0.0
eth2 192.168.10.0
[root@rac2 bin]# ./oifcfg setif -global eth1/61.0.0.0:public
[root@rac2 bin]# ./oifcfg setif -global eth2/192.168.10.0:cluster_interconnect
[root@rac2 bin]# ./oifcfg getif
eth1 61.0.0.0 global public
eth2 192.168.10.0 global cluster_interconnect
[root@rac2 bin]#
============================================================================
BUG五:
如果遇到错误是
Runing vipca(silent) for configuring nodeapps
The given interface(s),"ethX" is not public.
Public interfaces should be used to configure virtual IPs.
直接运行vipca
考试时就会遇到这个报错.会叫你运行一个脚本.
这个脚本必须符合指定的路径安装才可以.
否则不能运行脚本.只能手动运行vipca解决.
============================================================================
最后在node2运行/u02/oracle/crs/bin/vipca 添加VIP别名.
全部服务都OK.回node1安装主菜单点确定按钮.

检查一下.
[root@rac1 bin]# ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
[root@rac1 bin]#
============================================================
4).安装DB software(只安装软件)
注意题中说的安装位置.
并且只安装软件.不要安装数据库.
5).使用DBCA配置ASM
选择 raw3 raw4 建立一个DISK GROUP

[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> select name,state from v$asm_diskgroup;

NAME STATE
------------------------------ -----------
DG1 MOUNTED

SQL>
[oracle@rac1 ~]$ asmcmd
ASMCMD> help
ASMCMD> ls
DG1/
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N N 512 4096 1048576 26624 26529 0 26529 0 DG1/
ASMCMD>


[oracle@rac2 ~]$ export ORACLE_SID=+ASM2
[oracle@rac2 ~]$ sqlplus / as sysdba
SQL> select INSTANCE_NAME,STATUS from v$Instance;

INSTANCE_NAME STATUS
---------------- ------------
+ASM2 STARTED

SQL>
[oracle@rac1 ~]$ echo $ORACLE_SID
+ASM1
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> select INSTANCE_NAME,STATUS from v$Instance;

INSTANCE_NAME STATUS
---------------- ------------
+ASM1 STARTED

SQL>
6).使用DBCA建库
全局数据库名 和 前缀 取决于$ORACLE_SID
注意大小写一致.
建立数据库到ASM
闪回和归档不要打开.后面有专门的题.
修改一下内存.看考试机的内存大小而定.
字符集随意.

建库过程中 留意资源情况
watch ./crs_stat -t

最后安装完
[root@rac2 bin]# ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
ora.racdb.db application ONLINE ONLINE rac2
ora....b1.inst application ONLINE ONLINE rac1
ora....b2.inst application ONLINE ONLINE rac2
[root@rac2 bin]#




4.安装失败.清除RAC
1).找到 OCR VOT盘
OCR==>/etc/oracle/ocr.loc
VOT==>./crsctl query css votedisk
下面操作每个节点一起做
2).vim /etc/inittab
删除CRS启动脚本
3).删除 /etc/ora*
4).删除 /var/tmp/.oracle/ 目录
5).删除 oraInventory $ORACLE_HOME $CRS_HOME目录
在其中一个节点做 DD
6).dd清除OCR和VOT 会重启.
























新增ocr备份


./ocrconfig -export /tmp/ocr_01.bak -s online

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

ocm考试大纲

TABLESPACE_NAME

FILE_NAME

TABLESPACE_NAME

FILE_NAME

MEMBER

NAME

NAME

ROLE

PRIVILEGE

ROLE

PRIVILEGE

ROLE

PRIVILEGE

软件包检查失败 这个是在RHEL5上安装,要求的软件包已经有高版本 所以下面的可以忽略 点上勾,用户验证通过

INFO: Configuration assistant “OMS Configuration” failed

[oracle@ocm2 bin]$ pwd /u01/oracle/OracleHomes/oms10g/opmn/bin [oracle@ocm2 bin][oracle@ocm2bin] [oracle@ocm2 bin][oracle@ocm2bin] ./opmnctl startall opmnctl: starting opmn and all managed processes…

3.启动agent odd+even: [oracle@ocm1 bin]$ pwd /u01/app/oracle/agent10g/agent10g/bin [oracle@ocm1 bin]$ ./emctl status agent Oracle Enterprise Manager 10g Release 10.2.0.1.0. Copyright © 1996, 2005 Oracle Corporation. All rights reserved.

[oracle@ocm2 bin]$ pwd /u01/app/oracle/OracleHomes/agent10g/bin [oracle@ocm2 bin]$ ./emctl status agent Oracle Enterprise Manager 10g Release 10.2.0.1.0. Copyright © 1996, 2005 Oracle Corporation. All rights reserved.

Agent is Not Running [oracle@ocm2 bin][oracle@ocm2bin] [oracle@ocm2 bin][oracle@ocm2bin] pwd /u01/app/oracle/OracleHomes/agent10g/bin [oracle@ocm2 bin]$ ./emctl status agent Oracle Enterprise Manager 10g Release 10.2.0.1.0. Copyright © 1996, 2005 Oracle Corporation. All rights reserved.

VIEW_THRESHOLDS(OBJECT_TYPE, OBJECT_NAME, METRICS_ID, INSTANCE_NAME, FLAGS, WARNING_OPERATOR, WARNING_VALUE, CRITICAL_OP

进去后 点 create 按钮

DBMS_METADATA.GET_DDL(‘TABLESPACE’,‘REGISTRATION’)

‘ALTERINDEX’||OWNER||’.’||INDEX_NAME||‘REBUILD;’

COUNT(*)

COUNT(*)

看prod_master.log文件内容 … CREATE DIRECTORY statements needed for files

建立数据库目录 使用scripts/ 结尾带路径的. 否则可能有bug

名称用系统取得就可以

看prod_master.log文件内容 … CREATE TABLE statement for external table:

COUNT(*)

COUNT(*)

TABLESPACE_NAME

FILE_NAME

DBMS_METADATA.GET_DDL(‘TABLE’,‘MAGAZINE_ARTICLES’)

NAME

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

TABLE_NAME

SQL_TEXT

COUNT(*)

CURRENT_SCN

COUNT(*)

COUNT(*)

COUNT(*)

资源限制参数

TYPE

COUNT(*)

‘ALTERINDEX’||INDEX_NAME||‘MONITORINGUSAGE;’

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

‘ALTERINDEX’||INDEX_NAME||‘NOMONITORINGUSAGE;’

'SELECTDBMS_METADATA.GET_DDL(

DBMS_METADATA.GET_DDL(‘INDEX’,

DBMS_METADATA.GET_DDL(‘INDEX’,

DBMS_METADATA.GET_DDL(‘INDEX’,

DBMS_METADATA.GET_DDL(‘TABLE’,

DBMS_METADATA.GET_DDL('TABLESP

Execution Plan

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 68 | 1 (0)| 00:00:01 | | 1 | VIEW | EMPLOYEES | 1 | 68 | 1 (0)| 00:00:01 | |* 2 | FILTER | | | | | | | 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 68 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |

Predicate Information (identified by operation id):

Note

Choose the PERFSTAT user’s password

Choose the Default tablespace for the PERFSTAT user

Choose the Temporary tablespace for the PERFSTAT user

评论

软件包检查失败这个是在RHEL5上安装,要求的软件包已经有高版本所以下面的可以忽略
点上勾,用户验证通过

[oracle@ocm2 bin]$ pwd
/u01/oracle/OracleHomes/oms10g/opmn/bin
[oracle@ocm2 bin] $[oracle@ocm2 bin]$ ./opmnctl startall
opmnctl: starting opmn and all managed processes…

3.启动agent
odd+even:
[oracle@ocm1 bin]$ pwd
/u01/app/oracle/agent10g/agent10g/bin
[oracle@ocm1 bin]$ ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright © 1996, 2005 Oracle Corporation. All rights reserved.

[oracle@ocm2 bin]$ pwd
/u01/app/oracle/OracleHomes/agent10g/bin
[oracle@ocm2 bin]$ ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright © 1996, 2005 Oracle Corporation. All rights reserved.

Agent is Not Running
[oracle@ocm2 bin] $[oracle@ocm2 bin]$ pwd
/u01/app/oracle/OracleHomes/agent10g/bin
[oracle@ocm2 bin]$ ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright © 1996, 2005 Oracle Corporation. All rights reserved.

进去后点 create 按钮

看prod_master.log文件内容
…
CREATE DIRECTORY statements needed for files

建立数据库目录使用scripts/ 结尾带路径的. 否则可能有bug

看prod_master.log文件内容
…
CREATE TABLE statement for external table:

| 0 | SELECT STATEMENT | | 1 | 68 | 1 (0)| 00:00:01 |
| 1 | VIEW | EMPLOYEES | 1 | 68 | 1 (0)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 68 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |