Pro*C编写自动数据库闪回程序

最近进行压力测试，经常需要对数据库进行闪回，并且闪回完毕重新建立闪回点。

为了方便起见自己用Pro*C写了一个自动闪回的程序，程序比较简单，对于执行命令是否成功没有异常处理，而且仅仅支持RAC环境。

 

程序源码如下：

 

#include
#include

exec sql include sqlca;

static void sqlerror()
{
    exec sql whenever sqlerror continue;
    fprintf(stderr,"\nORACLE error detected:");
    fprintf(stderr,"\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
    exec sql rollback work release;
    exit(1);
}

 void show_msg()
 {
   char *msg1="Autoflash: Release 1(AIX) - Production on ";
   char *msg2="\nCreated by yan shoupeng .";
   printf("\n%s\n%s\n",msg1,msg2);
 }
 

int main(int argc,char *argv[])
{

 static char * sid    = NULL;
 
 exec sql begin declare section;
    char * userid=" / ";
    char spname[50];
    char execcmd[120];
    char dbname[12];
 exec sql end   declare section;

 
 show_msg();
 
 if ( !strncmp( argv[1], "sid=", 4 ) && (argc=2))
 {
              sid = argv[1]+4;
 }
else

{
      fprintf( stderr,"usage: %s %s \n", argv[0],"sid=xxx" );
      exit(1);
 }
 
 /*设置ORACLE_SID*/
 setenv("ORACLE_SID",sid,1);
 
 exec sql whenever sqlerror do sqlerror();
 
  /*连接到数据库查询数据库名称*/
 exec sql connect :userid IN SYSDBA MODE;
 exec sql select lower(name) into :dbname from v$database;
 
 
  /*查找最近闪回点*/
 exec sql select name into :spname from (select name from v$restore_point order by time desc) where rownum=1;
 printf("The database will flashback to this restore point  :%s\n",spname);
 
 /*释放数据库连接*/
 exec sql commit work release;
 
 /*关闭数据库*/
 sprintf(execcmd,"srvctl stop database -d %s",dbname);
 printf("Execute command :%s\n",execcmd);
 system(execcmd);
 
 /*启动数据到mount状态*/
 sprintf(execcmd,"srvctl start inst -d %s -i %s -o mount ",dbname,sid);
 printf("Execute command :%s\n",execcmd);
 system(execcmd);
 
 /*连接到数据库*/
 exec sql connect :userid IN SYSDBA MODE;
 

 
 /*闪回到最近闪回点*/
 sprintf(execcmd,"flashback database to restore point %s",spname);
 printf("Execute command :%s\n",execcmd);
 exec sql execute immediate :execcmd;
 
 /*打开数据库*/
 exec sql alter database open resetlogs;
 
  /*释放数据库连接*/
 exec sql commit work release;
 
 /*关闭数据库*/
 sprintf(execcmd,"srvctl stop database -d %s",dbname);
 printf("Execute command :%s\n",execcmd);
 system(execcmd);
 
  /*启动数据库*/
 sprintf(execcmd,"srvctl start database -d %s",dbname);
 printf("Execute command :%s\n",execcmd);
 system(execcmd);
 
 /*连接到数据库*/
 exec sql connect :userid IN SYSDBA MODE;
 
 /*创建闪回点*/
 exec sql select 'GP_'||to_char(sysdate,'YYYYMMDD_HH24MI') into :spname from dual;
 printf("Create new restore point  :%s\n",spname);
 sprintf(execcmd,"create restore point %s guarantee flashback database",spname);
 exec sql execute immediate :execcmd;
 
 /*查询新的闪回点*/
 exec sql select name into :spname from (select name from v$restore_point order by time desc) where rownum=1;
 printf("New restore point is : %s ,please check!\n",spname);

 printf("Flashback database completed.");
 
 exec sql commit work release;
}

 

执行情况如下：

 

[oracle@sxffdb1 yansp]$ ./autoflash sid=zyxdb1

Autoflash: Release 1(AIX) - Production on

Created by yan shoupeng .

The database will flashback to this restore point  :GP_20120413_1552                                
Execute command :srvctl stop database -d zyxdb     
Execute command :srvctl start inst -d zyxdb       -i zyxdb1 -o mount
Execute command :flashback database to restore point GP_20120413_1552                                
Execute command :srvctl stop database -d zyxdb     
Execute command :srvctl start database -d zyxdb     
Create new restore point  :GP_20120416_1449                                
New restore point is : GP_20120416_1449                                  ,please check!
Flashback database completed.