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
Wednesday, January 11, 2012
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.
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:
Subscribe to:
Posts (Atom)