Wednesday, January 11, 2012

Flashback Version and Flashback Transaction Query

Flashback technology offers us the chance to traverse the undo tablespace for detailed version information of data changes within the database.

Flashback version is particularly useful for investigating complex applications covering a labyrinth of subroutines and conditions.
Below is a basic example of its use.


--PREP WORK

drop table ray.employee;
create table ray.employee (
  id       number,
  name     varchar2(60),
  position varchar2(20)
  );


insert into ray.employee VALUES (1,'TOPPER HARLEY','TOP GUN');
commit;

set lines 1000



select * from ray.employee;
        ID NAME                                                         POSITION

---------- ------------------------------------------------------------ --------------------
         1 TOPPER HARLEY                                                TOP GUN


update ray.employee set name = 'SPUD WESTERN';

commit;

update ray.employee set name = 'JOHN MCWHEELY';


commit;


update ray.employee set name = 'DUKE SPENCER', position='PILOT';


commit;


delete from ray.employee;


commit;




--View the record versions

set lines 1000
COLUMN start FORMAT a20
COLUMN end FORMAT a20
COLUMN VERSIONS_STARTSCN FORMAT 999999999999
COLUMN VERSIONS_ENDSCN FORMAT 999999999999
COLUMN VERSIONS_XID FORMAT 999999999999
COLUMN VERSIONS_OPERATION FORMAT a2
COLUMN id FORMAT 999
COLUMN name FORMAT a20
COLUMN position FORMAT a20

select TO_CHAR(VERSIONS_STARTTIME,'DD-MON-YY HH24:MI:SS') "start", 

       TO_CHAR(VERSIONS_ENDTIME,'DD-MON-YY HH24:MI:SS') "end", 
       versions_operation, 
       versions_xid,
       t.*
  from ray.employee versions between scn minvalue and maxvalue t 
 where id = 1;

start                end                  VE VERSIONS_XID       ID NAME                 POSITION

-------------------- -------------------- -- ---------------- ---- -------------------- --------------------
12-JAN-12 09:16:22                        D  0900180032010000    1 DUKE SPENCER         PILOT
12-JAN-12 09:16:13   12-JAN-12 09:16:22   U  080013002F010000    1 DUKE SPENCER         PILOT
12-JAN-12 09:16:13   12-JAN-12 09:16:13   U  07001C0038010000    1 JOHN MCWHEELY        TOP GUN
12-JAN-12 09:15:58   12-JAN-12 09:16:13   U  0600160035010000    1 SPUD WESTERN         TOP GUN
                     12-JAN-12 09:15:58                          1 TOPPER HARLEY        TOP GUN

--Above, the start time and scn of the record version are given to use as well as the data of that version. We also see the type of transaction which generated the version (U/D). We can look as far back as the undo tablespace size and retention period allow us to.

--Using FLASHBACK QUERY with VERSION 
--For more transaction related information, we can use FLASHBACK_TRANSACTION_QUERY view to see details about the transaction.
The privilege 
FLASHBACK ANY TABLE must be given to the person doing the selecting.




COLUMN start FORMAT a20
COLUMN end FORMAT a20
COLUMN VERSIONS_STARTSCN FORMAT 999999999999
COLUMN VERSIONS_ENDSCN FORMAT 999999999999
COLUMN VERSIONS_XID FORMAT 999999999999
COLUMN VERSIONS_OPERATION FORMAT a2
COLUMN LOGON_USER FORMAT a10
COLUMN UNDO_SQL FORMAT a10
COLUMN TABLE_NAME FORMAT a10
COLUMN TABLE_OWNER FORMAT a10
COLUMN XID FORMAT a20
COLUMN ROWID FORMAT a20
COLUMN OPERATION FORMAT a20
COLUMN id FORMAT 999
COLUMN name FORMAT a20
COLUMN position FORMAT a20

select * 

  from flashback_transaction_query where xid in (
select VERSIONS_XID
  from ray.employee versions between scn minvalue and maxvalue r 
 where versions_operation = 'U');

XID                   START_SCN START_TIM COMMIT_SCN COMMIT_TI LOGON_USER UNDO_CHANGE# OPERATION            TABLE_NAME TABLE_OWNE ROW_ID              UNDO_SQL

-------------------- ---------- --------- ---------- --------- ---------- ------------ -------------------- ---------- ---------- ------------------- ----------
0600160035010000        1354577 12-JAN-12    1354589 12-JAN-12 SYS                   1 UNKNOWN              EMPLOYEE
0600160035010000        1354577 12-JAN-12    1354589 12-JAN-12 SYS                   2 BEGIN
07001C0038010000        1354589 12-JAN-12    1354597 12-JAN-12 SYS                   1 UNKNOWN              EMPLOYEE
07001C0038010000        1354589 12-JAN-12    1354597 12-JAN-12 SYS                   2 BEGIN
080013002F010000        1354597 12-JAN-12    1354600 12-JAN-12 SYS                   1 UNKNOWN              EMPLOYEE
080013002F010000        1354597 12-JAN-12    1354600 12-JAN-12 SYS                   2 BEGIN

