课程第29/30次（2018-08-19星期日上下午）

botang · 发表于 2018-8-19 09:32:01

自动接受SQL Profile：

BEGIN
dbms_sqltune.set_auto_tuning_task_parameter( 'ACCEPT_SQL_PROFILES', 'TRUE');
END;

复制代码

In Oracle Database 11g, the performance improvement factor has to be at least three before a SQL profile is implemented.

调整在Maintenance窗口中的调有频率（在界面上调不了）：

begin
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',
'TIME_LIMIT', 7200);
end;

复制代码

Maximum Time Spent Per SQL During Tuning (sec)：

BEGIN
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 1400);
end;

复制代码

Maximum SQL Profiles Implemented Per Execution：

BEGIN
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK','MAX_SQL_PROFILES_PER_EXEC', 50);
end;

复制代码

Maximum SQL Profiles Implemented (Overall)：

BEGIN
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'MAX_AUTO_SQL_PROFILES', 10002);
end;

复制代码

DBA_ADVISOR_EXECUTIONS: Get data about each execution of the task
DBA_ADVISOR_SQLSTATS: See the test-execute statistics generated while testing the SQL profiles
DBA_ADVISOR_SQLPLANS: See the plans encountered during test-execute
SPA + STA的结果在下面：

SQL> desc DBA_ADVISOR_SQLPLANS
Name Null? Type
----------------------------------------- -------- ----------------------------
TASK_NAME VARCHAR2(30)
TASK_ID NOT NULL NUMBER(38)
EXECUTION_NAME NOT NULL VARCHAR2(30)
SQL_ID NOT NULL VARCHAR2(13)
OBJECT_ID NOT NULL NUMBER(38)
ATTRIBUTE VARCHAR2(34)
STATEMENT_ID VARCHAR2(30)
PLAN_HASH_VALUE NOT NULL NUMBER
PLAN_ID NOT NULL NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_ALIAS VARCHAR2(65)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NOT NULL NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30)
OTHER_XML CLOB
SQL>

复制代码

比较：
默认的PLAN_TABLE（全局临时表）

SQL> desc plan_table
Name Null? Type
----------------------------------------- -------- ----------------------------
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_ALIAS VARCHAR2(65)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
OTHER_XML CLOB
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30)

复制代码

比较自己建的PLAN_TABLE：

@/u01/app/oracle/acfsmounts/acfs_db1/rdbms/admin/utlxplan.sql

复制代码

SQL> desc plan_table
Name Null? Type
----------------------------------------- -------- ----------------------------
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_ALIAS VARCHAR2(65)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30)
OTHER_XML CLOB

复制代码

