Again, one of application testing people came to me and ask "How could we find character in string?". Well i suddenly remember there is one new SQL function in Oracle 10g. Lets take a look the example
vd@bahamut> select postal from t2;
POSTAL
----------
11470
11520
15880
13963
11788
11880
15576
1190a
1A7I1
11ao2
16502
15570
16890
I99it
We have 4 rows that contains character in postal code column. How do we find this 4 rows?
vd@bahamut> select postal from t2 WHERE REGEXP_LIKE (postal, '[a-zA-Z]');
POSTAL
----------
1190a
1A7I1
11ao2
I99it
yes, the answer is SQL new function REGEXP_LIKE.
[a-zA-Z] is to find from 'a' to 'z' and 'A' to 'Z'.
remember that this only work in Oracle 10g and future release
Hope this article will help you
Cheers
FZheng
Thursday, February 28, 2008
Wednesday, February 20, 2008
How about if my current redolog is lost?
Some of people might be not aware of losing current redolog and some other might be thinking 'No, We won't be able to do recovery if current redolog is lost?. So the question can we do or can't we do?
Actually yes we can do recover and depends on your database is closed consistently or not. If your database is not closed consistently then you need to restore from backup and perform point in time recovery (you should run your database in archivelog mode) and there might be lost transaction.
Here i did a testing that database is not closed consistently (i presumed i have lost everthing, i mean the box)
First restore from backup, here i used RMAN
RMAN> run {
2> allocate channel ch_1 type disk;
3> restore controlfile to '/backup/restore/dbvdasm/control01.ctl';
4> set newname for datafile '/backup/restore/dbvd/sysaux01.dbf' to '/backup/restore/dbvdasm/sysaux01.dbf';
5> set newname for datafile '/backup/restore/dbvd/system01.dbf' to '/backup/restore/dbvdasm/system01.dbf';
6> set newname for datafile '/backup/restore/dbvd/test1.dbf' to '/backup/restore/dbvdasm/test1.dbf';
7> set newname for datafile '/backup/restore/dbvd/undotbs01.dbf' to '/backup/restore/dbvdasm/undotbs01.dbf';
8> set newname for datafile '/backup/restore/dbvd/users01.dbf' to '/backup/restore/dbvdasm/users01.dbf';
9> restore database;
10> set archivelog destination to '/backup/restore/dbvdasm/';
11> restore archivelog all;
12> release channel ch_1;
13> }
allocated channel: ch_1
channel ch_1: sid=142 devtype=DISK
Starting restore at 20-FEB-08
channel ch_1: starting datafile backupset restore
channel ch_1: restoring control file
output filename=/backup/restore/dbvdasm/control01.ctl
channel ch_1: reading from backup piece /backup/restore/df_0mj96lbf_1_1.DBVD.bus
channel ch_1: restored backup piece 1
piece handle=/backup/restore/df_0mj96lbf_1_1.DBVD.bus tag=TAG20080220T145750
channel ch_1: restore complete, elapsed time: 00:00:03
Finished restore at 20-FEB-08
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-FEB-08
channel ch_1: starting datafile backupset restore
channel ch_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /backup/restore/dbvdasm/system01.dbf
restoring datafile 00002 to /backup/restore/dbvdasm/undotbs01.dbf
restoring datafile 00003 to /backup/restore/dbvdasm/sysaux01.dbf
restoring datafile 00004 to /backup/restore/dbvdasm/users01.dbf
restoring datafile 00005 to /backup/restore/dbvdasm/test1.dbf
channel ch_1: reading from backup piece /backup/restore/df_0lj96lbe_1_1.DBVD.bus
channel ch_1: restored backup piece 1
piece handle=/backup/restore/df_0lj96lbe_1_1.DBVD.bus tag=TAG20080220T145750
channel ch_1: restore complete, elapsed time: 00:00:08
Finished restore at 20-FEB-08
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 20-FEB-08
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=10
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=17
channel ch_1: restoring archive log
archive log thread=1 sequence=18
channel ch_1: restoring archive log
archive log thread=1 sequence=19
channel ch_1: restoring archive log
archive log thread=1 sequence=20
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=11
channel ch_1: restoring archive log
archive log thread=1 sequence=12
channel ch_1: restoring archive log
archive log thread=1 sequence=13
channel ch_1: restoring archive log
archive log thread=1 sequence=14
channel ch_1: restoring archive log
archive log thread=1 sequence=15
channel ch_1: restoring archive log
archive log thread=1 sequence=16
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=21
RMAN>
At this point we have a restored of controlfile, datafiles and archives but remember we still don't have redolog at this moment.
ora10g@bahamut:/backup/restore/dbvdasm> ls -arlt
total 1320552
drwxr-xr-x 4 ora10g dba 4096 Feb 20 14:57 ..
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_31_647005853.dbf
-rw-r----- 1 ora10g dba 287744 Feb 20 15:05 1_30_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_29_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_28_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_27_647005853.dbf
-rw-r----- 1 ora10g dba 6144 Feb 20 15:05 1_26_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_25_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_24_647005853.dbf
-rw-r----- 1 ora10g dba 3950592 Feb 20 15:05 1_23_647005853.dbf
-rw-r----- 1 ora10g dba 391168 Feb 20 15:05 1_22_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_32_647005853.dbf
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:05 .
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:12 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:12 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:12 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:12 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:12 sysaux01.dbf
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:12 control01.ctl
Now go to sqlplus and change the location of datafiles and redologs to new location
ora10g@bahamut:/home/ora10g/10.2/dbs> sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 20 15:08:02 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to an idle instance.
SYS@dbvd> startup mount;
ORACLE instance started.
Total System Global Area 922746880 bytes
Fixed Size 2075568 bytes
Variable Size 494928976 bytes
Database Buffers 419430400 bytes
Redo Buffers 6311936 bytes
Database mounted.
SYS@dbvd> alter database rename file '/backup/restore/dbvd/sysaux01.dbf' to '/backup/restore/dbvdasm/sysaux01.dbf';
Database altered.
----------------------------------some more output here-----------------------------------------------------------
Next we need to recover the database
SYS@dbvd> recover database using backup controlfile until cancel;
ORA-00279: change 214846 generated at 02/20/2008 14:57:50 needed for thread 1
ORA-00289: suggestion : /backup/restore/dbvdasm/1_32_647005853.dbf
ORA-00280: change 214846 for thread 1 is in sequence #32
Specify log: {=suggested | filename | AUTO | CANCEL}
/backup/restore/dbvdasm/1_32_647005853.dbf
ORA-00279: change 214850 generated at 02/20/2008 14:57:52 needed for thread 1
ORA-00289: suggestion : /backup/restore/dbvdasm/1_33_647005853.dbf
ORA-00280: change 214850 for thread 1 is in sequence #33
ORA-00278: log file '/backup/restore/dbvdasm/1_32_647005853.dbf' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SYS@dbvd> alter database open resetlogs;
Database altered.
SYS@dbvd> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
Last but not least don't forget to re-create temporary tablespace
ora10g@bahamut:/backup/restore/dbvdasm> ls -arlt
total 1525368
drwxr-xr-x 4 ora10g dba 4096 Feb 20 14:57 ..
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_31_647005853.dbf
-rw-r----- 1 ora10g dba 287744 Feb 20 15:05 1_30_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_29_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_28_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_27_647005853.dbf
-rw-r----- 1 ora10g dba 6144 Feb 20 15:05 1_26_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_25_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_24_647005853.dbf
-rw-r----- 1 ora10g dba 3950592 Feb 20 15:05 1_23_647005853.dbf
-rw-r----- 1 ora10g dba 391168 Feb 20 15:05 1_22_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_32_647005853.dbf
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:13 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:13 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:13 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:13 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:13 sysaux01.dbf
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:13 redo01.log
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:13 .
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:13 redo02.log
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:13 control01.ctl
But how about if we did shutdown database and suddendly current redolog is corrupt or lost? For this we do not need to perform restore from backup
SYS@dbvd> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@dbvd> startup mount;
ORACLE instance started.
Total System Global Area 922746880 bytes
Fixed Size 2075568 bytes
Variable Size 494928976 bytes
Database Buffers 419430400 bytes
Redo Buffers 6311936 bytes
Database mounted.
SYS@dbvd> select status, group#, members from v$log;
STATUS GROUP# MEMBERS
---------------- ---------- ----------
CURRENT 1 1
INACTIVE 3 1
INACTIVE 2 1
ora10g@bahamut:/backup/restore/dbvd> ls -a redo01*
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:38 redo01.log
ora10g@bahamut:/backup/restore/dbvd> mv redo01.log ../
ora10g@bahamut:/backup/restore/dbvd> ls -arlt
total 1680960
drwxr-xr-x 6 ora10g dba 256 Feb 18 11:46 dump
-rw-r----- 1 ora10g dba 20979712 Feb 18 12:00 temp01.dbf
-rw-r----- 1 ora10g dba 52429312 Feb 18 12:34 redo03.log
-rw-r--r-- 1 ora10g dba 723 Feb 20 09:29 sqlnet.log
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:37 redo02.log
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:37 redo_03.log
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:37 arc
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:38 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:38 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:38 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:38 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:38 sysaux01.dbf
drwxr-xr-x 4 ora10g dba 4096 Feb 20 15:39 ..
drwxr-xr-x 4 ora10g dba 4096 Feb 20 15:39 .
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control03.ctl
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control02.ctl
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control01.ctl
As we can see our redo01.log is gone
SYS@dbvd> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
Oppss we need to perform point in time recovery
SYS@dbvd> recover database until cancel;
Media recovery complete.
SYS@dbvd> alter database open resetlogs;
Database altered.
SYS@dbvd> select status, group#, members from v$log;
STATUS GROUP# MEMBERS
---------------- ---------- ----------
CURRENT 1 1
UNUSED 2 1
UNUSED 3 1
ora10g@bahamut:/backup/restore/dbvd> ls -a redo01*
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:40 redo01.log
Yes, Thanks GOD our database are back now and we can see the redologs are back too. Moral of the story, always make sure you have a valid and good backup
Have a nice day
Cheers
FZheng
Actually yes we can do recover and depends on your database is closed consistently or not. If your database is not closed consistently then you need to restore from backup and perform point in time recovery (you should run your database in archivelog mode) and there might be lost transaction.
Here i did a testing that database is not closed consistently (i presumed i have lost everthing, i mean the box)
First restore from backup, here i used RMAN
RMAN> run {
2> allocate channel ch_1 type disk;
3> restore controlfile to '/backup/restore/dbvdasm/control01.ctl';
4> set newname for datafile '/backup/restore/dbvd/sysaux01.dbf' to '/backup/restore/dbvdasm/sysaux01.dbf';
5> set newname for datafile '/backup/restore/dbvd/system01.dbf' to '/backup/restore/dbvdasm/system01.dbf';
6> set newname for datafile '/backup/restore/dbvd/test1.dbf' to '/backup/restore/dbvdasm/test1.dbf';
7> set newname for datafile '/backup/restore/dbvd/undotbs01.dbf' to '/backup/restore/dbvdasm/undotbs01.dbf';
8> set newname for datafile '/backup/restore/dbvd/users01.dbf' to '/backup/restore/dbvdasm/users01.dbf';
9> restore database;
10> set archivelog destination to '/backup/restore/dbvdasm/';
11> restore archivelog all;
12> release channel ch_1;
13> }
allocated channel: ch_1
channel ch_1: sid=142 devtype=DISK
Starting restore at 20-FEB-08
channel ch_1: starting datafile backupset restore
channel ch_1: restoring control file
output filename=/backup/restore/dbvdasm/control01.ctl
channel ch_1: reading from backup piece /backup/restore/df_0mj96lbf_1_1.DBVD.bus
channel ch_1: restored backup piece 1
piece handle=/backup/restore/df_0mj96lbf_1_1.DBVD.bus tag=TAG20080220T145750
channel ch_1: restore complete, elapsed time: 00:00:03
Finished restore at 20-FEB-08
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-FEB-08
channel ch_1: starting datafile backupset restore
channel ch_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /backup/restore/dbvdasm/system01.dbf
restoring datafile 00002 to /backup/restore/dbvdasm/undotbs01.dbf
restoring datafile 00003 to /backup/restore/dbvdasm/sysaux01.dbf
restoring datafile 00004 to /backup/restore/dbvdasm/users01.dbf
restoring datafile 00005 to /backup/restore/dbvdasm/test1.dbf
channel ch_1: reading from backup piece /backup/restore/df_0lj96lbe_1_1.DBVD.bus
channel ch_1: restored backup piece 1
piece handle=/backup/restore/df_0lj96lbe_1_1.DBVD.bus tag=TAG20080220T145750
channel ch_1: restore complete, elapsed time: 00:00:08
Finished restore at 20-FEB-08
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 20-FEB-08
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=10
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=17
channel ch_1: restoring archive log
archive log thread=1 sequence=18
channel ch_1: restoring archive log
archive log thread=1 sequence=19
channel ch_1: restoring archive log
archive log thread=1 sequence=20
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=11
channel ch_1: restoring archive log
archive log thread=1 sequence=12
channel ch_1: restoring archive log
archive log thread=1 sequence=13
channel ch_1: restoring archive log
archive log thread=1 sequence=14
channel ch_1: restoring archive log
archive log thread=1 sequence=15
channel ch_1: restoring archive log
archive log thread=1 sequence=16
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=21
RMAN>
At this point we have a restored of controlfile, datafiles and archives but remember we still don't have redolog at this moment.
ora10g@bahamut:/backup/restore/dbvdasm> ls -arlt
total 1320552
drwxr-xr-x 4 ora10g dba 4096 Feb 20 14:57 ..
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_31_647005853.dbf
-rw-r----- 1 ora10g dba 287744 Feb 20 15:05 1_30_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_29_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_28_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_27_647005853.dbf
-rw-r----- 1 ora10g dba 6144 Feb 20 15:05 1_26_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_25_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_24_647005853.dbf
-rw-r----- 1 ora10g dba 3950592 Feb 20 15:05 1_23_647005853.dbf
-rw-r----- 1 ora10g dba 391168 Feb 20 15:05 1_22_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_32_647005853.dbf
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:05 .
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:12 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:12 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:12 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:12 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:12 sysaux01.dbf
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:12 control01.ctl
Now go to sqlplus and change the location of datafiles and redologs to new location
ora10g@bahamut:/home/ora10g/10.2/dbs> sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 20 15:08:02 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to an idle instance.
SYS@dbvd> startup mount;
ORACLE instance started.
Total System Global Area 922746880 bytes
Fixed Size 2075568 bytes
Variable Size 494928976 bytes
Database Buffers 419430400 bytes
Redo Buffers 6311936 bytes
Database mounted.
SYS@dbvd> alter database rename file '/backup/restore/dbvd/sysaux01.dbf' to '/backup/restore/dbvdasm/sysaux01.dbf';
Database altered.
----------------------------------some more output here-----------------------------------------------------------
Next we need to recover the database
SYS@dbvd> recover database using backup controlfile until cancel;
ORA-00279: change 214846 generated at 02/20/2008 14:57:50 needed for thread 1
ORA-00289: suggestion : /backup/restore/dbvdasm/1_32_647005853.dbf
ORA-00280: change 214846 for thread 1 is in sequence #32
Specify log: {
/backup/restore/dbvdasm/1_32_647005853.dbf
ORA-00279: change 214850 generated at 02/20/2008 14:57:52 needed for thread 1
ORA-00289: suggestion : /backup/restore/dbvdasm/1_33_647005853.dbf
ORA-00280: change 214850 for thread 1 is in sequence #33
ORA-00278: log file '/backup/restore/dbvdasm/1_32_647005853.dbf' no longer needed for this recovery
Specify log: {
CANCEL
Media recovery cancelled.
SYS@dbvd> alter database open resetlogs;
Database altered.
SYS@dbvd> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
Last but not least don't forget to re-create temporary tablespace
ora10g@bahamut:/backup/restore/dbvdasm> ls -arlt
total 1525368
drwxr-xr-x 4 ora10g dba 4096 Feb 20 14:57 ..
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_31_647005853.dbf
-rw-r----- 1 ora10g dba 287744 Feb 20 15:05 1_30_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_29_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_28_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_27_647005853.dbf
-rw-r----- 1 ora10g dba 6144 Feb 20 15:05 1_26_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_25_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_24_647005853.dbf
-rw-r----- 1 ora10g dba 3950592 Feb 20 15:05 1_23_647005853.dbf
-rw-r----- 1 ora10g dba 391168 Feb 20 15:05 1_22_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_32_647005853.dbf
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:13 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:13 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:13 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:13 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:13 sysaux01.dbf
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:13 redo01.log
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:13 .
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:13 redo02.log
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:13 control01.ctl
But how about if we did shutdown database and suddendly current redolog is corrupt or lost? For this we do not need to perform restore from backup
SYS@dbvd> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@dbvd> startup mount;
ORACLE instance started.
Total System Global Area 922746880 bytes
Fixed Size 2075568 bytes
Variable Size 494928976 bytes
Database Buffers 419430400 bytes
Redo Buffers 6311936 bytes
Database mounted.
SYS@dbvd> select status, group#, members from v$log;
STATUS GROUP# MEMBERS
---------------- ---------- ----------
CURRENT 1 1
INACTIVE 3 1
INACTIVE 2 1
ora10g@bahamut:/backup/restore/dbvd> ls -a redo01*
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:38 redo01.log
ora10g@bahamut:/backup/restore/dbvd> mv redo01.log ../
ora10g@bahamut:/backup/restore/dbvd> ls -arlt
total 1680960
drwxr-xr-x 6 ora10g dba 256 Feb 18 11:46 dump
-rw-r----- 1 ora10g dba 20979712 Feb 18 12:00 temp01.dbf
-rw-r----- 1 ora10g dba 52429312 Feb 18 12:34 redo03.log
-rw-r--r-- 1 ora10g dba 723 Feb 20 09:29 sqlnet.log
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:37 redo02.log
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:37 redo_03.log
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:37 arc
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:38 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:38 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:38 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:38 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:38 sysaux01.dbf
drwxr-xr-x 4 ora10g dba 4096 Feb 20 15:39 ..
drwxr-xr-x 4 ora10g dba 4096 Feb 20 15:39 .
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control03.ctl
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control02.ctl
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control01.ctl
As we can see our redo01.log is gone
SYS@dbvd> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
Oppss we need to perform point in time recovery
SYS@dbvd> recover database until cancel;
Media recovery complete.
SYS@dbvd> alter database open resetlogs;
Database altered.
SYS@dbvd> select status, group#, members from v$log;
STATUS GROUP# MEMBERS
---------------- ---------- ----------
CURRENT 1 1
UNUSED 2 1
UNUSED 3 1
ora10g@bahamut:/backup/restore/dbvd> ls -a redo01*
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:40 redo01.log
Yes, Thanks GOD our database are back now and we can see the redologs are back too. Moral of the story, always make sure you have a valid and good backup
Have a nice day
Cheers
FZheng
Friday, February 15, 2008
Rank Row
One of the developer came to me yesterday. she was asking "I have some duplicate rows in col1 but not in col2 how can i just want to display the first row?
Here is the sample table so you can imagine it
me@d012band> select cust_no, name1 from vd.t2;
CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900000377 ABC 2
0000000900000377 ABC 3
0000000900001059 DEF 1
0000000900001059 DEF 2
0000000900001059 DEF 3
0000000900001059 BEA 1
0000000900003125 WER 1
0000000900003125 WER 2
0000000900003125 WER 3
0000000900004777 AFG 1
0000000900004777 AFG 2
0000000900004777 AFG 3
13 rows selected.
the output that she wanted to is something like this
CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900001059 DEF 1
0000000900003125 WER 1
0000000900004777 AFG 1
So, what i did is run this query and bingo we got the answer she wanted to
me@d012band> select cust_no, name1 from
2 (select cust_no, name1,rank() over (partition by cust_no order by rowid) rank_n from vd.t2)
3 where rank_n = 1;
CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900001059 DEF 1
0000000900003125 WER 1
0000000900004777 AFG 1
I used rank() function to rank every row, thanks to Oracle that build this kind function so that can make our query easier
Have a nice day
FZheng
Here is the sample table so you can imagine it
me@d012band> select cust_no, name1 from vd.t2;
CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900000377 ABC 2
0000000900000377 ABC 3
0000000900001059 DEF 1
0000000900001059 DEF 2
0000000900001059 DEF 3
0000000900001059 BEA 1
0000000900003125 WER 1
0000000900003125 WER 2
0000000900003125 WER 3
0000000900004777 AFG 1
0000000900004777 AFG 2
0000000900004777 AFG 3
13 rows selected.
the output that she wanted to is something like this
CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900001059 DEF 1
0000000900003125 WER 1
0000000900004777 AFG 1
So, what i did is run this query and bingo we got the answer she wanted to
me@d012band> select cust_no, name1 from
2 (select cust_no, name1,rank() over (partition by cust_no order by rowid) rank_n from vd.t2)
3 where rank_n = 1;
CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900001059 DEF 1
0000000900003125 WER 1
0000000900004777 AFG 1
I used rank() function to rank every row, thanks to Oracle that build this kind function so that can make our query easier
Have a nice day
FZheng
Subscribe to:
Posts (Atom)