dblink 丢失

3^#

发表于 2012-2-6 11:53:00

dblink之前就已经创建好了，是在恢复后，丢失的，丢的是private,public 的没丢

4^#

发表于 2012-2-6 15:13:51

回复 3# 的帖子

是如何备份恢复的？ exp imp? rman restore ?

提供的信息不足，无法提出更好的建议。

5^#

发表于 2012-2-6 15:15:33

用rman做的备份

6^#

发表于 2012-2-6 15:23:39

做过测试，备份前创建了两个dblink,一个是public的，一个是private的，在B机上还原后，只public的能在dba_db_links 中查到

7^#

发表于 2012-2-6 15:27:04

回复 5# 的帖子

rman 备份是如何做恢复的？

完全恢复 complete recovery 还是 incomplete recovery 不完全恢复？

select * from DBA_DB_LINKS;

8^#

发表于 2012-2-6 15:36:57

基于时间点的不完全恢复，select * from DBA_DB_LINKS 只能看到public的dblink。

9^#

发表于 2012-2-6 15:44:37

执行以下脚本，并贴出输出

select * from DBA_DB_LINKS
spool results01.txt
set echo on feedback on time on timing on pagesize 100 linesize 80 numwidth 13
show user
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select * from v$version;
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as current_date from dual;
column name format a30
column value format a49
select name, value from v$parameter where isdefault='FALSE' order by 1;
column parameter format a30
column value format a49
select * from v$nls_parameters order by parameter;
column name format a10
select dbid, name,
to_char(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode, log_mode,
to_char(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
to_char(controlfile_change#, '999999999999999') as controlfile_change#,
to_char(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time,
to_char(resetlogs_change#, '999999999999999') as resetlogs_change#,
to_char(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs_time
from v$database;
select * from v$instance;
archive log list;
select * from v$thread order by thread#;
select * from v$log order by first_change#;
column member format a45
select * from v$logfile;
column name format a79
select '#' || ts.name || '#' as tablespace_name, ts.ts#,
'#' || df.name || '#' as filename, df.file#, df.status, df.enabled, df.creation_change#,
to_char(df.creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
to_char(df.checkpoint_change#, '999999999999999') as checkpoint_change#,
to_char(df.checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
to_char(df.offline_change#, '999999999999999') as offline_change#,
to_char(df.online_change#, '999999999999999') as online_change#,
to_char(df.online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
to_char(df.unrecoverable_change#, '999999999999999') as online_change#,
to_char(df.unrecoverable_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
to_char(df.bytes, '9,999,999,999,990') as bytes, block_size
from v$datafile df, v$tablespace ts
where ts.ts# = df.ts#
and ( df.status <> 'ONLINE'
or df.checkpoint_change# <> (select checkpoint_change# from v$database) );
select '#' || ts.name || '#' as tablespace_name, ts.ts#,
'#' || dh.name || '#' as filename, dh.file#, dh.status, dh.error, dh.
fuzzy, dh.creation_change#,
to_char(dh.creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
to_char(dh.checkpoint_change#, '999999999999999') as checkpoint_change#,
to_char(dh.checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
to_char(dh.resetlogs_change#, '999999999999999') as resetlogs_change#,
to_char(dh.resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
to_char(dh.bytes, '9,999,999,999,990') as bytes
from v$datafile_header dh, v$tablespace ts
where ts.ts# = dh.ts#
and ( dh.status <> 'ONLINE'
or dh.checkpoint_change# <> (select checkpoint_change# from v$database) );
select * from v$tempfile;
select HXFIL File_num,substr(HXFNM,1,60) file_name, FHTNM tablespace_name,
FHTYP type, HXERR validity,
FHSCN SCN, FHTIM SCN_Time, FHSTA status,
FHTHR Thread, FHRBA_SEQ Sequence
from X$KCVFH
--where HXERR > 0
order by HXERR, FHSTA, FHSCN, HXFIL;
column error format a15
select error, fuzzy, status, checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by error, fuzzy, status, checkpoint_change#, checkpoint_time
order by checkpoint_change#, checkpoint_time;
select * from V$INSTANCE_RECOVERY;
select * from v$recover_file order by change#;
select * from dba_tablespaces where status <> 'ONLINE';
SELECT * FROM database_properties order by property_name;
select *
from X$KCCLH, (select min(checkpoint_change#) df_min_scn,
min(checkpoint_change#) df_max_scn
from v$datafile_header
where status='ONLINE') df
where LHLOS in (select first_change# from v$log)
or df.df_min_scn between LHLOS and LHNXS
or df.df_max_scn between LHLOS and LHNXS;

复制代码

10^#

发表于 2012-2-6 16:00:23

回复 9# 的帖子

抱歉 select * from DBA_DB_LINKS 这个要在源数据库执行，获取DB LINK的创建时间。

11^#

发表于 2012-2-8 10:57:37

maclean，你好
经过测试，发现问题是这样的：
在A机的tnsnames.ora 中有个“TO_DB_1”，B机上也有一个，只是IP地址不同。
A机上使用如下方式创建dblink:
1、create dblink xxx using 'TO_DB_1'
B机上不可以恢复。
2、create dblink xxx using ' (DESCRIPTION= 。。。‘
B机上可以恢复
3、create public dblink xxx using 'TO_DB_1'
B机上可以恢复。

12^#

发表于 2012-2-8 15:44:21

1. 对这个结论表示怀疑，如果确实是使用rman恢复的话至少dblink这个对象是该存在的，可能失效。

因为rman是物理层面的备份恢复，建议你查一下原库中这个dblink的dba_objects记录，然后到目标库也查一下。

13^#

发表于 2012-2-9 10:28:48

源库
OWNER                         OBJECT_NAME       OBJECT_TYPE       STATUS                      TO_CHAR(CREATED,'YYYY-MM-DDHH24:MI:SS')
------------------------------ -------------------- -------------------- ---------------------------- ----------------------------------------------------------------------------
PUBLIC                      TEST1             DATABASE LINK       VALID                      2012-02-03 09:12:20
PUBLIC                      ANOTHER             DATABASE LINK       VALID                      2011-02-14 15:40:32
DWH1                         TEST3             DATABASE LINK       VALID                      2012-02-08 14:59:11
DWH1                         TEST2             DATABASE LINK       VALID                      2012-02-03 09:13:11
STG1                         LOC_GP3LOG    DATABASE LINK       VALID                  2011-09-02 13:22:20
STG1                         LOC_GP3PRD    DATABASE LINK       VALID                   2011-09-02 13:22:20

目标库
OWNER                         OBJECT_NAME       OBJECT_TYPE       STATUS                      TO_CHAR(CREATED,'YYYY-MM-DDHH24:MI:SS')
------------------------------ -------------------- -------------------- ---------------------------- ----------------------------------------------------------------------------
PUBLIC                      TEST1             DATABASE LINK       VALID                      2012-02-03 09:12:20
PUBLIC                      ANOTHER             DATABASE LINK       VALID                      2011-02-14 15:40:32
STG1                            LOC_GP3LOG    DATABASE LINK       VALID                      2012-02-09 05:44:04  <---
STG1                         LOC_GP3PRD    DATABASE LINK       VALID                      2012-02-09 05:44:04

DWH1 用户创建的私有dblink都没有恢复过来，并且STG1  恢复出来的dblink的创建时间跟源库中的不一样。