Wednesday 14 May 2008

Using Logminer Example

This is one in the list of my Example Series in this blog. Check this blog for more possible examples.

If your database is in Archivelog Mode and you have already enabled Supplemental Logging before the rows were create(This is done using ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;), You can use LogMiner to find the changes in the database for any user. My test example here finds changes made by TEST user from today. You can change the username to any user and add more columns from V$LOGMNR_CONTENTS view.
SQL> connect /as sysdba
Connected.
SQL>

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME > TRUNC(SYSDATE);

NAME
---------------------------------------------------------------------------------------
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_99_3LMO6WJF_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_100_3LMOF7HQ_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_101_3LMOFN8Q_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_102_3LMQ0ZGL_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_103_3LMQ1756_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_104_3LMQ1D0D_.ARC

10 rows selected.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_101_3LMOFN8Q_.ARC',OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_100_3LMOF7HQ_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_102_3LMQ0ZGL_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_103_3LMQ1756_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_104_3LMQ1D0D_.ARC');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

SQL> set lines 200
SQL> set pages 9999
SQL> col USERNAME format a10
SQL> col XID format a10
SQL> col SQL_REDO format a40
SQL> col SQL_UNDO format a40
SQL>
SQL> SELECT username,session#,

to_char(timestamp,'DD/MM/YYYY HH24:MI:SS') timestamp,
(XIDUSN '.' XIDSLT '.' XIDSQN) AS XID,SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS WHERE username = 'TEST'
2
SQL>

--- Your results Display here

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.
SQL>

Friday 2 May 2008

Row-level Dependency Tracking ROWDEPENDENCIES Example

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

Oracle 10g provided one of the best features I like using. This is the pseudo column ORA_ROWSCN. Using scn_to_timestamp with ORA_ROWSCN, you can find the timestamp your block or row was changed. But this does not come automatically without the usual changes and space (about 6 bytes per row).

By default, Oracle 10g tables are created with the clause NOROWDEPENDENCIES. This means that Block-level Dependency Tracking is used. You can create your table with ROWDEPENDENCIES thereby enabling Row-level Dependency Tracking.

SQL> CREATE TABLE TESTTAB
(COL1 NUMBER(5),
COL2 VARCHAR2(30),
COL3 DATE)
ROWDEPENDENCIES;
2 3 4 5
Table created.

SQL>

I will demonstrate with EMP table which has NOROWDEPENDENCIES

SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from emp;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.49.00.000000000 AM SMITH 7369 2000
02-MAY-08 11.49.00.000000000 AM ALLEN 7499 1600
02-MAY-08 11.49.00.000000000 AM WARD 7521 1250
02-MAY-08 11.49.00.000000000 AM JONES 7566 2975
02-MAY-08 11.49.00.000000000 AM MARTIN 7654 1250
02-MAY-08 11.49.00.000000000 AM BLAKE 7698 2850
02-MAY-08 11.49.00.000000000 AM CLARK 7782 2450
02-MAY-08 11.49.00.000000000 AM SCOTT 7788 3000
02-MAY-08 11.49.00.000000000 AM KING 7839 5000
02-MAY-08 11.49.00.000000000 AM TURNER 7844 1500
02-MAY-08 11.49.00.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.49.00.000000000 AM JAMES 7900 950
02-MAY-08 11.49.00.000000000 AM FORD 7902 3000
02-MAY-08 11.49.00.000000000 AM MILLER 7934 1300

14 rows selected.

SQL> update emp set sal=3000 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

As you can see from the result below, there was NO row level tracking

SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from emp;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.54.10.000000000 AM SMITH 7369 3000
02-MAY-08 11.54.10.000000000 AM ALLEN 7499 1600
02-MAY-08 11.54.10.000000000 AM WARD 7521 1250
02-MAY-08 11.54.10.000000000 AM JONES 7566 2975
02-MAY-08 11.54.10.000000000 AM MARTIN 7654 1250
02-MAY-08 11.54.10.000000000 AM BLAKE 7698 2850
02-MAY-08 11.54.10.000000000 AM CLARK 7782 2450
02-MAY-08 11.54.10.000000000 AM SCOTT 7788 3000
02-MAY-08 11.54.10.000000000 AM KING 7839 5000
02-MAY-08 11.54.10.000000000 AM TURNER 7844 1500
02-MAY-08 11.54.10.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.54.10.000000000 AM JAMES 7900 950
02-MAY-08 11.54.10.000000000 AM FORD 7902 3000
02-MAY-08 11.54.10.000000000 AM MILLER 7934 1300

14 rows selected.

I will now demonstrate with EMPDPD table which has ROWDEPENDENCIES

SQL> create table empdpd ROWDEPENDENCIES as select * from emp;

Table created.

SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from empdpd;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM SMITH 7369 3000
02-MAY-08 11.55.26.000000000 AM ALLEN 7499 1600
02-MAY-08 11.55.26.000000000 AM WARD 7521 1250
02-MAY-08 11.55.26.000000000 AM JONES 7566 2975
02-MAY-08 11.55.26.000000000 AM MARTIN 7654 1250
02-MAY-08 11.55.26.000000000 AM BLAKE 7698 2850
02-MAY-08 11.55.26.000000000 AM CLARK 7782 2450
02-MAY-08 11.55.26.000000000 AM SCOTT 7788 3000
02-MAY-08 11.55.26.000000000 AM KING 7839 5000
02-MAY-08 11.55.26.000000000 AM TURNER 7844 1500
02-MAY-08 11.55.26.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM JAMES 7900 950
02-MAY-08 11.55.26.000000000 AM FORD 7902 3000
02-MAY-08 11.55.26.000000000 AM MILLER 7934 1300

14 rows selected.

SQL> update empdpd set sal=4000 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

As you can see from the result below, there was Row level tracking


SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from empdpd;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.56.20.000000000 AM SMITH 7369 4000
02-MAY-08 11.55.26.000000000 AM ALLEN 7499 1600
02-MAY-08 11.55.26.000000000 AM WARD 7521 1250
02-MAY-08 11.55.26.000000000 AM JONES 7566 2975
02-MAY-08 11.55.26.000000000 AM MARTIN 7654 1250
02-MAY-08 11.55.26.000000000 AM BLAKE 7698 2850
02-MAY-08 11.55.26.000000000 AM CLARK 7782 2450
02-MAY-08 11.55.26.000000000 AM SCOTT 7788 3000
02-MAY-08 11.55.26.000000000 AM KING 7839 5000
02-MAY-08 11.55.26.000000000 AM TURNER 7844 1500
02-MAY-08 11.55.26.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM JAMES 7900 950
02-MAY-08 11.55.26.000000000 AM FORD 7902 3000
02-MAY-08 11.55.26.000000000 AM MILLER 7934 1300

14 rows selected.

SQL>select table_name,dependencies from user_tables;

TABLE_NAME DEPENDEN
------------------------------ --------
EMP DISABLED
DEPT DISABLED
BONUS DISABLED
SALGRADE DISABLED
DUMMY DISABLED
EMPDPD ENABLED
TESTTAB ENABLED

EXTERNAL_TEST1 DISABLED
TEST1 DISABLED
TEST2 DISABLED
EXTERNAL_TEST DISABLED

11 rows selected.

SQL>

Note: using ROWDEPENDENCIES will normally add about 6 bytes to each row. You need to read moer from Oracle Documentation for limitations/restrictions

Friday 25 April 2008

Physical Standby Database Example

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

This is an example Physical Standby Database Creation. This is mostly from the logs I created. You can use it as a guide or hint. It does not show all the step-by-step, but it should help. Because it is logs, some processes may be duplicated. I will try to clean it up as much as I can.

