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.
Monday, January 18, 2010
Sql Server 2008 Mirroring Auto Remove Config
Setup
I am using vmware.
DB2 is sql server db, I name this as DB2 cause this is the 2nd testing db. Not to promote IBM product.
WIN-J6MUYPE0L46 ( lazy to change Server name, sorry), I will use WIN as short form from here onwards
SERVER2 is another host.
WIN - Mirror
Server2 - Principal
Created Snapshot of Server2 at 11.06pm while this Server2 is still at Principal state.
This will use to restore later (simulate the fxxx Arcserve).
I swing to WIN server to make it as Principal.
Server2 is now restoring mirror mode.
Shutdown WIN server.
Break the mirror at Win
Win now is standalone.
Restore back the Snapshot taken for Server2 at 11.06pm , that is still Principal.
After Server2 boot up.
From Win, you noticed that the mirroring terminated at 11.17pm.
From Server2. You will noticed that the mirroring remove at 11.06pm.
Why 11.06pm ? Cause the snapshot taken at 11.06pm mah..
When I revert the snapshot, the system time still 11.06pm ... mah.....
Why don't we do it other way around ???
Shutdown host Win.
Use back the snapshot for Server2 taken at 11.06 which is still principal.
Hey !!!. Server2 is principal mode !!!
But what if I turn on Win server ?
It will gone again..
I not going to give any comment , you all decide.
Cause is time for me to Zzzz...
I am using vmware.
DB2 is sql server db, I name this as DB2 cause this is the 2nd testing db. Not to promote IBM product.
WIN-J6MUYPE0L46 ( lazy to change Server name, sorry), I will use WIN as short form from here onwards
SERVER2 is another host.
WIN - Mirror
Server2 - Principal
Created Snapshot of Server2 at 11.06pm while this Server2 is still at Principal state.
This will use to restore later (simulate the fxxx Arcserve).
I swing to WIN server to make it as Principal.
Server2 is now restoring mirror mode.
Shutdown WIN server.
Break the mirror at Win
Win now is standalone.
Restore back the Snapshot taken for Server2 at 11.06pm , that is still Principal.
After Server2 boot up.
From Win, you noticed that the mirroring terminated at 11.17pm.
From Server2. You will noticed that the mirroring remove at 11.06pm.
Why 11.06pm ? Cause the snapshot taken at 11.06pm mah..
When I revert the snapshot, the system time still 11.06pm ... mah.....
Why don't we do it other way around ???
Shutdown host Win.
Use back the snapshot for Server2 taken at 11.06 which is still principal.
Hey !!!. Server2 is principal mode !!!
But what if I turn on Win server ?
It will gone again..
I not going to give any comment , you all decide.
Cause is time for me to Zzzz...
Wednesday, November 18, 2009
Fedora 12 kde version
I know this is a bit out of topic, but if you don't have a proper OS setup, you won't learn much.
Currently I am using Ubuntu 9.04. But I would like to give Fedora 12 a try.
Here goes.
1. Download Fedora from http://fedoraproject.org. I am using Fedora-12-x86_64-Live-KDE cause I am using 64 bit. Please select other to suit your computer hardware.
2. Burn it using Brasero Disc Burner.
Currently I am using Ubuntu 9.04. But I would like to give Fedora 12 a try.
Here goes.
1. Download Fedora from http://fedoraproject.org. I am using Fedora-12-x86_64-Live-KDE cause I am using 64 bit. Please select other to suit your computer hardware.
2. Burn it using Brasero Disc Burner.
Tuesday, November 17, 2009
DML with inline view
Found out that inline view will execute first even the session is being block by other.
Below the steps to reproduce my testing.
create the below object in testing schema first.
LUCOTUS @ orcl1> select dbms_metadata.get_ddl('FUNCTION','FUNC_VALUE') from dual;
DBMS_METADATA.GET_DDL('FUNCTION','FUNC_VALUE')
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "LUCOTUS"."FUNC_VALUE" (i int) return int
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate 'insert into test2 values (1)' ;
commit;
return i;
end;
LUCOTUS @ orcl1> ed
Wrote file afiedt.buf
1* select dbms_metadata.get_ddl('TABLE','TEST2') from dual
LUCOTUS @ orcl1> /
DBMS_METADATA.GET_DDL('TABLE','TEST2')
--------------------------------------------------------------------------------
CREATE TABLE "LUCOTUS"."TEST2"
( "I" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
Session 1
=========
LUCOTUS @ orcl1> delete from t_test_con;
insert into t_test_con values ('1',1,null,'a','abc');
insert into t_test_con values ('2',1,null,'b','abc');
commit;
1 row deleted.
LUCOTUS @ orcl1> delete from test2;
2 rows deleted.
LUCOTUS @ orcl1> commit;
Commit complete.
LUCOTUS @ orcl1> update t_test_con
set search_col = '1st',
version_no = version_no + 1,
constant_cond_col='ABC'
where key_col = (
select *
from
(
select func_value(1)
from dual
)
where rownum = 1
) 2 3 4 5 6 7 8 9 10 11 12 13 ;
1 row updated.
SESSION 2
=========
LUCOTUS @ orcl1> select * from test2;
I
----------
1
SESSION 3
=========
LUCOTUS @ orcl1> update t_test_con
set search_col = '2nd',
version_no = version_no + 1,
constant_cond_col='ABC'
where key_col = (
select *
from
(
select func_value(1)
from dual
)
where rownum = 1
) 2 3 4 5 6 7 8 9 10 11 12 13 ;
(hang due to block)
SESSION 2
=========
LUCOTUS @ orcl1> SELECT * FROM TEST2;
I
----------
1
1
(Even session 3 not fully execute, seems the function been run successfully).
SESSION 1
=========
LUCOTUS @ orcl1> commit;
Commit complete.
SESSION 3
=========
LUCOTUS @ orcl1> commit;
Commit complete.
LUCOTUS @ orcl1> select * from t_test_con;
KEY_COL VERSION_NO SEARCH_COL ORDER_BY_C CONSTANT_C
---------- ---------- ---------- ---------- ----------
1 3 2nd a ABC
2 1 b abc
The point here is to show inline view will execute even the session been block by other session.
Below the steps to reproduce my testing.
create the below object in testing schema first.
LUCOTUS @ orcl1> select dbms_metadata.get_ddl('FUNCTION','FUNC_VALUE') from dual;
DBMS_METADATA.GET_DDL('FUNCTION','FUNC_VALUE')
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "LUCOTUS"."FUNC_VALUE" (i int) return int
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate 'insert into test2 values (1)' ;
commit;
return i;
end;
LUCOTUS @ orcl1> ed
Wrote file afiedt.buf
1* select dbms_metadata.get_ddl('TABLE','TEST2') from dual
LUCOTUS @ orcl1> /
DBMS_METADATA.GET_DDL('TABLE','TEST2')
--------------------------------------------------------------------------------
CREATE TABLE "LUCOTUS"."TEST2"
( "I" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
Session 1
=========
LUCOTUS @ orcl1> delete from t_test_con;
insert into t_test_con values ('1',1,null,'a','abc');
insert into t_test_con values ('2',1,null,'b','abc');
commit;
1 row deleted.
LUCOTUS @ orcl1> delete from test2;
2 rows deleted.
LUCOTUS @ orcl1> commit;
Commit complete.
LUCOTUS @ orcl1> update t_test_con
set search_col = '1st',
version_no = version_no + 1,
constant_cond_col='ABC'
where key_col = (
select *
from
(
select func_value(1)
from dual
)
where rownum = 1
) 2 3 4 5 6 7 8 9 10 11 12 13 ;
1 row updated.
SESSION 2
=========
LUCOTUS @ orcl1> select * from test2;
I
----------
1
SESSION 3
=========
LUCOTUS @ orcl1> update t_test_con
set search_col = '2nd',
version_no = version_no + 1,
constant_cond_col='ABC'
where key_col = (
select *
from
(
select func_value(1)
from dual
)
where rownum = 1
) 2 3 4 5 6 7 8 9 10 11 12 13 ;
(hang due to block)
SESSION 2
=========
LUCOTUS @ orcl1> SELECT * FROM TEST2;
I
----------
1
1
(Even session 3 not fully execute, seems the function been run successfully).
SESSION 1
=========
LUCOTUS @ orcl1> commit;
Commit complete.
SESSION 3
=========
LUCOTUS @ orcl1> commit;
Commit complete.
LUCOTUS @ orcl1> select * from t_test_con;
KEY_COL VERSION_NO SEARCH_COL ORDER_BY_C CONSTANT_C
---------- ---------- ---------- ---------- ----------
1 3 2nd a ABC
2 1 b abc
The point here is to show inline view will execute even the session been block by other session.
Subscribe to:
Comments (Atom)
