[20130522]service以及一些falover的测试.txt

[20130522]service以及一些falover的测试.txt

以前我记得我第一次安装rac的时候,那个时候许多东西不懂,基本按照人家写的文档安装,其中的辛苦只有自己知道.
对于service名之类的东西,更加不理解.

直到看一些相关书籍,才慢慢一点点理解oracle服务以及falover的含义.

比如在rac中.

$ ./dba_crs.perl
HA Resource                                  TYPE        Target      State
---------------------------------------------------------------------------------------
ora.rac1.LISTENER_rac1.lsnr            application       ONLINE      ONLINE on rac1
ora.rac1.gsd                           application       ONLINE      ONLINE on rac1
ora.rac1.ons                           application       ONLINE      ONLINE on rac1
ora.rac1.vip                           application       ONLINE      ONLINE on rac1
ora.rac2.LISTENER_rac2.lsnr            application       ONLINE      ONLINE on rac2
ora.rac2.gsd                           application       ONLINE      ONLINE on rac2
ora.rac2.ons                           application       ONLINE      ONLINE on rac2
ora.rac2.vip                           application       ONLINE      ONLINE on rac2
ora.orcl.db                            application       ONLINE      ONLINE on rac1
ora.orcl.orcl1.inst                    application       ONLINE      ONLINE on rac1
ora.orcl.orcl2.inst                    application       ONLINE      ONLINE on rac2
ora.orcl.orcl_12.cs                    application       ONLINE      ONLINE on rac1
ora.orcl.orcl_12.orcl1.srv             application       ONLINE      ONLINE on rac1
ora.orcl.orcl_21.cs                    application       ONLINE      ONLINE on rac1
ora.orcl.orcl_21.orcl2.srv             application       ONLINE      ONLINE on rac2
ora.orcl.orcl_taf.cs                   application       ONLINE      ONLINE on rac1
ora.orcl.orcl_taf.orcl1.srv            application       ONLINE      ONLINE on rac1
ora.orcl.orcl_taf.orcl2.srv            application       ONLINE      ONLINE on rac2

我可以定义orcl_12服务在rac1上运行,而orcl_21服务在rac2上运行.有问题的时候可以自动切换到对方的机器.
但是10g上有点毛病,机器恢复正常后自己不会切换回来.需要手工切换.

--实际上这些东西也可以运用到单实例中.我自己做一些测试:
--配置客户端连接如下:
40 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.40)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test.com)
    )
  )
 
40G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.40)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test.com)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

 

1.从client登录: 

SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
--如果使用40
SQL> column service_name format a30
SQL> select SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum
SERVICE_NAME                   FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
test.com                       NONE          NONE       NO

--如果使用40g 

SQL> column service_name format a30
SQL> select SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum
SERVICE_NAME                   FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
test.com                       SELECT        BASIC      YES

--说明一下,在sql提示符前面加40G表示使用falover的方式连接,不再另外说明.

40G_SQL> select * from dba_objects ;
...
--由于输出信息很多,要等待一段时间.这个时候切换到服务器重启数据库.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size            1006636496 bytes
Database Buffers          587202560 bytes
Redo Buffers                7344128 bytes
Database mounted.
Database opened.

--从客户端看,你可以发现输出停止,等到数据库open后,可以正常使用以及输出. 

40G_SQL> select sid,SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum
       SID SERVICE_NAME  FAILOVER_TYPE FAILOVER_M FAI
---------- ------------- ------------- ---------- ---
         6 test.com      SELECT        BASIC      NO 
40G_SQL> select * from dba_objects ;
....
40G_SQL> select sid,SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum
       SID SERVICE_NAME  FAILOVER_TYPE FAILOVER_M FAI
---------- ------------- ------------- ---------- ---
         7 test.com      SELECT        BASIC      YES

--可以发现sid发生了变化.另外FAILED_OVER no=>yes.

--而使用40连接就没有这个特性.

40_SQL> select sid,SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum
       SID SERVICE_NAME  FAILOVER_TYPE FAILOVER_M FAI
---------- ------------- ------------- ---------- ---
       133 test.com      NONE          NONE       NO

ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 21766
Session ID: 133 Serial number: 5
400 rows selected.

2.以上我是在客户端设置,很明显要大量修改客户端的tnsnames.ora配置是不可行的,这个时候我们可以配置服务端的failover设置.

SQL> column FAILOVER_METHOD format a20
SQL> column FAILOVER_TYPE format a20
SQL> column FAILOVER_RETRIES format 999999
SQL> column FAILOVER_delay format 999999

SQL> select name,failover_method,failover_type,failover_retries,failover_delay from dba_services where name='test.com';
NAME                 FAILOVER_METHOD      FAILOVER_TYPE        FAILOVER_RETRIES FAILOVER_DELAY
-------------------- -------------------- -------------------- ---------------- --------------
test.com

--可以发现服务名test.com没有配置failover.
BEGIN
   DBMS_SERVICE.modify_service (service_name          => 'test.com',
                                failover_method       => DBMS_SERVICE.failover_method_basic,
                                failover_type         => DBMS_SERVICE.failover_type_select,
                                failover_retries      => 180,
                                failover_delay        => 5
                               );
END;
/