This is based on Oracle 10.2.0.2 on Windows XP on my test database. Both Primary and Standby is on the Same Machine. As you know, this is a test database and show not be configured to be on same database for Production. This does not show how the Primary Database was created.

To complete this, I followed Oracle® Data Guard Concepts and Administration

Here is the Primary Database Initialisation Paranmeter


testdb.__db_cache_size=37748736
testdb.__java_pool_size=4194304
testdb.__large_pool_size=4194304
testdb.__shared_pool_size=79691776
testdb.__streams_pool_size=0
*._awr_flush_threshold_metrics=FALSE
*._system_trig_enabled=FALSE
*.audit_file_dest='C:\oracle\admin\testdb\adump'
*.audit_trail='DB'
*.background_dump_dest='C:\oracle\admin\testdb\bdump'
*.compatible='10.2.0.2.0'
*.control_files='C:\oracle\oradata\testdb\control01.ctl','C:\oracle\oradata\testdb\control02.ctl','C:\oracle\oradata\testdb\control03.ctl'
*.core_dump_dest='C:\oracle\admin\testdb\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='standby','testdb'
*.db_name='testdb'
*.db_recovery_file_dest='C:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='TESTDB'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
*.fal_client='TESTDB'
*.fal_server='STANDBY'
*.job_queue_processes=4
*.log_archive_config='DG_CONFIG=(testdb,standby)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST LGWR MANDATORY REOPEN=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='ARC%S_%R.%T'
*.LOG_FILE_NAME_CONVERT='C:\oracle\oradata\standby','C:\oracle\oradata\testdb'
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=12777216
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='testdb'
*.sga_target=127772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\admin\testdb\udump'


Create Standby Database Intialisation Parameter from Primary

SQL> create pfile='C:\oracle\ora10g\database\INITSTANDBYDB.ora' from spfile;File created.

Here is the amended Standby Initialisation Paranmeter

standby.__db_cache_size=29360128
standby.__java_pool_size=8388608
standby.__large_pool_size=4194304
standby.__shared_pool_size=83886080
standby.__streams_pool_size=0
*._awr_flush_threshold_metrics=FALSE
*._system_trig_enabled=FALSE
*.audit_file_dest='C:\oracle\admin\standby\adump'
*.audit_trail='DB'
*.background_dump_dest='C:\oracle\admin\standby\bdump'
*.compatible='10.2.0.2.0'
*.control_files='C:\oracle\oradata\standby\control01.ctl','C:\oracle\oradata\standby\control02.ctl','C:\oracle\oradata\standby\control03.ctl'
*.core_dump_dest='C:\oracle\admin\standby\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='testdb','standby'
*.db_name='testdb'
*.db_recovery_file_dest='C:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='standby'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.fal_client='STANDBY'
*.fal_server='TESTDB'
*.job_queue_processes=4
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdb,standby)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST LGWR MANDATORY REOPEN=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.log_archive_dest_2='SERVICE=testdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdb'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='ARC%S_%R.%T'
*.LOG_FILE_NAME_CONVERT='C:\oracle\oradata\testdb','C:\oracle\oradata\standby'
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=12777216
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='standby'
*.sga_target=127772160
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\admin\standby\udump'

Create Password File for Standby

C:\>orapwd file=C:\oracle\ora10g\database\PWDstandbydb.ora password=oracl3


Swtich Logfile

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

Get Current Log Sequence

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
117

SQL> create pfile='C:\oracle\ora10g\database\INITSTANDBYDB.ora' from spfile;

File created.

Connect to RMAN (I am not using Catalog)

C:\Documents\dbmsdirect\Testing\StandBy>rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Nov 28 11:04:10 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: TESTDB (DBID=2403050596)


Startup MOUNT the Primary Database

RMAN> STARTUP MOUNT

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 130023424 bytes

Fixed Size 1288148 bytes
Variable Size 96471084 bytes
Database Buffers 29360128 bytes
Redo Buffers 2904064 bytes

