PING[ARC1] Heartbeat failed to connect to standby ‘orclbk‘. Error is 12154 、如何避免sequence意外删除

1、PING[ARC2]: Heartbeat failed to connect to standby ‘orcl’. Error is 16009

备库查询归档目标

SQL>  select dest_id,error from v$archive_dest;


   DEST_ID ERROR
---------- -----------------------------------------------------------------
         1
         2 ORA-16009: invalid redo transport destination

查看standby的log_archive_dest_2如下：

-- 查看standby的log_archive_dest_2如下：
SQL> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=orcl async valid_for=(all_logfiles,all_roles) db_unique_name=orcl

修改standby的log_archive_dest_2如下：

SQL> alter system set log_archive_dest_2='service=orcl async valid_for=(online_logfiles,primary_roles) db_unique_name=orcl';

System altered.

参考资料

https://www.cnblogs.com/yhq1314/p/11084105.html

https://blog.csdn.net/u011616400/article/details/51241623

https://www.modb.pro/db/1722450388749197312

2、如何避免sequence意外删除

创建测试用户

create user test_user identified by test;
alter user test_user quota unlimited on users;
grant connect,resource to test_user;

create or replace directory bak_dir as '/oracle/backup';
grant read,write on directory bak_dir to test_user;

创建触发器

CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE
   BEFORE TRUNCATE OR DROP ON DATABASE
DISABLE
BEGIN
   dbms_output.put_line( ora_dict_obj_type);
   IF ora_dict_obj_type in ( 'TABLE','SEQUENCE') AND ora_dict_obj_owner = 'TEST_USER'
   THEN
      raise_application_error (-20000, 'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!');
   END IF;
END;
/


-- enable 触发器
alter TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE enable ;

测试

test_user@testdb(testos)> set serveroutput on
test_user@testdb(testos)> create sequence s1;

Sequence created.

test_user@testdb(testos)> drop sequence s1;
SEQUENCE
drop sequence s1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: YOU CAN NOT TRUNCATE or DROP S1 TABLE!
ORA-06512: at line 5

实际上上面的写法会存在一些问题，比如无法执行索引的 REBUILD online;以及使用expdp导出时建立的表无法删除

test_user@testdb(testos)> create table test_t(id int,name varchar2(10));

Table created.

test_user@testdb(testos)> create index pk_id on test_t(id);

Index created.

test_user@testdb(testos)> alter index pk_id rebuild online;
TABLE
TABLE
alter index pk_id rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20000: YOU CAN NOT TRUNCATE or DROP SYS_JOURNAL_87358 TABLE!
ORA-06512: at line 5
ORA-00604: error occurred at recursive SQL level 2
ORA-20000: YOU CAN NOT TRUNCATE or DROP SYS_JOURNAL_87358 TABLE!
ORA-06512: at line 5

test_user@testdb(testos)> alter index pk_id rebuild ;
alter index pk_id rebuild
*
ERROR at line 1:
ORA-08104: this index object 87358 is being online built or rebuilt

参照ORA-08104的解决方法。

expdp测试

[oracle@testos:/home/oracle]$ expdp test_user/test directory=bak_dir dumpfile=test_user.dmp logfile=test_user.log

Export: Release 11.2.0.4.0 - Production on Sat May 18 23:49:49 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST_USER"."SYS_EXPORT_SCHEMA_02":  test_user/******** directory=bak_dir dumpfile=test_user.dmp logfile=test_user.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "TEST_USER"."SYS_EXPORT_SCHEMA_01"          44.43 KB       5 rows
. . exported "TEST_USER"."SYS_JOURNAL_87358"                 0 KB       0 rows
. . exported "TEST_USER"."TEST_T"                            0 KB       0 rows
>>> ORA-31642: the following SQL statement fails: 
BEGIN "SYS"."DBMS_SCHED_EXPORT_CALLOUTS".SCHEMA_CALLOUT(:1,1,0,'11.02.00.04.00'); END;
ORA-20000: YOU CAN NOT TRUNCATE or DROP SCHEDULER$_PROGRAM_ARG TABLE!
Master table "TEST_USER"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST_USER.SYS_EXPORT_SCHEMA_02 is:
  /oracle/backup/test_user.dmp
Job "TEST_USER"."SYS_EXPORT_SCHEMA_02" successfully completed at Sat May 18 23:50:15 2024 elapsed 0 00:00:25

什么是叫 SCHEDULER$_PROGRAM_ARG

test_user@testdb(testos)> select * from dba_objects where object_name='SCHEDULER$_PROGRAM_ARG';

no rows selected

test_user@testdb(testos)> select * from tab where tname like 'SYS_%';

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SYS_EXPORT_SCHEMA_01           TABLE
SYS_EXPORT_SCHEMA_02           TABLE
SYS_JOURNAL_87358              TABLE

此外还有一些物化视图刷新也存在类似问题，参考链接：

https://blog.itpub.net/267265/viewspace-2764778

修改存储过程

CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE
   BEFORE TRUNCATE OR DROP
   ON DATABASE
   DISABLE
BEGIN
   --//dbms_output.put_line( ora_dict_obj_type);
   IF     ora_dict_obj_type IN ('TABLE', 'SEQUENCE')
      AND ora_dict_obj_owner = 'SCOTT'
      AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS\_JOURNAL\_%' ESCAPE '\'
      AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS\_EXPORT\_SCHEMA_%' ESCAPE '\'
   THEN
      raise_application_error
      (
         -- 20000
        ,'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!'
      );
   END IF;
END;
/

alter TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE enable;

测试

DECLARE
   ret BOOLEAN;
BEGIN
   ret := DBMS_REPAIR.ONLINE_INDEX_CLEAN(91079);
END;
/


test_user@testdb(testos)> drop table SYS_EXPORT_SCHEMA_01 purge ;

Table dropped.

test_user@testdb(testos)> drop table SYS_EXPORT_SCHEMA_02 purge ;

Table dropped.

test_user@testdb(testos)> drop table SYS_JOURNAL_87358 purge ;

Table dropped.

[oracle@testos:/oracle/backup]$ expdp test_user/test directory=bak_dir dumpfile=test_user.dmp logfile=test_user.log

Export: Release 11.2.0.4.0 - Production on Sun May 19 00:01:15 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST_USER"."SYS_EXPORT_SCHEMA_01":  test_user/******** directory=bak_dir dumpfile=test_user.dmp logfile=test_user.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "TEST_USER"."TEST_T"                            0 KB       0 rows
Master table "TEST_USER"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST_USER.SYS_EXPORT_SCHEMA_01 is:
  /oracle/backup/test_user.dmp
Job "TEST_USER"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun May 19 00:01:18 2024 elapsed 0 00:00:03

参考资料

https://blog.itpub.net/267265/viewspace-2764778