Recovery by DBA

MySQL DBA | OCP 1z0-908 备考攻略 MySQL 1Z0-908是Oracle官方的MySQL 8.0数据库管理员认证考试,涵盖数据库安装配置、管理、高可用性、性能优化、安全管理和故障诊断六大核心领域。考试为120分钟73道选择题,重点考察实际管理能力,包括InnoDB集群部署、SQL优化、安全加固等。典型考题涉及系统数据库结构、集群元数据操作、函数索引优化及Linux权限安全等场景,要求考生掌握MySQL 8.0的核心管理技术。备考需注意多选题型占比高,建议通过Oracle官网注册考试并准备双证件参加线下测试。 阅读详情
 
  

 

  

   At a chance meeting with a staff member from another company in our tea room they asked me what I did. I explained that I was a DBA and that I administered databases, made backups and checked things were working. As they walked off they said "Oh so we blame you then if anything goes missing".

I was a bit perplexed at that statement and could only offer "…only if it is a backup that goes missing…." as the person darted off down the hall.

When I got back to my desk I realised that actually a DBA recovers lost things. When someone else wrecks data a DBA gets it back from backups.

For example the other day I had to recover some data for an analyst. However restoring the whole “metal” database at 16G was too much when all that was lost is several rows from 18 tables. But how do you safely recover that? This is the basic walkthrough:

Firstly I created an empty database called “backup_metal” on the same SQL instance. To create an empty database I usually alternate mouse click on “databases” in Enterprise Manager and choose New Database.

Then I examined the backup job to find the path where the backups were written. Once I found the location I asked the IT systems guys to get a backup off tape from a few days previous when the analyst was sure the records were uncorrupted.

Then I restored the “metal” database over backup_metal. Alternate mouse click on the backup_metal database and choose All tasks then restore database. I usually use the From device option and browse to the database backup file. Most importantly, before I initiated the restore, I clicked on the options tab and ensured that the metal database was restored over the backup_metal database physical files.

Then I requested that the analyst delete only one of the corrupted records as they know the business process for that and it meant one less risk for me.

One of the developers gave me the delete code which was helpful as I needed to know what tables required data again.

Delete * from property where propno= @propno

Delete * from tenement where propno= @propno

Delete * from status where propno= @propno

Delete * from options where propno= @propno

Delete * from claim where propno= @propno

            (and so on for 18 tables)

I specified the database and table names to keep it clear and did some manual testing with code like this:

Insert into Metal.dbo.property

select * from backup_metal.dbo.property where propno=1234

This worked fine so I left that record there. However I found the insert does not work when the table had an identity field. An identity field can not normally have values inserted into it. This can be overcome using the SET IDENTITY_INSERT table_name ON command but I found it more reliable to nominate the fields to be inserted and omit any field that was an identity column.

So this code:

INSERT INTO [Metal].[dbo].[Tenement]

                  SELECT *

      FROM  backup_metal.dbo.Tenement where propno = @propno

Became like this:

 INSERT INTO [Metal].[dbo].[Tenement]([propno], [status], [notes], [image])

                  SELECT [propno], [status], [notes], [image]

      FROM  backupmetal.dbo.Tenement where propno = @propno

That is with the identity field removed from the field lists. To create the field list I used an alternate mouse button click option on the table concerned in Query Analyzer.

I constructed a stored procedure that inserted the data required into each of table in turn. Here is part of the stored procedure I created:

-- usp_recoverProperty

Create procedure [usp_recoverProperty]

@propno int

as

-- GJO June 2008

-- Source is                : backup_metal

-- Destination is        : metal  (Live database)

-- Check to see if data is there first

IF not exists ( select * from  metal.dbo.property where propno = @propno)

            BEGIN

            -- Insert the primary record

INSERT INTO metal.dbo.property

SELECT * FROM backup_metal.dbo.property where propno = @propno

END

else

            print 'Propno exists in propery table'

--

IF not exists ( select * from  metal.dbo.Tenement where propno = @propno)

            BEGIN

            -- Identity field was [ten_id]

            INSERT INTO [Metal].[dbo].[Tenement]([propno],[status], [notes], [image])

                  SELECT [propno], [status], [notes], [image]

                        FROM  backup_metal.dbo.Tenement where propno = @propno

            END

