课程第33/34次（2018-08-26星期日上下午）
Skillset 1Section5：
11.1配置DNFS：
find this file in directory
(1)$ORACLE_HOME/dbs/oranfstab
(2)/etc/oranfstab
(3)/etc/mtab
cd $ORACLE_HOME/dbs
vi oranfstab
server: MyDataServer1
path: 132.34.35.12
export: /vol/oradata mount: /u02/oradata/prod1
cd $ORACLE_HOME/lib
mv libodm11.so libodm11.so_stub
ln -s libnfsodm11.so libodm11.so
SQL>create tablespace nfs_tb1 datafile '/nfs_df/nfs_tb101.dbf' size 10m;
警告日志出现如下错误:
Direct NFS: please check that oradism is setuid
解决办法:
1、先把 oradism 文件修改为 root 属主:
chown root /u01/app/oracle/product/11.1.0/db_1/bin/oradism
2、再把这个文件加上 setuid 的权限:
chmod u+s /u01/app/oracle/product/11.1.0/db_1/bin/oradism
11.2配置DNFS：
• 1、进入以下路径:
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/
• 2、重新编译文件:
make -f ins_rdbms.mk dnfs_on
Section 6: Applying a Patch
export PATH=$ORACLE_HOME/OPatch
cd /home/oracle/scripts
unzip p8342329_111070_linux-x86.zip
cd 8342329
$ORACLE_HOME/OPatch/opatch query -is_online_patch `pwd`
--prod1/prod2 should be shutdown
$ORACLE_HOME/OPatch/opatch apply
$ORACLE_HOME/OPatch/opatch lsinventory
Section9：
RMAN> backup tag 'Q107' database plus archivelog tag 'Q107' keep forever ;
Skillset 2：
rman target sys/oracle@PROD1auxiliary sys/oracle@SBDB1 cmdfile=sbdb1.rcv
duplicate target database for standby
from active database
nofilenamecheck
spfile
set control_files='/u01/app/oracle/oradata/SBDB1/control01.ctl','/u01/app/oracle/oradata/SBDB1/control02.ctl','/u01/app/oracle/oradata/SBDB1/control03.ctl'
set db_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/SBDB1'
set log_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/SBDB1'
set db_unique_name='SBDB1'
set fal_server='PROD1'
setfal_client='SBDB1'
setlog_archive_config='dg_config=(PROD1,SBDB1)'
setlog_archive_dest_1='location=use_db_recovery_file_dest'
setlog_archive_dest_2='service=PROD1valid_for=(online_logfiles, primary_role) db_unique_name=PROD1'
setaudit_file_dest='/u01/app/oracle/admin/SBDB1/adump';备库监听器：
# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SBDB1)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = SBDB1)
)
(SID_DESC =
(GLOBAL_DBNAME = SBDB1_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = SBDB1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = station37.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
主库监听器：
# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD1)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = PROD1)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD1_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = PROD1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = station38.example.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
打开ADG：
$ sqlplus /nolog
SQL*Plus: Release 10.1.0.5.0 - Production on Sat Aug 25 03:41:53 2018
Copyright (c) 1982, 2005, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> selectopen_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter database open read only ;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress
SQL> alter database recover managed standby database cancel ;
Database altered.
SQL> alter database open read only ;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session ;
Database altered.
SQL>
----create dataguard broker
----make static register in listener.ora, global name must be PROD1_DGMGRL and SBDB1_DGMGRL/ make tnsnames.ora connect PROD1_DGMGRL and SBDB1_DGMGRL
conn sys/oracle@prod1 as sysdba
alter system set dg_broker_start=true;
conn sys/oracle@sbdb1 as sysdba
alter system set dg_broker_start=true;
dgmgrl sys/oracle@prod1
CREATE CONFIGURATION 'dgconfig' AS PRIMARY DATABASE IS PROD1 CONNECT IDENTIFIER IS PROD1;
add database SBDB1 as connect identifier is sbdb1 MAINTAINED AS physical;
ENABLE CONFIGURATION;
Section 2: Testing the Standby Database
--convert database from physical standby to snapshot standby
convert database SBDB1 to snapshot standby;
Section 3: Restoring the Standby Database
--convert database from snapshot standby to phyical standby
convert database SBDB1 to physical standby;
--active dataguard
edit database SBDB1 set state='apply-off';
alter database open;
edit database SBDB1 set state='apply-on';
Skillset 3: Data and Data Warehouse Management
CREATE MATERIALIZED VIEW sh.prod_mv AS SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, prod_category FROM products GROUP BY prod_category;
select* from dba_mviewsm
where m.OWNER='SH' and m.MVIEW_NAME='PROD_MV';
$ sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 25 08:55:36 2018
Copyright (c) 1982, 2008, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlxmv.sql
Table created.
SQL>
begin
dbms_mview.explain_mview(mv => 'SH.PROD_MV');
end;
commit;
select* fromMV_CAPABILITIES_TABLE;
STATEMENT_IDMVOWNERMVNAMECAPABILITY_NAMEPOSSIBLERELATED_TEXTRELATED_NUMMSGNOMSGTXTSEQ
1 SHPROD_MVPCTN 1
2 SHPROD_MVREFRESH_COMPLETEY 1002
3 SHPROD_MVREFRESH_FASTN 2003
4 SHPROD_MVREWRITEN 3004
5 SHPROD_MVPCT_TABLENPRODUCTS932068relation is not a partitioned table4005
6 SHPROD_MVREFRESH_FAST_AFTER_INSERTNSH.PRODUCTS 2162the detail table does not have a materialized view log5006
7 SHPROD_MVREFRESH_FAST_AFTER_ONETAB_DMLNM172143SUM(expr) without COUNT(expr)6007
8 SHPROD_MVREFRESH_FAST_AFTER_ONETAB_DMLN 2146see the reason why REFRESH_FAST_AFTER_INSERT is disabled6008
9 SHPROD_MVREFRESH_FAST_AFTER_ONETAB_DMLN 2142COUNT(*) is not present in the select list6009
10 SHPROD_MVREFRESH_FAST_AFTER_ONETAB_DMLN 2143SUM(expr) without COUNT(expr)6010
11 SHPROD_MVREFRESH_FAST_AFTER_ANY_DMLN 2161see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled7011
12 SHPROD_MVREFRESH_FAST_PCTN 2157PCT is not possible on any of the detail tables in the materialized view8012
13 SHPROD_MVREWRITE_FULL_TEXT_MATCHN 2159query rewrite is disabled on the materialized view9013
14 SHPROD_MVREWRITE_PARTIAL_TEXT_MATCHN 2159query rewrite is disabled on the materialized view10014
15 SHPROD_MVREWRITE_GENERALN 2159query rewrite is disabled on the materialized view11015
16 SHPROD_MVREWRITE_PCTN 2158general rewrite is not possible or PCT is not possible on any of the detail tables12016
17 SHPROD_MVPCT_TABLE_REWRITENPRODUCTS932068relation is not a partitioned table13017
----
SQL> set pagesize 10000
SQL> set long 10000
SQL> selectdbms_metadata.get_ddl('MATERIALIZED_VIEW','PROD_MV','SH') from dual;
DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','PROD_MV','SH')
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW "SH"."PROD_MV" ("M1", "M2", "PROD_CATEGORY")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, p
rod_category FROM products GROUP BY prod_category
把以上的输出改成：
CREATE MATERIALIZED VIEW SH.PROD_MV REFRESH Fast enable QUERY REWRITE AS SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, prod_category FROM products GROUP BY prod_category
tuning脚本3_1.sql:
create directory dir3_1as '/home/oracle/dir3_1';
declare
v_task varchar2(200);
begin
dbms_advisor.tune_mview(
v_task,
'CREATE MATERIALIZED VIEW SH.PROD_MV REFRESH Fast enable QUERY REWRITE AS SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, prod_category FROM sh.products GROUP BY prod_category');
dbms_advisor.create_file (
dbms_advisor.get_task_script(v_task),
'DIR3_1',
'mvtune_script.sql');
end;
/
$ sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 25 09:16:47 2018
Copyright (c) 1982, 2008, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @3_1.sql
Directory created.
PL/SQL procedure successfully completed.
SQL>
在目录对象里找到mvtune_script.sql, 去掉其中的alter段落，删除原有prod_mv，执行mvtune_script.sql：
truncatetable MV_CAPABILITIES_TABLE;
begin
dbms_mview.explain_mview(mv => 'SH.PROD_MV');
end;
commit;
select* fromMV_CAPABILITIES_TABLE;
STATEMENT_IDMVOWNERMVNAMECAPABILITY_NAMEPOSSIBLERELATED_TEXTRELATED_NUMMSGNOMSGTXTSEQ
1 SHPROD_MVPCTN 1
2 SHPROD_MVREFRESH_COMPLETEY 1002
3 SHPROD_MVREFRESH_FASTY 2003
4 SHPROD_MVREWRITEY 3004
5 SHPROD_MVPCT_TABLENSH.PRODUCTS2722068relation is not a partitioned table4005
6 SHPROD_MVREFRESH_FAST_AFTER_INSERTY 5006
7 SHPROD_MVREFRESH_FAST_AFTER_ONETAB_DMLY 6007
8 SHPROD_MVREFRESH_FAST_AFTER_ANY_DMLY 7008
9 SHPROD_MVREFRESH_FAST_PCTN 2157PCT is not possible on any of the detail tables in the materialized view8009
10 SHPROD_MVREWRITE_FULL_TEXT_MATCHY 9010
11 SHPROD_MVREWRITE_PARTIAL_TEXT_MATCHY 10011
12 SHPROD_MVREWRITE_GENERALY 11012
13 SHPROD_MVREWRITE_PCTN 2158general rewrite is not possible or PCT is not possible on any of the detail tables12013
14 SHPROD_MVPCT_TABLE_REWRITENSH.PRODUCTS2722068relation is not a partitioned table13014
能否进行快速刷新：
begin
dbms_mview.refresh(list => 'SH.PROD_MV',method => 'F');
end;
能否进行查询重写：
SQL>SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, prod_category FROM products GROUP BY prod_category;
Execution Plan
----------------------------------------------------------
Plan hash value: 3752038752
----------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 265 | 3 (0)| 00:00:01 |
| 1 |MAT_VIEW REWRITE ACCESS FULL| PROD_MV | 5 | 265 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
1633recursive calls
0db block gets
323consistent gets
1physical reads
0redo size
662bytes sent via SQL*Net to client
420bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
8sorts (memory)
0sorts (disk)
5rows processed
SQL>
XTTS准备考题时是这样：
$ expdp system/oracle1directory=dir1 dumpfile=trans3_2.dmpTRANSPORT_TABLESPACES=trpdata
Export: Release 11.1.0.7.0 - Production on Saturday, 25 August, 2018 9:47:25
Copyright (c) 2003, 2007, Oracle.All rights reserved.
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":system/******** directory=dir1 dumpfile=trans3_2.dmp TRANSPORT_TABLESPACES=trpdata
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/scripts/trans3_2.dmp
******************************************************************************
Datafiles required for transportable tablespace TRPDATA:
/u01/app/oracle/oradata/EMREP/TRPDATA_6
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 09:47:39
$ sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 25 09:47:49 2018
Copyright (c) 1982, 2008, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> desc v$transportable_platform
Name Null? Type
----------------------------------------- -------- ----------------------------
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
ENDIAN_FORMAT VARCHAR2(14)
SQL> select PLATFORM_NAME from v$transportable_platform;
PLATFORM_NAME
--------------------------------------------------------------------------------
Solaris OE (32-bit)
Solaris OE (64-bit)
Microsoft Windows IA (32-bit)
Linux IA (32-bit)
AIX-Based Systems (64-bit)
HP-UX (64-bit)
HP Tru64 UNIX
HP-UX IA (64-bit)
Linux IA (64-bit)
HP Open VMS
Microsoft Windows IA (64-bit)
PLATFORM_NAME
--------------------------------------------------------------------------------
IBM zSeries Based Linux
Linux x86 64-bit
Apple Mac OS
Microsoft Windows x86 64-bit
Solaris Operating System (x86)
IBM Power Based Linux
HP IA Open VMS
Solaris Operating System (x86-64)
19 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ rman target /
Recovery Manager: Release 11.1.0.7.0 - Production on Sat Aug 25 09:48:24 2018
Copyright (c) 1982, 2007, Oracle.All rights reserved.
connected to target database: EMREP (DBID=4076569368)
RMAN> convert tablespacetrpdata to platform 'Solaris OE (64-bit)' format '/home/oracle/TRPDATA_6';
Starting conversion at source at 25-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1610 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00008 name=/u01/app/oracle/oradata/EMREP/TRPDATA_6
converted datafile=/home/oracle/TRPDATA_6
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at source at 25-AUG-18
RMAN>
页:
[1]