Backup current controlfile for Standby

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

Starting backup at 28-NOV-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=100 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NCNNF_TAG20071128T110608_3NTLX352_.BKP tag=TAG20071128T110608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 28-NOV-07

Open Primary Database

RMAN> ALTER DATABASE OPEN;

database opened

Archive the current log so to ensure that backup is consistent and recoverable

RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT

Backup Database and all archivelogs

RMAN> BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;

Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=99 recid=72 stamp=637507805
input archive log thread=1 sequence=100 recid=73 stamp=637508008
input archive log thread=1 sequence=101 recid=74 stamp=637508021
input archive log thread=1 sequence=102 recid=75 stamp=637509664
input archive log thread=1 sequence=103 recid=76 stamp=637509671
input archive log thread=1 sequence=104 recid=77 stamp=637509676
input archive log thread=1 sequence=105 recid=78 stamp=638121625
input archive log thread=1 sequence=106 recid=79 stamp=639147639
input archive log thread=1 sequence=107 recid=80 stamp=639661045
input archive log thread=1 sequence=108 recid=81 stamp=639661317
input archive log thread=1 sequence=109 recid=82 stamp=639661904
input archive log thread=1 sequence=110 recid=83 stamp=639662160
input archive log thread=1 sequence=111 recid=84 stamp=639668842
input archive log thread=1 sequence=112 recid=85 stamp=639832029
input archive log thread=1 sequence=113 recid=86 stamp=639832036
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T110717_3NTLZ9RT_.BKP tag=TAG20071128T110717 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 28-NOV-07

RMAN> backup database plus archivelog delete input;


Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=99 recid=72 stamp=637507805
input archive log thread=1 sequence=100 recid=73 stamp=637508008
input archive log thread=1 sequence=101 recid=74 stamp=637508021
input archive log thread=1 sequence=102 recid=75 stamp=637509664
input archive log thread=1 sequence=103 recid=76 stamp=637509671
input archive log thread=1 sequence=104 recid=77 stamp=637509676
input archive log thread=1 sequence=105 recid=78 stamp=638121625
input archive log thread=1 sequence=106 recid=79 stamp=639147639
input archive log thread=1 sequence=107 recid=80 stamp=639661045
input archive log thread=1 sequence=108 recid=81 stamp=639661317
input archive log thread=1 sequence=109 recid=82 stamp=639661904
input archive log thread=1 sequence=110 recid=83 stamp=639662160
input archive log thread=1 sequence=111 recid=84 stamp=639668842
input archive log thread=1 sequence=112 recid=85 stamp=639832029
input archive log thread=1 sequence=113 recid=86 stamp=639832036
input archive log thread=1 sequence=114 recid=87 stamp=639833067
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112428_3NTMZH70_.BKP tag=TAG20071128T112428 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:57
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_99_3LMO6WJF_.ARC recid=72 stamp=637507805
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_100_3LMOF7HQ_.ARC recid=73 stamp=637508008
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_101_3LMOFN8Q_.ARC recid=74 stamp=637508021
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_102_3LMQ0ZGL_.ARC recid=75 stamp=637509664
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_103_3LMQ1756_.ARC recid=76 stamp=637509671
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_104_3LMQ1D0D_.ARC recid=77 stamp=637509676
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_08\O1_MF_1_105_3M6DNLSO_.ARC recid=78 stamp=638121625
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_20\O1_MF_1_106_3N5PMM5P_.ARC recid=79 stamp=639147639
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_107_3NOCZDMX_.ARC recid=80 stamp=639661045
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_108_3NOD7V4F_.ARC recid=81 stamp=639661317
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_109_3NODT9KL_.ARC recid=82 stamp=639661904
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_110_3NOF23DK_.ARC recid=83 stamp=639662160
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_26\O1_MF_1_111_3NOMM5PR_.ARC recid=84 stamp=639668842
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_112_3NTLYVL7_.ARC recid=85 stamp=639832029
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_113_3NTLZ4L0_.ARC recid=86 stamp=639832036
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_114_3NTMZCGZ_.ARC recid=87 stamp=639833067
Finished backup at 28-NOV-07

