oracle: ocp题解与实验(7/205) .

最新推荐文章于 2011-12-15 11:20:48 发布
最新推荐文章于 2011-12-15 11:20:48 发布
阅读量990 收藏
点赞数
分类专栏： oracle与c++开发 文章标签： oracle database thread centos system file
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lzx_bupt/article/details/6946892
版权


 题目：

7. In your database, online redo log files are multiplexed and one of the
members in a group is lost due to media failure?
How would you recover the lost redo log member?
A.import the database from the last export
B.restore all the members in the group from the last backup
C.drop the lost member from the database and then add a new member to the
group
D.restore all the database files from the backup and then perform a
complete recovery
E.restore all the database files from the backup and then perform an
incomplete recovery
Answer: C

题目翻译：数据库中online redo log 是multiplexed的，一个group其中的一个member发生media failure，怎么恢复呢？

答案解释：一个group做了multiplex，那么就有多个member，这些member是完全相同的，所以先drop掉坏的再add一个新的就可以了。如下操作：

第一步：模拟删除正在运行中的数据库的在线日志文件；

[oracle@centos oradata]$ ll
total 1134676
-rw-r----- 1 oracle oinstall  31465472 Nov  8 06:15 app101.dbf
-rw-r----- 1 oracle oinstall   9846784 Nov  9 05:47 ora_control1
-rw-r----- 1 oracle oinstall   9846784 Nov  9 05:47 ora_control2
-rw-r----- 1 oracle oinstall  52429312 Nov  9 05:46 redo01a.log
-rw-r----- 1 oracle oinstall  52429312 Nov  9 05:46 redo01b.log
-rw-r----- 1 oracle oinstall  52429312 Nov  9 05:37 redo02a.log
-rw-r----- 1 oracle oinstall  52429312 Nov  9 05:37 redo02b.log
-rw-r----- 1 oracle oinstall 209723392 Nov  9 05:43 sysaux01.dbf
-rw-r----- 1 oracle oinstall 270344192 Nov  9 05:43 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Nov  8 04:54 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Nov  9 05:43 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Nov  9 05:37 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Oct 15 08:04 users01.dbf.bak
[oracle@centos oradata]$ rm redo02b.log 
[oracle@centos oradata]$ ll
total 1083472
-rw-r----- 1 oracle oinstall  31465472 Nov  8 06:15 app101.dbf
-rw-r----- 1 oracle oinstall   9846784 Nov  9 05:48 ora_control1
-rw-r----- 1 oracle oinstall   9846784 Nov  9 05:48 ora_control2
-rw-r----- 1 oracle oinstall  52429312 Nov  9 05:48 redo01a.log
-rw-r----- 1 oracle oinstall  52429312 Nov  9 05:48 redo01b.log
-rw-r----- 1 oracle oinstall  52429312 Nov  9 05:37 redo02a.log
-rw-r----- 1 oracle oinstall 209723392 Nov  9 05:47 sysaux01.dbf
-rw-r----- 1 oracle oinstall 270344192 Nov  9 05:47 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Nov  8 04:54 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Nov  9 05:47 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Nov  9 05:37 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 Oct 15 08:04 users01.dbf.bak
[oracle@centos oradata]$

第二步：日志中发现错误，因为少了一个member

执行几次日志切换，以便于在日志中找到问题

SQL> startup open;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2217632 bytes
Variable Size             503318880 bytes
Database Buffers          289406976 bytes
Redo Buffers                6758400 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL>

查看alert_mercury.log（mercury是我数据库的SID）会发现如下错误，但数据库正确运行是没有任何影响：

Errors in file /oracle/mercury/diag/diag/rdbms/mercury/mercury/trace/mercury_arc0_1910.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oracle/mercury/oradata/redo02b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /oracle/mercury/diag/diag/rdbms/mercury/mercury/trace/mercury_arc0_1910.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oracle/mercury/oradata/redo02b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Archived Log entry 22 added for thread 1 sequence 2 ID 0x212ba1b7 dest 1:
Wed Nov 09 05:49:54 2011
Errors in file /oracle/mercury/diag/diag/rdbms/mercury/mercury/trace/mercury_m000_2014.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oracle/mercury/oradata/redo02b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Checker run found 1 new persistent data failures

第三步：drop并add对应的member

SQL> desc v$logfile;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP#                                             NUMBER
 STATUS                                             VARCHAR2(7)
 TYPE                                               VARCHAR2(7)
 MEMBER                                             VARCHAR2(513)
 IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

