课程第33/34次（2018-08-26星期日上下午）

botang

Skillset 1
Section5：

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：

1. RMAN> backup tag 'Q107' database plus archivelog tag 'Q107' keep forever ;

复制代码

Skillset 2：

1. rman target sys/oracle@PROD1  auxiliary sys/oracle@SBDB1   cmdfile=sbdb1.rcv

复制代码

1. duplicate target database for standby
   
2. from active database
   
3. nofilenamecheck
   
4. spfile
   
5. set control_files='/u01/app/oracle/oradata/SBDB1/control01.ctl','/u01/app/oracle/oradata/SBDB1/control02.ctl','/u01/app/oracle/oradata/SBDB1/control03.ctl'
   
6. set db_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/SBDB1'
   
7. set log_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/SBDB1'
   
8. set   db_unique_name='SBDB1'
   
9. set fal_server='PROD1'
   
10. set  fal_client='SBDB1'
    
11. set  log_archive_config='dg_config=(PROD1,SBDB1)'
    
12. set  log_archive_dest_1='location=use_db_recovery_file_dest'
    
13. set  log_archive_dest_2='service=PROD1  valid_for=(online_logfiles, primary_role)   db_unique_name=PROD1'
    
14. set  audit_file_dest='/u01/app/oracle/admin/SBDB1/adump';

复制代码

备库监听器：

1. # listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
   
2. # Generated by Oracle configuration tools.
   
3. 
   
4. SID_LIST_LISTENER =
   
