muyeqing

1 nocatalog mode

例：target database: gate234

rman target sys/guanliyuan@gate234 nocatalog

2 catalog mode

例：target database: gate234
catalog database: gateway

rman target sys/guanliyuan@gate234 catalog rman/guanliyuan@gateway

CREATE TYPE recordDev AS OBJECT (dev_id NUMBER, dev_name varchar2(100));
/

CREATE TYPE tableDev AS TABLE OF recordDev
/

create or replace function returndevdata(p_dg_id number)
return tabledev is
v_record_temp recorddev := recorddev(null,null);
v_loc_temp_table tabledev;
BEGIN

v_loc_temp_table := tabledev(null);
v_loc_temp_table.Trim;

for c in (SELECT d.dev_id AS dev_id,g.DG_NAME||'/'||d.dev_name AS group_and_dev_name
FROM DEVICECONFIG d, DEVICEGROUPASSIGNMENT a,DEVICEGROUPCONFIG g
WHERE a.dga_dg_id IN
(SELECT dg_id FROM DEVICEGROUPCONFIG START WITH dg_id=p_dg_id CONNECT BY
PRIOR dg_id=dg_parent_id)
AND
a.dga_dev_id = d.dev_id
AND
a.dga_dg_id = g.DG_ID
)
loop
v_record_temp.dev_id := c.dev_id;
v_record_temp.dev_name := c.group_and_dev_name;
v_loc_temp_table.Extend;
v_loc_temp_table(v_loc_temp_table.Last) := v_record_temp;
end loop;

return v_loc_temp_table;

end;
/

select * from table(returndevdata(1000));

import带有blob字段的表到不同的表空间会报错： ora-00959 表空间不存在

FIX：

1. 将该表在要导入的数据库中先先建好

2. 导入时选择参数ignore=y

3. 导入该表

连接数据库的时候报错：关闭的连接

FIX: 使用以下方法连接数据库

<username>/<pwd>@<host>:1521:<sid>

参考metalink254587.1

1. stop所有的distribution, iis和oracle服务, 如果还是不行重启机器再关服务

2. 安装patch

3. 启动数据库, migrate模式

startup migrate

4. 运行catpatch.sql (%ORACLE_HOME%rdbmsadmin)

解决ora-28575的问题(参考METALINK 70638.1):

引起该错误的原英:
1) 数据库不能建立到到external procedure listener的连接
数据库使用tnsnames.ora文件中的extproc_connection_data服务名连接external_procedure_listener
2) 或者external procedure listener不能执行extproc agent
external procedure listener启动一个external procedure agent进程为external procedure execution请求服务

解决方法: 正确地配置listener.ora和tnsnames.ora文件
1)listener.ora文件, 在文件中增加下面两段内容
CALLOUT_LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = extproc)
)
)

SID_LIST_CALLOUT_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = plsextproc)
(ORACLE_HOME = D:oracleora92)
(PROGRAM = extproc)
)
)

2)tnsnames.ora文件

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

注意: 这里的KEY必须等于listener.ora文件中的key, sid必须等于listener.ora文件中的sid_name

3)重新启动listener
lsnrctl stop listener
lsnrctl start listener
lsnrctl stop callout_listener
lsnrctl start callout_listener
如果执行以上命令返回错误tns-12536,则先运行lsnrctl start以创建新的listener service

4)测试:
tnsping extproc_connection_data

1 初始化设置
1) 设置UTL_FILE_DIR
2) 设置DATABASE运行在ARCHIVE_LOG模式下

2 以SYSDBA登陆

3 生成字典文件, 在运行dbms_logmnr.start_logmnr时会用到

dbms_logmnr_d.build(dictionary_filename->

?supplemental logging?

4 dbms_logmnr.add_logfile 增加一个LOG文件到待处理列表中
例: execute dbms_logmnr.add_logfile(logfilename => 'D:oracleoradatatvsnbbcarcharch_133.arc',options => dbms_logmnr.new);

5 dbms_logmnr.start_logmnr(dictfilename, options)
例: execute dbms_logmnr.start_logmnr(dictfilename => 'd:ora_fileminer_0801.dic',options => dbms_logmnr.committed_data_only);
参数说明
dictfilename: step 3中所生成的字典文件
options: dbms_logmnr.committed_data_only
dbms_logmnr.SKIP_CORRUPTION
STARTTIME, ENDTIME
STARTSCN, ENDSCN

execute dbms_logmnr.start_logmnr(dictfilename => 'd:ora_fileminer_0801.dic');

6 select * from v$logmnr_contents;

7 execute dbms_logmnr.end_logmnr;

Oracle recommendations:
1) 缺省, oracle将所有log miner相关的表都建在SYSTEM表空间, 可以运行DBMS_LOGMNR_D.SET_TABLESPACE将所有的LOG MINER相关的表建在指定TABLESPACE中