SQL> select name,failover_method,failover_type,failover_retries,failover_delay from dba_services where name='test.com';
NAME                 FAILOVER_METHOD      FAILOVER_TYPE        FAILOVER_RETRIES FAILOVER_DELAY
-------------------- -------------------- -------------------- ---------------- --------------
test.com             BASIC                SELECT                            180              5

--使用40连接登录: 

40_SQL> select sid,SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum
       SID SERVICE_NAME  FAILOVER_TYPE FAILOVER_M FAI
---------- ------------- ------------- ---------- ---
        69 test.com      SELECT        BASIC      NO
40_SQL> select * from dba_objects ;
..
--再停止数据库启动数据库,可以发现连接hang再那里,启动后输出正常.
40_SQL> select sid,SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum
       SID SERVICE_NAME  FAILOVER_TYPE FAILOVER_M FAI
---------- ------------- ------------- ---------- ---
        71 test.com      SELECT        BASIC      YES

--同样sid也发生了变化.另外FAILED_OVER no=>yes.

3.如果client有事务没有完成,只能退出回话.大家有兴趣自己可以测试.

收尾工作:

BEGIN
   DBMS_SERVICE.modify_service (service_name          => 'test.com',
                                failover_method       => DBMS_SERVICE.failover_method_none,
                                failover_type         => DBMS_SERVICE.failover_type_none,
                                failover_retries      => 180,
                                failover_delay        => 5
                               );
END;
/

SQL> select name,failover_method,failover_type,failover_retries,failover_delay from dba_services where name='test.com';
NAME                 FAILOVER_METHOD      FAILOVER_TYPE        FAILOVER_RETRIES FAILOVER_DELAY
-------------------- -------------------- -------------------- ---------------- --------------
test.com             NONE                 NONE


 
				


				
时间： 2024-09-20 16:32:53

		
		


		


	

	
	

		


		
[20130522]service以及一些falover的测试.txt的相关文章


								

		

			

                [20170428]延迟块清除测试.txt
			

		

		

									

				[20170428]延迟块清除测试.txt --//做一个延迟块清除测试,前面的测试太乱了,思路非常不清楚. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------------------------------------------			

		

	

				

		

			

                [20170623]传输表空间补充测试.txt
			

		

		

									

				[20170623]传输表空间补充测试.txt --//昨天测试了使用dblink+传输表空间,链接如下:http://blog.itpub.net/267265/viewspace-2141115/ --//今天补充测试看看加参数SQLFILE生成的脚本是什么内容. impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.d			

		

	

				

		

			

                [20151109]提升scn号11g测试.txt
			

		

		

									

				[20151109]提升scn号11g测试.txt --以前的测试都在10g下进行的,在11.2.0.4下重复测试. 1.测试环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------------------------------------			

		

	

				

		

			

                [20170214]在线重定义测试.txt
			

		

		

									

				[20170214]在线重定义测试.txt --//以前测试过,重复测试,因为生产系统要做一次相同的操作. --//实际的原理利用物化事务.注例子好像来源于piner的<构建oracle高可用环境>,当时版本是9i,好像没有 --//dbms_redefinition.copy_table_dependents函数. 1.准备工作: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER			

		

	

				

		

			

                [20170221]nocache工具的小测试.txt
			

		

		

									

				[20170221]nocache工具的小测试.txt --nocache 这个小工具可以显示文件在缓存的数量. --其中 cachestats 有一个-v  参数可以以表格形式显示.看看它的标识是否正确. 1.环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------			

		

	

				

		

			

                [20140324]延迟块提交测试.txt
			

		

		

									

				[20140324]延迟块提交测试.txt 做一个延迟块提交测试.主要出现一般是修改许多记录,修改后当前块已经不在buffer,在commit时,如果这时要读取在磁盘的数据块代 价太大,oracle使用一种延迟机制,在下次select时读取它,提交修改信息,这个时候产生redo信息. SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Or			

		

	

				

		

			

                [20120926]DBFS测试.txt
			

		

		

									

				[20120926]DBFS测试.txt     DBFS就是Oracle数据库11gR2中提供的能够在Linux操作系统中将Oracle数据库当成文件系统来使用的功能.在DBFS内部, 文件是以SecureFiles LOBs(对比与以前的BasicFiles LOBs)的形式存储在数据表中.        自己做一些测试:     在测试过程中参考了链接: http://juliandyke.wordpress.com/2011/11/02/dbfs/ SQL> select  * from			

		

	

				

		

			

                [20120813]11GR2下flashback data archive的测试.txt
			

		

		

									

				[20120813]11GR2下flashback data archive的测试.txt         oracle 11GR2有一个新特性,就是flashback data archive,就是通过一个表空间记录表的一些变化,查询历史数据.自己知道这个特性,从来也没有测试过.实际上这个就是flashback table的扩展(个人认为)!. 测试如下: 1.测试环境: SQL> select * from v$version ; BANNER -----------------------			

		

	

				

		

			

                [20140425]11GR2 truncate后恢复测试.txt
			

		

		

									

				[20140425]11GR2 truncate后恢复测试.txt --做一个truncate后恢复测试在11GR2下,我的测试环境使用使用dataguard.备用库打开flashback. --可以利用在备用库flashback到truncate前,然后在传输到主库的方式看看. 1.建立测试环境: -- 我的测试环境:primary的tnsnames别名test,standby主机的tnsnames别名testdg.数据库版本:11GR2. -- 备用库打开在read only模式,并且rea