5.   (SID_LIST =
   
6.     (SID_DESC =
   
7.       (GLOBAL_DBNAME = SBDB1)
   
8.       (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
   
9.       (SID_NAME = SBDB1)
   
10.     )
    
11.     (SID_DESC =
    
12.       (GLOBAL_DBNAME = SBDB1_DGMGRL)
    
13.       (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
    
14.       (SID_NAME = SBDB1)
    
15.     )
    
16.   )
    
17. 
    
18. 
    
19. 
    
20. 
    
21. LISTENER =
    
22.   (DESCRIPTION_LIST =
    
23.     (DESCRIPTION =
    
24.       (ADDRESS = (PROTOCOL = TCP)(HOST = station37.example.com)(PORT = 1521))
    
25.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    
26.     )
    
27.   )
    

复制代码

主库监听器：

1. # listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
   
2. # Generated by Oracle configuration tools.
   
3. 
   
4. SID_LIST_LISTENER =
   
5.   (SID_LIST =
   
6.     (SID_DESC =
   
7.       (GLOBAL_DBNAME = PROD1)
   
8.       (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
   
9.       (SID_NAME = PROD1)
   
10.     )
    
11.     (SID_DESC =
    
12.       (GLOBAL_DBNAME = PROD1_DGMGRL)
    
13.       (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
    
14.       (SID_NAME = PROD1)
    
15.     )
    
16.   )
    
17. 
    
18. LISTENER =
    
19.   (DESCRIPTION_LIST =
    
20.     (DESCRIPTION =
    
21.       (ADDRESS = (PROTOCOL = TCP)(HOST = station38.example.com)(PORT = 1521))
    
22.     )
    
23.     (DESCRIPTION =
    
24.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    
25.     )
    
26.   )
    

复制代码

打开ADG：

1. [oracle@station37 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 10.1.0.5.0 - Production on Sat Aug 25 03:41:53 2018
   
4. 
   
5. Copyright (c) 1982, 2005, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> select  open_mode from v$database;
   
10. 
    
11. OPEN_MODE
    
12. ----------
    
13. MOUNTED
    
14. 
    
15. SQL> alter database open read only ;
    
16. alter database open read only
    
17. *
    
18. ERROR at line 1:
    
19. ORA-10456: cannot open standby database; media recovery session may be in
    
20. progress
    
21. 
    
22. 
    
23. SQL> alter database recover managed standby database cancel ;
    
24. 
    
25. Database altered.
    
26. 
    
27. SQL> alter database open read only ;
    
28. 
    
29. Database altered.
    
30. 
    
31. SQL> alter database recover managed standby database using current logfile disconnect from session ;
    
32. 
    
33. Database altered.
    
34. 
    
35. 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

1. 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;

复制代码

1. select  * from dba_mviews  m
   
2. where m.OWNER='SH' and m.MVIEW_NAME='PROD_MV';
   

复制代码

[oracle@station38 admin]$ 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>

1. begin
   
2.   dbms_mview.explain_mview(mv => 'SH.PROD_MV');
   
3. end;
   
4. 
   
5. commit;
   
6. 
   
7. select  * from  MV_CAPABILITIES_TABLE;

复制代码

	STATEMENT_ID	MVOWNER	MVNAME	CAPABILITY_NAME	POSSIBLE	RELATED_TEXT	RELATED_NUM	MSGNO	MSGTXT	SEQ
1		SH	PROD_MV	PCT	N					1
2		SH	PROD_MV	REFRESH_COMPLETE	Y					1002
3		SH	PROD_MV	REFRESH_FAST	N					2003
4		SH	PROD_MV	REWRITE	N					3004
5		SH	PROD_MV	PCT_TABLE	N	PRODUCTS	93	2068	relation is not a partitioned table	4005
6		SH	PROD_MV	REFRESH_FAST_AFTER_INSERT	N	SH.PRODUCTS		2162	the detail table does not have a materialized view log	5006
7		SH	PROD_MV	REFRESH_FAST_AFTER_ONETAB_DML	N	M1	7	2143	SUM(expr) without COUNT(expr)	6007
8		SH	PROD_MV	REFRESH_FAST_AFTER_ONETAB_DML	N			2146	see the reason why REFRESH_FAST_AFTER_INSERT is disabled	6008
9		SH	PROD_MV	REFRESH_FAST_AFTER_ONETAB_DML	N			2142	COUNT(*) is not present in the select list	6009
10		SH	PROD_MV	REFRESH_FAST_AFTER_ONETAB_DML	N			2143	SUM(expr) without COUNT(expr)	6010
11		SH	PROD_MV	REFRESH_FAST_AFTER_ANY_DML	N			2161	see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled	7011
12		SH	PROD_MV	REFRESH_FAST_PCT	N			2157	PCT is not possible on any of the detail tables in the materialized view	8012
13		SH	PROD_MV	REWRITE_FULL_TEXT_MATCH	N			2159	query rewrite is disabled on the materialized view	9013
14		SH	PROD_MV	REWRITE_PARTIAL_TEXT_MATCH	N			2159	query rewrite is disabled on the materialized view	10014
15		SH	PROD_MV	REWRITE_GENERAL	N			2159	query rewrite is disabled on the materialized view	11015
16		SH	PROD_MV	REWRITE_PCT	N			2158	general rewrite is not possible or PCT is not possible on any of the detail tables	12016
17		SH	PROD_MV	PCT_TABLE_REWRITE	N	PRODUCTS	93	2068	relation is not a partitioned table	13017

----

1. SQL> set pagesize 10000
   
2. SQL> set long 10000
   
3. SQL> select  dbms_metadata.get_ddl('MATERIALIZED_VIEW','PROD_MV','SH')    from dual;
   
4. 
   
5. DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','PROD_MV','SH')
   
6. --------------------------------------------------------------------------------
   
7. 
   
8.   CREATE MATERIALIZED VIEW "SH"."PROD_MV" ("M1", "M2", "PROD_CATEGORY")
   
9.   ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
   
10. GING
    
11.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    
12.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    
13.   TABLESPACE "USERS"
    
14.   BUILD IMMEDIATE
    
15.   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
    
16.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    
17.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    
18.   TABLESPACE "USERS"
    
19.   REFRESH FORCE ON DEMAND
    
20.   USING DEFAULT LOCAL ROLLBACK SEGMENT
    
21.   USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
    
22.   AS SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, p
    
23. rod_category FROM products GROUP BY prod_category
    

复制代码

把以上的输出改成：

1. 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:

1. create directory dir3_1  as '/home/oracle/dir3_1';
   
2. 
   
3. 
   
4. declare
   
5.   v_task varchar2(200);
   
6. begin
   
7.   dbms_advisor.tune_mview(
   
8.   v_task,
   
9.   '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');
   
10.   dbms_advisor.create_file (
    
11.     dbms_advisor.get_task_script(v_task),
    
12.    'DIR3_1',
    
13.    'mvtune_script.sql');
    
14. end;
    
15. /
    

复制代码

1. [oracle@station38 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 25 09:16:47 2018
   
4. 
   
5. Copyright (c) 1982, 2008, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> @3_1.sql
   
10. 
    
11. Directory created.
    
12. 
    
13. 
    
14. PL/SQL procedure successfully completed.
    
15. 
    
16. SQL>
    

复制代码

在目录对象里找到mvtune_script.sql, 去掉其中的alter段落，删除原有prod_mv，执行mvtune_script.sql：

1.     truncate  table MV_CAPABILITIES_TABLE;
   
2.    
   
3.     begin
   
4.       dbms_mview.explain_mview(mv => 'SH.PROD_MV');
   
5.     end;
   
6. 
   
7.     commit;
   
8. 
   
9.     select  * from  MV_CAPABILITIES_TABLE;

复制代码

	STATEMENT_ID	MVOWNER	MVNAME	CAPABILITY_NAME	POSSIBLE	RELATED_TEXT	RELATED_NUM	MSGNO	MSGTXT	SEQ
1		SH	PROD_MV	PCT	N					1
2		SH	PROD_MV	REFRESH_COMPLETE	Y					1002
3		SH	PROD_MV	REFRESH_FAST	Y					2003
4		SH	PROD_MV	REWRITE	Y					3004
5		SH	PROD_MV	PCT_TABLE	N	SH.PRODUCTS	272	2068	relation is not a partitioned table	4005
6		SH	PROD_MV	REFRESH_FAST_AFTER_INSERT	Y					5006
7		SH	PROD_MV	REFRESH_FAST_AFTER_ONETAB_DML	Y					6007
8		SH	PROD_MV	REFRESH_FAST_AFTER_ANY_DML	Y					7008
9		SH	PROD_MV	REFRESH_FAST_PCT	N			2157	PCT is not possible on any of the detail tables in the materialized view	8009
10		SH	PROD_MV	REWRITE_FULL_TEXT_MATCH	Y					9010
11		SH	PROD_MV	REWRITE_PARTIAL_TEXT_MATCH	Y					10011
12		SH	PROD_MV	REWRITE_GENERAL	Y					11012
13		SH	PROD_MV	REWRITE_PCT	N			2158	general rewrite is not possible or PCT is not possible on any of the detail tables	12013
14		SH	PROD_MV	PCT_TABLE_REWRITE	N	SH.PRODUCTS	272	2068	relation is not a partitioned table	13014

能否进行快速刷新：

1. begin
   
2.   dbms_mview.refresh(list => 'SH.PROD_MV',method => 'F');
   
3. end;

复制代码

能否进行查询重写：

1. SQL>  SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, prod_category FROM products GROUP BY prod_category;
   
2. 
   
3. 
   
4. Execution Plan
   
5. ----------------------------------------------------------
   
6. Plan hash value: 3752038752
   
7. 
   
8. ----------------------------------------------------------------------------------------
   
9. | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
   
10. ----------------------------------------------------------------------------------------
    
11. |   0 | SELECT STATEMENT             |               |     5 |   265 |     3         (0)| 00:00:01 |
    
12. |   1 |  MAT_VIEW REWRITE ACCESS FULL| PROD_MV |     5 |   265 |     3         (0)| 00:00:01 |
    
13. ----------------------------------------------------------------------------------------
    
14. 
    
15. Note
    
16. -----
    
17.    - dynamic sampling used for this statement
    
18. 
    
19. 
    
20. Statistics
    
21. ----------------------------------------------------------
    
22.        1633  recursive calls
    
23.           0  db block gets
    
24.         323  consistent gets
    
25.           1  physical reads
    
26.           0  redo size
    
27.         662  bytes sent via SQL*Net to client
    
28.         420  bytes received via SQL*Net from client
    
29.           2  SQL*Net roundtrips to/from client
    
30.           8  sorts (memory)
    
31.           0  sorts (disk)
    
32.           5  rows processed
    
33. 
    
34. SQL>
    

复制代码

XTTS准备考题时是这样：

1. [oracle@station38 ~]$ expdp system/oracle1  directory=dir1 dumpfile=trans3_2.dmp  TRANSPORT_TABLESPACES=trpdata
   
2. 
   
3. Export: Release 11.1.0.7.0 - Production on Saturday, 25 August, 2018 9:47:25
   
4. 
   
5. Copyright (c) 2003, 2007, Oracle.  All rights reserved.
   
6. 
   
7. UDE-28002: operation generated ORACLE error 28002
   
8. ORA-28002: the password will expire within 7 days
   
9. 
   
10. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    
11. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
12. Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=dir1 dumpfile=trans3_2.dmp TRANSPORT_TABLESPACES=trpdata
    
13. Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    
14. Processing object type TRANSPORTABLE_EXPORT/TABLE
    
15. Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
    
16. Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
    
17. ******************************************************************************
    
18. Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
    
19.   /home/oracle/scripts/trans3_2.dmp
    
20. ******************************************************************************
    
21. Datafiles required for transportable tablespace TRPDATA:
    
22.   /u01/app/oracle/oradata/EMREP/TRPDATA_6
    
23. Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 09:47:39
    
24. 
    
25. [oracle@station38 ~]$ sqlplus /nolog
    
26. 
    
27. SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 25 09:47:49 2018
    
28. 
    
29. Copyright (c) 1982, 2008, Oracle.  All rights reserved.
    
30. 
    
31. SQL> conn / as sysdba
    
32. Connected.
    
33. SQL> desc v$transportable_platform
    
34. Name                                           Null?    Type
    
35. ----------------------------------------- -------- ----------------------------
    
36. PLATFORM_ID                                            NUMBER
    
37. PLATFORM_NAME                                            VARCHAR2(101)
    
38. ENDIAN_FORMAT                                            VARCHAR2(14)
    
39. 
    
40. SQL> select PLATFORM_NAME from v$transportable_platform;
    
41. 
    
42. PLATFORM_NAME
    
43. --------------------------------------------------------------------------------
    
44. Solaris[tm] OE (32-bit)
    
45. Solaris[tm] OE (64-bit)
    
46. Microsoft Windows IA (32-bit)
    
47. Linux IA (32-bit)
    
48. AIX-Based Systems (64-bit)
    
49. HP-UX (64-bit)
    
50. HP Tru64 UNIX
    
51. HP-UX IA (64-bit)
    
52. Linux IA (64-bit)
    
53. HP Open VMS
    
54. Microsoft Windows IA (64-bit)
    
55. 
    
56. PLATFORM_NAME
    
57. --------------------------------------------------------------------------------
    
58. IBM zSeries Based Linux
    
59. Linux x86 64-bit
    
60. Apple Mac OS
    
61. Microsoft Windows x86 64-bit
    
62. Solaris Operating System (x86)
    
63. IBM Power Based Linux
    
64. HP IA Open VMS
    
65. Solaris Operating System (x86-64)
    
66. 
    
67. 19 rows selected.
    
68. 
    
69. SQL> exit
    
70. Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    
71. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
72. [oracle@station38 ~]$ rman target /
    
73. 
    
74. Recovery Manager: Release 11.1.0.7.0 - Production on Sat Aug 25 09:48:24 2018
    
75. 
    
76. Copyright (c) 1982, 2007, Oracle.  All rights reserved.
    
77. 
    
78. connected to target database: EMREP (DBID=4076569368)
    
79. 
    
80. 
    
81. RMAN> convert tablespace  trpdata to platform 'Solaris[tm] OE (64-bit)' format '/home/oracle/TRPDATA_6';
    
82. 
    
83. Starting conversion at source at 25-AUG-18
    
84. using target database control file instead of recovery catalog
    
85. allocated channel: ORA_DISK_1
    
86. channel ORA_DISK_1: SID=1610 device type=DISK
    
87. channel ORA_DISK_1: starting datafile conversion
    
88. input datafile file number=00008 name=/u01/app/oracle/oradata/EMREP/TRPDATA_6
    
89. converted datafile=/home/oracle/TRPDATA_6
    
90. channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
    
91. Finished conversion at source at 25-AUG-18
    
92. 
    
93. RMAN>
    

复制代码