YourSQLDba 配置——修改备份路径

    公司一直使用YourSQLDba做本地备份，磁带机将本地备份文件上带做异地容灾备份。近期整理、验证备份时发现本地备份目录命名五花八门

其中有历史原因，也有无规划化的缘故，看着这些五花八门的目录，越看越不顺眼。于是想统一规范化。备份目录统一为DB_BACKUP，完整备份位于FULL_BACKUP子目录，事务日志备份位于LOG_BACKUP子目录下。例如如下所示：

    完整备份目录    ：G:\DB_BACKUP\FULL_BACKUP\

    事务日志备份目录：G:\DB_BACKUP\LOG_BACKUP\

于是需要修改YourSQLDba备份路径，本来是一个简单的事情，但是在测试过程中，发现了一些小问题，特此记录一下：

 

问题1：在哪里修改备份路径？ 修改是否立即生效？

    修改YourSQLDba的备份路径，需要修改两个作业YourSQLDba_FullBackups_And_Maintenance 和YourSQLDba_LogBackups的配置. 即修改调用的存储过程Maint.YourSqlDba_DoMaint中的@FullBackupPath和@LogBackupPath参数。如下所 示：

 

YourSQLDba_FullBackups_And_Maintenance作业

 @oper = YourSQLDba_Operator

, @MaintJobName = YourSQLDba: DoInteg,DoUpdateStats,DoReorg,Full backups

, @DoInteg = 1

, @DoUpdStats = 1

, @DoReorg = 1

, @DoBackup = F

, @FullBackupPath = G:\DB_BACKUP\FULL_BACKUP\

, @LogBackupPath = G:\DB_BACKUP\LOG_BACKUP\

-- Flush database backups older than the number of days

, @FullBkpRetDays = 1

-- Flush log backups older than the number of days

, @LogBkpRetDays = 2

-- Spread Update Stats over 7 days

, @SpreadUpdStatRun =1

-- Maximum number of consecutive days of failed full backups allowed

-- for a database before putting that database (Offline).

, @ConsecutiveFailedbackupsDaysToPutDbOffline = 9999

-- Each database inclusion filter must be on its own line between the following quote pair

, @IncDb =

-- Each database exclusion filter must be on its own line between the following quote pair

, @ExcDb =

-- Each database exclusion filter must be on its own line between the following quote pair

, @ExcDbFromPolicy_CheckFullRecoveryModel =

, @FullBackupPath = G:\DB_BACKUP\FULL_BACKUP\

, @LogBackupPath = G:\DB_BACKUP\LOG_BACKUP\

-- Specify to user that full database backups are mandatory before log backups

, @NotifyMandatoryFullDbBkpBeforeLogBkp = 1

, @BkpLogsOnSameFile = 1

-- Each database inclusion filter must be on its own line between the following quote pair

, @IncDb =

-- Each database exclusion filter must be on its own line between the following quote pair

, @ExcDb =

 

将@FullBackupPath和 @LogBackupPath 从D:\backup\修改为G:\DB_BACKUP\FULL_BACKUP\与G:\DB_BACKUP\LOG_BACKUP\后，修 改后是否立即生效？  修改过后，如果没有先做一次完整备份，事务日志备份文件（事务日志备份有可能半小时。一小时一次等多种选择）并不会在新目录G:\DB_BACKUP \LOG_BACKUP\生成，依然还是在旧目录G:\backup\下生成。直到完整备份备份后，事务日志备份才在新目录生效。这样就导致我必须等到旧 目录数据文件完全上带后才能删除旧目录。但是为什么会这样呢？

从Maint.YourSqlDba_DoMaint追查开始，发现其调用[yMaint].[Backups]做备份，做事务日志备份的一段代码如下

If @DoFullBkp = 1 And DATABASEPROPERTYEX(@DbName, Recovery) Simple

 Begin

 Set @fileName = yMaint.MakeBackupFileName(@DbName, L, @LogBackupPath, @Language, @LogBkExt)

 Set @sql = yMaint.MakeBackupCmd

 (

 @DbName

 , L -- say explicitely full backup command

 , @fileName

 , 1

 , @MaintJobName

 )

 -- Launch first log backup that creates the file that will be used

 -- to stored log backups usually for the rest of the days unless

 -- end-user launch Maint.SaveDbOnNewFileSet

 Exec yExecNLog.LogAndOrExec

 @context = yMaint.backups

 , @sql = @sql

 , @Info = Log backups (init)

 , @JobNo = @JobNo

 , @errorN = @errorN_BkpPartielInit output

 -- Restore the backup to the mirror server if enabled

 Exec yMirroring.QueueRestoreToMirrorCmd

 @context = yMaint.backups (queue restore of log backup init)

 , @JobNo = @JobNo

 , @DbName = @DbName

 , @bkpTyp = NL

 , @fileName = @fileName

 , @MirrorServer = @MirrorServer

 , @BrokerDlgHandle = @BrokerDlgHandle OUT

 If @errorN_BkpPartielInit = 0 -- version

 Begin

 Update Maint.JobLastBkpLocations

 Set lastLogBkpFile = @filename

 , MirrorServer = @MirrorServer

 Where dbName = @DbName

 -- shrink the log after backup (the procedure acts depending on the size)

 -- Exec yMaint.ShrinkLog @DbName, @JobNo

 End

 End

