设为首页收藏本站

Botang唐波's Oracle Station

查看: 166|回复: 0

课程第33/34次(2018-08-26上下 午)

[复制链接]

744

主题

1117

帖子

8077

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8077
发表于 2018-8-26 09:56:15 | 显示全部楼层 |阅读模式
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. SID_LIST_LISTENER =
  4.   (SID_LIST =
  5.     (SID_DESC =
  6.       (GLOBAL_DBNAME = SBDB1)
  7.       (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
  8.       (SID_NAME = SBDB1)
  9.     )
  10.     (SID_DESC =
  11.       (GLOBAL_DBNAME = SBDB1_DGMGRL)
  12.       (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
  13.       (SID_NAME = SBDB1)
  14.     )
  15.   )




  16. LISTENER =
  17.   (DESCRIPTION_LIST =
  18.     (DESCRIPTION =
  19.       (ADDRESS = (PROTOCOL = TCP)(HOST = station37.example.com)(PORT = 1521))
  20.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  21.     )
  22.   )
复制代码

主库监听器:
  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. SID_LIST_LISTENER =
  4.   (SID_LIST =
  5.     (SID_DESC =
  6.       (GLOBAL_DBNAME = PROD1)
  7.       (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
  8.       (SID_NAME = PROD1)
  9.     )
  10.     (SID_DESC =
  11.       (GLOBAL_DBNAME = PROD1_DGMGRL)
  12.       (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
  13.       (SID_NAME = PROD1)
  14.     )
  15.   )

  16. LISTENER =
  17.   (DESCRIPTION_LIST =
  18.     (DESCRIPTION =
  19.       (ADDRESS = (PROTOCOL = TCP)(HOST = station38.example.com)(PORT = 1521))
  20.     )
  21.     (DESCRIPTION =
  22.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  23.     )
  24.   )
复制代码

打开ADG:
  1. [oracle@station37 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 10.1.0.5.0 - Production on Sat Aug 25 03:41:53 2018

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

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> select  open_mode from v$database;

  7. OPEN_MODE
  8. ----------
  9. MOUNTED

  10. SQL> alter database open read only ;
  11. alter database open read only
  12. *
  13. ERROR at line 1:
  14. ORA-10456: cannot open standby database; media recovery session may be in
  15. progress


  16. SQL> alter database recover managed standby database cancel ;

  17. Database altered.

  18. SQL> alter database open read only ;

  19. Database altered.

  20. SQL> alter database recover managed standby database using current logfile disconnect from session ;

  21. Database altered.

  22. 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. commit;

  5. select  * from  MV_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

----
  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. DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','PROD_MV','SH')
  5. --------------------------------------------------------------------------------

  6.   CREATE MATERIALIZED VIEW "SH"."PROD_MV" ("M1", "M2", "PROD_CATEGORY")
  7.   ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
  8. GING
  9.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  10.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  11.   TABLESPACE "USERS"
  12.   BUILD IMMEDIATE
  13.   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  14.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  15.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  16.   TABLESPACE "USERS"
  17.   REFRESH FORCE ON DEMAND
  18.   USING DEFAULT LOCAL ROLLBACK SEGMENT
  19.   USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  20.   AS SELECT SUM(prod_list_price - prod_min_price) M1, COUNT(prod_category) M2, p
  21. 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. declare
  3.   v_task varchar2(200);
  4. begin
  5.   dbms_advisor.tune_mview(
  6.   v_task,
  7.   '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');
  8.   dbms_advisor.create_file (
  9.     dbms_advisor.get_task_script(v_task),
  10.    'DIR3_1',
  11.    'mvtune_script.sql');
  12. end;
  13. /
复制代码
  1. [oracle@station38 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 25 09:16:47 2018

  3. Copyright (c) 1982, 2008, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> @3_1.sql

  7. Directory created.


  8. PL/SQL procedure successfully completed.

  9. 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.     commit;

  7.     select  * from  MV_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

能否进行快速刷新:
  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. Execution Plan
  3. ----------------------------------------------------------
  4. Plan hash value: 3752038752

  5. ----------------------------------------------------------------------------------------
  6. | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
  7. ----------------------------------------------------------------------------------------
  8. |   0 | SELECT STATEMENT             |               |     5 |   265 |     3         (0)| 00:00:01 |
  9. |   1 |  MAT_VIEW REWRITE ACCESS FULL| PROD_MV |     5 |   265 |     3         (0)| 00:00:01 |
  10. ----------------------------------------------------------------------------------------

  11. Note
  12. -----
  13.    - dynamic sampling used for this statement


  14. Statistics
  15. ----------------------------------------------------------
  16.        1633  recursive calls
  17.           0  db block gets
  18.         323  consistent gets
  19.           1  physical reads
  20.           0  redo size
  21.         662  bytes sent via SQL*Net to client
  22.         420  bytes received via SQL*Net from client
  23.           2  SQL*Net roundtrips to/from client
  24.           8  sorts (memory)
  25.           0  sorts (disk)
  26.           5  rows processed

  27. SQL>
复制代码


XTTS准备考题时是这样:
  1. [oracle@station38 ~]$ expdp system/oracle1  directory=dir1 dumpfile=trans3_2.dmp  TRANSPORT_TABLESPACES=trpdata

  2. Export: Release 11.1.0.7.0 - Production on Saturday, 25 August, 2018 9:47:25

  3. Copyright (c) 2003, 2007, Oracle.  All rights reserved.

  4. UDE-28002: operation generated ORACLE error 28002
  5. ORA-28002: the password will expire within 7 days

  6. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
  7. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  8. Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=dir1 dumpfile=trans3_2.dmp TRANSPORT_TABLESPACES=trpdata
  9. Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
  10. Processing object type TRANSPORTABLE_EXPORT/TABLE
  11. Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
  12. Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
  13. ******************************************************************************
  14. Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  15.   /home/oracle/scripts/trans3_2.dmp
  16. ******************************************************************************
  17. Datafiles required for transportable tablespace TRPDATA:
  18.   /u01/app/oracle/oradata/EMREP/TRPDATA_6
  19. Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 09:47:39

  20. [oracle@station38 ~]$ sqlplus /nolog

  21. SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 25 09:47:49 2018

  22. Copyright (c) 1982, 2008, Oracle.  All rights reserved.

  23. SQL> conn / as sysdba
  24. Connected.
  25. SQL> desc v$transportable_platform
  26. Name                                           Null?    Type
  27. ----------------------------------------- -------- ----------------------------
  28. PLATFORM_ID                                            NUMBER
  29. PLATFORM_NAME                                            VARCHAR2(101)
  30. ENDIAN_FORMAT                                            VARCHAR2(14)

  31. SQL> select PLATFORM_NAME from v$transportable_platform;

  32. PLATFORM_NAME
  33. --------------------------------------------------------------------------------
  34. Solaris[tm] OE (32-bit)
  35. Solaris[tm] OE (64-bit)
  36. Microsoft Windows IA (32-bit)
  37. Linux IA (32-bit)
  38. AIX-Based Systems (64-bit)
  39. HP-UX (64-bit)
  40. HP Tru64 UNIX
  41. HP-UX IA (64-bit)
  42. Linux IA (64-bit)
  43. HP Open VMS
  44. Microsoft Windows IA (64-bit)

  45. PLATFORM_NAME
  46. --------------------------------------------------------------------------------
  47. IBM zSeries Based Linux
  48. Linux x86 64-bit
  49. Apple Mac OS
  50. Microsoft Windows x86 64-bit
  51. Solaris Operating System (x86)
  52. IBM Power Based Linux
  53. HP IA Open VMS
  54. Solaris Operating System (x86-64)

  55. 19 rows selected.

  56. SQL> exit
  57. Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
  58. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  59. [oracle@station38 ~]$ rman target /

  60. Recovery Manager: Release 11.1.0.7.0 - Production on Sat Aug 25 09:48:24 2018

  61. Copyright (c) 1982, 2007, Oracle.  All rights reserved.

  62. connected to target database: EMREP (DBID=4076569368)


  63. RMAN> convert tablespace  trpdata to platform 'Solaris[tm] OE (64-bit)' format '/home/oracle/TRPDATA_6';

  64. Starting conversion at source at 25-AUG-18
  65. using target database control file instead of recovery catalog
  66. allocated channel: ORA_DISK_1
  67. channel ORA_DISK_1: SID=1610 device type=DISK
  68. channel ORA_DISK_1: starting datafile conversion
  69. input datafile file number=00008 name=/u01/app/oracle/oradata/EMREP/TRPDATA_6
  70. converted datafile=/home/oracle/TRPDATA_6
  71. channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
  72. Finished conversion at source at 25-AUG-18

  73. RMAN>
复制代码












回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-11-18 01:14 , Processed in 0.179747 second(s), 24 queries .

快速回复 返回顶部 返回列表