与OCM考试相关：

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
from DBA_ADVISOR_SQLPLANS where task_name='MYSPA' and execution_name='SECOND_SQL_TRIAL' and sql_id in('',''.....）

复制代码

--------------------------------------------------------------
ASM快速镜像重同步：

ALTER DISKGROUP DATA OFFLINE DISK ASMDISK02 DROP AFTER 3.6 h；

复制代码

[root@station36 ~]# cd /dev/oracleasm/disks/
[root@station36 disks]# ls -l ASMDISK02
brw-rw---- 1 oracle asmadmin 7, 1 Aug 17 16:28 ASMDISK02
[root@station36 disks]# ls -l /dev/loop*
brw-r----- 1 root disk 7, 0 Aug 17 16:27 /dev/loop0
brw-r----- 1 root disk 7, 1 Aug 17 16:27 /dev/loop1

ALTER DISKGROUP SET ATTRIBUTE 'DISK_REPAIR_TIME'='2D4H30M' ；

复制代码

--------------------------------------------

[oracle@station36 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 18 09:59:32 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysasm
Connected.
SQL> desc v$asm_disk;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
DISK_NUMBER NUMBER
COMPOUND_INDEX NUMBER
INCARNATION NUMBER
MOUNT_STATUS VARCHAR2(7)
HEADER_STATUS VARCHAR2(12)
MODE_STATUS VARCHAR2(7)
STATE VARCHAR2(8)
REDUNDANCY VARCHAR2(7)
LIBRARY VARCHAR2(64)
OS_MB NUMBER
TOTAL_MB NUMBER
FREE_MB NUMBER
HOT_USED_MB NUMBER
COLD_USED_MB NUMBER
NAME VARCHAR2(30)
FAILGROUP VARCHAR2(30)
LABEL VARCHAR2(31)
PATH VARCHAR2(256)
UDID VARCHAR2(64)
PRODUCT VARCHAR2(32)
CREATE_DATE DATE
MOUNT_DATE DATE
REPAIR_TIMER NUMBER
READS NUMBER
WRITES NUMBER
READ_ERRS NUMBER
WRITE_ERRS NUMBER
READ_TIME NUMBER
WRITE_TIME NUMBER
BYTES_READ NUMBER
BYTES_WRITTEN NUMBER
PREFERRED_READ VARCHAR2(1)
HASH_VALUE NUMBER
HOT_READS NUMBER
HOT_WRITES NUMBER
HOT_BYTES_READ NUMBER
HOT_BYTES_WRITTEN NUMBER
COLD_READS NUMBER
COLD_WRITES NUMBER
COLD_BYTES_READ NUMBER
COLD_BYTES_WRITTEN NUMBER
VOTING_FILE VARCHAR2(1)
SECTOR_SIZE NUMBER
FAILGROUP_TYPE VARCHAR2(7)
SQL>

复制代码

----------------------------
+ASM实例：

select * from v$asm_diskgroup;
select * from v$asm_template
where group_number=2;
alter diskgroup data add template template1
attributes ( unprotected fine );

复制代码

DB：

create tablespace tbsasm datafile '+data(template1)/orcl/datafile/tbsasm.dbf'
size 5M ;

复制代码

+ASM：

select f.REDUNDANCY , f.STRIPED
from v_$asm_file f
where (f.GROUP_NUMBER,f.FILE_NUMBER)=(
select a.GROUP_NUMBER, a.FILE_NUMBER
from v_$asm_alias a
where upper(a.NAME) ='TBSASM.DBF');

复制代码

	REDUNDANCY	STRIPED
1	UNPROT	FINE

select f.NAME
from v_$asm_alias f
where (f.GROUP_NUMBER,f.FILE_NUMBER) in (
select a.GROUP_NUMBER, a.FILE_NUMBER
from v_$asm_alias a
where upper(a.NAME) ='TBSASM.DBF');

复制代码

	NAME
1	TBSASM.274.984479179
2	tbsasm.dbf

984479179是文件incarnation：

[oracle@station36 ~]$ asmcmd ls +data/orcl/datafile | sort -n -t . -k 3
tbsasm.dbf
SYSAUX.257.979604823
EXAMPLE.265.979605007
UNDOTBS1.260.981998301
USERS.263.981998613
SYSTEM.256.982001513
TBS_NOCOMPRESSION.267.982770231
TBS_BASIC.268.982770235
TBS_OLTP.269.982770243
TBS_QUERY.270.982770251
TBS_ARCHIVE.271.982770261
TBSENC.272.983997167
TBSJFV.273.984463489
TBSASM.274.984479179
[oracle@station36 ~]$ asmcmd ls +data/orcl/datafile | sort -n -t . -k 3 -r
TBSASM.274.984479179
TBSJFV.273.984463489
TBSENC.272.983997167
TBS_ARCHIVE.271.982770261
TBS_QUERY.270.982770251
TBS_OLTP.269.982770243
TBS_BASIC.268.982770235
TBS_NOCOMPRESSION.267.982770231
SYSTEM.256.982001513
USERS.263.981998613
UNDOTBS1.260.981998301
EXAMPLE.265.979605007
SYSAUX.257.979604823
tbsasm.dbf
[oracle@station36 ~]$

复制代码

--------------------

[oracle@station36 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 18 10:36:34 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysasm
Connected.
SQL> alter diskgroup data check ;
Diskgroup altered.
SQL> alter diskgroup fra check ;
Diskgroup altered.

复制代码

-----------------------------------------------------

ASMCMD> md_backup -b /home/oracle/fra.bak -g fra
WARNING:option 'g' is deprecated for 'md_backup'
please use 'G'
WARNING:option 'b' is deprecated for 'md_backup'
Disk group metadata to be backed up: FRA
Current alias directory path: ORCL/CHANGETRACKING
Current alias directory path: ORCL/ARCHIVELOG/2018_08_18
Current alias directory path: ORCL/ONLINELOG
Current alias directory path: ORCL
Current alias directory path: ORCL/ARCHIVELOG/2018_08_19
Current alias directory path: ORCL/AUTOBACKUP/2018_08_16
Current alias directory path: ORCL/AUTOBACKUP
Current alias directory path: ORCL/ARCHIVELOG/2018_08_17
Current alias directory path: ORCL/ARCHIVELOG/2018_08_16
Current alias directory path: ORCL/CONTROLFILE
Current alias directory path: ORCL/ARCHIVELOG
ASMCMD>

复制代码

DB RMAN：

[oracle@station90 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Aug 19 16:06:59 2018
connected to target database: ORCL (DBID=1343950367)
RMAN> backup recovery area;
Starting backup at 19-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=207 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=83 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
specification does not match any datafile copy in the repository
channel ORA_SBT_TAPE_1: starting archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=163 RECID=155 STAMP=984345215
input archived log thread=1 sequence=164 RECID=156 STAMP=984345256
input archived log thread=1 sequence=165 RECID=157 STAMP=984345283
input archived log thread=1 sequence=166 RECID=158 STAMP=984345312
input archived log thread=1 sequence=167 RECID=159 STAMP=984345349
input archived log thread=1 sequence=168 RECID=160 STAMP=984346310
input archived log thread=1 sequence=169 RECID=161 STAMP=984346328
input archived log thread=1 sequence=170 RECID=162 STAMP=984346475
input archived log thread=1 sequence=171 RECID=163 STAMP=984346477
input archived log thread=1 sequence=172 RECID=164 STAMP=984392142
input archived log thread=1 sequence=173 RECID=165 STAMP=984395491
input archived log thread=1 sequence=174 RECID=166 STAMP=984408613
channel ORA_SBT_TAPE_1: starting piece 1 at 19-AUG-18
channel ORA_SBT_TAPE_2: starting archived log backup set
channel ORA_SBT_TAPE_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=175 RECID=167 STAMP=984425903
input archived log thread=1 sequence=176 RECID=168 STAMP=984430861
input archived log thread=1 sequence=177 RECID=169 STAMP=984472180
input archived log thread=1 sequence=178 RECID=170 STAMP=984473980
input archived log thread=1 sequence=179 RECID=171 STAMP=984483013
input archived log thread=1 sequence=180 RECID=172 STAMP=984501656
input archived log thread=1 sequence=181 RECID=173 STAMP=984501677
input archived log thread=1 sequence=182 RECID=174 STAMP=984509119
input archived log thread=1 sequence=183 RECID=175 STAMP=984516076
input archived log thread=1 sequence=184 RECID=176 STAMP=984561300
input archived log thread=1 sequence=185 RECID=177 STAMP=984562511
input archived log thread=1 sequence=186 RECID=178 STAMP=984564021
input archived log thread=1 sequence=187 RECID=179 STAMP=984574839
channel ORA_SBT_TAPE_2: starting piece 1 at 19-AUG-18
channel ORA_SBT_TAPE_1: finished piece 1 at 19-AUG-18
piece handle=1ptav5pl_1_1 tag=TAG20180819T160717 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:36
channel ORA_SBT_TAPE_1: starting archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=188 RECID=180 STAMP=984579361
channel ORA_SBT_TAPE_1: starting piece 1 at 19-AUG-18
channel ORA_SBT_TAPE_2: finished piece 1 at 19-AUG-18
piece handle=1qtav5pm_1_1 tag=TAG20180819T160717 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:35
channel ORA_SBT_TAPE_2: input backup set: count=56, stamp=984345646, piece=1
channel ORA_SBT_TAPE_2: starting piece 1 at 19-AUG-18
channel ORA_SBT_TAPE_2: backup piece +FRA/orcl/autobackup/2018_08_16/s_984345646.283.984345647
channel ORA_SBT_TAPE_1: finished piece 1 at 19-AUG-18
piece handle=1rtav5qp_1_1 tag=TAG20180819T160717 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:17
piece handle=c-1343950367-20180816-00 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_2: finished piece 1 at 19-AUG-18
channel ORA_SBT_TAPE_2: backup piece complete, elapsed time: 00:00:26
Finished backup at 19-AUG-18
Starting Control File and SPFILE Autobackup at 19-AUG-18
piece handle=c-1343950367-20180819-00 comment=API Version 2.0,MMS Version 10.4.0.4
Finished Control File and SPFILE Autobackup at 19-AUG-18
RMAN>

复制代码

ASMCMD> md_restore /home/oracle/fra.bak -G fra --full
Current Diskgroup metadata being restored: FRA
Diskgroup FRA created!
System template ONLINELOG modified!
System template AUTOBACKUP modified!
System template ASMPARAMETERFILE modified!
System template OCRFILE modified!
System template ASM_STALE modified!
System template OCRBACKUP modified!
System template PARAMETERFILE modified!
System template ASMPARAMETERBAKFILE modified!
System template FLASHFILE modified!
System template XTRANSPORT modified!
System template DATAGUARDCONFIG modified!
System template TEMPFILE modified!
System template ARCHIVELOG modified!
System template CONTROLFILE modified!
System template DUMPSET modified!
System template BACKUPSET modified!
System template FLASHBACK modified!
System template DATAFILE modified!
System template CHANGETRACKING modified!
Directory +FRA/ORCL re-created!
Directory +FRA/ORCL/AUTOBACKUP re-created!
Directory +FRA/ORCL/ARCHIVELOG re-created!
Directory +FRA/ORCL/CHANGETRACKING re-created!
Directory +FRA/ORCL/ONLINELOG re-created!
Directory +FRA/ORCL/CONTROLFILE re-created!
Directory +FRA/ORCL/ARCHIVELOG/2018_08_16 re-created!
Directory +FRA/ORCL/ARCHIVELOG/2018_08_19 re-created!
Directory +FRA/ORCL/ARCHIVELOG/2018_08_18 re-created!
Directory +FRA/ORCL/ARCHIVELOG/2018_08_17 re-created!
Directory +FRA/ORCL/AUTOBACKUP/2018_08_16 re-created!
ASMCMD>

复制代码

把数据用rman还原回来：

RMAN> restore archivelog sequence 188 ;
Starting restore at 19-AUG-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=188
channel ORA_SBT_TAPE_1: reading from backup piece 1rtav5qp_1_1
channel ORA_SBT_TAPE_1: piece handle=1rtav5qp_1_1 tag=TAG20180819T160717
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:35
Finished restore at 19-AUG-18
RMAN>

复制代码

ASMCMD> ls
thread_1_seq_188.257.984586895

复制代码

ALTER DISKGROUP DATA SET PERMISSION OWNER=Read write, GROUP=Read only, OTHER=Read only FOR FILE '+DATA/ORCL/spfileorcl.ora'

复制代码

----------------IDP：
+ASM实例：

select * from v$asm_diskgroup;
select * from v$asm_template
where group_number=2;
alter diskgroup data add template template2
attributes ( unprotected fine hot mirrorhot );

复制代码

DB：

create tablespace tbsasm2 datafile '+data(template2)/orcl/datafile/tbsasm2.dbf'
size 5M ;

复制代码

+ASM：

select f.REDUNDANCY , f.STRIPED ,f.PRIMARY_REGION , f.MIRROR_REGION
from v$asm_file f
where (f.GROUP_NUMBER,f.FILE_NUMBER)=(
select a.GROUP_NUMBER, a.FILE_NUMBER
from v$asm_alias a
where upper(a.NAME) ='TBSASM2.DBF');

复制代码

	REDUNDANCY	STRIPED	PRIMARY_REGION	MIRROR_REGION
1	UNPROT	FINE	HOT	HOT

SYSTEM_MOVING_WINDOW 10g没有， 11g专有的，就是用来设定自适应metric（维度）。
我们自己的基线，只有收集完统计信息（既不是Optimizer statistics也不是Database statistics），才能做为自适应metric（维度）的标准：