未备份归档日志导致数据丢失的实验

未备份归档日志导致数据丢失的实验

SQL>
SQL>truncate table test;

Table truncated.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
         0

SQL>conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> insert into peak.test select * from scott.emp;

14 rows created.

SQL> insert into peak.test select * from scott.emp;

14 rows created.

SQL> insert into peak.test select * from peak.test;

28 rows created.

SQL> insert into peak.test select * from peak.test;

56 rows created.

SQL> insert into peak.test select * from peak.test;

112 rows created.

SQL>commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> show user;
USER is "SYS"
SQL>
SQL>insert into peak.test select * from peak.test;

224 rows created.

SQL>insert into peak.test select * from peak.test;

448 rows created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node51 ~]$ rman  target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri May 18 08:16:44 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: PEAK (DBID=1833158544)

RMAN> backup database;

Starting backup at 18-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=308 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/db/oracle10g/oradata/peak/system01.dbf
input datafile fno=00002 name=/db/oracle10g/oradata/peak/undotbs01.dbf
input datafile fno=00003 name=/db/oracle10g/oradata/peak/sysaux01.dbf
input datafile fno=00005 name=/db/oracle10g/oradata/test/test01.dbf
input datafile fno=00004 name=/db/oracle10g/oradata/peak/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-MAY-12
channel ORA_DISK_1: finished piece 1 at 18-MAY-12
piece handle=/data/backup/PEAK_1_1_91 tag=TAG20120518T081651 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 18-MAY-12

Starting Control File and SPFILE Autobackup at 18-MAY-12
piece handle=/data/backup/c-1833158544-20120518-0c comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAY-12

RMAN> backup archivelog all delete input;

Starting backup at 18-MAY-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=105 stamp=783591308
input archive log thread=1 sequence=2 recid=106 stamp=783591355
input archive log thread=1 sequence=3 recid=107 stamp=783591393
input archive log thread=1 sequence=4 recid=108 stamp=783591524
channel ORA_DISK_1: starting piece 1 at 18-MAY-12
channel ORA_DISK_1: finished piece 1 at 18-MAY-12
piece handle=/data/backup/PEAK_1_1_93 tag=TAG20120518T081844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/db/oracle10g/archive/1_1_783582290.arc recid=105 stamp=783591308
archive log filename=/db/oracle10g/archive/1_2_783582290.arc recid=106 stamp=783591355
archive log filename=/db/oracle10g/archive/1_3_783582290.arc recid=107 stamp=783591393
archive log filename=/db/oracle10g/archive/1_4_783582290.arc recid=108 stamp=783591524
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=104 stamp=783582290
input archive log thread=1 sequence=4 recid=102 stamp=783582290
input archive log thread=1 sequence=5 recid=103 stamp=783582290
channel ORA_DISK_1: starting piece 1 at 18-MAY-12
channel ORA_DISK_1: finished piece 1 at 18-MAY-12
piece handle=/data/backup/PEAK_1_1_94 tag=TAG20120518T081844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/db/oracle10g/archive/1_3_783578257.dbf recid=104 stamp=783582290
archive log filename=/db/oracle10g/archive/1_4_783578257.dbf recid=102 stamp=783582290
archive log filename=/db/oracle10g/archive/1_5_783578257.dbf recid=103 stamp=783582290
Finished backup at 18-MAY-12

Starting Control File and SPFILE Autobackup at 18-MAY-12
piece handle=/data/backup/c-1833158544-20120518-0d comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAY-12

RMAN> exit


Recovery Manager complete.
[oracle@node51 ~]$ sqlplus /  as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 18 08:19:17 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>select count(*) from peak.test;

  COUNT(*)
----------
       896

SQL>
SQL>insert into peak.test select * from peak.test;

896 rows created.

SQL>insert into peak.test select * from peak.test;

1792 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from peak.test;

  COUNT(*)
----------
      3584

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node51 ~]$ cd /db/oracle10g/oradata/
[oracle@node51 oradata]$ ls
archive  peak  test
[oracle@node51 oradata]$ cd peak/
[oracle@node51 peak]$ls
control01.ctl  control02.ctl  control03.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@node51 peak]$ ls -al
总计 1381344
drwxr-x--- 2 oracle oinstall      4096 05-18 05:43 .
drwxr-x--- 5 oracle oinstall      4096 05-14 13:51 ..
-rw-r----- 1 oracle oinstall   7225344 05-18 08:21 control01.ctl
-rw-r----- 1 oracle oinstall   7225344 05-18 08:21 control02.ctl
-rw-r----- 1 oracle oinstall   7225344 05-18 08:21 control03.ctl
-rw-r----- 1 oracle oinstall  52429312 05-18 08:18 redo01.log
-rw-r----- 1 oracle oinstall  52429312 05-18 08:20 redo02.log
-rw-r----- 1 oracle oinstall  52429312 05-18 08:16 redo03.log
-rw-r----- 1 oracle oinstall 272637952 05-18 08:20 sysaux01.dbf
-rw-r----- 1 oracle oinstall 524296192 05-18 08:20 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 05-18 05:50 temp01.dbf
-rw-r----- 1 oracle oinstall 429924352 05-18 08:20 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 05-18 08:20 users01.dbf
[oracle@node51 peak]$ rm -f control0*
[oracle@node51 peak]$ ls
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@node51 peak]$ rm -f sys*
sysaux01.dbf  system01.dbf  
[oracle@node51 peak]$rm -f sys*
[oracle@node51 peak]$ ls -al
总计 581116
drwxr-x--- 2 oracle oinstall      4096 05-18 08:21 .
drwxr-x--- 5 oracle oinstall      4096 05-14 13:51 ..
-rw-r----- 1 oracle oinstall  52429312 05-18 08:18 redo01.log
-rw-r----- 1 oracle oinstall  52429312 05-18 08:20 redo02.log
-rw-r----- 1 oracle oinstall  52429312 05-18 08:16 redo03.log
-rw-r----- 1 oracle oinstall  20979712 05-18 05:50 temp01.dbf
-rw-r----- 1 oracle oinstall 429924352 05-18 08:20 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 05-18 08:20 users01.dbf
[oracle@node51 peak]$
[oracle@node51 peak]$
[oracle@node51 peak]$ rm -f redo0*
[oracle@node51 peak]$ ls
temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@node51 peak]$ rm -f users01.dbf
[oracle@node51 peak]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 18 08:22:10 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  473956352 bytes
Fixed Size                  1268148 bytes
Variable Size             364906060 bytes
Database Buffers          104857600 bytes
Redo Buffers                2924544 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node51 peak]$  rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri May 18 08:22:37 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: peak (not mounted)