yMaint.MakeBackupFileName(@DbName, L, @LogBackupPath, @Language, @LogBkExt)取值取决于@LogBackupPath， 而@LogBackupPath来源于Maint.JobHistory中（如下所示）。每次全备在Maint.JobHistory中生成一条记录，而 全备后不管事务日志备份多少次，只会在第一次事务日志备份时生成记录，所以即使修改了@FullBackupPath和 @LogBackupPath这两个参数的值，Maint.JobHistory表中最后一次事务日志备份记录的值依然为修改前的旧值。从而出现上面描述 的问题。

 , @FullBkpRetDays = FullBkpRetDays

 , @LogBkpRetDays = LogBkpRetDays

 , @NotifyMandatoryFullDbBkpBeforeLogBkp = NotifyMandatoryFullDbBkpBeforeLogBkp

 , @BkpLogsOnSameFile = BkpLogsOnSameFile

 , @FullBackupPath = yUtl.NormalizePath(FullBackupPath )

 , @LogBackupPath = yUtl.NormalizePath(LogBackupPath )

 , @FullBkExt = FullBkExt

 , @LogBkExt = LogBkExt

 , @ConsecutiveFailedbackupsDaysToPutDbOffline = ConsecutiveFailedbackupsDaysToPutDbOffline 

 , @IncDb = IncDb 

 , @ExcDb = ExcDb 

 , @jobStart = JobStart 

 , @MirrorServer = MirrorServer

 , @JobId = JobId

 , @StepId = StepId

 From Maint.JobHistory Where JobNo = @JobNo

如下所示，在存储过程[yMaint].[Backups]里面删除过期备份时，@Path参数来自于@FullBackupPath 和@LogBackupPath

 

这两个参数的值来源于表Maint.JobHistory， 而当修改了YourSQLDba备份路径后，如果没有做一次全备，对应记录FullBackupPath 与LogBackupPath字段的值都是原先的路径（因为事务日志备份的路径从Maint.JobHistory最后一次事务日志记录取值，也即原先的 路径），做了一次全备后，其值都是修改后路径，这样就会导致以前备份删除不了的情况。此时只能手工删除。

 , @FullBkpRetDays = FullBkpRetDays

 , @LogBkpRetDays = LogBkpRetDays

 , @NotifyMandatoryFullDbBkpBeforeLogBkp = NotifyMandatoryFullDbBkpBeforeLogBkp

 , @BkpLogsOnSameFile = BkpLogsOnSameFile

 , @FullBackupPath = yUtl.NormalizePath(FullBackupPath )

 , @LogBackupPath = yUtl.NormalizePath(LogBackupPath )

 , @FullBkExt = FullBkExt

 , @LogBkExt = LogBkExt

 , @ConsecutiveFailedbackupsDaysToPutDbOffline = ConsecutiveFailedbackupsDaysToPutDbOffline

 , @IncDb = IncDb

 , @ExcDb = ExcDb

 , @jobStart = JobStart

 , @MirrorServer = MirrorServer

 , @JobId = JobId

 , @StepId = StepId

 From Maint.JobHistory Where JobNo = @JobNo

YourSQLDba的输出日志文件 MaintenanceReport.txt并不会随着@FullBackupPath和 @LogBackupPath 的修改会保存到新目录。这个文件的输出目录的值放置在msdb.dbo.sysjobsteps表中，对应记录的output_file_name字段。

可以通过两种方式来修改：

 

1 SQL脚本方式：

EXEC msdb.dbo.sp_update_jobstep @job_id=N41662692-c7b2-47ae-8e07-df3eb12e1fe2, @step_id=1 ,

  @output_file_name=NG:\DB_BACKUP\MaintenanceReport.txt

2：UI图形界面：

问题4：是否可以用其它方式。

可以修改@FullBackupPath和 @LogBackupPath参数值后，将旧备份文件拷贝到对应新目录，不过对备份比较大的服务器，会产生一定的IO消耗。修改修改 Maint.JobLasstBkpLocations记录中的LastLogBkpFile和LastFullBkpFile字段, 以及Maint.JobHistory中最后一条记录的FullBackupPath与LogBackupPath。

UPDATE Maint.JobLastBkpLocations set lastLogBkpFile=G:\DB_BACKUP\LOG_BACKUP\xxxx_[2014-07-15_17h48m08_Tue]_logs.TRN, 

lastFullBkpFile=G:\DB_BACKUP\FULL_BACKUP\xxxx_[2014-07-15_17h48m07_Tue]_database.BAK

WHERE .....

UPDATE Maint.JobHistory SET FullBackupPath=G:\DB_BACKUP\LOG_BACKUP\, LogBackupPath=G:\DB_BACKUP\FULL_BACKUP where JobNo=@JobNo

[20171211]检查dg配置参数.txt [20171211]检查dg配置参数.txt --//写一个脚本,用来检查dg配置参数. col name    for a30 col value   for a120 col ses_mod for a10 col sys_mod for ...
[20170515]检查数据库scn脚本.txt [20170515]检查数据库scn脚本.txt --//简单写一个脚本检查数据库各个scn的大小: column TABLESPACE_NAME format a20 SELECT b.
   某位仁兄新装一套11gR2 RAC的过程中，在GI的安装配置阶段遇到了安装目录无法写入的报错，于是他便将$GRID_HOME下所有目录和文件属主改成了grid:oinstall，将$GRID_HOME下所有目录和文件权限改...
配置rman备份的默认备份路径_default path RMAN configure channel device type disk format /oracle/orclarch/%U_%d new RMAN configuration parameters:CONFIGURE CHANNEL DEVICE ...
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry，兴趣广泛，广泛涉猎，个性随意，不善言辞。执意做一名会写代码的DBA，混迹于IT行业