oracle11g DG转为快照库用于测试

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_41702082/article/details/82773308

oracle 11g环境下:

11g中加入了Snapshot Standby Database的新特性,其实也就是上面10g功能的一个包装而已,唯一不同的是在转换为读写模式后任然可以继续接受主库过来的归档日志。

步骤如下:

1:取消日志应用

SQL> alter database recover managed standby database cancel;

 

2:重启并转换成Snapshot Standby Database。

SQL> shutdown immediate;

SQL> startup mount

SQL> alter database convert to snapshot standby;

 

3:打开后可进行读写相关测试

SQL> alter database open;

 

4:重新转换成physical Standby并开启同步

SQL > shutdown immediate

SQL> startup mount

SQL> alter database convert to physical standby;

SQL> shutdown immediate;

SQL> startup nomount

SQl> alter database mount standby database;

SQL> alter database open;

SQL> alter database recover managed standby database disconnect from session;

 

需要注意的点:

10g中创建的还原点必须在闪回后进行删除,否则闪回恢复区可能会被撑满,导致DB Hang住。11g中不用担心这个问题,在重新转换为physical standby时会自动进行还原点删除。

展开阅读全文

oracle11g函数执行次数的测试

11-07

oracle11grnrncreate or replace function test_fun return number isrnbeginrn dbms_output.put_line(1);rn return dbms_random.value;rnend;rnrncreate or replace function test_fun1 return number determinitic isrnbeginrn dbms_output.put_line(1);rn return dbms_random.value;rnend;rnrn上述两个函数,第二个指定了determinitic,也是欺骗了一下oracle,为了看差异方便些。rnrncreate or replace function test_fun2(i in number) return number isrnbeginrn dbms_output.put_line(1);rn return i;rnend;rnrncreate or replace function test_fun3(i in number) return number determinitic isrnbeginrn dbms_output.put_line(1);rn return i;rnend;rn上述两个函数有输入参数,输出就是输入,第二个是determiniticrnrn不用函数测试:rnselect dd,dd,dd,dd from (select dbms_random.value dd from dual); --得到一个dd值rn测试函数语句:rnselect dd,dd,dd,dd from (select test_fun dd from test2 where ids in (1,2)); --test2表里就两行数据rnselect dd,dd,dd,dd from (select test_fun dd from dual);rnselect dd,dd,dd,dd from (select test_fun dd from dual connect by level<=1);rnselect dd,dd,dd,dd from (select test_fun dd from dual connect by level<=2);rnrnselect dd,dd,dd,dd from (select test_fun1 dd from test2 where ids in (1,2)); --test2表里就两行数据rnselect dd,dd,dd,dd from (select test_fun1 dd from dual);rnselect dd,dd,dd,dd from (select test_fun1 dd from dual connect by level<=1);rnselect dd,dd,dd,dd from (select test_fun1 dd from dual connect by level<=2);rnrnrn每个语句的函数分别执行了几次呢?预期函数只被执行一次,可是看output可以看出来:rn1、执行8次,有8个dd值rn2、执行4次,4个dd值都不同rn3、执行1次,只有一个dd值rn4、执行2次,两行4列数据,每行的4个dd值相同rnrn5、执行4次,两行4列数据,每列的2个dd值相同rn6、执行4次,4个dd值都不同rn7、执行1次,只有一个dd值rn8、执行1次,两行4列数据,只有一个dd值rnrn再测试有输入的函数:rnselect dd,dd,dd,dd from (select test_fun2(rownum) dd from test2 where ids in (1,2)); --test2表里就两行数据rnselect dd,dd,dd,dd from (select test_fun2(9) dd from test2 where ids in (1,2)); --test2表里就两行数据rnselect dd,dd,dd,dd from (select test_fun2(ids) dd from test2 where ids in (1,2));rnselect dd,dd,dd,dd from (select test_fun2(rownum) dd from dual);rnselect dd,dd,dd,dd from (select test_fun2(9) dd from dual);rnselect dd,dd,dd,dd from (select test_fun2(rownum) dd from dual connect by level<=1);rnselect dd,dd,dd,dd from (select test_fun2(9) dd from dual connect by level<=1);rnselect dd,dd,dd,dd from (select test_fun2(rownum) dd from dual connect by level<=2);rnselect dd,dd,dd,dd from (select test_fun2(9) dd from dual connect by level<=2);rnrnselect dd,dd,dd,dd from (select test_fun3(rownum) dd from test2 where ids in (1,2)); --test2表里就两行数据rnselect dd,dd,dd,dd from (select test_fun3(9) dd from test2 where ids in (1,2)); --test2表里就两行数据rnselect dd,dd,dd,dd from (select test_fun3(ids) dd from test2 where ids in (1,2));rnselect dd,dd,dd,dd from (select test_fun3(rownum) dd from dual);rnselect dd,dd,dd,dd from (select test_fun3(9) dd from dual);rnselect dd,dd,dd,dd from (select test_fun3(rownum) dd from dual connect by level<=1);rnselect dd,dd,dd,dd from (select test_fun3(9) dd from dual connect by level<=1);rnselect dd,dd,dd,dd from (select test_fun3(rownum) dd from dual connect by level<=2);rnselect dd,dd,dd,dd from (select test_fun3(9) dd from dual connect by level<=2);rn每个语句的函数分别执行了几次呢?预期函数也只被执行一次,可是看output可以看出来:rn1、执行2次rn[color=#FF0000]2、执行8次[/color]rn3、执行8次rn4、执行1次rn5、执行4次rn6、执行1次rn7、执行1次rn8、执行2次rn[color=#FF0000]9、执行2次[/color]rnrn10、执行2次rn[color=#FF0000]11、执行4次[/color]rn12、执行8次rn13、执行1次rn14、执行4次rn15、执行1次rn16、执行1次rn17、执行2次rn[color=#FF0000]18、执行1次[/color]rnrn好了,结果出来了,信息量很大,各种差异。可是为什么会是这样的?哪位可以做个详细的解释?rnrn最初预想是函数只会执行一次,别名使用的是函数执行的结果值,可是多次使用别名嵌套查询导致函数执行次数增多,而且使用connect by、rownum等也会使函数执行次数发生变化(目前只测试了这几种,没有将所有语法都测试)。然后使用了determinitic期待能稳定执行次数,可是对于结果理解的复杂度却再次增加。 论坛

没有更多推荐了,返回首页