Starting backup at 28-NOV-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\ORADATA\TESTDB\SYSTEM01.DBF
input datafile fno=00002 name=C:\ORACLE\ORADATA\TESTDB\UNDOTBS01.DBF
input datafile fno=00003 name=C:\ORACLE\ORADATA\TESTDB\SYSAUX01.DBF
input datafile fno=00004 name=C:\ORACLE\ORADATA\TESTDB\USERS01.DBF
input datafile fno=00006 name=C:\ORACLE\ORADATA\TESTDB\DISC_EUL1.ORA
input datafile fno=00005 name=C:\ORACLE\ORADATA\TESTDB\TBSALERT01.ORA
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NNNDF_TAG20071128T112530_3NTN1CGF_.BKP tag=TAG20071128T112530 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
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 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NCSNF_TAG20071128T112530_3NTN5C4T_.BKP tag=TAG20071128T112530 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 28-NOV-07

Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=115 recid=88 stamp=639833263
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112743_3NTN5JLT_.BKP tag=TAG20071128T112743 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_115_3NTN5GRZ_.ARC recid=88 stamp=639833263
Finished backup at 28-NOV-07


RMAN> backup current controlfile for standby format='C:\Documents\dbmsdirect\Testing\StandBy\stby_cfile.%U';

Starting backup at 28-NOV-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\DOCUMENTS\dbmsdirect\TESTING\STANDBY\STBY_CFILE.09J265GJ_1_1 tag=TAG20071128T113338 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-NOV-07

RMAN> backup archivelog all delete input;

Starting backup at 28-NOV-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=116 recid=89 stamp=639833685
input archive log thread=1 sequence=117 recid=90 stamp=639833686
input archive log thread=1 sequence=118 recid=91 stamp=639833741
channel ORA_DISK_1: starting piece 1 at 28-NOV-07
channel ORA_DISK_1: finished piece 1 at 28-NOV-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T113541_3NTNNHGQ_.BKP tag=TAG20071128T113541 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_116_3NTNLNWB_.ARC recid=89 stamp=639833685
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_117_3NTNLOXB_.ARC recid=90 stamp=639833686
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_28\O1_MF_1_118_3NTNNF9M_.ARC recid=91 stamp=639833741
Finished backup at 28-NOV-07

RMAN> exit

Connect to RMAN and Auxilliary

C:\Documents\dbmsdirect\Testing\StandBy>rman target / auxiliary sys/xxxx@standby

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Nov 28 13:38:52 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: TESTDB (DBID=2403050596)
connected to auxiliary database: TESTDB (not mounted)
RMAN>

Use Diplicate Database to Create the Standby from Primary

RMAN> run {
2> set until sequence = 117 thread = 1;
3> allocate auxiliary channel ch1 type disk;
4> duplicate target database for standby dorecover nofilenamecheck ;
5> }

executing command: SET until clause
using target database control file instead of recovery catalog

allocated channel: ch1
channel ch1: sid=101 devtype=DISK

Starting Duplicate Db at 28-NOV-07