SQL> col member for a30
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         2         ONLINE  /oracle/mercury/oradata/redo02 NO
                           a.log

         2         ONLINE  /oracle/mercury/oradata/redo02 NO
                           b.log

         1         ONLINE  /oracle/mercury/oradata/redo01 NO
                           a.log

         1         ONLINE  /oracle/mercury/oradata/redo01 NO
                           b.log

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---


SQL> ALTER DATABASE DROP LOGFILE MEMBER '/oracle/mercury/oradata/redo02b.log'
  2  ;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- ------------------------------ ---
         2         ONLINE  /oracle/mercury/oradata/redo02 NO
                           a.log

         1         ONLINE  /oracle/mercury/oradata/redo01 NO
                           a.log

         1         ONLINE  /oracle/mercury/oradata/redo01 NO
                           b.log


SQL> col member for a40
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oracle/mercury/oradata/redo02b.log' TO GROUP 2;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         2         ONLINE  /oracle/mercury/oradata/redo02a.log      NO
         2 INVALID ONLINE  /oracle/mercury/oradata/redo02b.log      NO
         1         ONLINE  /oracle/mercury/oradata/redo01a.log      NO
         1         ONLINE  /oracle/mercury/oradata/redo01b.log      NO

SQL>

Note:

Fully specify the filenames of new log members to indicate where the operating system files should be created. Otherwise, the files will be created in either the default or current directory of the database server, depending upon your operating system. You may also note that the status of the new log member is shown asINVALID. This is normal and it will change to active (blank) when it is first used.

至此就可以使在线日志文件恢复到正常状态了；

SQL> alter system switch logfile;

System altered.

SQL> /
 
System altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         2         ONLINE  /oracle/mercury/oradata/redo02a.log      NO
         2         ONLINE  /oracle/mercury/oradata/redo02b.log      NO
         1         ONLINE  /oracle/mercury/oradata/redo01a.log      NO
         1         ONLINE  /oracle/mercury/oradata/redo01b.log      NO

SQL>

alert_mercury.log也不提示错误了

Wed Nov 09 06:06:24 2011
Thread 1 advanced to log sequence 4 (LGWR switch)
  Current log# 2 seq# 4 mem# 0: /oracle/mercury/oradata/redo02a.log
  Current log# 2 seq# 4 mem# 1: /oracle/mercury/oradata/redo02b.log
Wed Nov 09 06:06:24 2011
Archived Log entry 23 added for thread 1 sequence 3 ID 0x212ba1b7 dest 1:
Thread 1 cannot allocate new log, sequence 5
Checkpoint not complete
  Current log# 2 seq# 4 mem# 0: /oracle/mercury/oradata/redo02a.log
  Current log# 2 seq# 4 mem# 1: /oracle/mercury/oradata/redo02b.log
Thread 1 advanced to log sequence 5 (LGWR switch)
  Current log# 1 seq# 5 mem# 0: /oracle/mercury/oradata/redo01a.log
  Current log# 1 seq# 5 mem# 1: /oracle/mercury/oradata/redo01b.log
Wed Nov 09 06:06:29 2011
Archived Log entry 24 added for thread 1 sequence 4 ID 0x212ba1b7 dest 1:



 

 

 