else

            print 'Propno exists in Tenement table'

--

IF not exists ( select * from  metal.dbo. status where propno = @propno)

            BEGIN

            -- No identity field

            INSERT INTO [Metal].[dbo].[status]

                  SELECT *

      FROM  backup_metal.dbo.status where propno = @propno

            END

else

            print 'Propno exists in status table'

-- Other tables followed

-- Example Usage: usp_recoverProperty 1122

Running the stored procedure gave me a row count of one for each successful insert and if the propno existed in a table then that table name would be listed. After the first run I found I have a repeated table name so had to edit the stored procedure. Then when I ran it every insert gave back a row count of one.

I tried out the stored procedure on one record only then checked with the data analysis guy who checked that property number in the system using the font end application. He was overjoyed. So then we went ahead and deleted all the corrupted records and used the stored procedure to recovery them.

Once reliable backup systems are in place data losses should not be a problem. I think a DBA can get data back!

Graham Okely B App Sc

Senior Database Administrator

By Graham Okely, 2008/09/29

Flash_recovery_area(db_recovery_file_dest)之介绍 1. 设置闪回恢复区(默认rman备份在$ORACLE_HOME/dbs下) 闪回恢复区主要通过3个初始化参数来设置和管理: db_recovery_file_dest:指定闪回恢复区的位置        db_recovery_file_dest_size:指定闪回恢复区的可用空间大小 db_flashback_retention_target:指定数据库可以回退的时间,单位为分钟,默认 阅读详情

相关推荐

FPGA新手必看:Create HDL Wrapper和Generating Output Products到底该先用哪个?

本文深入解析了FPGA设计中Create HDL Wrapper与Generating Output Products的正确使用顺序,帮助新手避免常见错误。详细介绍了两者的功能差异、执行顺序的底层逻辑以及工程管理的最佳实践,特别适合使用Vivado进行FPGA开发的初学者。

xk7n92h3p5m的博客 1065

RMAN(Recovery Manager)入门

1. RMAN环境概述 RMAN(Recovery Manager)是一个Oracle数据库客户端,它在数据库上执行备份和恢复任务和自动管理备份策略。它大大地简化了备份,还原(restore)和恢复(recover)数据库文件。 RMAN环境包括在备份数据时扮演各个角色的工具和数据库。RMAN环境至少包括以下组件: 1)目标(target)数据库,RMAN使用TARGET关键字连接的数据库。目标数据库是RMAN执行备份和恢复操作的数据库。RMAN总是在数据库的控制文件中维护关于它对数据库的操作的元数据(me

jetliu05的博客 1万+

VSCode 1.85便携版安装与配置:解决远程服务器glibc版本过低报错的保姆级指南

本文详细介绍了如何通过VSCode 1.85便携版解决远程服务器glibc版本过低导致的兼容性问题。通过环境隔离和版本固化的方法,开发者可以在老旧服务器上稳定运行Remote-SSH插件,同时保留主力VSCode的配置。文章提供了从下载安装到深度配置的完整指南,帮助用户快速搭建稳定的开发环境。

weixin_30279315的博客 578

052试题60 - recovery

题目: 60. In which of the scenarios will the DBA perform recovery? (Choose all that apply.) A.The alert log file is corrupted. B.A tablespace is accidentally dropped. C.One of the redo log members is c...

文档搬运工 465

解决鸿蒙/荣耀设备GMS应用调试中的“Play保护机制认证”报错

本文详细解析了在鸿蒙或荣耀设备上调试GMS应用时,遇到“Play保护机制认证”报错的根本原因与解决方案。核心在于获取设备的GMS Device ID,并通过Google官方渠道完成临时注册,从而绕过认证限制。文章提供了无需Root和ADB查询两种具体方法,并分享了授权后的故障排查与实战经验,帮助开发者高效解决问题。

weixin_29288313的博客 304

【Unity3D】关于unity中spriteAtlas的打包方案及使用时内存状态的多组对照实验

unity项目中使用精灵(Sprite)为了降低性能消耗,我们可以使用精灵图集(Sprite Atlas)技术,它能够将多个纹理合并成一个大纹理

云渊寒烬 5101

About What Is a DBA?

About What Is a DBA?

u011868279的博客 793

STM32F407 HAL库 LIN DMA 配置实战与汽车电子应用解析

本文详细解析了STM32F407 HAL库在LIN总线通信中的DMA配置实战,重点介绍了硬件自动处理断点检测、同步场和校验和的优势。通过具体代码示例和汽车电子应用案例(如车窗控制、座椅调节),阐述了如何利用DMA提升通信效率与稳定性,为成本敏感的汽车电子系统提供高效解决方案。

study的专栏 968

oracle中dba_开头的表,DBA的工作列表上写了些什么_oracle

数据库管理员(DBA)的主要工作启动和关闭数据库一) 启动数据库$ svrmgrlSVRMGR> connect internal (实例启动)SVRMGR> startuphttp://www.gaodaima.com/31486.htmlDBA的工作列表上写了些什么_oracle二) 关闭数据库$ svrmgrlSVRMGR> connect internalSVRMGR&g...

