Bo's Oracle Station

查看: 3599|回复: 0

第93/94次:2016-03-27星期日

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2016-3-28 09:54:29 | 显示全部楼层 |阅读模式
本帖最后由 botang 于 2016-5-22 16:17 编辑
  1. select  * from dict
  2. where table_name like '%MODIFICATION%';

  3. select  * from DBA_TAB_MODIFICATIONS m
  4.   where m.table_owner <> 'SYS';
  5.   
  6.   
  7. select  * from  DBA_STAT_EXTENSIONS e
  8.   where e.owner='HR'
  9.    and e.table_name='EMPLOYEES';


  10.   select
  11.     dbms_stats.create_extended_stats('HR','EMPLOYEES','(salary,commission_pct)') from dual;
  12.    
  13.     begin
  14.        dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'EMPLOYEES',
  15.        method_opt => 'for all columns size auto for columns (salary,commission_pct) size 254');
  16.      end;
  17.       
  18.      
  19.      select  * from dba_tab_col_statistics s where s.owner='HR'
  20.         and s.table_name='EMPLOYEES';     
  21.         
  22.      select  * from dba_histograms h
  23.       where h.owner='HR' and h.table_name='EMPLOYEES';
  24.       
  25.       begin
  26.           dbms_stats.create_stat_table(ownname => 'SYSTEM',stattab => 'MYSTAT');
  27.         end;
  28.                
  29.         select  * from system.mystat;
  30.         
  31.         begin
  32.            dbms_stats.export_table_stats(ownname => 'HR',tabname => 'EMPLOYEES',
  33.            statown => 'SYSTEM',
  34.            
  35.              stattab =>  'MYSTAT');
  36.            end;  
  37.            
  38.            
  39.            begin
  40.               dbms_stats.delete_table_stats(ownname => 'HR',tabname => 'EMPLOYEES');
  41.              end;

  42. select  t.last_analyzed
  43. from dba_tables t where t.owner='HR' and t.table_name='EMPLOYEES';

  44. begin
  45.            dbms_stats.import_table_stats(ownname => 'HR',tabname => 'EMPLOYEES',
  46.            statown => 'SYSTEM',
  47.            
  48.              stattab =>  'MYSTAT');
  49.            end;  

  50. ---

  51. select  * from v$sql_monitor;
  52. select  * from v$sql_plan_monitor;

  53. select  *
  54. from v$sql where sql_id='05s9358mm6vrr';


  55. select  * from dict
  56.   where table_name like 'DBA%ACTIVE%';
  57.   
  58.   
  59.   select * from DBA_HIST_ACTIVE_SESS_HISTORY;