Dojo的频道
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle: ocp题解与实验(3/205)
懒猫会打盹的专栏
11-07 1501
题目： 3.One of the tablespace is read-only in your database. The loss of all control file forced you to recreate the control file. Which operation do you need to perform after re-creating the control
oracle: ocp题解与实验(11/205) .
懒猫会打盹的专栏
11-09 937
题目： 11. You work as a database administrator at Certkiller .com. Your database is open and running in ARCHIVELOG mode. You take RMAN full backups every Sunday night. On Monday morning, while query
oracle: ocp题解与实验(10/205) .
懒猫会打盹的专栏
11-09 1062
题目： 10. The current time zone for one of the user sessions is set to the database local time zone. For one application, the user session requires the time zone to be set to the local operating sy
oracle: ocp题解与实验(2/205) .
懒猫会打盹的专栏
11-07 1157
题目： 看exhibit 2. In your production database, the total waits and the time waited for log file parallel write are significantly high. While investigating the reason, you find that there are three
oracle: ocp题解与实验(15/205)
懒猫会打盹的专栏
11-10 879
题目： 15. You work as a database administrator at Certkiller .com. In your production database there is a job, CALC_STAT, which has been scheduled to run every Friday at 5.00 p.m. CALC_STAT updates
oracle: ocp题解与实验(1/205)
懒猫会打盹的专栏
11-04 1943
从今天起，想边学习ocp课程，边做实验记录生活的点点滴滴。理论与动手两不误才是学习的硬道理。万事开头难，希望自己走好这第一步； 简单说下实验方法：因为面向ocp，所以我直接引用考题作为实验目标，充分认识ocp所传授的知识，认识选项的正确、错误之处；开始吧！ 题目： 1. You observe that a database performance has degraded over a p
oracle: ocp题解与实验(5/205)
懒猫会打盹的专栏
11-08 1702
题目： 5. You are performing a block media recovery on the tools01.dbf data file in the SALES database using RMAN. Which two statements are correct in this scenario? (Choose two.) A. You must ensure
oracle: ocp题解与实验(14/205)
懒猫会打盹的专栏
11-10 1015
题目： 14. Consider the following configuration: /devices/D1 is a member of disk group dgroupA. /devices/D2 is a member of disk group dgroupA. /devices/D3 is a member of disk group dgroupA. You plan
oracle: ocp题解与实验(4/205)
懒猫会打盹的专栏
11-07 1067
题目： 4. You have set some of the initialization parameters as: DB_BLOCK_SIZE=8KB SGA_MAX_SIZE=2GB SGA_TARGET =0 SHARED_POOL_SIZE=120MB DB_CHCHE_SIZE=896MB STREAM_POOL_SIZE=0 LARGE_POOL_SIZE=110
解决fatal:remote error:You can’t push to git://github.com/username/*.git问题的办法
01-10
解决fatal:remote error:You can’t push to git://github.com/username/*.git问题的办法 今天Git push的时候 fatal:remote error: You can't push to git://github....看来我是没有权限push啊。 解决方法： ...接
Oracle 19c OCP 082 试题带翻译-2022.5.2更新
05-16
Oracle数据库是世界上最流行的关系型数据库管理系统之一，尤其在企业级应用中广泛应用。本文将深入探讨在Oracle 19c数据库中关于节省空间功能、视图特性和查询优化的知识点。 首先，我们关注节省空间的功能。在...
ORACLE OCP 19C 电子档教材 eKits.zip
08-05
ZIP文件打开方法 1、选中压缩文件，鼠标右键找到“解压文件”的按钮，随即会弹出一个“解压路径和选项”的窗口。 2、设置一下更新方式、覆盖方式以及解压文件的存放位置等。 3、设置完成之后，点击一下“确定”就...
Oracle12c OCP视频.zip
06-15
目录网盘文件永久链接 第1天 01.mp4 02.mp4 03.mp4 【12c-安装篇】Oracle 12c删除数据库.pdf 【12c-安装篇】Oracle 12c利用DBCA创建数据库.pdf 【12c-安装篇】Oracle 12c软件安装.pdf ...实验.......
Oracle 19c OCP讲义目录
最新发布
02-06
Oracle 19c OCP讲义目录
c/c++: 多线程编程基础讲解（一）
热门推荐
懒猫会打盹的专栏
10-27 3万+
利用几篇文章简单记录下c与c++多线程编程基础入门的东西，每篇一个程序来记录，备忘。这些章节是由浅入深组织的，赖死狗！ 在注释中讲吧，佛楼米！ #include #include //头文件是必须的，符合posix标准使程序可移植众多平台 using namespace std; #define NUM_THREADS 5 void* say_hello(void* ar
c/c++： 读取配置文件（文本）内容的简单实现
懒猫会打盹的专栏
12-15 3万+
引：读取配置文件是很常见的，尤其在后台服务器（linux、unix上）跑程序为了扩展性，都常用配置文件作为程序的输入来完成不同的工作内容。本文提供了简单的实现方法： 话不多说，看函数： /* *parameter: cfgfilepath 文件的绝对路径名如: /user/home/my.cfg * key 文本中的变量名 *
oracle: ORA-32102: invalid OCI handle
懒猫会打盹的专栏
08-09 2万+
ORA-32102: invalid OCI handle  Cause: An invalid OCI handle is passed to an OCI call.  Action: This is an internal OCCI Error. Please cont
linux: 虚拟机使用NAT方式连网《不会你抽我系列》
懒猫会打盹的专栏
09-23 1万+
闲来无事，扯点皮，详细说下NAT配置过程 NAT全称Network Address Translation网络地址转换，顾名思义，配置的重点也是地址转换。 步骤1、配置局域网段及网关 打开vmware的visual network editor，设置成下图样式： 图示
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值