weixin_28990605的博客 302

详解Linux下使用vscode编译运行和调试C/C++

本教程将具体详解使用Visual Studio Code在Linux环境下使用C/C++方法。 GCC C++ 编译器 (g++) 和 GDB 调试器的方法。 GCC是GNU 编译器集合; GDB 是 GNU 调试器。 1.先决条件 安装Visual Studio Code. 安装C++拓展插件。点击在vscode界面最左侧的Extensions图标(打开快捷键:ctrl+shift+X),搜索“C/C++”,点击进行安装。 确保已安装gcc. 一般ubuntu系统会预装gcc.在终端窗口中输入如下命令

休的博客 6243

VxWorks系列 快速导航

Vxworks系列快速阅览导航

Mr.Sugarcane 1315

开发DBA和产品DBA

开发DBA和产品DBA 区分数据库管理工作的一个有效方法是按照其对应用系统支持工作的类型来划分。我们可以列出DBA的职责,然后用一条线将不同的职能分开,根据区分开的职能,DBA...

congnianmi7492的博客 338

SIMULINK开发项目实例 1000 例专栏之第802例--基于电机驱动系统的场景实例:永磁同步电机(PMSM)在电动汽车中的矢量控制仿真

随着全球对环境保护的关注增加,电动汽车(Electric Vehicle, EV)因其环保特性而受到广泛关注。永磁同步电机(Permanent Magnet Synchronous Motor, PMSM)由于其高效率、高功率密度和良好的动态响应性能,在电动汽车驱动系统中得到了广泛应用。矢量控制(Field-Oriented Control, FOC)是实现PMSM高性能控制的关键技术之一,它能够实现对电机转矩和磁场的独立控制,从而达到类似直流电机的控制效果。本文将详细介绍如何使用来构建一个。

xiaoheshang_123的博客 269

DBA的工作列表上写了些什么

数据库管理员(DBA)的主要工作  启动和关闭数据库  一) 启动数据库  $ svrmgrl  SVRMGR> connect internal (实例启动)  SVRMGR> startup  二) 关闭数据库  $ svrmgrl  SVRMGR> connect internal  SVRMGR> shutdown [immediate/abort]

peter_666的专栏 513

android 蓝牙通讯demo

android 下蓝牙通讯demo,实现了发现蓝牙,使能蓝牙,通过蓝牙发送消息。

Devexpress16.2.6破解工具

Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具

数据库异常状态:Recovery Pending,Suspect,估计Recovery的剩余时间

一,RECOVERY PENDING状态 今天修改了SQL Server的Service Account的密码,然后重启SQL Server的Service,发现有db处于Recovery Pending状态。 Recovery Pending状态是指:数据库在还原(recovery)时遇到跟资源相关的错误,虽然数据库没有损坏,但是文件可能丢失,或者系统资源的限制,导致该数据库不能开始还原进程。...

悦光阴的博客 1440

ssm框架搭建

这个是一个在idea上成功跑起来的ssm框架项目,保存在这里方便以后使用直接下载

[转载]oracle DBA technologies

之前看到一篇文章, 觉得不错! 看了后,发觉自己还有很多地方需要补足, 还需要努力啊!任重而道远!<a onclick="function onclick(){tagshow(event, DBA);}" href="javascript:;" target="_self">DBA需要的技能dba掌握的技术    1.      os : <a onclick="function

wujiandao的专栏 884