复制代码
utlpwdmg.sql:
  1. Rem
  2. Rem $Header: utlpwdmg.sql 02-aug-2006.08:18:05 asurpur Exp $
  3. Rem
  4. Rem utlpwdmg.sql
  5. Rem
  6. Rem Copyright (c) 2006, Oracle. All rights reserved.  
  7. Rem
  8. Rem    NAME
  9. Rem      utlpwdmg.sql - script for Default Password Resource Limits
  10. Rem
  11. Rem    DESCRIPTION
  12. Rem      This is a script for enabling the password management features
  13. Rem      by setting the default password resource limits.
  14. Rem
  15. Rem    NOTES
  16. Rem      This file contains a function for minimum checking of password
  17. Rem      complexity. This is more of a sample function that the customer
  18. Rem      can use to develop the function for actual complexity checks that the
  19. Rem      customer wants to make on the new password.
  20. Rem
  21. Rem    MODIFIED   (MM/DD/YY)
  22. Rem    asurpur     05/30/06 - fix - 5246666 beef up password complexity check
  23. Rem    nireland    08/31/00 - Improve check for username=password. #1390553
  24. Rem    nireland    06/28/00 - Fix null old password test. #1341892
  25. Rem    asurpur     04/17/97 - Fix for bug479763
  26. Rem    asurpur     12/12/96 - Changing the name of password_verify_function
  27. Rem    asurpur     05/30/96 - New script for default password management
  28. Rem    asurpur     05/30/96 - Created
  29. Rem


  30. -- This script sets the default password resource parameters
  31. -- This script needs to be run to enable the password features.
  32. -- However the default resource parameters can be changed based
  33. -- on the need.
  34. -- A default password complexity function is also provided.
  35. -- This function makes the minimum complexity checks like
  36. -- the minimum length of the password, password not same as the
  37. -- username, etc. The user may enhance this function according to
  38. -- the need.
  39. -- This function must be created in SYS schema.
  40. -- connect sys/<password> as sysdba before running the script

  41. CREATE OR REPLACE FUNCTION verify_function_11G
  42. (username varchar2,
  43.   password varchar2,
  44.   old_password varchar2)
  45.   RETURN boolean IS
  46.    n boolean;
  47.    m integer;
  48.    differ integer;
  49.    isdigit boolean;
  50.    ischar  boolean;
  51.    ispunct boolean;
  52.    db_name varchar2(40);
  53.    digitarray varchar2(20);
  54.    punctarray varchar2(25);
  55.    chararray varchar2(52);
  56.    i_char varchar2(10);
  57.    simple_password varchar2(10);
  58.    reverse_user varchar2(32);
  59.    part1  varchar2(30);
  60.    part2 varchar2(30);

  61. BEGIN
  62.    digitarray:= '0123456789';
  63.    chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

  64.    -- Check for the minimum length of the password
  65.    IF length(password) < 8  or length(password)  > 30  THEN
  66.       raise_application_error(-20001, 'Password length less than 8 or more than 30');
  67.    END IF;


  68.    -- Check if the password is same as the username or username(1-100)
  69.    IF NLS_LOWER(password) = NLS_LOWER(username) THEN
  70.      raise_application_error(-20002, 'Password same as or similar to user');
  71.    END IF;
  72.    FOR i IN 1..100 LOOP
  73.       i_char := to_char(i);
  74.       if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
  75.         raise_application_error(-20005, 'Password same as or similar to user name ');
  76.       END IF;
  77.     END LOOP;

  78.    -- Check if the password is same as the username reversed
  79.    
  80.    FOR i in REVERSE 1..length(username) LOOP
  81.      reverse_user := reverse_user || substr(username, i, 1);
  82.    END LOOP;
  83.    IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
  84.      raise_application_error(-20003, 'Password same as username reversed');
  85.    END IF;

  86.    
  87.   





  88.    IF length(password)>length(username) THEN

  89.   part1 := SUBSTR(password, 1, length(username));

  90.   part2 := SUBSTR(password, length(username)+1);

  91.   IF part1=username THEN
  92.    
  93.       isdigit:=FALSE;
  94.       m := length(part2);
  95.       FOR i IN 1..10 LOOP
  96.        FOR j IN 1..m LOOP
  97.          IF substr(part2,j,1) = substr(digitarray,i,1) THEN
  98.             isdigit:=TRUE;
  99.          END IF;
  100.       END LOOP;
  101.    END LOOP;

  102.      IF isdigit=true THEN

  103.        raise_application_error(-20013,'Password same as or similar to username');                 

  104.      END IF;





  105.   END IF;

  106.   END IF;




  107.    -- Check if the password is the same as server name and or servername(1-100)
  108.    select name into db_name from sys.v$database;
  109.    if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
  110.       raise_application_error(-20004, 'Password same as or similar to server name');
  111.    END IF;
  112.    FOR i IN 1..100 LOOP
  113.       i_char := to_char(i);
  114.       if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
  115.         raise_application_error(-20005, 'Password same as or similar to server name ');
  116.       END IF;
  117.     END LOOP;

  118.    -- Check if the password is too simple. A dictionary of words may be
  119.    -- maintained and a check may be made so as not to allow the words
  120.    -- that are too simple for the password.
  121.    IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
  122.       raise_application_error(-20006, 'Password too simple');
  123.    END IF;

  124.    -- Check if the password is the same as oracle (1-100)
  125.     simple_password := 'oracle';
  126.     FOR i IN 1..100 LOOP
  127.       i_char := to_char(i);
  128.       if simple_password || i_char = NLS_LOWER(password) THEN
  129.         raise_application_error(-20007, 'Password too simple ');
  130.       END IF;
  131.     END LOOP;

  132.    -- Check if the password contains at least one letter, one digit
  133.    -- 1. Check for the digit
  134.    isdigit:=FALSE;
  135.    m := length(password);
  136.    FOR i IN 1..10 LOOP
  137.       FOR j IN 1..m LOOP
  138.          IF substr(password,j,1) = substr(digitarray,i,1) THEN
  139.             isdigit:=TRUE;
  140.              GOTO findchar;
  141.          END IF;
  142.       END LOOP;
  143.    END LOOP;

  144.    IF isdigit = FALSE THEN
  145.       raise_application_error(-20008, 'Password must contain at least one digit, one character');
  146.    END IF;
  147.    -- 2. Check for the character
  148.    <<findchar>>
  149.    ischar:=FALSE;
  150.    FOR i IN 1..length(chararray) LOOP
  151.       FOR j IN 1..m LOOP
  152.          IF substr(password,j,1) = substr(chararray,i,1) THEN
  153.             ischar:=TRUE;
  154.              GOTO endsearch;
  155.          END IF;
  156.       END LOOP;
  157.    END LOOP;
  158.    IF ischar = FALSE THEN
  159.       raise_application_error(-20009, 'Password must contain at least one \
  160.               digit, and one character');
  161.    END IF;


  162.    <<endsearch>>
  163.    -- Check if the password differs from the previous password by at least
  164.    -- 3 letters
  165.    IF old_password IS NOT NULL THEN
  166.      differ := length(old_password) - length(password);

  167.      differ := abs(differ);
  168.      IF differ < 3 THEN
  169.        IF length(password) < length(old_password) THEN
  170.          m := length(password);
  171.        ELSE
  172.          m := length(old_password);
  173.        END IF;

  174.        FOR i IN 1..m LOOP
  175.          IF substr(password,i,1) != substr(old_password,i,1) THEN
  176.            differ := differ + 1;
  177.          END IF;
  178.        END LOOP;

  179.        IF differ < 3 THEN
  180.          raise_application_error(-20011, 'Password should differ from the \
  181.             old password by at least 3 characters');
  182.        END IF;
  183.      END IF;
  184.    END IF;
  185.    -- Everything is fine; return TRUE ;   
  186.    RETURN(TRUE);
  187. END;
  188. /

  189. -- This script alters the default parameters for Password Management
  190. -- This means that all the users on the system have Password Management
  191. -- enabled and set to the following values unless another profile is
  192. -- created with parameter values set to different value or UNLIMITED
  193. -- is created and assigned to the user.

  194. ALTER PROFILE DEFAULT LIMIT
  195. PASSWORD_LIFE_TIME 180
  196. PASSWORD_GRACE_TIME 7
  197. PASSWORD_REUSE_TIME UNLIMITED
  198. PASSWORD_REUSE_MAX UNLIMITED
  199. FAILED_LOGIN_ATTEMPTS 10
  200. PASSWORD_LOCK_TIME 1
  201. PASSWORD_VERIFY_FUNCTION verify_function_11G;



  202. -- Below is the older version of the script

  203. -- This script sets the default password resource parameters
  204. -- This script needs to be run to enable the password features.
  205. -- However the default resource parameters can be changed based
  206. -- on the need.
  207. -- A default password complexity function is also provided.
  208. -- This function makes the minimum complexity checks like
  209. -- the minimum length of the password, password not same as the
  210. -- username, etc. The user may enhance this function according to
  211. -- the need.
  212. -- This function must be created in SYS schema.
  213. -- connect sys/<password> as sysdba before running the script