分析LOGMNR结果
1)DBMS_LOGMNR.MINE_VALUE
Syntax
dbms_logmnr.mine_value(
sql_redo_undo IN RAW,
column_name IN VARCHAR2 default '') RETURN VARCHAR2;
sql_redo_undo: 指定v$logmnr_contents的redo_value或undo_value字段
column_name: 对应字段的全名

例: select vlc.SEG_NAME,vlc.REDO_VALUE,dbms_logmnr.mine_value(vlc.redo_value,'perfstat.perftest.col1')
from v$logmnr_contents vlc
where seg_name='PERFTEST';

2)DBMS_LOGMRN.COLUMN_PRESENT
Syntax

OLTP - online transaction processing

OLAP - online analytical processing

设计OLTP和OLAP时的区别

OLTP:

1. 设置较多但较小的回滚段

2. PCTFREE 高

3. SHARED POOL大

4. 较大的日志文件

5. MTS

6. db_block_size大: 16K

OLTP

1. 设置较少但较大的回滚段

2. PCTFREE = 0

3. 设置较大的BUFFER_CACHE

4. 较小的日志文件

5. 较大的SORT_AREA_SIZE

6. db_block_size小: 2k或4k

An interval specifies a period of time.

interval_year_to_month
INTERVAL 'integer [- integer]' { YEAR | MONTH } [( precision )] [TO { YEAR | MONTH }]

Examples:
INTERVAL '123-2' YEAR(3) TO MONTH
An interval of 123 years, 2 months. You must specify the leading field precision if it is greater than the default of 2 digits.

INTERVAL '123' YEAR(3)
An interval of 123 years 0 months.

INTERVAL '300' MONTH(3)
An interval of 300 months.

INTERVAL '4' YEAR
Maps to INTERVAL '4-0' YEAR TO MONTH and indicates 4 years.

INTERVAL '50' MONTH
Maps to INTERVAL '4-2' YEAR TO MONTH and indicates 50 months or 4 years 2 months.

INTERVAL '123' YEAR
Returns an error, because the default precision is 2, and '123' has 3 digits.

interval_day_to_second
INTERVAL ' { integer | integer time_expr | time_expr } '
{ { DAY | HOUR | MINUTE } [( leading_precision )])
| SECOND [( leading_precision [, fractional_seconds_precision] )]
}
[TO { DAY | HOUR | MINUTE | SECOND [( fractional_seconds_precision )] }]

Examples:
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second.

INTERVAL '4 5:12' DAY TO MINUTE
4 days, 5 hours and 12 minutes.

INTERVAL '400 5' DAY(3) TO HOUR
400 days 5 hours.

INTERVAL '400' DAY(3)
400 days.

INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)
11 hours, 12 minutes, and 10.2222222 seconds.

INTERVAL '11:20' HOUR TO MINUTE
11 hours and 20 minutes.

INTERVAL '10' HOUR
10 hours.

INTERVAL '10:22' MINUTE TO SECOND
10 minutes 22 seconds.

INTERVAL '10' MINUTE
10 minutes.

INTERVAL '4' DAY
4 days.

INTERVAL '25' HOUR
25 hours.

INTERVAL '40' MINUTE
40 minutes.

INTERVAL '120' HOUR(3)
120 hours

INTERVAL '30.12345' SECOND(2,4)
30.1235 seconds. The fractional second '12345' is rounded to '1235' because the precision is 4.

1 related parameters

• UNDO_MANAGEMENT = AUTO
• set UNDO_TABLESPACE to provide an undo tablespace
• UNDO_RETENTION

2 grant flashback to

3 query use "as of" clause

• Example1 query by timestamp:

select *

from emp

as of timestamp (systimestamp - interval '6' minute)

where ename='Miller';

• Example2 query by SCN (query SCN， select dbms_flashback.get_system_change_number from dual；）

select *

from emp

as of SCN 3297508

where ename='Miller';