-- OCM 11G 考试
-- 考场机器环境：
Oracle Linux Release 5.4 (32-bit)
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 (32-bit)
Oracle Enterprise Manager 11g Grid Control Release 1 (11.1.0.1.0) (32-bit)
-- Data recovery Advisor
list failure;
advise failure;
repair failure;
change failure;
rman target / catalog rmanrc/rmanrc@prod4
一、Server Configuration
1.Create the database
$ mkdir -p /u01/app/oracle/oradata/PROD3
$ orapwd file=$ORACLE_HOME/dbs/orapwprod3 password=oracle entries=5
$ vi ora.ora
$ vi /home/oracle/3.ora
db_create_file_dest='/u01/app/oracle/oradata/PROD3'
db_block_size=8192
db_name=PROD3
sga_target=500m
control_files='/u01/app/oracle/oradata/PROD3/control01.ctl'
$ . oraenv
PROD1
$ export ORACLE_SID=PROD3
$ sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/ora.ora'
SQL> create database prod3 character set al32utf8 extent management local;
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> alter user system identified by oracle;
SQL> conn system/oracle
SQL> @?/sqlplus/admin/pupbld.sql
SQL> conn / as sysdba
SQL> startup force
SQL> create spfile from memory;
SQL> alter user dbsnmp identified by oracle account unlock;
$ emca -config dbconsole db -repos recreate
$ emctl status dbcosole
https://10.10.10.10:1158/em
# vi /etc/oratab
orcl:/u01/app/oracle/product/11.2.0/db_1:N
PROD2:/u01/app/oracle/product/11.2.0/db_1:N
2.Determine and set sizing parameters for database structures
3.Create and manage temporary, permanent, and undo tablespaces
SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/PROD1/temp1.dbf' size 50m autoextend on tablespace group temp_grp;
SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/PROD1/temp2.dbf' size 50m autoextend on tablespace group temp_grp;
SQL> alter database default temporary tablespace temp_grp;
SQL> create tablespace indx datafile '/u01/app/oracle/oradata/PROD1/indx1.dbf' size 40m;
SQL> create tablespace tools datafile '/u01/app/oracle/oradata/PROD1/tools1.dbf' size 10m;
SQL> create tablespace oltp datafile '/u01/app/oracle/oradata/PROD1/oltp1.dbf' size 48m autoextend on next 2m extent management local uniform size 2m segment space management auto;
SQL> alter system set undo_retention=5400;
SQL> alter system set undo_management=auto scope=spfile;
SQL> alter system set processes=135 scope=spfile;
4.Stripe data files across multiple physical devices and locations
5.Configure the database environment to support optimal data access performance
6.Create and manage database configuration files
7.Create and manage bigfile tablespaces
SQL> create bigfile tablespace test datafile '/u01/app/oracle/oradata/PROD1/test01.dbf' size 400m autoextend on next 1m maxsize 4t extent management local uniform size 1m;
8.Create and Manage a tablespace that uses NFS mounted file system file
SQL> shutdown immediate
$ cd $ORACLE_HOME/rdbms/lib/
$ make -f ins_rdbms.mk dnfs_on
SQL> startup
SQL>create tablespace shared_data datafile '/u02/oradata/prod1/shared_data1.dbf' size 10m;
SQL>select * from v$dnfs_servers;
$ cd ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk dnfs_off
9.Create and manage multiple network configuration files
$ vi tnsname.ora
10.Create and configure a listener
$ vi listener.ora
11.Configure the database instance to support shared server connections
alter systemset shared_servers = 1 scope=both;
alter systemset max_shared_servers = 5 scope=both ;
alter systemset dispatchers='(PROTOCOL=TCP) (SERVICE=emrepXDB)';
alter system set dispatchers='(protocol=TCP)(disp=8)(serv=xxx)';
-- 上面的模式指定只启动某个服务的共享模式，如果要设置所有服务都使用共享模式，则设置为
alter system set dispatchers='(PROTOCOL=TCP)';
alter systemset shared_servers = 0 ; -- 关闭共享
-- TNS配置 在客户端的tnsnames.ora 的tns设置中，在 CONNECT_DATA 设置中将SERVER 的值 DEDICATED 改为，即可使用共享服务器连接。
EMREP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gcprod2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED/SHARED)
(SERVICE_NAME = emrep)
)
)
-- 查看会话是否使用了共享
SELECT SID, USERNAME, OSUSER, MACHINE,TERMINAL, SERVER FROM V$SESSION WHERE USERNAME IS NOT NULL;
COL USERNAME FOR A20
COL OSUSER FOR A10
COL MACHINE FOR A20
COL TERMINAL FOR A20;
SELECT SID, USERNAME, OSUSER, MACHINE,TERMINAL, SERVER FROM V$SESSION
12.Set up network tracing
13.Manage Oracle network processes
14.Configure the network environment to allow connections to multiple databases
15.Use configurationless connections
16.Use OPatch to install a patch
$ ./opatch apply
-- 检查版本
-- OUI 补丁
$ crsctl query crs softwareversion [hostname] -- 单节点
$ crsctl query crs activersion -- 集群
$ srvctl stop nodeapps -n node1 -r
$ cd patchset_dir/
$ ./runInstaller
-- OPatch 补丁
$ export ORACLE_HOME=$GI_HOME
$ opatch command [options] or
$ opatch command -oh GI_HOME [options]
$ opatch command -help
$ opatch query -all | grep -i Rolling
-- grid OPatch
$ opatch lsinventory -detail -oh GI_HOME
# crsctl stop crs
# cd <patch_location>
# ./custom/scripts/prerootpatch.sh -crshome GI_HOME -crsuser grid
$ ./custom/scripts/prepatch.sh -crshome GI_HOME
$ opatch apply -local -oh GI_HOME patch_location
$ ./custom/scripts/postpatch.sh -crshome GI_HOME
# ./custom/scripts/postrootpatch.sh -crshome GI_HOME
$ opatch lsinventory -detail -oh GI_HOME
-- psu
$ $GI_HOME/OPatch/ocm/bin/emocmrsp -output /app/setup/ocm.rsp
$ $GI_HOME/OPatch/opatch auto <patch_location> -ocmrf /app/setup/ocm.rsp
chown oracle:asmadmin /u01/app/oracle/product/11.2.0/db_1/bin/oracle
chmod 6751 /u01/app/oracle/product/11.2.0/db_1/bin/oracle
@?/rdbms/admin/catbundle.sql psu apply
@?/rdbms/admin/utlrp.sql
17.Use Grid Infrastructure to manage oracle databases and other resources
-- 安装 asmlib 库 oracleasm-support\oracleasm
-- 初始化服务
# oracleasm configure -i
# oracleasm init
# ls -ltr /dev/oracleasm/disks
-- 创建磁盘
# oracleasm createdisk asmdisk1 /dev/sdb1
# oracleasm listdisks
# ls -ltr /dev/oracleasm/disks
-- 安装grid
-- 这里执行asmca之前需要在目录 $GRID_HOME/crs/install/找到脚本roothas.pl
-- /u01/app/11.2.0/grid/crs/install/roothas.pl
# $GRID_HOME/crs/install/roothas.pl
-- 上面执行配置完成后执行下面命令
$ export DISPLAY=192.168.56.1:0.0
$ ./asmca -- 创建两个磁盘组，data为正常冗余、fra为外部冗余 磁盘组到 oracle restart
-- 加如下服务来让grid管理：emrep,prod4,listener
-- 加监听
$ $CRS_HOME/bin/srvctl add listener -l LISTENER -s -o $ORACLE_HOME
$ ./srvctl config listener
$ ./srvctl start listener
-- 加数据库
$ ./srvctl add database -d emrep -o /u01/app/oracle/product/dbhome_1
$ ./srvctl start database -d emrep
$ ./srvctl config database
$ ./srvctl config database -v
$ ./srvctl status database -d emrep
$ ./srvctl config database -d emrep -a
18.Use Enterprise Manager Configuration Assistant(EMCA) utility 使用企业管理配置助手（EMCA）工具
二、Enterprise Manager Grid Control
1.Install and Patch Enterprise Manager Grid Control software
-- 主要是 agent 的安装
$ emca -deconfig dbcontrol db -repos drop -- 卸载系统原有的dbcontrol
-- 配置数据库参数
ALTER SYSTEM SET log_buffer=10485760 SCOPE=SPFILE;
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
ALTER SYSTEM SET session_cached_cursors=200 SCOPE=SPFILE;
alter database datafile '/u01/app/oracle/oradata/prod1/undotbs01.dbf' resize 200m;
-- 环境
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=prod1
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_MIDDLEWARE_HOME_LOCATION=/u01/app/Middleware
export OMS_HOME=/u01/app/gc_inst
-- 安装 agent
EM 图形化安装安装
emctl exportconfig oms -dir <backup location>
-- 如何启动 GC 考试考自动启动
/u01/app/Middleware/oms11g/bin/emctl status oms -details
/u01/app/Middleware/oms11g/opmn/bin/opmnctl status
/u01/app/Middleware/oms11g/bin/emctl secure unlock -sysman_pwd oracle123
-- oms log trc 文件位置
/u01/app/gc_inst/em/EMGC_OMS1/sysman/log/
/u01/app/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/logs
2.Configure the Enterprise Manager repository
3.Create Enterprise Manager Grid Control users
-- setup->administrator->create(super administrator)
4.Use Enterprise Manager to modify a database configuration
5.Configure Enterprise Manager to modify database availability
6.Create and manage jobs
7.Create and monitor alerts
8.Create notifications
9.Implement Grid Control and Database Control
10.Choose the appropriate tablespace type for the intended use
11.Create Scheduler jobs
12.Create schedules
13.Assign jobs to windows
14.Create programs
15.Create job classes
16.Install the Enterprise Manager Grid Control infrastructure
17.Deploy Enterprise Manager Grid Control agents
18.Configure Grid Control for business requirements
三、Managing Database Availability
1.Mantain recovery catalogs
-- 创建恢复目录步骤
-- prod4 创建恢复目录数据库表空间及用户
create tablespace ts_rman datafile '/u01/app/oracle/oradata/orcl/ts_rman01.dbf' size 100m autoextend on;
create user rmanrc identified by rmanrc default temporary tablespace temp default tablespace ts_rman quota on ts_rman unlimited;
grant recovery_catalog_owner to rmanrc;
-- 创建恢复目录数据库
rman catalog rmanrc/rmanrc@prod4
create catalog;
-- prod1、prod2 连接到恢复目录并注册到prod4中 考试从这里开始
rman target sys/oracle@prod1 catalog rmanrc/rmanrc@prod4
-- rman target sys/oracle@prod1
-- connect catalog rmanrc/rmanrc@prod4
upgrade catalog;
register database;
list db_unique_name all;
resync catalog;
-- local script
create script scp1 {
allocate channel c1 device type disk;
backup tablespace users;
release channel c1;
}
list script names;
print script scp1;
replace global script scp2 {rman command;}
replace global script scp2 from file 'file_name';
delete script scp2;
-- global script
create script scp2 {
allocate channel c1 device type disk;
backup spfile;
release channel c1;
}
run {execute script scp1;}
report schema; -- 查看需要备份的数据
backup as backupset tablespace users;
backup as copy tablespace users format '/home/oracle/backup/';
backup database plus archivelog;
-- 考试需要配置快速恢复区
alter system set db_recovery_file_dest='/u01/app/fast_recovery_area';
alter system set db_recovery_file_dest_size=10Gp;
-- 增量更新备份 比较占空间
recover copy of database with tag 'dialy_inc';
backup incremental level 1 for recover of copy with tag 'daily_inc' database;
-- 0 level backup
backup incremental level 0 database;
backup incremental level 1 database; -- 差异增量
backup incremental level 1 cumulative database; -- 累积增量 level 0 开始
crosscheck backup;
list expired backup;
-- 压缩备份
configure compression algorithm 'high/medium/low/basic'
backup as compressed backupset database plus archivelog;
-- 多段备份 比较大的数据文件分段，比如大文件表空间
validate datafile <option> section size <integer> [K M G];
backup database 5 section size = 500M tag 'section25mb';
vi rman.sh -- 考试可以按照如下方式备份
rman target / <<EOF
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup tablespace sysaux section size 50m tag 'sysauxsection50m';
}
EOF
-- 双工备份 同时备份两份
backup as backupset device type sbt copies 2 incremental level 0 database;
-- 备份备份集
backup device type disk as backupset database plus archivelog;
backup device type sbt backupset all;
-- 配置保留备份
backup tablesapce users format '/home/oracle/db_%U.bak' keep until time 'sysdate+365' -- 备份保留一年
-- 创建归档备份 考试要求创建一个0级备份永久保留 考试执行下面语句即可，但是备份必须连接到恢复目录数据库
backup incremental level 0 database format '/home/oracle/db_%U.bak' keep forever tag='ABC';
-- 备份快速恢复区文件
backup recovery area to destination '/home/oracle';
backup recovery files; -- 所有文件
-- 控制文件的备份
-- 手工创建控制文件到跟踪文件
alter database backup controlfile to trace;
alter database backup controlfile to trace as '/home/oracle/control.sql' noresetlogs;
-- 恢复目录添加备份文件 如添加归档日志到恢复目录
catalog archivelog '/tmp/arch_logs/archive1_731.log','/tmp/arch_logs/archive1_732.log';
catalog start with '/tmp/arch_logs/';
-- block change tracking
alter system set db_create_file_dest = '/u01/app/oracle/oradata/';
alter database enable block change tracking; -- 默认存放到上述指定的路径下
alter database enable block change tracking using file '/../../blcok...';
-- 验证块改变比
select file#,
avg(datafile_blocks),
avg(blocks_read),
avg(blocks_read / datafile_blocks) * 100 as pct_read_for_backup,
avg(blocks)
from v$backup_datafile
where used_change_tracking = 'YES'
and incremental_level > 0
group by file#;
-- 恢复数据库
-- 设定快速恢复时间
alter system set fast_start_mttr_target = 30 scope=both;
-- 考试考对system sysaux 表空间的恢复
shutdown abort;
startup mount;
rman target /
restore datafile 1;
recover datafile 1;
-- 还原点
create restore point before_mods;
create restore point end_q1 as of scn 100;
-- 闪回 保证保留时间内能闪回查询数据（想要回退闪回事物操作必须开启附加日志）
-- 可以查看表 dba_flashback_txn_state/report 了解闪回事物回退情况
alter database add supplemental log data;
alter database add supplemental log data (primary key) columns;
alter tablespace undotbs1 retention guarantee;
-- 闪回查询 闪回版本查询
-- 考试考闪回事物
-- 调用如下包做事物回退
-- 考试可以使用如下方式，也可以配置使用EM
-- 首先找到事物ID
select xid,operation,undo_sql from flashback_transaction_query where table_name='EMPLOYEES';
declare
v_xid xid_appay;
begin
v_xid:=sys.xid_appay('0050050000035b');
dbms_flashback.transaction_backout(1,v_xid);
end;
/
select employee_id,salary from employees as of timestamp <time1> where employee_id=200;
select employee_id,salary from employees as of timestamp to_timestamp('2019-06-06 14:14:14','yyyy-mm-ddhh24:mi:ss') where employee_id=200;
select versions_xid,versions_starttime,versions_endtime,salary from employees versions between timestamp <time1> and <time2> where employee_id=200;
alter table employees enable row movement;
flashback table hr.departments to timestamp to_timestamp('2019-06-06 14:14:14','yyyy-mm-ddhh24:mi:ss');
select * from flashback_transaction_query; -- 查询闪回事物记录
-- 闪回删除12C OCM 会考到
flashback table "BIN$employees" to before drop rename to emp_dept;
-- 闪回数据归档(支持DDL操作) 12C 会考到(需要有系统权限 flashback archive administer)
create tablespace fda_tbs1 datafile '+DATA' size 100m;
create flashback archive fda1 tablesapce fda_tbs1 optimize data quota 10M retention 1 year;
alter table hr.employees flashback archive fda1;
-- 闪回数据库
alter system set db_flashback_retention_target=2880 scope=both; -- 两天
alter database flashback on;
flashback database to time = "to_date('2019-06-06 14:14:14','yyyy-mm-ddhh24:mi:ss')";
flashback database to scn=23565;
flashback database to sequence=223 thread=1;
flashback database to timestamp(sysdate-1/24);
flashback database to restore point b4_load;
create restore point before_upgrade guarantee flashback database;
2.Configure Recovery Manager
3.Use Recovery Manager to perform database backups
4.Use Recover Manager to perform complete database restore and recovery operations
5.Configure RMAN
6.Create different types of RMAN backups to cater for different performance and retention requirements
7.Set Flashback Database parameters
8.Configure a Fast Recovery Area
9.Perform various recovery operations using Flashback technology
四、Data Management
1.Manage Materialized Views to improve rewrite and refresh performance
-- prod1创建允许快速刷新以及允许重写的物化视图
select owner,mview_name from dba_mviews where mview_name='PROD_COST_MV';
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','PROD_COST_MV','SH') from dual;
-- 考试考查询重写及快速刷新（改造他给你的视图）定义刷新机制
-- 使用下面助手
dbms_advisor.tune_mview
-- 考试利用这个过程产生新的SQL语句，执行新语句创建物化视图
dbms_advisor.explain_mview -- 解释出物化视图有些什么功能，比如查询重写、快速刷新
dbms_mview.explain_rewrite
-- 解释为什么不重写的原因
-- 允许查询重写
create materialized view cust_sales_mv
pctfree 0 tablespace users
storage(initial 1m next 1m pctincrease 0)
build deferred -- build immediate
refresh fast next sysdate + 7
-- refresh complete
-- refresh fast next sysdate +7
-- refresh start with round(sysdate+1)+11/24
-- next next_day(trunc(sysdate),'MONDAY') + 15/24
enable query rewrite
as
select c.cust_id,sum(amount_sold)
from sales s,customers c
where s.cust_id=c.cust_id
group by c.cust_id;
drop materialized view cust_sales_mv;
--
exec dbms_mview.refresh('cust_sales_mv','FC','',false,true,0,0,0,false);
variable fail number;
exec dbms_mview.refresh_all_mviews(:fail,'C','',true,false);
variable fail number;
exec dbms_mview.refresh_dependent(:fail,'CUSTOMERS','SALES','CF','',TRUE,FALSE);
variable deplist varchar2(500)
exec dbms_mview.get_mv_dependencies('CUSTOMERS,SALES',:deplist);
dbms_mview.fast_refreshable
--
create materialized view log on sh.sales
tablespace users
nologging
parallel
with rowid,sequence(cust_id,"AMOUNT_SOLD","QUANTITY_SOLD") i ncluding new vslues;
drop materialized view log on customers;
comment on materialized view cust_sales_mv is 'sales materialized view';
alter materialized view cust_sales_mv refresh fast on commit;
alter materialized view cust_sales_mv compile;
alter materialized view cust_sales_mv disable query rewrite;
alter table cust_sales_mv allocate extent;
alter materialized view cust_sales_mv nologging;
alter materialized view cust_sales_mv log force;
alter materialized view cust_sales_mv truncate partition year_1995;
alter materialized view cust_sales_mv drop partition year_1995;
alter materialized view exchange partition year_1995 with table sales_1995;
alter materialized view MV_name consider fresh;
QUERY_REWRITE_INTEGRITY -- 控制参数完整性(ENFORCED TRUSTED STALE_TOLERATED)
-----------------------------------
dbms_mview.explain_mview
@?/rdbms/admin/utlxmv.sql
truncate table mv_capabilities_table;
BEGIN
DBMS_MVIEW.EXPLAIN_MVIEW('select a.id,b.name,a.rowid a_rowid,b.rowid b_rowid from comp1 a,comp2 b where a.id=b.id');
END;
/
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('cust_sales_mv','123');
SELECT MSGTXT
FROM MV_CAPABILITIES_TABLE
WHERE CAPABILITY_NAME = 'REFRESH_FAST_AFTER_INSERT';
select capability_name,possible,related_text,msgtxt from mv_capabilities_table where statement_id='123' order by seq;
SELECT * FROM MV_CAPABILITIES_TABLE;
-----------------------------------
-----------------------------------
dbms_mview.explain_rewrite
@?/rdbms/admin/utlxrw.sql
BEGIN
DBMS_MVIEW.EXPLAIN_REWRITE(QUERY => 'select a.id,b.name,a.rowid a_rowid,b.rowid b_rowid from comp1 a,comp2 b where a.id=b.id',
MV => 'sh.cust_sales_mv' STATEMENT_ID => '');
END;
/
SELECT MESSAGE FROM REWRITE_TABLE;
alter materialized view cust_sales_mv enable query rewrite;
truncate table rewrite_table;
-----------------------------------
-- 要想让多个相关的SQL走物化视图（一个物化视图满足多个查询，反之也可以，需要去衡量），必须有外键依赖及修改下面参数
alter system set query_rewrite_integrity=trusted; -- 考试改为trusted
alter table sales modify constraint sales_customer_fk norely;
-- 创建一个物化视图对应会有一个物理表（容器表）和一个物化视图
select * from dba_mviews;
select * from dba_objects where object_name='cust_sales_mv';
-- 什么时候该选择物化视图呢
1、分析工作负载
2、使用 SQL access advisor 来帮我们选择
3、可以使用 dbms_mview.explain_rewrite 分析为什么没有使用物化视图
-- 考试考调优物化视图查询重写(考试就考DBMS_ADVISOR.TUNE_MVIEW的使用)
-- DBMS_ADVISOR.TUNE_MVIEW 使用示例
variable task_cust_mv varchar2(30);
variable create_mv_ddl varchar2(4000);
execute :task_cust_mv := 'cust_mv';
execute :create_mv_ddl := ' -
create materialized view cust_sales_mv -
pctfree 0 tablespace users -
storage(initial 1m next 1m pctincrease 0) -
build deferred -
refresh fast next sysdate + 7 -
enable query rewrite -
as -
select c.cust_id,sum(amount_sold) -
from sales s,customers c -
where s.cust_id=c.cust_id -
group by c.cust_id';
execute dbms_advisor.tune_mview(:task_cust_mv,:create_mv_ddl);
execute dbms_advisor.delete_task(:task_cust_mv);
-- chaeck report
create directory tune_results as '/home/oracle';
grant read,write on directory tune_results to sh;
execute dbms_advisor.create_file(dbms_advisor.get_task_script(:task_cust_mv),'tune_results','mv_create.sql');
select statement from user_tune_mview where task_name = :task_cust_mv and script_type='IMPLEMENTATION';
2.Configure and manage distributed materialized views 配置和管理分布式物化视图
3.Create and Manage encrypted tablespaces 创建和管理加密表空间
4.Manage Transport of tablespaces across platforms
-- 考试在prod1库考传输表空间 考 Solaris 64 拷贝过来的一个文件加载到linux 32(一个dump文件一个数据文件)
-- 考试的时候字节序没有转换，我们需要使用rman转换
-- 查询平台名称
select * from v$transportable_platform;
select platform_name from v$database;
-- 确定字节序
select tp.endian_format v$transportable_platform tp,v$database d where tp.platform_name = d.platform_name;
-- 例如使用 rman 转换
rman
connect target sys@orcl
sql 'alter tablespace hr read only';
convert tablesapce hr to platfoem 'Solaris[tm] OE (64-bit)' format '/tmp/transport_to_solaris/%U';
-- 考试示例 prod1
-- 可以选择配置 dbconsole
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create
alter tablespace tbs read only;
expdp system/oracle directory td dupfile=expdp_tran_tbs.dmp transport_tablespaces=tbs logfile=tran.log
rman target /
convert datafile '/home/oracle/tbs01.dbf' from platfoem 'Solaris[tm] OE (32-bit)' to platfoem 'Linux IA (32-bit)' format '/tmp/transport_to_solaris/tbs01.dbf';
cp /tmp/transport_to_solaris/tbs01.dbf /u01/app/oracle/oradada/prod1/tbs01.dbf
sqlplus / as sysdba
create user sst identified by sst;
grant connect,resource to sst;
create directory td as '/home/oracle';
impdp system/oracle directory=td dumpfile=expdp_tran_tbs.dmp remap_schema=sst:sst transport_datafiles='/u01/app/oracle/oradada/prod1/tbs01.dbf' logfile=tran.log
strings /u01/app/oracle/oradada/prod1/tbs01.dbf|grep more -- 查看数据文件字符集
-- 查看并修改表空间状态
select tablespace_name,status from dba_tablespaces;
alter tablespace tbs read write;
5.Configure a schema to support a star transformation query
-- 考试要求配置查询模式支持星型转换
-- 把如下参数设置为 true 就可以了
-- 注意：星型转换不支持绑定变量，自连接表以及部分分区表
-- 星型转换要求在连接(join)字段的每一个列上面有一个单列位图索引
ALTER SYSTEM SET star_transformation_enabled='TRUE' SCOPE=BOTH;
-- 建立位图连接索引
create bitmap index sales_q_bix on sales(times.calender_quarter_desc)
from sales,times where sales.time_id=times.time_id local;
6.Administer external tables
-- 考试一定要考的手工建立
-- 考试就是用如下方式生成一个外部表，控制文件已经准备好了
sqlldr sh/sh control=info.ctl external_table=GENERATE_ONLY log=info.sql
-- 考试时执行完成上面语句才能将原表删除，然后在用生成的脚本创建外部表
--
create directory ext_tab_dir as '/usr/apps/datafiles';
grant read on directory ext_tab_dir to sh;
vi info.dat
56,"november,15,1980",baker,mary,alice, 09/01/2009
create table emp_load
(
employee_id char(5),
emp_dob char(20),
last_name char(20),
first_name(15),
middle_name char(15),
hire_date date
)
organization external
(
type oracle_loader
default directory ext_tab_dir
access parameter
(
records delimited by newline characterset US7ASCII
badfile 'ext_tab_dir':'info.bad'
logfile 'info.log'
readsize 1048576
fields terminated by ',' optionally enclosed by '"' ldrtrim
missing filed values are null
reject rows with all null fields
(
"employee_id" char(255) terminated by "," optionally enclosed by '"',
"emp_dob" char(255) terminated by "," optionally enclosed by '"',
"last_name" char(255) terminated by "," optionally enclosed by '"',
"first_name" char(255) terminated by "," optionally enclosed by '"',
"middle_name" char(255) terminated by "," optionally enclosed by '"',
"hire_date" char(255) terminated by "," optionally enclosed by '"' date_format date mask "mm/mm/yyyy"
)
)location ('info.dat')
)reject limit unlimited
parallel;
--
create table ext_test
(
name char(256),
name_desc char(256)
)
organization external
(
type oracle_loader
default directory ext_tab_dir
access parameters
(
records delimited by newline
badfile ext_tab_dir:'test.bad'
logfile ext_tab_dir:'test.log'
discardfile ext_tab_dir:'test.dsc'
skip 1
fields terminated by';'
reject rows with all null fields
(
NAME char(256),
NAME_DESC char(256)
)
)
location ('test.csv')
)
reject limit unlimited
parallel;
7.Implement Data Pump export and import jobs for data transfer
8.Implement Data Pump to and from remote databases
-- 考试考使用 dblink prod2 连接到prod1
9.Configure and use parallel execution for queries
-- 考试考并行配置
SQL> alter system set parallel_degree_policy=limited; -- auto
SQL> alter system set parallel_max_servers=100 scope=spfile;
SQL> alter system set parallel_min_servers=10 scope=spfile;
--
SQL> alter system set parallel_min_percent=100 scope=spfile;
SQL> alter system set parallel_adaptive_multi_user=true scope=spfile;
--
SQL> alter table sh.sales parallel 8;
SQL> alter system set parallel_degree_policy=manual scope=spfile;
-- 执行时间少于5秒就不使用并行
SQL> alter system set parallel_min_time_threshold=5 scope=spfile;
--
SQL> alter session enable|disable|force parallel [DML|DDL|QUERY](parallel n)
-- General information
v$filestat v$sesstat v$sysstat
-- 并行相关的系统视图
v$px_session v$px_process v$px_process_sysstat v$pq_sesstat v$px_buffer_advice v$pq_tqstat
10.Use SQL*Loader
-- 考试必考使用命令方式
vi salesdec01.dat
133|6538|05-DEC-2001|P|999|1|33.25|
134|6539|06-DEC-2001|P|1999|2|34.25|
-- 在数据库创建一张表
sqlplus sh/sh
create table sales(
prod_id number not null,
cust_id number not null,
time_id date not null,
channel_id varchar2(10) not null,
promo_id number not null,
quantity_sold number(10,2) not null,
amount_sold number(10,2) not null
);
-- 创建一个控制文件
$ vi sales_dec01.ctl
load data infile salesdec01.dat
append into table sh.sales
fields terminated by "|" optionally enclosed by '"'
trailing nullcols
(
prod_id,
cust_id,
time_id date 'yyyy-mm-dd',
channel_id,
promo_id "trim(:promo_id)"(nullif promo_id=''),
quantity_sold,
amount_sold
)
$ sqlldr sh/sh control=sales_dec01.ctl direct=true skip 2
11.Administer, manage and tune parallel execution 管理和优化并行操作
五、Data Warehouse Management 数据仓库管理
1.Administer partitioned tables and indexes using appropriate methods and keys 使用适当地方法和键管控分区表和索引
-- 创建自动分区表
2.Perform partition maintenance operations
-- 考试考分区表 prod1 使用 sh 用户创建分区表(原题)
create table sales_history_2006(
prod_id number not null,
cust_id number not null,
time_id data not null,
channel_id number not null,
promo_id number not null,
quantity_sold number(10,2) not null,
amount_sold number(10,2) not null
)
partition by range(time_id)
interval (numtoyminterval('1','month'))
(
partition sal1 values less than (to_date('2002-01-01','yyyy-mm-dd')) tablespace sal01,
partition sal2 values less than (to_date('2003-01-01','yyyy-mm-dd')) tablespace sal02,
partition sal3 values less than (to_date('2004-01-01','yyyy-mm-dd')) tablespace sal03,
partition sal4 values less than (to_date('2005-01-01','yyyy-mm-dd')) tablespace sal04,
);
-- 考题引用分区
create table orders(
order_id number(12) not null,
order_date TIMESTAMP,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
partition by range(order_date)
(
partition Q1_2005 values less than (to_date('20150201','yyyymmdd')),
partition Q2_2005 values less than (to_date('20150301','yyyymmdd')),
partition Q3_2005 values less than (to_date('20150401','yyyymmdd')),
partition Q4_2005 values less than (to_date('20150501','yyyymmdd'))
);
create table order_items(
order_id number(12) not null,
line_item_id number(3) not null,
product_id number(6) not null,
unit_price number(8,2) not null,
quantity number(8) not null,
constraint order_items_fk foreign key(order_id) references new_orders(order_id)
)partition by reference(order_items_fk);
-- SQL Access Advisor 可以帮助我们判断是否需要建立分区
create table example(
idx number,name varchar2(20)
)
partition by range(idx)
(
partition p1 values less than(0) tablespace users,
partition p2 values less than(maxvalue) tablespace users
);
select * from dba_tab_partitions where table_name='EXAMPLE';
delete from example subpartition(sp1);
insert into example partition(sp1) values(4,5,...);
select idx from example partition(p1);
create table com2 as select * from example partition(sp1);
-- 数据泵导出分区数据
expdp sh/sh tables=(SALES:SALES_Q1_2001)
-- 分区索引
create index idx on emp(first_name) global partition by range(first_name)
(
partition x1 values less than('H') tablespace users,
partition x2 values less than(maxvalue)
);
create index idx on hr_emp(first_name) local;
drop index idx;
create index idx on hr_emp(first_name) tablespace indx01 local
(
partition ex1 tablespace index02,
partition ex2 tablespace index03,
partition ex3
);
select * from dba_ind_partitions where index_name='IDX';
3.Maintain indexes on a partitioned table
-- 考点
4.Implement securefile LOB
5.Create and manage LOB segments
-- 考题 创建和管理LOB段
6.Implement fine-grained access control
-- 考题细粒度 审计
7.Create and manage contexts
8.Administer flashback data archive and schema evolution
-- 考题 管理闪回数据归档和模式演进
六、Performance Management
1.Administer Resource Manager
-- 考试考创建资源计划 prod1，HR 用户作为资源管理用户，这里要授予资源管理权限，必须配置好 GC 才可以做
dbms_resource_manager_privs.grant_system_privilege(privilege_name=>'ADMINISTER_RESOURCE_MANAGER',grantee_name=>'HR',admin_option=>FALSE);
-- 然后创建一个消费组OLTP和DSS,然后创建资源计划TOP1(OLTP,DSS)
2.Use Result Cache
3.Use multi column statistics
-- 考试考收集多列统计信息
exec sys.dbms_stats.create_extended_stats(ownname => 'SH',tabname => 'CUSTOMERS',extension => '(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID)');
select * from dba_tab_col_statistics where table_name='CUSTOMERS' and owner='SH';
select * from dba_tab_cols where table_name='CUSTOMERS' and owner='SH';
select name,value from v$sys_optimizer_env;
begin
dbms_stats.gather_table_stats(
ownname => 'SH',
tabname => 'CUSTOMERS',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade =>DBMS_STATS.AUTO_CASCADE,
degree => null,
no_invalidate => DBMS_STATS.AUTO_INVALIDATE,
granularity => 'AUTO',
methods_opt => 'FOR ALL COLUMNS(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID) SIZE 10'
);
end;
4.Gather statistics on a specific table without invalidating cursors Use partitioned indexes
5.Administer and tune schema object to support various access methods Interpret execution plan
6.Use SQL tuning tools and features
7.Use SQL Tuning Advisor
-- 使用 助手
8.Use SQL Access Advisor
9.Use SQL Performance Analyzer Configure baseline templates
10.Use SQL Plan Management feature Implement instance caging
-- 考试考SQL计划管理，考试的时候要求把执行计划抓取出来，只需要把如下参数改为TRUE就可以
alter system set optimizer_capture_sql_plan_baselines=TRUE;
select signature,sql_handle,sql_text,plan_name,origin,enabled,accepted,fixed,autopurge from dba_sql_plan_baselines;
exec :cnt := dbms_spm.alter_sql_plan_baseline(
sql_handle => 'SYS_SQL_....',
plan_name => 'SYS_SQL_PLAN_...',
attribute_name => 'ENABLED',
attribute_value => 'NO'
);
declare
c_re number;
begin
c_re := dbms_spm.drop_sql_plan_baseline('SQL_...','SQL_PLAN_...');
end;
七、Grid Infrastructure and ASM
1.Install Oracle Grid Infrastructure Create ASM Disk Groups
2.Create and manage as ASM instance Implement ASM failure groups Creating ACFS File System
-- 考试要求创建acfs快照注意使用命令操作
$ acfsutil -h|grep snap -- 查看帮助
$ acfsutil snap create/delete SNAP01 /u01/app/oracle/db_files
$ cd /u01/app/oracle/db_files
$ ls -ltr .ACFS
$ acfsutil info fs <mount point> ls -l <mount_point>/.ACFS/snaps
$ acfsutil info fs -- 查看信息
-- 可以通过快照恢复数据到创建的时间点
-- 必考创建和管理ASM实例，实现ASM故障恢复（失败组）组，创建ACFS ASM集群文件系统
-- 拷贝文件到ACFS 并备份acfs
3.Start, stop, configure and administer Oracle Grid Infrastructure
4.backup grid aource ocr.
-- OCR做镜像备份
八、Real Application Clusters
1.Install the Oracle Database 11gR2 software
-- 通过VNC远程连接上去安装
# vncvlewer host01:2 -- 输入密码就可以登录到需要安装集群的机器上
$ cd /stage/grid
$ ./runInstaller
-- 也可使用SSH 先登录本地oracle用户
# xhost +
# su - oracle
$ ssh -X host01
# dd if=/dev/zero of=/dev/oracleasm/disks/ASMDISK01 bs=1024K count=10
-- 考试要求重启机器后不自动启集群 CRS
# $GRID_HOME/bin/crsctl config crs
# $GRID_HOME/bin/crsctl disable crs
-- CRS CHECK
# crsctl query css votedisk
$ cat /etc/oracle/ocr.loc
$ grep voting <GI_HOME>/log/<hostname>/ceed/ocssd.log
$ cluvfy comp ocr -n all -verbose
$ ocrcheck
# crsctl add css votedisk path_to_voting_disk -- 只能把CRS放到ASM或是集群文件系统
# crsctl replace votedisk +asm_disk_group
# crsctl delete css votedisk path_to_voting_disk
$ crsctl query crs activeversion
-- 考试让你加一块 OCR 镜像盘上去执行下面命令(考试就加FRA磁盘组)
# ./ocrconfig -add +FRA
# ./ocrconfig -add /dev/sde1
# ./ocrconfig -replace /dev/sde1 -replacement +DATA2
# ./ocrconfig -repair -add +DATA1
# ./ocrcheck -- 就可以看到两个磁盘组
-- 考点 CRS 的备份
# ./ocrconfig -showbackup manual
# ./ocrconfig -manualbackup -- 物理备份
# ./ocrconfig -export /home/oracle/ocr.backup -- import 导入配置
-- 考点 备份 OLR(oracle local register)
# ./ocrdump -local /home/oracle/MYOLR -- 考试(两个节点)
# ./ocrdump -local
-- 恢复OCR
# ./ocrconfig -showbackup
# ./crsctl stop cluster -all
# ./crsctl stop crs
# ./ocrconfig -restore /u01/app/.../cdata/cluster01/day.ocr
# ./ocrconfig -import /shared/export/ocrback.dmp
# ./crsctl start crs
$ cluvfy comp ocr -n all
-- 网络设置
$ oifcfg iflist -p -n
$ oifcfg getif
$ srvctl config nodeapps -a
$ srvctl stop service -d RDBA -s sales,oltp -n host01
$ srvctl config vip -n host01
$ srvctl stop listener
$ srvctl stop vip -n host01
$ srvctl modify nodeapps -n host01 -A 192.168.2.125/255.255.255.0/eth0
$ srvctl config nodeapps -n host01
$ srvctl start vip -n host01
$ cluvfy comp nodecon -n all -verbose
-- 修改私网网卡 IP
$ oifcfg setif -global eth2/192.0.2.0:cluster_interconnect
# oifcfg getif
# crsctl stop crs
# ifconfig eth2 192.0.2.15 netmask 255.255.255.0 broadcast 192.0.2.255
$ oifcfg delif -global eth1/192.168.1.0
# crsctl start crs
-- ADD SCAN IP
$ srvctl add scan -n cluster01-scan
$ srvctl remove scan [-f]
$ srvctl add scan_listener
$ srvctl add scan_listener -p 65536 -- using mondefault port number
$ srvctl remove scan_listener [-f]
$ srvctl modify scan -n cluster01-scan
$ srvctl modify scan_listener -u
2.Configure ASM for the shared disks and create a clustered database Configure archiving
3.Configure services using both Manual and Policy Managed methods
-- 注意 使用手动和策略管理方法配置服务
-- 还会考创建一个服务只允许访问一个节点
4.add and delete cluster node.
-- add cluster node
-- 在存在的节点执行检查
-- 硬件及操作系统检查
$ cluvfy stage -post hows -n node3
$ cluvfy comp peer -refnode node1 -n node3 -orainv oinstall -osdba asmdba -verbose
$ cluvfy stage -pre nodeadd -n node3
$ export IGNORE_PREADDNODE_CHECKS=Y
$ $GI_HOME/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={node3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={node3-vip}"
# /u01/app/oraInventory/orainstRoot.sh
# /u01/app/11.2.0/grid/root.sh
$ cluvfy stage -post nodeadd -n node3-verbose
-- delete cluster node
node1# crsctl unpin css -n node3
node3# $GI_HOME/crs/install/rootcrs.pl -delete -force
node1# crsctl delete node -n node3
node3$ $GI_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=GI_HOME "CLUSTER_NODES={node3}" CRS=TRUE -local
node3$ ./runInstaller -detachHome ORACLE_HOME=/u01/app/11.2.0/grid
node3# ./deinstall -local
node1$ $GI_HOME/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/11.2.0/grid "CLUSTER_NODES={node1,node2}" CRS=TRUE
node1$ cluvfy stage -post nodedel -n node3 [-verbose]
九、Data Guard
1.Create Physical Standby Database with real-time apply
-- 手工创建
-- 注意：配置使用连接串的大小写要一致
-- primary change to force logging mode;
SQL> select log_mode,force_logging from v$database;
SQL> alter database force logging;
-- primary parameter
*.audit_file_dest='/u01/app/oracle/admin/prod2/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prod2/control01.ctl','/u01/app/oracle/oradata/prod2/control02.ctl'
*.db_block_size=8192
*.db_domain='com'
*.db_name='prod2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod2XDB)'
*.log_archive_dest_1='location=/u01/app/oracle/archive_log'
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.DB_UNIQUE_NAME='prod2'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod2,prod2sby)'
*.LOG_ARCHIVE_DEST_2='SERVICE=prod2sby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod2sby'
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod2/','/u01/app/oracle/oradata/prod2sby/'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod2/','/u01/app/oracle/oradata/prod2sby/'
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
*.FAL_CLIENT = 'prod2'
*.FAL_SERVER = 'prod2sby'
alter system set standby_file_management=auto;
-- primary tns
pc01sby1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = edbvr6p2.us.oracle.com)
(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pc01sby1.us.oracle.com)
)
)
-- standby parameter
*.audit_file_dest='/u01/app/oracle/admin/prod2/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prod2/control01.ctl','/u01/app/oracle/oradata/prod2/control02.ctl'
*.db_block_size=8192
*.db_domain='com'
*.db_name='prod2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod2XDB)'
*.log_archive_dest_1='location=/u01/app/oracle/archive__prod2_log'
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.DB_UNIQUE_NAME='prod2sby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod2,prod2sby)'
*.LOG_ARCHIVE_DEST_2='SERVICE=prod2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod2'
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod2/','/u01/app/oracle/oradata/prod2sby/'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod2/','/u01/app/oracle/oradata/prod2sby/'
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
*.FAL_CLIENT = 'prod2sby'
*.FAL_SERVER = 'prod2'
alter system set standby_file_management=auto;
-- standby listener UNKNOWN
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pc01sby1.us.oracle.com)
(OACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = pc01sby1)
)
)
-- standby listener READY
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = edbvr6p1.us.oracle.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
-- standby tns
pc01prmy =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = edbvr6p1.us.oracle.com)
(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pc01prmy.us.oracle.com)
)
)
-- 以上配置好后进行活动数据库复制
-- rman target sys/oracle@pc01prmy auxiliary sys/oracle@pc01sby1
rman target /
connect target sys/oracle
-- primary db
connect auxiliary sys/oracle@pc01sby1
-- standby db
run {
allocate channel prmy1 device type disk;
allocate channel prmy2 device type disk;
allocate channel prmy3 device type disk;
allocate channel prmy4 device type disk;
allocate auxiliary channel stby device type disk;
-- duplicate target database for standby;
duplicate target database for standby from active database;
-- duplicate target database for standby from active database nofilenamecheck;
}
rman target sys/oracle@pc01prmy auxiliary sys/oracle@pc01sby1
run{
allocate auxiliary channel aux1 device type disk;
allocate auxiliary channel aux2 device type disk;
duplicate target database to auxdb;
}
-- 在主备库都增加配置备用日志文件，如果主库有三组日志文件，则在备库创建四组日志，比主库多一组；如果是rac集群则比主库多两组。
alter database add standby logfile group 4 ('/u01/app/oracle/oradada/redo04.log') size 100M;
alter database add standby logfile group 5 ('/u01/app/oracle/oradada/redo05.log') size 100M;
alter database add standby logfile group 6 ('/u01/app/oracle/oradada/redo06.log') size 100M;
alter database add standby logfile group 7 ('/u01/app/oracle/oradada/redo07.log') size 100M;
-- alter database add standby logfile thread 1 '+REDO' size 100M; -- 默认thread 1 也就是第一节点
-- standby db 启用实时应用日志
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select process,status from v$managed_standby;
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> alter system switch logfile;
-- ORA-01152
SQL> alter database recover managed standby database disconnect from session;
SQL> select dest_name,status from v$archive_dest;
SQL> select * from v$archive_gap;
-- 把没有传到备库的日志拷贝过去并注册到备库
SQL> alter database register physical logfile '/u01/app/oracle/archive_log/1_6_928338773.dbf';
SQL> alter database recover managed standby database disconnect from session;
-- EM 创建ADG --------------------------------**********-------------------------------------------
-- broker 命令方式进入 broker 直接输入 DGMGRL
-- 注意静态监听GLOBAL_DBNAME的配置(db_unique_name_DGMGRL.do_main)，主备库添加如下配置
-- 主库
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod2_DGMGRL.com)
(OACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = prod2)
)
)
SQL> alter system set dg_broker_start=true; -- ps -ef|grep dmon
$ dgmgrl
connect sys/oracle@prod2
-- 创建主库配置
create configuration dgconfig1 as primary database is prod2 connect identifier is prod2;
-- 在主库增加备库配置
add database prod1 as connect identifier is prod1 maintained as physical;
-- 查看配置
show configuration verbose;
-- show configuration verbose prod2;
-- 启用配置
enable configuration;
-- 编辑配置
edit database prod2 set property LogXptMode='SYNC';
edit database prod1 set state='APPLY-OFF'; -- 不应用日志
edit database prod2 set state='TRANSPORT-OFF'; -- 不传输日志
-- 备库
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod1_DGMGRL.com)
(OACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = prod1)
)
)
remove database 'prod2' [PRESERVE DESTINATIONS];
remove configuration [PRESERVE DESTINATIONS];
-- 通过GC EM 创建物理备库 ---------------------------------------------------------
-- EM 创建broker configuration
-- EM 创建物理备库
-- add a database to a broker configuration
-- 考试主库 prod1 备库 sbdb1
-- EM 图形化界面创建备库方式省略
-- 最大保护模式
-- 修改备库为 SYNC 同步
edit database sbdb1 set property LogXptMode='SYNC';
edit dataase set protection mode as MaxAvailability; -- 考试改为最大可用模式
2.Configure the data guard environment to reduce overheads of fast incremental backups on the primary database
-- 主要配置块改变跟踪(使用备库做备份)
select * from v$block_change_tracking;
alter database enable block change tracking using file '/u01/.../block.trc'
3.Configure the Observer
4.Switchover and switch back
-- 主备切换，切换过后一定要切换回来
-- switchover failover
-- switchover
dgmgrl
connect sys/oracle@prod1
switchover to sbdb1;
-- failover
failover to sbdb1 [IMMEDIATE];
-- 重新恢复主库
REINSTARTE DATABASE prod1;
ENABLE DATABASE prod1;
5.Configure connect time failover
-- ADG 最后一题 快速启动failover -- 前面的题做好了我推荐不做这题，以免切换失败
-- Fast Failover
edit configuration set property FastStartFailoverLagLimit = {n};
edit configuration set property FastStartFailoverThreshold = threshold-val;
edit database prod1 set property LogXptMode=SYNC/ASYNC;
edit database sbdb1 set property LogXptMode=SYNC/ASYNC;
edit configuration set protection MODE AS Maxvailability/MaxPerformance; --
edit configuration set property FastStartFailoverTarget = sbdb1;
edit configuration set property FastStartFailoverTarget = prod1;
edit configuration set property FastStartFailoverAutoReinnstate = {TRUE|FALSE}
edit database prod1 set property ObserverConnectIdentifier = '';
enable FAST_DTART FailoverCondition "value" --
enable FAST_START FAILOVER;
START OBSERVER;
STOP OBSERVER;
show fast_start failover;
REINSTATE DATABASE prod1; -- restart init
-- 考试的时候在偶数机使用服务控制工具srvctl去创建一个服务也就是在备库创建
-- 注意sbdb1也需要加入到ASM里面去 . oraenv -> +ASM -> 添加数据库
srvctl add service -d <db_unique_name> -s <server_name> [-l PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY] [-y AUTOMATIC|MANUAL]
srvctl add service -d sbdb1 -s s0001 -l PHYSICAL_STANDBY -y AUTOMATIC;
srvctl start service -d sbdb1 -s s0001 -- 先把备库切换为主库启动服务，然后在切换回来服务就自动起来了
srvctl config service -d sbdb1 -s s0001
-- 服务起来过后去奇数机创建服务(也就是偶数机创建服务奇数机创建连接串)
netmgr -> db_prod -> gcprod2(连接到偶数机) -> s0001(服务名在偶数机上面查看lsnrctl status)
-- 如果没有勾选 Configure Standby Database with Oracle Restart 则在备库执行下面语句手动添加 （-a diskgroup_list）
srvctl add database -d sbdb1 -o $ORACLE_HOME -m gcprod2 -p $ORACLE_HOME/dbs/spfilesbdb1.ora -r PHYSICAL_STANDBY -n prod1 -a "SBDAT,SBFRA"
-- 注意考试的时候偶数机要作为主库
6.Convert the standby to a snapshot standby
-- 图形界面点下就可以（是备库而不是快照）
-- snapshot standby -- EM图形界面就可以帮助我们完成（是备库而不是快照）
-- 在物理备库的基础上把物理备库转换成快照备库，快照备库可以以读写模式打开做DML操作
-- 快照备库可以接受主库的日志但是不会应用，创建快照备库的前提是数据库开启闪回数据库
-- 创建快照备库前需要创建一个保证还原点，用完了要退回来取消快照备库到物理备库
-- 快照数据库不能做主备切换和failover操作，如果只有一个备库并且在最大保护模式也不能做快照备库
-- 手工转换
DGMGRL
connect sys/oracle@prod1
convert database sbdb1 to snapshot standby;
SQL> select flashback_on from v$database;
SQL> select name from v$restore_point;
SQL> select database_role from v$datbase;
convert database sbdb1 to physical standby;
-- EM 转换 点击下 convert 就可以
7.Configure archivelog deletion policy for the Dataguard configuration
-- 在备库配置删除归档策略，一定要备库应用过后才能删除
以上是考试之前在自己搭建的环境下练习记录的笔记，考试环境自己可以模仿搭建，建议多多练习，
本人考试感觉如果考试过程中出错时间可能不是很充裕哦。