复制代码


OCM参考答案:

  1. **********************************************************
  2. Skillset 1: Database, RMAN, EM, and Network Configuration *
  3. **********************************************************
  4. Section 1: Network Configuration
  5. 配置链接串 对prod1/prod2进行验证。创建EMREP
  6. ---archive log list;
  7. ---show parameter db_recovery_
  8. ---enable flashback database
  9. ---check sys/system password
  10. ---check dedicate server
  11. ---both node alter user dbsnmp identified by oracle;


  12. Section 2: Collecting Statistics

  13. connect sys/oracle@prod1 as sysdba
  14. exec dbms_stats.gather_schema_stats('HR');


  15. Section 3: Authentication Method
  16. CONNECT sys/oracle@prod1 as SYSDBA
  17. ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=true;
  18. orapwd file=orapwPROD1 password entries=5 ignorecase=n

  19. CONNECT sys/oracle@prod1 as SYSDBA
  20. --add code
  21. vi utlpwdmg.sql
  22. part1 varchar2(30);
  23. part2 varchar2(30);
  24. function is_number(i_str varchar2)
  25. return boolean
  26. is
  27.   v_number number;
  28. begin
  29.   select to_number(i_str) into v_number from dual;
  30.   if v_number is not null then
  31.     return true;
  32.   else
  33.     return false;
  34.   end if;
  35. EXCEPTION
  36.   WHEN OTHERS THEN
  37.     return false;
  38. end;

  39. IF length(password)>30 then
  40.   raise_application_error(-20012,'Password length more then 30');
  41. END IF;

  42. IF length(password)>length(username) THEN
  43.   part1 := SUBSTR(password, 1, length(username));
  44.   part2 := SUBSTR(password, length(username)+1, length(password)-length(username));
  45.   IF part1=username THEN
  46.      IF is_number(part2) THEN
  47.        raise_application_error(-20013,'Password same as or similar to username');                 
  48.      END IF;
  49.   END IF;
  50. END IF;


  51. @?/rdbms/admin/utlpwdmg.sql
  52. ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function_11g;


  53. Section 4: Storing Diagnostic Information
  54. CONNECT sys/oracle@prod1 as SYSDBA
  55. alter system set DIAGNOSTIC_DEST='/u01/app/oracle/product/11.1.0/db_1'


  56. Section 5: Improving Access to the Network File Directories
  57. find this file in directory
  58.   (1)$ORACLE_HOME/dbs/oranfstab
  59.   (2)/etc/oranfstab
  60.   (3)/etc/mtab

  61. cd $ORACLE_HOME/dbs
  62. vi oranfstab
  63. server: MyDataServer1
  64. path: 132.34.35.12
  65. export: /vol/oradata mount: /u02/oradata/prod1

  66. cd $ORACLE_HOME/lib
  67. mv libodm11.so libodm11.so_stub
  68. ln -s libnfsodm11.so libodm11.so

  69. conn sys/oracle@prod1 as sysdba
  70. shutdown immediate
  71. startup
  72. create tablespace SHARED_DATA datafile '/u02/oradata/prod1/shared_data.tbs.dbf' size 10m;
  73. --check
  74. select * from v$dnfs_servers;

  75. Section 6: Applying a Patch
  76. export PATH=$ORACLE_HOME/OPatch
  77. cd /home/oracle/scripts
  78. unzip p8342329_111070_linux-x86.zip
  79. cd 8342329
  80. $ORACLE_HOME/OPatch/opatch query -is_online_patch `pwd`

  81. --prod1/prod2 should be shutdown

  82. $ORACLE_HOME/OPatch/opatch apply
  83. $ORACLE_HOME/OPatch/opatch lsinventory

  84. Section 7: Enabling Grid Control to Manage Targets on the Database Machine
  85. http://oms:4889/agent_download/10.2.0.1.0/linux
  86. http://oms:4889/agent_download/10.2.0.5.0/linux
  87. download agentDownload.linux
  88. chmod a+x agentDownload.linux
  89. export PATH=$ORACLE_HOME/jdk/bin:$PATH
  90. java -version
  91. which java
  92. --10.2.0.5
  93. 1
  94. ./agentDownload.linux -b  /u01/app/oracle/product/11.1.0/ -m oms.us.oracle.com -r 4889

  95. ssh oms-->tar -cvf agent10g.tar agent10g/-->scp agent10.tar to dbsever-->tar -xvf agent10g.tar
  96. cd oui/bin
  97. runInstaller -clone -forceClone ORACLE_HOME=/u01/app/oracle/product/11.1.0/agent10g ORACLE_HOME_NAME=agent10ghome -noconfig -silent

  98. agentca -f

  99. ./root.sh

  100. emctl secure agent


  101. --10.2.0.1
  102. ./agentDownload.linux -b  /u01/app/oracle/product/11.1.0/



  103. Section 8: Creating a Recovery Catalog
  104. conn sys/oracle@emrep as sysdba
  105. create bigfile tablespace rc_data datafile '/u01/app/oracle/oradata/PROD2/rc_data.dbf' size 100m;
  106. ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=true;
  107. create user rc_admin identified by RC_ADMIN default tablespace rc_data;
  108. grant resource,connect,recovery_catalog_owner to rc_admin;

  109. rman catalog rc_admin/RC_ADMIN@emrep
  110. create catalog tablespace rc_data;
  111. --from catalog register
  112. rman target sys/oracle@prod1 catalog rc_admin/RC_ADMIN@emrep
  113. register database;
  114. rman target sys/oracle@prod2 catalog rc_admin/RC_ADMIN@emrep
  115. register database;


  116. Section 9: Backups
  117. --prod1 and prod2
  118. rman target sys/oracle@prod1 catalog rc_admin/RC_ADMIN@emrep
  119. configure controlfile autobackup on;
  120. run {
  121.    backup database tag='Q107' format '/home/oracle/files/%U'  plus archivelog tag='Q107' format '/home/oracle/files/%U' KEEP FOREVER;}

  122. select bytes/1024/1024/30 from v$datafile a, v$tablespace b where b.tablespace_name='EXAMPLE' and b.ts#=a.ts#;

  123. run{
  124. allocate channel c1 device type disk;
  125. allocate channel c2 device type disk;
  126. allocate channel c3 device type disk;
  127. allocate channel c4 device type disk;
  128. backup tablespace example section size 30m format '/home/oracle/files/%U';}


  129. rman target sys/oracle@prod2 catalog rc_admin/RC_ADMIN@emrep
  130. configure controlfile autobackup on;
  131. backup database tag='prod2_full' format '/home/oracle/files/%U' plus archivelog tag='prod2_full' format '/home/oracle/files/%U' delete all input


  132. ************************
  133. Skillset 2: Data Guard*
  134. ************************
  135. Section 1: Creating a Physical Standby Database
  136.    conn sys/oracle@prod1 as sysdba
  137.    alter database force logging;
  138.    alter database add standby logfile '/u01/app/oracle/oradata/PROD1/stdby01.log' size 50m;
  139.    alter database add standby logfile '/u01/app/oracle/oradata/PROD1/stdby02.log' size 50m;
  140.    alter database add standby logfile '/u01/app/oracle/oradata/PROD1/stdby03.log' size 50m;
  141.    alter database add standby logfile '/u01/app/oracle/oradata/PROD1/stdby04.log' size 50m;
  142. ---primary role parameter setting   
  143.    alter system set log_archive_config='dg_config=(PROD1,SBDB1)';
  144.    alter system set log_archive_dest_1='LOCATION=/home/oracle/files/arch/prod1 VALID_FOR=(online_logfiles,primary_role) db_unique_name=PROD1';
  145.    alter system set log_archive_dest_2='service=SBDB1 SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=SBDB1';
  146.    
  147. ---standby role parameter setting make a just parameter db_name=SBDB1  vi initSBDB1 in /home/oracle/files   
  148. ---configure tnsnames.ora on primary and standby
  149. ---make static register in listener on standby
  150. ---copy password file to standby host ,and rename it
  151. ---make directory on standby host
  152.    mkdir -p /u01/app/oracle/admin/SBDB1/adump
  153.    mkdir -p /u01/app/oracle/flash_recovery_area
  154.    mkdir -p /u01/app/oracle/oradata/SBDB1
  155. ---duplicate standby
  156.    conn sys/oracle@sbdb1 as sysdba
  157.    startup nomount pfile=/home/oracle/file/initSBDB1;
  158.    rman target sys/oracle@prod1 auxiliary sys/oracle@sbdb1 catalog rc_admin/RC_ADMIN@emrep;
  159. run {
  160.      allocate channel c1 device type disk;
  161.      allocate channel c2 device type disk;
  162.      allocate channel c3 device type disk;
  163.      allocate channel c4 device type disk;
  164.      allocate auxiliary channel stby type disk;
  165.      duplicate target database for standby from active database
  166.      spfile parameter_value_convert '/PROD1','/SBDB1'
  167.      set db_unique_name='SBDB1'
  168.      set db_file_name_convert='/u01/app/oracle/oradata/PROD1/','/u01/app/oracle/oradata/SBDB1/'
  169.      set log_file_name_convert='/u01/app/oracle/oradata/PROD1/','/u01/app/oracle/oradata/SBDB1/'
  170.      set standby_file_management='auto'
  171.      set FAL_CLIENT='SBDB1'
  172.      set FAL_SERVER='PROD1'
  173.      set log_archive_dest_1='location=/home/oracle/files/arch/sbdb1 valid_for=(online_logfile,primary_role) db_unique_name=SBDB1'
  174.      set log_archive_dest_3='location=/home/oracle/files/arch/prod1 valid_for=(standby_logfile,standby_role) db_unique_name=SBDB1'
  175.      set log_archive_dest_2='service=PROD1 SYNC AFFIRM valid_for=(online_logfile,primary_role) db_unique_name=PROD1';}

  176.      
  177. ---enable flashback database
  178. ---enable realtime-apply
  179.    alter database flashback on;
  180.    alter database recover managed standby database using current logfile disconnect from session;
  181.    
  182. ----create dataguard broker
  183. ----make static register in listener.ora, global name must be PROD1_DGMGRL and SBDB1_DGMGRL/ make tnsnames.ora connect PROD1_DGMGRL and SBDB1_DGMGRL
  184.    conn sys/oracle@prod1 as sysdba
  185.    alter system set dg_broker_start=true;   
  186.    conn sys/oracle@sbdb1 as sysdba
  187.    alter system set dg_broker_start=true;
  188.    
  189.    dgmgrl sys/oracle@prod1
  190.    CREATE CONFIGURATION 'dgconfig' AS PRIMARY DATABASE IS PROD1 CONNECT IDENTIFIER IS PROD1;
  191.    add database SBDB1 as connect identifier is sbdb1 MAINTAINED AS physical;
  192.    ENABLE CONFIGURATION;
  193.    
  194.       
  195. Section 2: Testing the Standby Database
  196. --convert database from physical standby to snapshot standby
  197.   convert database SBDB1 to snapshot standby;

  198. Section 3: Restoring the Standby Database
  199. --convert database from snapshot standby to phyical standby
  200.   convert database SBDB1 to physical standby;
  201.   
  202. --active dataguard
  203.   edit database SBDB1 set state='apply-off';
  204.   alter database open;
  205.   edit database SBDB1 set state='apply-on';

  206. 4、  
  207. ---ENABLE BLOCK CHANGE TRACKING ON STANDBY
  208.    sqlplus /nolog
  209.    conn sys/oracle@sbdb1 as sysdba
  210.    alter database enable block change tracking using file '/home/oracle/files/block_change_tracking.f';
  211.    
  212. ---PROTECT ARCHIVELOG
  213.   rman target / catalog rc_admin/RC_ADMIN@emrep
  214.   CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
  215.   
  216. ---ENABLE FAST-START FAILOVER
  217.    1、edit database PROD1 set property FastStartFailoverTarget="SBDB1";
  218.       edit database SBDB1 set property FastStartFailoverTarget="PROD1";
  219.       
  220.    2、enable fast_start failover;
  221.    3、start observer
  222.   


  223. **********************************************************
  224. Skillset 3: Data and Data Warehouse Management           *
  225. **********************************************************
  226. Section 1: Fast Refreshable Materialized View

  227. alter user hr identified by hr account unlock;
  228. alter user sh identified by sh account unlock;
  229. alter user bi identified by bi account unlock;
  230. alter user oe identified by OE account unlock;

  231. create directory dump_dir as '/home/oracle/files';
  232. grant dba,advisor to sh;
  233. conn sh/sh@prod1

  234. var adv_name varchar2(20)
  235. begin
  236.   dbms_advisor.tune_mview
  237.       (
  238.           :adv_name,
  239.           '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');
  240. end;

  241. /

  242. begin
  243.    dbms_advisor.create_file (
  244.       dbms_advisor.get_task_script(:adv_name),  
  245.    'DUMP_DIR',
  246.    'mvtune_script.sql');
  247. end;
  248. /

  249. Answer :
  250.     CREATE MATERIALIZED VIEW LOG ON "SH"."PRODUCTS"  
  251.     WITH ROWID, SEQUENCE ("PROD_CATEGORY","PROD_LIST_PRICE","PROD_MIN_PRICE")  
  252.     INCLUDING NEW VALUES
  253.     /
  254.    
  255.     CREATE MATERIALIZED VIEW SH.PROD_MV
  256.     REFRESH FAST WITH ROWID
  257.     ENABLE QUERY REWRITE
  258.     AS SELECT SH.PRODUCTS.PROD_CATEGORY C1, COUNT("SH"."PRODUCTS"."PROD_CATEGORY") M1,
  259.        SUM("SH"."PRODUCTS"."PROD_LIST_PRICE"-"SH"."PRODUCTS"."PROD_MIN_PRICE")
  260.        M2, COUNT("SH"."PRODUCTS"."PROD_LIST_PRICE"-"SH"."PRODUCTS"."PROD_MIN_PRICE")
  261.        M3, COUNT(*) M4 FROM SH.PRODUCTS GROUP BY SH.PRODUCTS.PROD_CATEGORY;


  262. Section 2: Creating a Database Link
  263.     CREATE PUBLIC DATABASE LINK "PROD_LINK" USING 'PROD1';

  264. Section 3: Creating a Plug-in Tablespace Using the Transportable Tablespace Feature

  265. create user sst identified by Sst1234;
  266. grant connect,resource to sst;
  267. rman target sys/oracle@prod1 catalog rc_admin/RC_ADMIN
  268. convert datafile '/home/oracle/scripts/TRPDATA_6' from platform 'Solaris[tm] OE (64-bit)' format='/home/oracle/files/TRPDATA_6.dbf';
  269. impdp system/oracle dumpfile=dump_dir:trans3_2.dmp transport_datafiles=/u01/app/oracle/oradata/PROD1/TRPDATA_6.dbf
  270. alter tablespace trpdata read write;

  271. Section 4: Optimizing Star Queries
  272. alter system set star_transformation_enabled=true;

  273. create bitmap index sales_channel_bix on sh.sales(channel_id);
  274. create bitmap index sales_time_bix on sh.sales(time_id);
  275. create bitmap index sales_cust_bix on sh.sales(cust_id);

  276. alter session set star_transformation_enabled=true;
  277. /
  278. SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
  279.    SUM(s.amount_sold) sales_amount
  280. FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  281. WHERE s.time_id = t.time_id
  282. AND   s.cust_id = c.cust_id
  283. AND   s.channel_id = ch.channel_id
  284. AND   c.cust_state_province = 'CA'
  285. AND   ch.channel_desc in ('Internet','Catalog')
  286. AND   t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
  287. GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
  288. /


  289. Section 5: Configuring Parallel Execution
  290. 1、
  291. alter system set parallel_max_servers=100;
  292. alter system set parallel_min_servers=10;

  293. 2、???????????
  294. alter system set PARALLEL_MIN_PERCENT=60 scope=spfile;

  295. 3、
  296. ALTER TABLE "SH"."SALES" PARALLEL 8;

  297. 4、????????????????????
  298. @utlxplan.sql
  299. alter session enable parallel dml
  300. /
  301. TRUNCATE TABLE plan_table
  302. /
  303. EXPLAIN PLAN
  304.     SET STATEMENT_ID = 'Raise quantity'
  305.     INTO plan_table
  306.     FOR UPDATE  sh.sales SET quantity_sold=quantity_sold+100
  307. /
  308. INSERT INTO sh.par_tab VALUES('Raise quantity')
  309. /
  310. COMMIT
  311. /
  312. SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY())
  313. /


  314. Section 6: Optimize the space usage for LOB Data
  315. 1、
  316.   create tablespace sf_data datafile '/u01/app/oracle/oradata/PROD1/sf_data.dbf' size 150m         
  317.   autoextend on extent management local uniform size 64M;

  318. 2、5
  319.   create table oe.CUSTOMER_PROFILES(
  320.         CUST_ID  NUMBER,
  321.         FIRST_NAME  VARCHAR2(20),
  322.         LAST_NAME  VARCHAR2(30),
  323.         PROFILE_INFO  BLOB
  324. )
  325. lob (profile_info) store as securefile
  326. (
  327.         disable storage in row
  328.         deduplicate
  329.         compress high
  330.         tablespace sf_data
  331. )
  332. tablespace sf_data
  333. /

  334. 3、
  335.   create directory cust_files as '/home/oracle/scripts/cust_files';
  336.   grant read,write on directory cust_files to oe;
  337.   conn oe/OE
  338. 4、  @3_4_6.sql
  339.   


  340. Section 7: Creating a Partitioned Table to save storing unnecessary columns
  341. conn sh/sh

  342. create table sh.new_order_items(
  343.         order_id number(12) not null,
  344.         product_id number not null,
  345.         quantity number not null,
  346.         sales_amount number not null,
  347.         constraint fk_order_id foreign key (order_id)
  348.         references sh.new_orders(order_id)
  349. )
  350. partition by reference (fk_order_id);
  351. /

  352. @3_7_1.sql


  353. Section 8: Using Partitioning to store non-existent partitions
  354. conn sys/oracle@prod1 as sysdba
  355. @3_8_1.sql

  356. create table sh.sales_history_2006(
  357. PROD_ID          NUMBER NOT NULL,
  358. CUST_ID         NUMBER NOT NULL,
  359. TIME_ID         DATE NOT NULL,
  360. CHANNEL_ID         NUMBER NOT NULL,
  361. PROMO_ID         NUMBER NOT NULL,
  362. QUANTITY_SOLD         NUMBER(10,2) NOT NULL,
  363. AMOUNT_SOLD         NUMBER(10,2) NOT NULL
  364. )
  365. partition by range (time_id)
  366. interval (interval '1' month)
  367. (
  368.          partition sal1 values less than (to_date('2003-01-01','YYYY-MM-DD')) tablespace SALES_TBS1,
  369.          partition sal2 values less than (to_date('2004-01-01','YYYY-MM-DD')) tablespace SALES_TBS2,
  370.          partition sal3 values less than (to_date('2005-01-01','YYYY-MM-DD')) tablespace SALES_TBS3,
  371.          partition sal4 values less than (to_date('2006-01-01','YYYY-MM-DD')) tablespace SALES_TBS4
  372. )
  373. /
  374. @3_8_2.sql

  375. Section 9: Configuring the Database to Retrieve All Previous Versions of the Table Rows

  376. create tablespace fratbs datafile '/u01/app/oracle/oradata/PROD1/fratbs.dbf' size 50m;
  377. create user fra_admin identified by abc123456;
  378. grant FLASHBACK ARCHIVE ADMINISTER,connect,resource to fra_admin;
  379. connect fra_admin/abc123456
  380. create flashback archive fra1 tablespace fratbs retention 1 year;
  381. GRANT FLASHBACK ARCHIVE ON FRA1 TO SH;
  382. conn sh/sh
  383. alter table sh.promotions flashback archive fra1;


  384. Section 10: Capturing and Propagating Streams
  385. ---on emrep/prod1
  386. ---archive log list;
  387. ---check hr user on target

  388. alter system set global_names=true;
  389. create directory src_dir as '/home/oracle/files';
  390. create directory dst_dir as '/home/oracle/files';

  391. create tablespace streams_tbs datafile '/u01/app/oracle/oradata/PROD1/streams_tbs.dbf' size 100m
  392. /
  393. create user strmadmin identified by streams_123 default tablespace streams_tbs
  394. /
  395. grant dba,select_catalog_role,select any dictionary to strmadmin
  396. /
  397. begin
  398.         dbms_streams_auth.grant_admin_privilege('STRMADMIN',true);
  399. end;
  400. /

  401. conn strmadmin/streams_123@prod1
  402. create database link emrep
  403. connect to strmadmin identified by streams_123
  404. using 'emrep'
  405. /

  406. conn strmadmin/streams_123@emrep
  407. create database link prod1
  408. connect to strmadmin identified by streams_123
  409. using 'prod1'
  410. /

  411. conn strmadmin/streams_123@prod1

  412. begin
  413.         dbms_streams_adm.maintain_tables(
  414.                 table_names=>'hr.employees,hr.departments',
  415.                 source_directory_object=>'SRC_DIR',
  416.                 destination_directory_object=>'DST_DIR',
  417.                 source_database=>'PROD1',
  418.                 destination_database=>'EMREP',
  419.                 capture_name => 'PROD1_CAPTURE',
  420.                 propagation_name => 'PROD1_PROPAGATION',
  421.                 perform_actions=>true,
  422.                 bi_directional=>false,
  423.                 include_ddl=>true,
  424.                 instantiation=>dbms_streams_adm.instantiation_table_network
  425.         );
  426. end;
  427. /



  428. select STREAMS_TYPE,rule_name,RULE_TYPE from DBA_STREAMS_TABLE_RULES

  429. conn hr/hr
  430. @3_10_4.sql

  431. BEGIN
  432. DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
  433. rule_name => 'departments4',
  434. transform_function => 'hr.zero_sal');
  435. END;
  436. /

  437. ---if error  see
  438. EXECUTE DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
  439. execute dbms_streams_adm.RECOVER_OPERATION(operation_mode = 'FORWARD'/operation_mode = 'ROLLBACK'/operation_mode = 'PURGE');
  440. DBA_RECOVERABLE_SCRIPT_ERRORS
  441. DBA_RECOVERABLE_SCRIPT
  442. DBA_RECOVERABLE_SCRIPT_PARAMS
  443. DBA_RECOVERABLE_SCRIPT_BLOCKS

  444. **********************************************************
  445. Skillset 4: Performance Management                       *
  446. **********************************************************
  447. Section 1: Configuring the Resource Manager

  448. alter user hr identified by hr account unlock;
  449. alter user sh identified by sh account unlock;
  450. alter user bi identified by bi account unlock;
  451. alter user oe identified by OE account unlock;

  452. BEGIN
  453. dbms_resource_manager.clear_pending_area();
  454. dbms_resource_manager.create_pending_area();
  455. dbms_resource_manager.create_consumer_group(consumer_group => 'OLTP',comment=>'');
  456. dbms_resource_manager.submit_pending_area();
  457. BEGIN
  458. dbms_resource_manager_privs.grant_switch_consumer_group(?,?,case ? when 'false' then false when 'true' then true else false end);
  459. END;
  460. END;

  461. BEGIN
  462. dbms_resource_manager.clear_pending_area();
  463. dbms_resource_manager.create_pending_area();
  464. dbms_resource_manager.create_plan( plan => 'DAYTIME',COMMENT=>'');
  465. dbms_resource_manager.create_plan_directive(
  466.     plan =>'DAYTIME',
  467.     group_or_subplan => 'LOW_GROUP',
  468.     COMMENT=>''
  469. );

  470. dbms_resource_manager.create_plan_directive(
  471.     plan =>'DAYTIME',
  472.     group_or_subplan => 'OLTP',
  473.     comment => '',
  474.     switch_io_reqs =>10000 ,
  475.     switch_io_megabytes =>2500,
  476.     switch_group =>'LOW_GROUP',
  477.     switch_for_call=> TRUE
  478. );
  479. dbms_resource_manager.create_plan_directive(
  480.     plan =>'DAYTIME',
  481.     group_or_subplan => 'OTHER_GROUPS',
  482.     comment => '',
  483.     switch_io_reqs =>10000 ,
  484.     switch_io_megabytes =>2500,
  485.     switch_group =>'LOW_GROUP',
  486.     switch_for_call => TRUE
  487. );
  488. dbms_resource_manager.submit_pending_area();
  489. END;

  490. alter system set resource_manager_plan='DAYTIME';


  491. Section 2: Tuning SQL Statements
  492. 2.1
  493.    alter system set RESULT_CACHE_MAX_SIZE=15m;
  494.    alter system set CLIENT_RESULT_CACHE_SIZE = 128m;
  495.    
  496. set feedback on
  497. set echo on
  498. pause Press enter to continue
  499. set autot on explain stat

  500. SELECT /*+RESULT_CACHE*/ department_id, AVG(salary)
  501. FROM employees
  502. GROUP BY department_id
  503. /

  504. set autot off
  505. pause Press enter to continue

  506. Update employees set salary=salary*.1+salary
  507. where department_id=20
  508. /

  509. commit
  510. /
  511. set autot on explain stat
  512. pause Press enter to continue

  513. SELECT /*+RESULT_CACHE*/ department_id, AVG(salary)
  514. FROM employees
  515. GROUP BY department_id
  516. /

  517. set autot off
  518. Update employees set salary=salary*.1+salary
  519. where job_id='SA_REP'
  520. /

  521. commit
  522. /

  523. pause Press enter to continue
  524. set autot on explain stat

  525. SELECT /*+RESULT_CACHE*/ department_id, AVG(salary)
  526. FROM employees
  527. GROUP BY department_id
  528. /

  529. set autot off

  530. 2.2
  531.    select dbms_stats.create_extended_stats('SH','CUSTOMERS','(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID)') from dual;
  532.    exec dbms_stats.gather_table_stats('SH','CUSTOMERS',method_opt=>'for all columns size 1 for columns (CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID) size 3');
  533.    select * from USER_STAT_EXTENSIONS
  534.    select dbms_stats.show_extended_stats_name('SH','CUSTOMERS','(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID)') from dual;
  535.    
  536. 2.3
  537.    exec dbms_stats.set_table_prefs('SH','SALES','PUBLISH','false');
  538.    exec dbms_stats.gather_table_stats('SH','SALES',NO_INVALIDATE=>TRUE);
  539.    alter session set optimizer_use_pending_statistics = true;
  540.    

  541. Section 3: Index Tuning
  542. CREATE INDEX PROD_IDX on sh.PRODUCT_INFORMATION_PART (PRODUCT_ID) GLOBAL PARTITION BY HASH (PRODUCT_ID) PARTITIONS 4 STORE IN (TBS1);
  543. (PARTITION p1  TABLESPACE tbs_1,PARTITION p2  TABLESPACE tbs_2,PARTITION p3  TABLESPACE tbs_3,PARTITION p4  TABLESPACE tbs_4);

  544. Section 4: Real Application Testing

  545. 4.1/4.2
  546. impdp apps/Apps1234 dumpfile=dump_dir:appsstg.dmp full=y logfile=dump_dir:impdp.log
  547. conn apps/Apps1234
  548. @?/rdbms/admin/utlxplan.sql
  549. grant advisor,dba to apps;

  550. begin
  551.         dbms_sqltune.unpack_stgtab_sqlset(
  552.                 replace=>true,
  553.                 staging_table_name=>'STS_PS_TAB',
  554.                 staging_schema_owner=>'APPS'
  555.         );
  556. end;
  557. /

  558. dbms_sqltune.create_tuning_task
  559. dbms_sqltune.execute_tuning_task('sql_tuning_test');
  560. USER_ADVISOR_TASKS
  561. dbms_sqltune.report_tning_task

  562. --SQL Performance Analyzer
  563. alter system set optimizer_features_enable='10.2.0.1';
  564. var tname varchar2(30);
  565. exec :tname:= dbms_sqlpa.create_analysis_task(sqlset_name => 'STS_PS', task_name => 'MYSPA');
  566. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'TEST EXECUTE', execution_name => 'before');
  567. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
  568. alter system set optimizer_features_enable='11.1.0.7';
  569. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'TEST EXECUTE', execution_name => 'after');
  570. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
  571. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'COMPARE PERFORMANCE');
  572. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;

  573. EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>:tname,execution_type => 'compare performance', execution_params =>
  574. dbms_advisor.arglist( 'execution_name1', 'before','execution_name2', 'after', 'comparison_metric', 'buffer_gets'));

  575. insert into plan_table select statement_id,plan_id,timestamp,remarks,OPERATION,OPTIONS,OBJECT_NODE,OBJECT_OWNER,OBJECT_NAME,OBJECT_ALIAS,OBJECT_INSTANCE,OBJECT_TYPE,OPTIMIZER,SEARCH_COLUMNS,ID,PARENT_ID,DEPTH,POSITION,COST,CARDINALITY,BYTES,OTHER_TAG,PARTITION_START,PARTITION_STOP,PARTITION_ID,OTHER,DISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES,PROJECTION,TIME,QBLOCK_NAME,OTHER_XML
  576. from DBA_ADVISOR_SQLPLANS where task_name='MYSPA' and execution_name='SECOND_SQL_TRIAL' and sql_id in('',''


  577. 4.3
  578. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

  579. create directory capture_dir '/home/oracle/scripts/capture';

  580. BEGIN
  581.   DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE ('CAPTURE_DIR');
  582. END;
  583. /

  584. BEGIN
  585.   DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => 'REPLAY1',replay_dir => 'CAPTURE_DIR');
  586. END;
  587. /

  588. select * from DBA_WORKLOAD_CONNECTION_MAP=>connection_id

  589. BEGIN
  590.   DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id => 1,
  591.                            replay_connection => 'hostname:1521/PROD1');
  592. END;
  593. /

  594. BEGIN
  595.   DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id => 2,
  596.                            replay_connection => 'hostname:1521/PROD1');
  597. END;
  598. /

  599. BEGIN
  600.   DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE);
  601. END;
  602. /

  603. wrc REPLAYDIR=/home/oracle/scripts/capture USERID=system PASSWORD=oracle
  604. wrc system/oracle@test mode=replay replaydir=/home/oracle/scripts/capture

  605. begin
  606. dbms_workload_replay.start_replay;
  607. end;
  608. /


  609. ----export to AWR
  610. BEGIN
  611.   DBMS_WORKLOAD_REPLAY.EXPORT_AWR (replay_id => 1);
  612. END;
  613. /

  614. -----interpruted replay
  615. BEGIN
  616.   DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
  617. END;
  618. /

  619. BEGIN
  620. DBMS_WORKLOAD_REPLAY.DELETE_REPLAY_INFO(REPLAY_ID=>?);
  621. END;
  622. /

  623. DBMS_WORKLOAD_CAPTURE

  624. select * from DBA_WORKLOAD_CONNECTION_MAP

  625. select * from DBA_WORKLOAD_REPLAYS


  626. Section 5: Evolving SQL Plan Baselines
  627. 5.1
  628. alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

  629. @4_5_1.sql

  630. select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

  631. declare
  632. test binary_integer;
  633. begin
  634. test := dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'')
  635. end;
  636. /

  637. variable ret clob
  638. declare
  639. begin
  640.         :ret :=
  641.                 dbms_spm.evolve_sql_plan_baseline(
  642.                         'SYS_SQL_f6cb7f742ef93547',
  643.                         'SYS_SQL_PLAN_2ef9354754bc8843'
  644.                 );
  645. end;
  646. /
  647. print ret



  648. select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

  649. Section 6: Capturing Performance Statistics
  650. BEGIN
  651.     DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
  652.                    start_time => '2010-07-31 18:00:00',
  653.                    end_time => '2010-08-01 18:00:00',
  654.                    baseline_name => 'WEEKEND',
  655.                    template_name => 'WEEKEND');
  656. END;
  657. /

  658. PERFORMANCE TUNNING GUIDE 5

复制代码



回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 21:27 , Processed in 0.034140 second(s), 24 queries .

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