注册登录
提 问
English中文版
smdt_wyd
作者smdt_wyd·2014-12-22 10:24
系统工程师·oracle
关注

USP_SWAP_DB

字数 21458阅读 1084评论 0赞 0

USE [eService_HLA_Stage_Dev]
GO

/****** Object:  StoredProcedure [dbo].[USP_SWAP_DB]    Script Date: 2014/12/22 10:23:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 


-- =============================================
--exec USP_SWAP_DB;
-- =============================================
CREATE PROCEDURE [dbo].[USP_SWAP_DB](@V_S_DB VARCHAR(50),@V_T_DB VARCHAR(50))
AS
BEGIN
 SET NOCOUNT ON;
 declare @step_flag    varchar(100);
 declare @process_step_flag  varchar(20);
 declare @flag     varchar(1);
 declare @error_count   int;
 declare @error_step    int;
 declare @retry_count   int;
 declare @error_flag    varchar(1);
 declare @additonal_sms_info  varchar(1000);
 declare @delay_time    varchar(8);
 
 set @step_flag = N'-------------------------------begin-------------------------------';
 set @process_step_flag = N'the 1st step';
 set @process_step_flag = N'current';
 set @error_count  = 0;
 set @error_flag   = 'N';
 set @retry_count  = 5;
 set @delay_time   = '00:00:02';
 
 --LOG DB SWAP PROCEDURE BEGIN
 --exec USP_DB_SWAP_LOG @step_flag;

 
 --BEGIN TO DUE FIRST STEP TO CHANGE CN_ODS_PROD_TEST TO SINGLE USER MODE
 while @process_step_flag = N'current'
  begin
   begin try
    --AT EVERY STEP,KILL USERS FIRSTLY
    exec dbo.USP_ETL_Loading_kill_users @V_S_DB;
    --SET SINGLE USER FOR CN_ODS_PROD_TEST
    exec sp_dboption @V_S_DB, 'single user', true;
    --LOG FIRST STEP SUCCESSFULLY
    --exec USP_DB_SWAP_LOG 'change CN_ODS_PROD_TEST to single user success';
    --MAKE WHILE LOOP STOP AND GO TO NEXT STEP
    set @process_step_flag = N'next';
   end try
   begin catch
    --IF THERE IS AN EXCEPTION;NOTE EXCEPTION
    set @additonal_sms_info = 'exception at 1st step,change CN_ODS_PROD_TEST to single user fail;' ;
    set @additonal_sms_info =  @additonal_sms_info
     +  'ERROR_NUMBER:' + CAST(ERROR_NUMBER() AS VARCHAR(10))
     + ',ERROR_PROCEDURE:'+ERROR_PROCEDURE()
     + ',ERROR_MESSAGE:' + ERROR_MESSAGE()
     + ',ERROR_SEVERITY:'+CAST(ERROR_SEVERITY() AS VARCHAR(10))
     + ',ERROR_STATE:' + CAST(ERROR_STATE() AS VARCHAR(10))
     + ',ERROR_LINE:'+ CAST(ERROR_LINE() AS VARCHAR(10));
    --LOG ERROR INFO
    --exec USP_DB_SWAP_LOG @additonal_sms_info;    
    
    --TRY A FIXED TIMES AND IF FAILE AGAIN,SEND A MSM TO OPERATORS AND MAKE A ROLLBACK FOR PRE TASK
    set @error_count = @error_count + 1;
    if @error_count > @retry_count
     begin
      --SEND MSM
      --exec DBO.UPP_SMS_SENDER 'SWAP_DB_ERROR',@additonal_sms_info;
      --MAKE PROCEDURE END
      set @process_step_flag = N'error_end';
      --ROLLBACK TO ORIGINAL STATUS
      begin try
       exec dbo.USP_ETL_Loading_kill_users @V_S_DB;
       exec sp_dboption @V_S_DB, 'single user', false;
      end try
      begin catch
       --IF ROLLBACK FAIL,WE CAN DO NOTHING,BUT WE RECEVIE MSM TO KNOW THE PROBLEM
       --exec USP_DB_SWAP_LOG 'sp can resolve the exception;do nothing';
      end catch
      break;
     end
     --WHEN RETRY THE STEP, EVERY TIME, THE PROCEDURE SLEEP A FIXED PERIOD.
    waitfor DELAY @delay_time;    
   end catch; 
  end
 if @process_step_flag = N'next'
  begin
   set @process_step_flag = N'current';
  end
 while @process_step_flag = N'current'
  begin
   begin try
    exec USP_ETL_Loading_kill_users @V_T_DB
    exec sp_dboption @V_T_DB, 'single user', true
    --exec USP_DB_SWAP_LOG 'change CN_ODS_SHADOW_TEST to single user success' ;
    set @process_step_flag = N'next';
   end try
   begin catch
    set @additonal_sms_info = 'exception at 2nd step,change CN_ODS_SHADOW_TEST to single user fail;' ;
    set @additonal_sms_info =  @additonal_sms_info
        +  'ERROR_NUMBER:' + CAST(ERROR_NUMBER() AS VARCHAR(10))
     + ',ERROR_PROCEDURE:'+ERROR_PROCEDURE()
     + ',ERROR_MESSAGE:' + ERROR_MESSAGE()
     + ',ERROR_SEVERITY:'+CAST(ERROR_SEVERITY() AS VARCHAR(10))
     + ',ERROR_STATE:' + CAST(ERROR_STATE() AS VARCHAR(10))
     + ',ERROR_LINE:'+ CAST(ERROR_LINE() AS VARCHAR(10)); 
    --exec USP_DB_SWAP_LOG @additonal_sms_info; 
    
    
    set @error_count = @error_count + 1;
    if @error_count > @retry_count
     begin
      --set @error_flag = 'Y';
      --exec DBO.UPP_SMS_SENDER 'SWAP_DB_ERROR',@additonal_sms_info;
      --rollback
      begin try
       exec dbo.USP_ETL_Loading_kill_users @V_S_DB;
       exec sp_dboption @V_S_DB, 'single user', false;
       exec dbo.USP_ETL_Loading_kill_users @V_T_DB;
       exec sp_dboption @V_T_DB, 'single user', false;
      end try
      begin catch
       --exec USP_DB_SWAP_LOG 'sp can resolve the exception;do nothing';
      end catch
      break;
     end     
    waitfor DELAY @delay_time;  
   end catch; 
   
  end
 if @process_step_flag = N'next'
  begin
   set @process_step_flag = N'current';
  end
 while @process_step_flag = N'current'
  begin
   begin try
    exec USP_ETL_Loading_kill_users @V_S_DB
    IF EXISTS(SELECT 1 FROM sys.databases WHERE name = @V_S_DB )
       and NOT EXISTS(SELECT 1 FROM sys.databases WHERE name = 'CN_ODS_WORK' )
      BEGIN
       exec sp_rename @V_S_DB, 'CN_ODS_WORK', 'DATABASE';
      END
    set @process_step_flag = N'next';
    --exec USP_DB_SWAP_LOG 'rename CN_ODS_PROD_TEST to CN_ODS_WORK success' ;
   end try
   begin catch
    set @additonal_sms_info = 'exception at 3rd step,rename CN_ODS_PROD_TEST to CN_ODS_WORK fail';
    set @additonal_sms_info =  @additonal_sms_info
        +  'ERROR_NUMBER:' + CAST(ERROR_NUMBER() AS VARCHAR(10))
     + ',ERROR_PROCEDURE:'+ERROR_PROCEDURE()
     + ',ERROR_MESSAGE:' + ERROR_MESSAGE()
     + ',ERROR_SEVERITY:'+CAST(ERROR_SEVERITY() AS VARCHAR(10))
     + ',ERROR_STATE:' + CAST(ERROR_STATE() AS VARCHAR(10))
     + ',ERROR_LINE:'+ CAST(ERROR_LINE() AS VARCHAR(10));
     
    --exec USP_DB_SWAP_LOG @additonal_sms_info; 
    
    
     
    set @error_count = @error_count + 1;
    if @error_count > @retry_count
     begin
      --set @error_flag = 'Y';
      --exec DBO.UPP_SMS_SENDER 'SWAP_DB_ERROR',@additonal_sms_info;
      
      exec dbo.USP_ETL_Loading_kill_users @V_S_DB;
      exec sp_dboption @V_S_DB, 'single user', false;
      exec dbo.USP_ETL_Loading_kill_users @V_T_DB;
      exec sp_dboption @V_T_DB, 'single user', false;
      
      break;
     end 
    waitfor DELAY @delay_time; 
   end catch; 
   
   
   
  end
 if @process_step_flag = N'next'
  begin
   set @process_step_flag = N'current';
  end
 while @process_step_flag = N'current'
  begin
   begin try
    exec USP_ETL_Loading_kill_users @V_T_DB
    
    IF EXISTS(SELECT 1 FROM sys.databases WHERE name = @V_T_DB )
       and not EXISTS(SELECT 1 FROM sys.databases WHERE name = @V_S_DB )
        BEGIN
         exec sp_rename @V_T_DB, @V_S_DB, 'DATABASE'
        END
    
    
    set @process_step_flag = N'next';
    --exec USP_DB_SWAP_LOG 'rename CN_ODS_SHADOW_TEST to CN_ODS_PROD_TEST success' ;
   end try
   begin catch
    set @additonal_sms_info = 'exception at 4rd step,rename CN_ODS_SHADOW_TEST to CN_ODS_PROD_TEST fail';
    set @additonal_sms_info =  @additonal_sms_info
        +  'ERROR_NUMBER:' + CAST(ERROR_NUMBER() AS VARCHAR(10))
     + ',ERROR_PROCEDURE:'+ERROR_PROCEDURE()
     + ',ERROR_MESSAGE:' + ERROR_MESSAGE()
     + ',ERROR_SEVERITY:'+CAST(ERROR_SEVERITY() AS VARCHAR(10))
     + ',ERROR_STATE:' + CAST(ERROR_STATE() AS VARCHAR(10))
     + ',ERROR_LINE:'+ CAST(ERROR_LINE() AS VARCHAR(10)); 
    --exec USP_DB_SWAP_LOG @additonal_sms_info; 
    
    
    
    set @error_count = @error_count + 1;
    if @error_count > @retry_count
     begin
      --set @error_flag = 'Y';
      --exec DBO.UPP_SMS_SENDER 'SWAP_DB_ERROR',@additonal_sms_info;
      
      begin try
       exec dbo.USP_ETL_Loading_kill_users @V_S_DB;
       exec sp_dboption @V_S_DB, 'single user', false;
       exec dbo.USP_ETL_Loading_kill_users @V_T_DB;
       exec sp_dboption @V_T_DB, 'single user', false;
      end try
      begin catch
       --exec USP_DB_SWAP_LOG 'sp can resolve the exception;do nothing';
      end catch
      
      break;
     end 
    waitfor DELAY @delay_time; 
   end catch;  
  end
 if @process_step_flag = N'next'
  begin
   set @process_step_flag = N'current';
  end
 while @process_step_flag = N'current'
  begin
   begin try
    exec USP_ETL_Loading_kill_users @V_S_DB
    exec USP_ETL_Loading_kill_users @V_T_DB
    IF EXISTS(SELECT 1 FROM sys.databases WHERE name = 'CN_ODS_WORK')
           and NOT EXISTS(SELECT 1 FROM sys.databases WHERE name = @V_T_DB)
      BEGIN
       exec sp_rename 'CN_ODS_WORK', @V_T_DB, 'DATABASE'
      END
    set @process_step_flag = N'next';
    --exec USP_DB_SWAP_LOG 'rename CN_ODS_WORK to CN_ODS_SHADOW_TEST success' ;
   end try
   begin catch
    set @additonal_sms_info =  'exception at 5rd step,rename CN_ODS_WORK to CN_ODS_SHADOW_TEST fail';
    set @additonal_sms_info =  @additonal_sms_info
        +  'ERROR_NUMBER:' + CAST(ERROR_NUMBER() AS VARCHAR(10))
     + ',ERROR_PROCEDURE:'+ERROR_PROCEDURE()
     + ',ERROR_MESSAGE:' + ERROR_MESSAGE()
     + ',ERROR_SEVERITY:'+CAST(ERROR_SEVERITY() AS VARCHAR(10))
     + ',ERROR_STATE:' + CAST(ERROR_STATE() AS VARCHAR(10))
     + ',ERROR_LINE:'+ CAST(ERROR_LINE() AS VARCHAR(10)); 
    --exec USP_DB_SWAP_LOG @additonal_sms_info; 
    set @error_count = @error_count + 1;
    if @error_count > @retry_count
     begin
      --set @error_flag = 'Y';
      --exec DBO.UPP_SMS_SENDER 'SWAP_DB_ERROR',@additonal_sms_info;
      begin try
       IF EXISTS(SELECT 1 FROM sys.databases WHERE name = @V_S_DB)
           and EXISTS(SELECT 1 FROM sys.databases WHERE name = 'CN_ODS_WORK')
         BEGIN
          exec sp_rename @V_S_DB, @V_T_DB, 'DATABASE'
         END

        exec dbo.USP_ETL_Loading_kill_users @V_S_DB;
        exec sp_dboption @V_S_DB, 'single user', false;
        exec dbo.USP_ETL_Loading_kill_users @V_T_DB;
        exec sp_dboption @V_T_DB, 'single user', false;
      end try
      begin catch
       --exec USP_DB_SWAP_LOG 'sp can resolve the exception;do nothing';
      end catch
      break;
     end 
    waitfor DELAY @delay_time; 
   end catch; 
   
   
  end
 if @process_step_flag = N'next'
  begin
   set @process_step_flag = N'current';
  end
 while @process_step_flag = N'current'
  begin
   begin try
    exec USP_ETL_Loading_kill_users @V_S_DB
    exec USP_ETL_Loading_kill_users @V_T_DB
    exec sp_dboption @V_S_DB, 'single user',false
    set @process_step_flag = N'next';
    --exec USP_DB_SWAP_LOG 'set CN_ODS_PROD_TEST to unsingle user success';
   end try
   begin catch
    set @additonal_sms_info =  'exception at 6rd step,set CN_ODS_PROD_TEST to unsingle user fail';
    set @additonal_sms_info =  @additonal_sms_info
        +  'ERROR_NUMBER:' + CAST(ERROR_NUMBER() AS VARCHAR(10))
     + ',ERROR_PROCEDURE:'+ERROR_PROCEDURE()
     + ',ERROR_MESSAGE:' + ERROR_MESSAGE()
     + ',ERROR_SEVERITY:'+CAST(ERROR_SEVERITY() AS VARCHAR(10))
     + ',ERROR_STATE:' + CAST(ERROR_STATE() AS VARCHAR(10))
     + ',ERROR_LINE:'+ CAST(ERROR_LINE() AS VARCHAR(10)); 
    --exec USP_DB_SWAP_LOG @additonal_sms_info; 

    set @error_count = @error_count + 1;
    if @error_count > @retry_count
     begin
      --set @error_flag = 'Y';
      --exec DBO.UPP_SMS_SENDER 'SWAP_DB_ERROR',@additonal_sms_info;
      
      begin try
       exec dbo.USP_ETL_Loading_kill_users @V_S_DB;
       exec sp_dboption @V_S_DB, 'single user', false;
      end try
      begin catch
       --exec USP_DB_SWAP_LOG 'sp can resolve the exception;do nothing';
      end catch
      
      break;
     end 
    waitfor DELAY @delay_time; 
   end catch; 
   
   
   
  end
 if @process_step_flag = N'next'
  begin
   set @process_step_flag = N'current';
  end
 while @process_step_flag = N'current'
  begin
   begin try
    exec USP_ETL_Loading_kill_users @V_S_DB
    exec USP_ETL_Loading_kill_users @V_T_DB
    exec sp_dboption @V_T_DB, 'single user', false
    set @process_step_flag = N'next';
    --exec USP_DB_SWAP_LOG 'set CN_ODS_SHADOW_TEST to unsingle user success';
   end try
   begin catch
    set @additonal_sms_info =   'exception at 7rd step,set CN_ODS_SHADOW_TEST to unsingle user fail';
    set @additonal_sms_info =  @additonal_sms_info
        +  'ERROR_NUMBER:' + CAST(ERROR_NUMBER() AS VARCHAR(10))
     + ',ERROR_PROCEDURE:'+ERROR_PROCEDURE()
     + ',ERROR_MESSAGE:' + ERROR_MESSAGE()
     + ',ERROR_SEVERITY:'+CAST(ERROR_SEVERITY() AS VARCHAR(10))
     + ',ERROR_STATE:' + CAST(ERROR_STATE() AS VARCHAR(10))
     + ',ERROR_LINE:'+ CAST(ERROR_LINE() AS VARCHAR(10)); 
    --exec USP_DB_SWAP_LOG @additonal_sms_info; 
    
    
    
    set @error_count = @error_count + 1;
    if @error_count > @retry_count
     begin
      --set @error_flag = 'Y';
      --exec DBO.UPP_SMS_SENDER 'SWAP_DB_ERROR',@additonal_sms_info;
      
      begin try
       exec dbo.USP_ETL_Loading_kill_users @V_T_DB;
       exec sp_dboption @V_T_DB, 'single user', false;
      end try
      begin catch
       --exec USP_DB_SWAP_LOG 'sp can resolve the exception;do nothing';
      end catch
      
      break;
     end 
    waitfor DELAY @delay_time;  
   end catch; 
  end
   
   set @step_flag = N'-------------------------------end-------------------------------';
   --exec USP_DB_SWAP_LOG @step_flag;
 --if @error_flag = 'Y'
  --begin
   --rollback tran swap_db_tran;
  --end
 --commit tran swap_db_tran;
END

 


GO


 

数据库
著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

赞0

添加新评论0 条评论

Ctrl+Enter 发表

作者其他文章

  • 2014122205
    评论 0 · 赞 0
  • 2014122202
    评论 0 · 赞 0
  • sysconfig
    评论 0 · 赞 0
  • 2014122204
    评论 0 · 赞 0
  • 2014122203
    评论 0 · 赞 0

    • 相关文章

  • Redis 的优势是如何在保单业务中发挥出来的
  • 企业传统Oracle数据库迁移国产数据库核心难点总结
  • 招聘：建信金科基础技术中心数据库产品团队
  • 数据库运维管理平台面面观
  • 数据库行业解决方案都写了啥

    • 相关问题

  • 对于业务系统的历史数据存放、查询，哪种类型的数据库更为合适？
  • 超融合能否扛起全闪做支撑交易类数据库最佳平台的大旗？
  • 金融行业传统数据库一般如何解决io需求？
  • 引入缺乏第三方服务生态的信创数据库，会不会令甲方被厂商锁定而失去议价能力？
  • 针对不同国产数据库数据存储介质选型，是采用本地盘、SAN存储，还是NAS存储？

    • 相关资料

  • Redhat7.6 Oracle 19C RAC部署手册
  • Redhat7.0 Oracle 12C RAC部署手册
  • IDC+中国金融行业国产分布式数据库白皮书
  • Redhat7.5 Oracle 18C RAC部署手册
  • 海通证券核心数据库系统高可用及存储设备选型经验分享

    • 关于TWT使用指南社区专家合作厂商入驻社区企业招聘投诉建议版权与免责声明联系我们
    © 2024talkwithtrend — talk with trend,talk with technologist京ICP备09031017号-30

    X社区推广