Thursday, July 7, 2011
Hardening of externaljob.ora
This video shows how changing externaljob.ora to other user /group will allow user with create job in Database exploit the security at system level.
Monday, July 4, 2011
Adaptive Cursor Sharing Execution Plan
I recently came across this feature in 11g r2 and wanna test to see if the execution plan whether is really working.
And I encounter some issue when I set autotrace on using bind variable, it unable to shows what I expected.
I post a question in asktom.oracle.com and turn out that bind variable doesn't work in explain plan.
Here the final testing.
And is working as expected using dbms_xplan.display_cursor.
LUCOTUS @ TEST> create table test as select dba_objects.*, 'Y' ind from dba_objects ;
Table created.
LUCOTUS @ TEST> insert into test select dba_objects.*, 'N' from dba_objects where rownum =1;
1 row created.
LUCOTUS @ TEST> commit;
Commit complete.
LUCOTUS @ TEST> create index idx_test on test(ind);
Index created.
LUCOTUS @ TEST>
LUCOTUS @ TEST> exec dbms_stats.gather_schema_stats(user);
PL/SQL procedure successfully completed.
LUCOTUS @ TEST> exec dbms_stats.gather_table_stats(user,'TEST',method_opt=>'FOR COLUMNS SIZE 2 IND', cascade=>true);
PL/SQL procedure successfully completed.
LUCOTUS @ TEST> variable v_ind varchar2
LUCOTUS @ TEST> exec :v_ind :='Y'
PL/SQL procedure successfully completed.
LUCOTUS @ TEST> select max(timestamp) from test where ind =:v_ind ;
MAX(TIMESTAMP)
-------------------
2011-07-05:10:54:59
LUCOTUS @ TEST> set long 9999999
LUCOTUS @ TEST> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID btycqu1h6y98b, child number 0
-------------------------------------
select max(timestamp) from test where ind =:v_ind
Plan hash value: 3467505462
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 55 (100)| |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| TEST | 14109 | 303K| 55 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("IND"=:V_IND)
19 rows selected.
LUCOTUS @ TEST> set pagesize 40
LUCOTUS @ TEST> exec :v_ind :='N'
PL/SQL procedure successfully completed.
LUCOTUS @ TEST> select max(timestamp) from test where ind =:v_ind ;
MAX(TIMESTAMP)
-------------------
2011-06-29:09:21:29
LUCOTUS @ TEST> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3p2ja0ksm977m, child number 0
-------------------------------------
select max(timestamp) from test where ind =:v_ind
Plan hash value: 3834481504
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 22 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("IND"=:V_IND)
20 rows selected.
And I encounter some issue when I set autotrace on using bind variable, it unable to shows what I expected.
I post a question in asktom.oracle.com and turn out that bind variable doesn't work in explain plan.
Here the final testing.
And is working as expected using dbms_xplan.display_cursor.
LUCOTUS @ TEST> create table test as select dba_objects.*, 'Y' ind from dba_objects ;
Table created.
LUCOTUS @ TEST> insert into test select dba_objects.*, 'N' from dba_objects where rownum =1;
1 row created.
LUCOTUS @ TEST> commit;
Commit complete.
LUCOTUS @ TEST> create index idx_test on test(ind);
Index created.
LUCOTUS @ TEST>
LUCOTUS @ TEST> exec dbms_stats.gather_schema_stats(user);
PL/SQL procedure successfully completed.
LUCOTUS @ TEST> exec dbms_stats.gather_table_stats(user,'TEST',method_opt=>'FOR COLUMNS SIZE 2 IND', cascade=>true);
PL/SQL procedure successfully completed.
LUCOTUS @ TEST> variable v_ind varchar2
LUCOTUS @ TEST> exec :v_ind :='Y'
PL/SQL procedure successfully completed.
LUCOTUS @ TEST> select max(timestamp) from test where ind =:v_ind ;
MAX(TIMESTAMP)
-------------------
2011-07-05:10:54:59
LUCOTUS @ TEST> set long 9999999
LUCOTUS @ TEST> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID btycqu1h6y98b, child number 0
-------------------------------------
select max(timestamp) from test where ind =:v_ind
Plan hash value: 3467505462
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 55 (100)| |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| TEST | 14109 | 303K| 55 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("IND"=:V_IND)
19 rows selected.
LUCOTUS @ TEST> set pagesize 40
LUCOTUS @ TEST> exec :v_ind :='N'
PL/SQL procedure successfully completed.
LUCOTUS @ TEST> select max(timestamp) from test where ind =:v_ind ;
MAX(TIMESTAMP)
-------------------
2011-06-29:09:21:29
LUCOTUS @ TEST> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3p2ja0ksm977m, child number 0
-------------------------------------
select max(timestamp) from test where ind =:v_ind
Plan hash value: 3834481504
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 22 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("IND"=:V_IND)
20 rows selected.
Tuesday, March 29, 2011
RMAN-20035: invalid high recid
This is to simulate the scenario of RMAN-20035: invalid high recid issue.
RMAN-20035: invalid high recid won’t happen that frequent, but somehow, it happen to me recently.
This is the steps to reproduce this RMAN error.
RMAN> backup database;
Starting backup at 29-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=26 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/ORCL/datafile/o1_mf_system_6s2rw3hg_.dbf
input datafile fno=00002 name=/u02/oradata/ORCL/datafile/o1_mf_sys_undo_6s2rwcrn_.dbf
input datafile fno=00003 name=/u02/oradata/ORCL/datafile/o1_mf_sysaux_6s2rwdj6_.dbf
channel ORA_DISK_1: starting piece 1 at 29-MAR-11
channel ORA_DISK_1: finished piece 1 at 29-MAR-11
piece handle=/u03/flash_recovery_area/ORCL/backupset/2011_03_29/o1_mf_nnndf_TAG20110329T130711_6s2t7z1x_.bkp tag=TAG20110329T130711 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 29-MAR-11
channel ORA_DISK_1: finished piece 1 at 29-MAR-11
piece handle=/u03/flash_recovery_area/ORCL/backupset/2011_03_29/o1_mf_ncsnf_TAG20110329T130711_6s2t8346_.bkp tag=TAG20110329T130711 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 29-MAR-11
SYS @ orcl> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/ORCL/controlfile/o1_mf_6s2rw1j6_.ctl
/u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl
SYS @ orcl> desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SYS @ orcl> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log
/u03/flash_recovery_area/ORCL/onlinelog/o1_mf_1_6s2rw1tj_.log
/u02/oradata/ORCL/onlinelog/o1_mf_2_6s2rw24s_.log
/u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log
SYS @ orcl> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ orcl> 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@db10g ~]$ cp -r /u02/oradata/ORCL/datafile /u02/oradata/ORCL/datafile_backup
[oracle@db10g ~]$
[oracle@db10g ~]$ cp /u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log /u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log_backup
[oracle@db10g ~]$ cp /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_1_6s2rw1tj_.log /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_1_6s2rw1tj_.log_backup
cp [oracle@db10g ~]$ cp /u02/oradata/ORCL/onlinelog/o1_mf_2_6s2rw24s_.log /u02/oradata/ORCL/onlinelog/o1_mf_2_6s2rw24s_.log_backup
cp[oracle@db10g ~]$ cp /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log_backup
cp: missing destination file operand after `/u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log_backup'
Try `cp --help' for more information.
[oracle@db10g ~]$ cp /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log_backup
[oracle@db10g ~]$ cp /u02/oradata/ORCL/controlfile/o1_mf_6s2rw1j6_.ctl /u02/oradata/ORCL/controlfile/o1_mf_6s2rw1j6_.ctl_backup
[oracle@db10g ~]$ cp /u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl /u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl_backup
RMAN> backup database ;
Starting backup at 29-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=25 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/ORCL/datafile/o1_mf_system_6s2rw3hg_.dbf
input datafile fno=00002 name=/u02/oradata/ORCL/datafile/o1_mf_sys_undo_6s2rwcrn_.dbf
input datafile fno=00003 name=/u02/oradata/ORCL/datafile/o1_mf_sysaux_6s2rwdj6_.dbf
channel ORA_DISK_1: starting piece 1 at 29-MAR-11
channel ORA_DISK_1: finished piece 1 at 29-MAR-11
piece handle=/u03/flash_recovery_area/ORCL/backupset/2011_03_29/o1_mf_nnndf_TAG20110329T131258_6s2tlty8_.bkp tag=TAG20110329T131258 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 29-MAR-11
channel ORA_DISK_1: finished piece 1 at 29-MAR-11
piece handle=/u03/flash_recovery_area/ORCL/backupset/2011_03_29/o1_mf_ncsnf_TAG20110329T131258_6s2tly1c_.bkp tag=TAG20110329T131258 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 29-MAR-11
RMAN> exit
Recovery Manager complete.
[oracle@db10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 29 13:13:13 2011
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
SYS @ orcl> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ orcl> 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@db10g ~]$ rm -r /u02/oradata/ORCL/datafile/
[oracle@db10g ~]$ mv /u02/oradata/ORCL/datafile_backup/ /u02/oradata/ORCL/datafile
[oracle@db10g ~]$ mv /u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log
o1_mf_1_6s2rw1nz_.log o1_mf_1_6s2rw1nz_.log_backup
[oracle@db10g ~]$ mv /u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log_backup /u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log
[oracle@db10g ~]$ mv /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_1_6s2rw1tj_.log_backup /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_1_6s2rw1tj_.log
[oracle@db10g ~]$ mv /u02/oradata/ORCL/onlinelog/o1_mf_2_6s2rw24s_.log_ba_backup /u02/oradata/ORCL/onlinelog/o1_mf_2_6s2rw24s_.log
[oracle@db10g ~]$ mv /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log_backup /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log
[oracle@db10g ~]$ mv /u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl
o1_mf_6s2rw1l3_.ctl o1_mf_6s2rw1l3_.ctl_backup
[oracle@db10g ~]$ mv /u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl_backup /u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl
[oracle@db10g ~]$
[oracle@db10g ~]$
[oracle@db10g ~]$ mv /u02/oradata/ORCL/controlfile/o1_mf_6s2rw1j6_.ctl_backup /u02/oradata/ORCL/controlfile/o1_mf_6s2rw1j6_.ctl
[oracle@db10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 29 13:15:36 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SYS @ orcl> startup ;
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1267260 bytes
Variable Size 100665796 bytes
Database Buffers 209715200 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SYS @ orcl> 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@db10g ~]$ rman target sys/xxx@orcl catalog rman/rman@catdb
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Mar 29 13:15:57 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1274840961)
connected to recovery catalog database
RMAN> backup database;
Starting backup at 29-MAR-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/29/2011 13:16:10
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high recid
Note 273446.1 from Support Oracle basically suggest to either
1) Unregister the database in the recovery catalog using DBMS_RCVCAT.UNREGISTERDATABASE procedure.
2) After unregistering the database re-register the database in the recovery catalog:
I would suggest that don’t mix offline backup into your online restoration procedure.
Please feel free to comment/suggest .
RMAN-20035: invalid high recid won’t happen that frequent, but somehow, it happen to me recently.
This is the steps to reproduce this RMAN error.
RMAN> backup database;
Starting backup at 29-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=26 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/ORCL/datafile/o1_mf_system_6s2rw3hg_.dbf
input datafile fno=00002 name=/u02/oradata/ORCL/datafile/o1_mf_sys_undo_6s2rwcrn_.dbf
input datafile fno=00003 name=/u02/oradata/ORCL/datafile/o1_mf_sysaux_6s2rwdj6_.dbf
channel ORA_DISK_1: starting piece 1 at 29-MAR-11
channel ORA_DISK_1: finished piece 1 at 29-MAR-11
piece handle=/u03/flash_recovery_area/ORCL/backupset/2011_03_29/o1_mf_nnndf_TAG20110329T130711_6s2t7z1x_.bkp tag=TAG20110329T130711 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 29-MAR-11
channel ORA_DISK_1: finished piece 1 at 29-MAR-11
piece handle=/u03/flash_recovery_area/ORCL/backupset/2011_03_29/o1_mf_ncsnf_TAG20110329T130711_6s2t8346_.bkp tag=TAG20110329T130711 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 29-MAR-11
SYS @ orcl> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/ORCL/controlfile/o1_mf_6s2rw1j6_.ctl
/u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl
SYS @ orcl> desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SYS @ orcl> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log
/u03/flash_recovery_area/ORCL/onlinelog/o1_mf_1_6s2rw1tj_.log
/u02/oradata/ORCL/onlinelog/o1_mf_2_6s2rw24s_.log
/u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log
SYS @ orcl> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ orcl> 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@db10g ~]$ cp -r /u02/oradata/ORCL/datafile /u02/oradata/ORCL/datafile_backup
[oracle@db10g ~]$
[oracle@db10g ~]$ cp /u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log /u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log_backup
[oracle@db10g ~]$ cp /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_1_6s2rw1tj_.log /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_1_6s2rw1tj_.log_backup
cp [oracle@db10g ~]$ cp /u02/oradata/ORCL/onlinelog/o1_mf_2_6s2rw24s_.log /u02/oradata/ORCL/onlinelog/o1_mf_2_6s2rw24s_.log_backup
cp[oracle@db10g ~]$ cp /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log_backup
cp: missing destination file operand after `/u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log_backup'
Try `cp --help' for more information.
[oracle@db10g ~]$ cp /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log_backup
[oracle@db10g ~]$ cp /u02/oradata/ORCL/controlfile/o1_mf_6s2rw1j6_.ctl /u02/oradata/ORCL/controlfile/o1_mf_6s2rw1j6_.ctl_backup
[oracle@db10g ~]$ cp /u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl /u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl_backup
RMAN> backup database ;
Starting backup at 29-MAR-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=25 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/ORCL/datafile/o1_mf_system_6s2rw3hg_.dbf
input datafile fno=00002 name=/u02/oradata/ORCL/datafile/o1_mf_sys_undo_6s2rwcrn_.dbf
input datafile fno=00003 name=/u02/oradata/ORCL/datafile/o1_mf_sysaux_6s2rwdj6_.dbf
channel ORA_DISK_1: starting piece 1 at 29-MAR-11
channel ORA_DISK_1: finished piece 1 at 29-MAR-11
piece handle=/u03/flash_recovery_area/ORCL/backupset/2011_03_29/o1_mf_nnndf_TAG20110329T131258_6s2tlty8_.bkp tag=TAG20110329T131258 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 29-MAR-11
channel ORA_DISK_1: finished piece 1 at 29-MAR-11
piece handle=/u03/flash_recovery_area/ORCL/backupset/2011_03_29/o1_mf_ncsnf_TAG20110329T131258_6s2tly1c_.bkp tag=TAG20110329T131258 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 29-MAR-11
RMAN> exit
Recovery Manager complete.
[oracle@db10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 29 13:13:13 2011
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
SYS @ orcl> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ orcl> 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@db10g ~]$ rm -r /u02/oradata/ORCL/datafile/
[oracle@db10g ~]$ mv /u02/oradata/ORCL/datafile_backup/ /u02/oradata/ORCL/datafile
[oracle@db10g ~]$ mv /u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log
o1_mf_1_6s2rw1nz_.log o1_mf_1_6s2rw1nz_.log_backup
[oracle@db10g ~]$ mv /u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log_backup /u02/oradata/ORCL/onlinelog/o1_mf_1_6s2rw1nz_.log
[oracle@db10g ~]$ mv /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_1_6s2rw1tj_.log_backup /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_1_6s2rw1tj_.log
[oracle@db10g ~]$ mv /u02/oradata/ORCL/onlinelog/o1_mf_2_6s2rw24s_.log_ba_backup /u02/oradata/ORCL/onlinelog/o1_mf_2_6s2rw24s_.log
[oracle@db10g ~]$ mv /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log_backup /u03/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6s2rw2on_.log
[oracle@db10g ~]$ mv /u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl
o1_mf_6s2rw1l3_.ctl o1_mf_6s2rw1l3_.ctl_backup
[oracle@db10g ~]$ mv /u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl_backup /u03/flash_recovery_area/ORCL/controlfile/o1_mf_6s2rw1l3_.ctl
[oracle@db10g ~]$
[oracle@db10g ~]$
[oracle@db10g ~]$ mv /u02/oradata/ORCL/controlfile/o1_mf_6s2rw1j6_.ctl_backup /u02/oradata/ORCL/controlfile/o1_mf_6s2rw1j6_.ctl
[oracle@db10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 29 13:15:36 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SYS @ orcl> startup ;
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1267260 bytes
Variable Size 100665796 bytes
Database Buffers 209715200 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SYS @ orcl> 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@db10g ~]$ rman target sys/xxx@orcl catalog rman/rman@catdb
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Mar 29 13:15:57 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1274840961)
connected to recovery catalog database
RMAN> backup database;
Starting backup at 29-MAR-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/29/2011 13:16:10
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high recid
Note 273446.1 from Support Oracle basically suggest to either
1) Unregister the database in the recovery catalog using DBMS_RCVCAT.UNREGISTERDATABASE procedure.
2) After unregistering the database re-register the database in the recovery catalog:
I would suggest that don’t mix offline backup into your online restoration procedure.
Please feel free to comment/suggest .
Saturday, January 1, 2011
I am back
I am back, after months of intense Oracle study and pass this Ocm 10g exam. I really hope I can study other IT topic like rails 3 and at the same time post my blog here.
Subscribe to:
Comments (Atom)