IMSL CNL 7.0.0 (x86-64)Crack

IMSL CNL 7.0.0 (x86-64)Crack intel的算法库破解文件,这是C++版本的破解文件

oracle partly available,DBA_ROLLBACK_SEGS entry of status "NEEDS RECOVERY" or "PARTLY AVAILABLE"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~DBA_ROLLBACK_SEGS entry of status "NEEDS RECOVERY"~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~The DBA_ROLLBACK_SEGS view shows a rollback segment as "NEEDS RECOVERY"if ...

weixin_42356475的博客 324

Oracle中,Restore 和 Recovery的区别

在Oracle的备份与恢复的知识点中,经常会出现Restore 和 Recovery两个词。 由于这两个词在字典中的解释很接近,困扰了我很久。直到我在Oracle的官方文档中看到了以下解释。 Restoration:A corrupted data file is overwritten from a backup of the data file .The data file is at a

chagaostu的专栏 1万+

Oracle闪回恢复区(fast recovery area,FRA)

Oracle闪回恢复区(fast recovery area,FRA) 闪回恢复区(Fast Recovery A...

小麦苗DBA宝典 1244

面向dba的linux shell 脚本简介,面向 DBA 的 Linux Shell 脚本简介

面向DBA的Linux Shell脚本简介作者:Casimir Saternos学习一些在Linux上安装、运行和维护Oracle数据库所需的基本bash shell脚本。本文相关下载:2005年11月发表大约7年前,Oracle发布了Linux上的第一个商业数据库。从那时起,Oracle、Red Hat和Novell/SUSE就不断地合作更改Linux内核,从而提高数据库和应用程序的性能。正因为...

weixin_33870147的博客 125

还原真实的 cache recovery

在maclean blog 上看到的这篇文章,很不错,转贴过来,原文链接如下: http://www.oracledatabase12g.com/archives/whats-smon-enabling-cache-recovery.html         我们在学习Oracle基础知识的时候会了解到实例恢复(Instance Recovery)或者说崩溃恢复(Crash

David Dai -- Focus on Oracle 1万+

DBA_2PC_PENDING

DBA_2PC_PENDINGOracle会自动处理分布事务,保证分布事务的一致性,所有站点全部提交或全部回滚。一般情况下,处理过程在很短的时间内完成,根本无法察觉到。但是,如果在commit或rollback的时候,出现了连接...

csqm87956的博客 2736

media recovery oracle,media recovery

以下脚本可以用于收集介质恢复诊断信息(recovery_info.sql):--- begin [recovery_info.sql] ---define spoolfile = &1spool &spoolfilealter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';alter session set timed_...

weixin_39667080的博客 179

60.In which of the scenarios will the DBA perform recovery? (Choose all that apply.)

60.In which of the scenarios will the DBA perform recovery? (Choose all that apply.)

dwj19830118的专栏 857

Oracle维护之日常伪DBA操作

好记性不如烂笔头,本人日常DBA的SQL如下,整理了常用的,创建用户之类的就直接不写了,因为他不算日常的SQL,偶尔用一下,直接通过笔记调出来, -- 查询当前用户所有表 select t.* from user_tables t -- impdp导出 imp xxx/xxx@xxx file=xx_DATA.DMP grants=N tables=tablename1,tablename2 ...

NickWU博客 648

MySQL崩溃恢复神器:innodb_force_recovery 参数详解,DBA 必备!

当 InnoDB 在启动时遇到严重错误(如页损坏、日志不一致、元数据异常等)而无法正常启动时,可通过设置该参数强制跳过某些恢复步骤,让实例以只读模式启动,从而导出关键数据。innodb_force_recovery 取值范围为 0~6,数值越大,跳过的恢复步骤越多,风险也越高。简单说:强制恢复的核心,就是“放弃数据一致性检查,放弃部分日志恢复步骤”,让InnoDB“带病启动”,只为给你留出导出数据的时间。一旦启动成功,立即导出数据!这意味着数据库启动后,可能残留部分未提交事务的数据,存在逻辑不一致。

gjc592的博客 399
上一篇: 每天坚持读10篇以上的技术类文章,一年后,二年后---(注无内容)
下一篇: Monitoring Changes in Your Database Using DDL Triggers
lchstudy
博客等级 码龄20年 6粉丝 10原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值