contents of Memory Script:
{
set until scn 2216367;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 28-NOV-07

channel ch1: starting datafile backupset restore
channel ch1: restoring control file
channel ch1: reading from backup piece C:\DOCUMENTS\dbmsdirect\TESTING\STANDBY\STBY_CFILE.09J265GJ_1_1
channel ch1: restored backup piece 1
piece handle=C:\DOCUMENTS\dbmsdirect\TESTING\STANDBY\STBY_CFILE.09J265GJ_1_1 tag=TAG20071128T113338
channel ch1: restore complete, elapsed time: 00:00:07
output filename=C:\ORACLE\ORADATA\STANDBY\CONTROL01.CTL
output filename=C:\ORACLE\ORADATA\STANDBY\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\STANDBY\CONTROL03.CTL
Finished restore at 28-NOV-07

sql statement: alter database mount standby database

contents of Memory Script:
{
set until scn 2216367;
set newname for tempfile 1 to
"C:\ORACLE\ORADATA\STANDBY\TEMP01.DBF";
switch clone tempfile all;
set newname for datafile 1 to
"C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF";
set newname for datafile 3 to
"C:\ORACLE\ORADATA\STANDBY\SYSAUX01.DBF";
set newname for datafile 4 to
"C:\ORACLE\ORADATA\STANDBY\USERS01.DBF";
set newname for datafile 5 to
"C:\ORACLE\ORADATA\STANDBY\TBSALERT01.ORA";
set newname for datafile 6 to
"C:\ORACLE\ORADATA\STANDBY\DISC_EUL1.ORA";
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to C:\ORACLE\ORADATA\STANDBY\TEMP01.DBF in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 28-NOV-07

channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\STANDBY\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\STANDBY\USERS01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\STANDBY\TBSALERT01.ORA
restoring datafile 00006 to C:\ORACLE\ORADATA\STANDBY\DISC_EUL1.ORA
channel ch1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NNNDF_TAG20071128T112530_3NTN1CGF_.BKP
channel ch1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_NNNDF_TAG20071128T112530_3NTN1CGF_.BKP tag=TAG20071128T112530
channel ch1: restore complete, elapsed time: 00:02:25
Finished restore at 28-NOV-07

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=7 stamp=639841305 filename=C:\ORACLE\ORADATA\STANDBY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy recid=8 stamp=639841305 filename=C:\ORACLE\ORADATA\STANDBY\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=9 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=10 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=11 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\TBSALERT01.ORA
datafile 6 switched to datafile copy
input datafile copy recid=12 stamp=639841306 filename=C:\ORACLE\ORADATA\STANDBY\DISC_EUL1.ORA

contents of Memory Script:
{
set until scn 2216367;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 28-NOV-07

starting media recovery

channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=115
channel ch1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112743_3NTN5JLT_.BKP
channel ch1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T112743_3NTN5JLT_.BKP tag=TAG20071128T112743
channel ch1: restore complete, elapsed time: 00:00:04
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_115_3NTW147J_.ARC thread=1 sequence=115
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_115_3NTW147J_.ARC recid=1 stamp=639841316
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=116
channel ch1: reading from backup piece C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T113541_3NTNNHGQ_.BKP
channel ch1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2007_11_28\O1_MF_ANNNN_TAG20071128T113541_3NTNNHGQ_.BKP tag=TAG20071128T113541
channel ch1: restore complete, elapsed time: 00:00:02
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_116_3NTW1BHF_.ARC thread=1 sequence=116
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\STANDBY\ARCHIVELOG\2007_11_28\O1_MF_1_116_3NTW1BHF_.ARC recid=2 stamp=639841322
media recovery complete, elapsed time: 00:00:04
Finished recover at 28-NOV-07
Finished Duplicate Db at 28-NOV-07
released channel: ch1

RMAN>


To open a standby database for read-only access when it is currently shut down:

Start, mount, and open the database for read-only access using the following statement:
SQL> STARTUP;

To open a standby database for read-only access when it is currently performing Redo Apply:
Cancel Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Open the database for read-only access:
SQL> ALTER DATABASE OPEN;

To change the standby database from being open for read-only access to performing Redo Apply:

Terminate all active user sessions on the standby database.
Restart Redo Apply. To start Redo Apply, issue the following statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


Switchovers Involving a Physical Standby Database

Step 1 Verify it is possible to perform a switchover.

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Step 2 Initiate the switchover on the primary database.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Step 3 Shut down and restart the former primary instance.

Shut down the former primary instance, and restart and mount the database:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 4 Verify the switchover status in the V$DATABASE view.

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Step 5 Switch the target physical standby database role to the primary role.

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Step 6 Finish the transition of the standby database to the primary role.

ALTER DATABASE OPEN;

Step 7 If necessary, restart log apply services on the standby databases.

Step 8 Begin sending redo data to the standby databases.

ALTER SYSTEM SWITCH LOGFILE;


Useful Queries


SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2 ;

select * from V$ARCHIVE_GAP;

select max(sequence#) from v$archived_log where applied='YES';

Select process,status from v$managed_standby;

select * from v$dataguard_status;

select * from v$dataguard_stats;

Friday 4 April 2008

RMAN Backup and Recovery Example

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

I have been using this to backup and recover database. I thought as usual, I should share it.
This is a simple script and should be used with care. This assumes a full backup and recover. It does not take other scenarios into consideration.


RMAN Backup Script

I assume you are running Unix.

This script will help you. Save it as shell script (e.g rman_backup.sh) and make required changes.

# Change and to your own (e.g /u01/oracle/backup)

#!/usr/bin/sh

export ORACLE_SID=TEST
export ORACLE_HOME=$ORACLE_HOME

# Add date to be used in logfile
export TDAY=`date +%a`
export backup_dir = /u01/oracle/backup
export LOGFILE=$backup_dir/$SID_clone.log

echo "Backup Started at `date` \n" >$LOGFILE

$ORACLE_HOME/bin/rman <<'!' 1>> $LOGFILE 2>&1

# Connect to the database. Change this to Sys logon if not using /

connect target /

# Allocate Disk channels. Allocate more if you have enough max process to use

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;

#backup the whole source database.
# Use tags for easy separation from other backups during restore
backup
tag whole_database_open
format '$backup_dir/df_%u'
database;

# switch out of the current logfile
sql 'alter system archive log current';

#backup the archived logs
backup
archivelog all
format '$backup_dir/al_%u';

# backup a copy of the controlfile that contains records for the backups just made
backup
current controlfile
tag = cf1
format '$backup_dir/cf_%u';

}
exit

echo "Backup Finished at `date` \n" >>$LOGFILE


RMAN Recovery Script

This one of the routines I use to clone my database from one Server to another. so it may help you, but you have to use it with caution .
hope you are a DBA and can make all the required changes to the scripts. Whereever you see $, it means run from OS. RMAN> means run from RMAN

--I assume the RMAN Backup has been restored to the DISK as well
--I assume your system is Unix
--I assume you will run the commands manually (e.g. copy and paste). If you can script them, thats ok.


Esnure you Rebuild all your configuration as before
Set all your ORACLE_HOME etc.
Prepare all your init ora file as before (restore a previous copy is possible)

Create all the starting mount point as it was for your datafiles.

Logon to RMAN
$ORACLE_HOME/bin/rman

Run the following command. This part can also be scripted if required.

RMAN> connect target /

Startup the Instance with nomount

RMAN> startup nomount;

# add the init parameter file to the above if not on default location

#If you have or know DBID, set the DBID

RMAN> set dbid

Identify and Restore the control file.

RMAN> RESTORE CONTROLFILE FROM 'mount_point//';


Create a password file

$create password file orapwd file=$ORACLE_HOME/dbs/orapw password=

Modify the script below to use to restore the database


export ORACLE_SID=
export ORACLE_HOME==$ORACLE_HOME


$ORACLE_HOME/bin/rman

connect target /

# Mount the database

alter database mount;

# Allocate Disk channels.

RMAN> run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;


restore database;

# If your rman used a tag,add "from tag " after database above

}

# Check the LOGFILE for errors


# Recover the Database

RMAN> run {
SET UNTIL logseq = thread = 1;
RECOVER DATABASE;
}


# Add Temp files because your backup will not have them
# Example

ALTER TABLESPACE "TEMP"
ADD TEMPFILE '//<_tempfile_name>' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 4000M;


# Run reset logs

RMAN> alter database open resetlogs;