RMAN>restore controlfile from '/data/backup/c-1833158544-20120518-0d';

Starting restore at 18-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=321 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/db/oracle10g/oradata/peak/control01.ctl
output filename=/db/oracle10g/oradata/peak/control02.ctl
output filename=/db/oracle10g/oradata/peak/control03.ctl
Finished restore at 18-MAY-12

RMAN>alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>restore database;

Starting restore at 18-MAY-12
Starting implicit crosscheck backup at 18-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=321 devtype=DISK
Crosschecked 11 objects
Finished implicit crosscheck backup at 18-MAY-12

Starting implicit crosscheck copy at 18-MAY-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-MAY-12

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /db/oracle10g/oradata/peak/system01.dbf
restoring datafile 00002 to /db/oracle10g/oradata/peak/undotbs01.dbf
restoring datafile 00003 to /db/oracle10g/oradata/peak/sysaux01.dbf
restoring datafile 00004 to /db/oracle10g/oradata/peak/users01.dbf
restoring datafile 00005 to /db/oracle10g/oradata/test/test01.dbf
channel ORA_DISK_1: reading from backup piece /data/backup/PEAK_1_1_91
channel ORA_DISK_1: restored backup piece 1
piece handle=/data/backup/PEAK_1_1_91 tag=TAG20120518T081651
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 18-MAY-12

RMAN> recover database;

Starting recover at 18-MAY-12
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /data/backup/PEAK_1_1_93
channel ORA_DISK_1: restored backup piece 1
piece handle=/data/backup/PEAK_1_1_93 tag=TAG20120518T081844
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/db/oracle10g/archive/1_4_783582290.arc thread=1 sequence=4
unable to find archive log
archive log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/18/2012 08:24:51
RMAN-06054: media recovery requesting unknown log: thread 1 seq 5 lowscn 1487610

RMAN>

RMAN>restore archivelog from logseq 1 until logseq 4;

Starting restore at 18-MAY-12
using channel ORA_DISK_1

archive log thread 1 sequence 4 is already on disk as file /db/oracle10g/archive/1_4_783582290.arc
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_DISK_1: reading from backup piece /data/backup/PEAK_1_1_93
channel ORA_DISK_1: restored backup piece 1
piece handle=/data/backup/PEAK_1_1_93 tag=TAG20120518T081844
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 18-MAY-12

RMAN> recover database;

Starting recover at 18-MAY-12
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/18/2012 08:28:27
RMAN-06054: media recovery requesting unknown log: thread 1 seq 5 lowscn 1487610

RMAN>

RMAN> alter database open resetlogs;

database opened

RMAN>

RMAN>exit


Recovery Manager complete.
[oracle@node51 peak]$sqlplus  / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 18 08:34:20 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from peak.test;

  COUNT(*)
----------
       896

所以，备份数据库非常重要，备份归档日志同样重要。

更多参考：

the specified nodes are not clusterable

根据rowid删除表中重复的行

Agent process exited abnormally during initialization

一次字符乱码的解决过程

rman实验（一）

rman实验（二）

ORA-00600: internal error code, arguments: [keltnf

ORA-00600: ORA-12012 ORA-08102解决

linux下完全删除oracle

INFO: /usr/bin/ld: crt1.o: No such file: No such file or directory

centos4.8_64上安装oracle10201建库报ORA-12547

EM乱码解决

ORA-31613 Master process DM00 failed during startup

ORA-00600: internal error code, arguments: [4194], [29], [27], [], [], [], [], []

ORA-24324 ORA-01041 ORA-03113

centos5.3升级oracle

pdksh-5.2.14-36.el5.i386.rpm

使用rman进行数据库迁移

oracle10.2.0.1升级到10.2.0.4报错

Upgrade Oracle 10g from 10.2.0.1 to 10.2.0.4 (Single Instance)

改oracle的name和dbid

修改oracle实例名

Solaris8上迁移oracle8i---上

Solaris8上迁移oracle8i---下

未备份归档日志导致数据丢失的实验

使用NBU进行数据库迁移

catalog备份数据库

RMAN FORMAT字符串格式化

Error: can not register my instance state - -1

not all alterations performed

The ASM instance configured on the local node is a single-instance ASM

/u01/crs102/bin/crsctl.bin: error while loading shared libraries: libstdc++.so.5: cannot open shared

Initializing the Oracle ASMLib driver: [FAILED]

ORA-00245: control file backup operation failed

WARNING: failed to read mirror side 1 of virtual extent 229 logical extent

模拟恢复参数文件

Interface eth0 checked failed

import server uses ZHS16GBK character set (possible charset conversion)