Saturday, January 7, 2012

Database Recovery #6 - Duplication/Cloning from RMAN backup

The steps below show how to perform a BACKUP BASED database duplication (not an active
duplication) The TARGET database is the source database you draw the backup pieces from, the AUXILIARY database is where you will restore to and recover up until.

A backup must be available and must be accessible from the auxiliary database via Oracle .Net
The server on which RMAN is being operated MUST have access the backup pieces. If the AUXILIARY db is going to reside on a different machine, the machine must have the backup location mapped from the machine on which the TARGET resides.

This guide was used with a TARGET instance in archivelog mode.
Its not necessary to have both instance using the same app tree but the TARGET and AUXILIARY must share the exact same application tree setup (versions, patches).
Compare with both trees using $ORACLE_HOME/OPatch/opatch lsinventory


--First backup the target database
. oraenv 
orcl
rman target=/
backup database plus archivelog delete input;


--Pick a time, SCN or archivelog number to duplicate AUXILIARY database up until. Do this by looking 
--at the catalog of your TARGET DATABASE to determine what is possible.
list backup;
list archivelog all;


--You want to make sure the TIME,SCN or SEQ you pick coincides with the backed up archivelogs you have available in the TARGET instance. RMAN will tell you flat out if you
--pick a value which is not possible to recover to.
--In the guide below, I took a backup a few hours earlier and backed up archivelog instead.


--PREPARE THE AUXILIARY DATABASE environemnt.
--Add new database to /etc/oratab : I use the SID - DUP
vi /etc/oratab

dup:/u01/app/oracle/product/11.1.0/db_1


--Add the new database to the listener.ora file
SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = dup)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (SID_NAME = dup)
    )
  )


--Add the new database to the tnsnames.ora file
DUP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SID = dup)
      (SERVER = DEDICATED)
    )
  )

--Give the AUXILIARY db an init.ora file by duplication the target database init file and changing the values.

--Change the control_files parameter
--Change the core_dump_dest name
--Change the db_name
--Change the dispatchers name
--Add the convert file name parameters 
--Change the log_archive_dest_1 location
----DB_FILE_NAME_CONVERT
----LOG_FILE_NAME_CONVERT


[oracle@vmorcl ~]$ cp $ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/initdup.ora
[oracle@vmorcl ~]$ vi $ORACLE_HOME/dbs/initdup.ora
*.audit_file_dest='/u01/app/oracle/admin/dup/adump'
*.audit_trail='DB'
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/dup/control01.ctl','/u01/app/oracle/oradata/dup/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/dup/cdump'
*.db_block_size=8192
*.db_cache_size=128M
*.db_domain='localdomain'
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/dup/'
*.db_name='dup'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/dup/'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=10000000000
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dupXDB)'
*.log_archive_dest_1='LOCATION=/redologs/stage2'
*.log_buffer=10485760
*.memory_max_target=512M
*.memory_target=512M
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'
*.processes=150
*.query_rewrite_enabled='TRUE'
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=2304K
*.sga_max_size=372M# internally adjusted
*.sga_target=372M
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTS'


--Create the datafile folders
mkdir /u01/app/oracle/oradata/dup/


--Create the diagnostic folders
mkdir /u01/app/oracle/admin/dup/dup
mkdir /u01/app/oracle/admin/dup/adump
mkdir /u01/app/oracle/admin/dup/bdump
mkdir /u01/app/oracle/admin/dup/cdump


--Reload the listener.
lsnrctl reload


--Duplicate password file
cp /u01/app/oracle/product/11.1.0/db_1/dbs/orapworcl /u01/app/oracle/product/11.1.0/db_1/dbs/orapwdup



--Startup the duplicate database with nomount
. oraenv
dup
sqlplus /nolog
conn / as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/initdup.ora';
exit;


--Connect to the target and auxiliary databases
rman target=sys/oracle@orcl auxiliary=sys/oracle@dup


--Duplicate the database
--You must have a full backup (cold or warm), and you must specify an until sequence,time or scn. 
--Below I use up until past the last sequence number in the archivelog backup
run {
  DUPLICATE TARGET DATABASE TO dup;
  SET UNTIL SEQUENCE 1;
}


--Provided all went well, your instance should be up and running. 

New Version of Putty

Your favourite terminal application – putty – has been upgraded. It seems to use the same registry as your regular putty, so there is no need to reconfigure. Just run it and use your saved configurations.

Transparency and automatic reconnect after coming out of standby are some of the new features.

Have a look at:

http://puttytray.goeswhere.com/

A taste of the action: