Total Recal flashback data archives is yet another cool feature of Oracle.
It's a recovery solution and the ability to track changes made on a table.
This is how you set it up and use it.
First you grant flashback archive administer to an user.
SQL>grant flashback archive administer to gda001;
Now we create a tablespace and a retention period of 1 year
SQL> create tablespace flashtest datafile '+DATA' size 50M;
SQL> create flashback archive DEFAULT flash1 tablespace flashtest
Now we create a test table
SQL> create table obj1 as select * from dba_objects;
Now we enable archive data on the table.
SQL>alter table obj1 flashback archive;
Table altered.
To see which tables area enabled for flashback archive
SQL>select TABLE_NAME,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME,STATUS
f rom USER_FLASHBACK_ARCHIVE_tables;
TABLE_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ -------------
OBJ1 FLASH1 SYS_FBA_HIST_72380 ENABLED
To see if compression is enabled
SQL> SELECT TABLE_NAME,PARTITION_NAME,COMPRESSION from USER_TAB_PARTITIONS;
TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
SYS_FBA_HIST_72380 HIGH_PART ENABLED
Now i'm going to delete some records
SQL> select count(*) from obj1;
COUNT(*)
----------
68894
SQl> delete from obj1 where rownum < 68000
SQL>commit;
SQL> select count(*) from obj1;
COUNT(*)
----------
904
wait 5 minutes and then
SQL> delete from obj1 where rownum < 800;
799 rows deleted.
SQL> select count(*) from obj1;
COUNT(*)
----------
105
To fake that we are using the flashback archive instead of undo i shutdown and start the database.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 680607744 bytes
Fixed Size 2231472 bytes
Variable Size 499123024 bytes
Database Buffers 171966464 bytes
Redo Buffers 7286784 bytes
Database mounted.
Database opened.
SQL> select count(*) from obj1
as of timestamp
to_timestamp ('2012-07-31 15:27:00','YYYY-MM-DD HH24:MI:SS');
COUNT(*)
----------
68994
now to find the one with the 105 records
SQL> select count(*) from obj1
as of timestamp
to_timestamp ('2012-07-31 15:32:00','YYYY-MM-DD HH24:MI:SS');
COUNT(*)
----------
105
Now you can off course disable flashback archive if you want
You will lose all historical data when you do this.
SQL> alter table obj2 no flashback archive;
Table altered.
The same happens when you drop of purge the pashback archive data.
SQL> Drop flashback archive flash1;
Flashback archive dropped.
SQL>Alter flashback archive flash1 PURGE ALL;
When on the table flashback aechive is enable some ddl statements fail
SQL> drop table OBJ1;
drop table OBJ1
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
but the same happens when you want to add a column or want to rename the table.
Enjoy this nice feature
Tuesday, July 31, 2012
Caputure Bind variables monitoring 11gr2
A developer ask me if i could give him the values of the bind variable.
There are several ways do this. In 11gr2 there is a way to capture the values of bind variable in currently running SQL.
You must have the enterprise edtion of Oracle 11gr2 and have the lisences for diagnostics & tuning packs.
If not i will show you other ways to get the values.
But here is an example of getting the values using oracle's real time monitoring
SQL>var id number
SQL>var naam varchar2(50)
SQL>exec :id :=71757;
SQL>exec :naam :='WRH$_SQLSTAT_PK';
Now run the query
SELECT
2 COUNT(*)
3 FROM
3 gda001.x a
5 , dba_objects b
6 , (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000) c
7 , (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000) d
8 where
9 a.object_id = b.object_id
10 and b.object_id < :id
11 and a.object_name != :naam
12 /
Now run the following statement while the above query is running
SQL> select xmltype(binds_xml) from v$sql_monitor where sid = 18 and status = 'EXECUTING';
The output will look like this
<binds>
<bind name=":SYS_B_1" pos="2" dty="2" dtystr="NUMBER" maxlen="22" len="2">10000</bind>
<bind name=":SYS_B_3" pos="4" dty="2" dtystr="NUMBER" maxlen="22" len="2">10000</bind>
<bind name=":ID" pos="5" dty="2" dtystr="NUMBER" maxlen="22" len="4">71757</bind>
<bind name=":NAAM" pos="6" dty="1" dtystr="VARCHAR2(2000)" maxlen="2000" csid="873" len="15">WRH$_SQLSTAT_PK</bind>
</binds>
And there you have it the values of the bind variables ID value 71757 and NAAM value wrh$_sqlstat_pk.
But what if you don't have the diagnostics & tuning packs or you use standard edition.
Well than you can use oradebug.
I use the same statement is above.
The open a sql session. You must have os process id (spid) for the session you use. In my case 12072
SQL> oradebug setospid 12072
Oracle pid: 31, Unix process pid: 12072, image: oracle@ota5070 (TNS V1-V3)
SQL> oradebug dump errorstack 3
Statement processed.
Now go to the place where you have set the udump and open the trace file.
Trace file /oracle/home/diag/rdbms/tst451p/TST451P1/trace/TST451P1_ora_12071.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/home/11.2.0.3.0
System name: Linux
Node name: ota5070
Release: 2.6.18-308.8.1.el5
Version: #1 SMP Fri May 4 16:43:02 EDT 2012
Machine: x86_64
VM name: VMWare Version: 6
Instance name: TST451P1
Redo thread mounted by this instance: 1
Oracle process number: 31
Unix process pid: 22336, image: oracle@ota5070 (TNS V1-V3)
*** 2012-07-31 10:55:05.199
*** SESSION ID:(277.12763) 2012-07-31 10:55:05.199
*** CLIENT ID:() 2012-07-31 10:55:05.199
*** SERVICE NAME:(SYS$USERS) 2012-07-31 10:55:05.199
*** MODULE NAME:(SQL*Plus) 2012-07-31 10:55:05.199
*** ACTION NAME:() 2012-07-31 10:55:05.199
Received ORADEBUG command (#1) 'dump errorstack 3' from process 'Unix process pid: 12072, image: <none>'
To locate the values of the bind variables search for Session Cursor Dump
----- Session Cursor Dump -----
Current cursor: 4, pgadep=0
Open cursors(pls, sys, hwm, max): 3(0, 2, 64, 1000)
NULL=2 SYNTAX=0 PARSE=0 BOUND=0 FETCH=1 ROW=0
Cached frame pages(total, free):
4k(14, 3), 8k(1, 1), 16k(1, 0), 32k(2, 2)
----- Current Cursor -----
xsc=0x2b30d3938e38 ctx=0x74469b88 pgactx=0x74469b88 ctxcbk=(nil) ctxqbc=(nil) ctxrws=0x7f56c158
----- Explain Plan Dump -----
----- Compact Format (Stream) -----
You can go down in the file from this point on and after the explain plan you find
Content of other_xml column
===========================
db_version : 11.2.0.3
parse_schema : GDA001
plan_hash : 1182576250
plan_hash_2 : 2708775469
outline : SYS_OUTLINE_12073016024169950
Peeked Binds
============
Bind variable information
position=5
datatype(code)=2
datatype(string)=NUMBER
precision=0
scale=0
max length=22
value=71757 Bind variable information
position=6
datatype(code)=1
datatype(string)=VARCHAR2(2000)
char set id=873
char format=1
max length=2000
value=WRH$_SQLSTAT_PK Outline Data:
/*+
BEGIN_OUTLINE_DATA
And in red you see the values of the bind variables.
a third way is being around since Oracle 10G and that is view called
$sql_bind_capture
Now to find the bind variables of the same query
SQL>select sql_id, .sql_text SQL_TEXT,
b.name BIND_NAME,
b.value_string BIND_STRING
from v$sql t join v$sql_bind_capture b using (sql_id)
where b.value_string is not null and sql_id='7z5czth4q5g4p'
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT :SYS_B_1 1000000
COUNT(*) FROM gda001.x e
, dba_objects d , (SELECT :"SYS_
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT C :SYS_B_3 1000000
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
OUNT(*) FROM gda001.x e
, dba_objects d , (SELECT :"SYS_
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT C :ID 71757
OUNT(*) FROM gda001.x e
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
, dba_objects d , (SELECT :"SYS_
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT C :NAAM WRH$_SQLSTAT_PK
OUNT(*) FROM gda001.x e
, dba_objects d , (SELECT :"SYS_
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
As this is a dynamic view after 30 minutes the data is not available anymore. if you want to see the data from longer ago you can use the view DBA_HIST_SQLBIND.
You need the sql_id and/of snapshot id. This means of course that this only works if you got
the diagnostics & tuning packs.
SQL>SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM DBA_HIST_SQLBIND where sql_id='7z5czth4q5g4p'
NAME POSITION DATATYPE_STRING VALUE_STRING
------------------------------ ---------- --------------- ------------------------------
:SYS_B_0 1 VARCHAR2(32)
:SYS_B_1 2 NUMBER 1000000
:SYS_B_2 3 VARCHAR2(32)
:SYS_B_3 4 NUMBER 1000000
:ID 5 NUMBER 71757
:NAAM 6 VARCHAR2(32) WRH$_SQLSTAT_PK
6 rows selected.
And there you have them.
Thats it
There are several ways do this. In 11gr2 there is a way to capture the values of bind variable in currently running SQL.
You must have the enterprise edtion of Oracle 11gr2 and have the lisences for diagnostics & tuning packs.
If not i will show you other ways to get the values.
But here is an example of getting the values using oracle's real time monitoring
SQL>var id number
SQL>var naam varchar2(50)
SQL>exec :id :=71757;
SQL>exec :naam :='WRH$_SQLSTAT_PK';
Now run the query
SELECT
2 COUNT(*)
3 FROM
3 gda001.x a
5 , dba_objects b
6 , (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000) c
7 , (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000) d
8 where
9 a.object_id = b.object_id
10 and b.object_id < :id
11 and a.object_name != :naam
12 /
Now run the following statement while the above query is running
SQL> select xmltype(binds_xml) from v$sql_monitor where sid = 18 and status = 'EXECUTING';
The output will look like this
<binds>
<bind name=":SYS_B_1" pos="2" dty="2" dtystr="NUMBER" maxlen="22" len="2">10000</bind>
<bind name=":SYS_B_3" pos="4" dty="2" dtystr="NUMBER" maxlen="22" len="2">10000</bind>
<bind name=":ID" pos="5" dty="2" dtystr="NUMBER" maxlen="22" len="4">71757</bind>
<bind name=":NAAM" pos="6" dty="1" dtystr="VARCHAR2(2000)" maxlen="2000" csid="873" len="15">WRH$_SQLSTAT_PK</bind>
</binds>
And there you have it the values of the bind variables ID value 71757 and NAAM value wrh$_sqlstat_pk.
But what if you don't have the diagnostics & tuning packs or you use standard edition.
Well than you can use oradebug.
I use the same statement is above.
The open a sql session. You must have os process id (spid) for the session you use. In my case 12072
SQL> oradebug setospid 12072
Oracle pid: 31, Unix process pid: 12072, image: oracle@ota5070 (TNS V1-V3)
SQL> oradebug dump errorstack 3
Statement processed.
Now go to the place where you have set the udump and open the trace file.
Trace file /oracle/home/diag/rdbms/tst451p/TST451P1/trace/TST451P1_ora_12071.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/home/11.2.0.3.0
System name: Linux
Node name: ota5070
Release: 2.6.18-308.8.1.el5
Version: #1 SMP Fri May 4 16:43:02 EDT 2012
Machine: x86_64
VM name: VMWare Version: 6
Instance name: TST451P1
Redo thread mounted by this instance: 1
Oracle process number: 31
Unix process pid: 22336, image: oracle@ota5070 (TNS V1-V3)
*** 2012-07-31 10:55:05.199
*** SESSION ID:(277.12763) 2012-07-31 10:55:05.199
*** CLIENT ID:() 2012-07-31 10:55:05.199
*** SERVICE NAME:(SYS$USERS) 2012-07-31 10:55:05.199
*** MODULE NAME:(SQL*Plus) 2012-07-31 10:55:05.199
*** ACTION NAME:() 2012-07-31 10:55:05.199
Received ORADEBUG command (#1) 'dump errorstack 3' from process 'Unix process pid: 12072, image: <none>'
To locate the values of the bind variables search for Session Cursor Dump
----- Session Cursor Dump -----
Current cursor: 4, pgadep=0
Open cursors(pls, sys, hwm, max): 3(0, 2, 64, 1000)
NULL=2 SYNTAX=0 PARSE=0 BOUND=0 FETCH=1 ROW=0
Cached frame pages(total, free):
4k(14, 3), 8k(1, 1), 16k(1, 0), 32k(2, 2)
----- Current Cursor -----
xsc=0x2b30d3938e38 ctx=0x74469b88 pgactx=0x74469b88 ctxcbk=(nil) ctxqbc=(nil) ctxrws=0x7f56c158
----- Explain Plan Dump -----
----- Compact Format (Stream) -----
You can go down in the file from this point on and after the explain plan you find
Content of other_xml column
===========================
db_version : 11.2.0.3
parse_schema : GDA001
plan_hash : 1182576250
plan_hash_2 : 2708775469
outline : SYS_OUTLINE_12073016024169950
Peeked Binds
============
Bind variable information
position=5
datatype(code)=2
datatype(string)=NUMBER
precision=0
scale=0
max length=22
value=71757 Bind variable information
position=6
datatype(code)=1
datatype(string)=VARCHAR2(2000)
char set id=873
char format=1
max length=2000
value=WRH$_SQLSTAT_PK Outline Data:
/*+
BEGIN_OUTLINE_DATA
And in red you see the values of the bind variables.
a third way is being around since Oracle 10G and that is view called
$sql_bind_capture
Now to find the bind variables of the same query
SQL>select sql_id, .sql_text SQL_TEXT,
b.name BIND_NAME,
b.value_string BIND_STRING
from v$sql t join v$sql_bind_capture b using (sql_id)
where b.value_string is not null and sql_id='7z5czth4q5g4p'
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT :SYS_B_1 1000000
COUNT(*) FROM gda001.x e
, dba_objects d , (SELECT :"SYS_
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT C :SYS_B_3 1000000
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
OUNT(*) FROM gda001.x e
, dba_objects d , (SELECT :"SYS_
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT C :ID 71757
OUNT(*) FROM gda001.x e
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
, dba_objects d , (SELECT :"SYS_
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
7z5czth4q5g4p /* SQL Analyze(17,1) */ SELECT C :NAAM WRH$_SQLSTAT_PK
OUNT(*) FROM gda001.x e
, dba_objects d , (SELECT :"SYS_
SQL_ID SQL_TEXT BIND_NAME BIND_STRING
------------- ---------------------------------------- ---------- ------------------------------
B_0" FROM dual CONNECT BY LEVEL <= :"SYS
_B_1") x , (SELECT :"SYS_B_2" FROM
dual CONNECT BY LEVEL <= :"SYS_B_3") y
WHERE e.object_id = d.object
_id AND d.object_id < :id AND e.OBJ
ECT_NAME != :naam
As this is a dynamic view after 30 minutes the data is not available anymore. if you want to see the data from longer ago you can use the view DBA_HIST_SQLBIND.
You need the sql_id and/of snapshot id. This means of course that this only works if you got
the diagnostics & tuning packs.
SQL>SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM DBA_HIST_SQLBIND where sql_id='7z5czth4q5g4p'
NAME POSITION DATATYPE_STRING VALUE_STRING
------------------------------ ---------- --------------- ------------------------------
:SYS_B_0 1 VARCHAR2(32)
:SYS_B_1 2 NUMBER 1000000
:SYS_B_2 3 VARCHAR2(32)
:SYS_B_3 4 NUMBER 1000000
:ID 5 NUMBER 71757
:NAAM 6 VARCHAR2(32) WRH$_SQLSTAT_PK
6 rows selected.
And there you have them.
Thats it
Monday, July 30, 2012
Create Logical Standby Database
A logical standby database is created by firs tcreating a physicale standby database.
For how to do this see my blog Create physical standby database with Oracle 11gr2 from active database
The primary database is the asm451p1
The logical standby database will be the asm452p1
First stop recovery on the physical standby database asm452p1
SQL> alter database recover managed standby database cancel;
On the primary database asm451p1 create the logminer catalog;
SQL> execute dbms_logstdby.build;
SQL> alter system switch logfile;
Now start the standby database asm452p1 with open resetlogs
SQL>alter database recover to logical standby asm452p;
SQL> alter database open resetlogs;
SQL> alter database start logical standby apply immediate;
With the apply immediate changes on the primary are send to the logical standby database
standby redologfiles; Otherwise you have to waite for a log switch to send the data.
Now you can create procedures for instance to create datafile on the logical standby database
after is created on the primary database.
create or replace procedure sys.handle_tbs_ddl (
old_stmt in varchar2
, stmt_typ in varchar2
, schema in varchar2
, name in varchar2
, xidusn in number
, xidslt in number
, xidsqn in number
, action out number
, new_stmt out varchar2
) as
begin
new_stmt := replace(old_stmt, 'ASM451P', 'ASM452P');
action := dbms_logstdby.skip_action_replace;
exception
when others then
action := dbms_logstdby.skip_action_error;
new_stmt := null;
end handle_tbs_ddl;
/
Now stop the logical apply process
SQL> alter database stop logical standby apply;
SQL> execute dbms_logstdby.skip(stmt => 'TABLESPACE', proc_name => 'sys.handle_tbs_ddl');
SQL> alter database start logical standby apply immediate;
Check if logical standby database is applied.
SQL> select * from dba_logstdby_progress;
READ_SCN READ_THREAD# READ_SEQUENCE# READ_TIME NEWEST_SCN NEWEST_THREAD# NEWEST_SEQUENCE#
------------------------------ ------------------------------ ------------------------------ --------- ------------------------------ ------------------------------ ------------------------------ --------- ------------------------------ ------------------------------ ------------------------------
NEWEST_TI
---------
79088719872 1 7906 30-JUL-12 79088718344 1 7906 30-JUL-12 79088719872 1 7906
30-JUL-12
To see why the logical standby database is not applying anymore
SQL> select * from logstdby_events
order by 1 desc
To skip a failed transaction.
SQL>alter database start logical standby apply immediate skip failed transaction;
When the logical standby database is running behind you can speed a things.
SQL>ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> execute dbms_logstdby.apply_set('MAX_SGA',500);
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
SQL>ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL>EXECUTE DBMS_LOGSTDBY.APPLY_SET(‘MAX_SERVERS’,15);
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Temporary tables can't be created on a logical standby. To prevent this
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL>EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'GINO', object_name => 'DBMS_TABCOMP_TEMP_UNCMP', proc_name => null);
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
To skip that the AUD$ get filled on the logical standby database
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> TRUNCATE TABLE SYS.AUD$;
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> exec dbms_logstdby.skip('DML','SYS','AUD$');
SQL> exec dbms_logstdby.skip('SCHEMA_DDL','SYS','AUD$');
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
To temporary set off the logical standby database
On the primary database ASM451P1
SQL> alter system set log_archive_dest_state_2 = 'DEFER' scope = both;
SQL> alter system set log_archive_dest_2 = '' scope = both;
To put it on again
On the primary database ASM451P1
SQL>SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=ASM452P LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ASM452P REGISTER NET_TIMEOUT=30' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'ENABLE' SCOPE=BOTH;
Thats it.
For how to do this see my blog Create physical standby database with Oracle 11gr2 from active database
The primary database is the asm451p1
The logical standby database will be the asm452p1
First stop recovery on the physical standby database asm452p1
SQL> alter database recover managed standby database cancel;
On the primary database asm451p1 create the logminer catalog;
SQL> execute dbms_logstdby.build;
SQL> alter system switch logfile;
Now start the standby database asm452p1 with open resetlogs
SQL>alter database recover to logical standby asm452p;
SQL> alter database open resetlogs;
SQL> alter database start logical standby apply immediate;
With the apply immediate changes on the primary are send to the logical standby database
standby redologfiles; Otherwise you have to waite for a log switch to send the data.
Now you can create procedures for instance to create datafile on the logical standby database
after is created on the primary database.
create or replace procedure sys.handle_tbs_ddl (
old_stmt in varchar2
, stmt_typ in varchar2
, schema in varchar2
, name in varchar2
, xidusn in number
, xidslt in number
, xidsqn in number
, action out number
, new_stmt out varchar2
) as
begin
new_stmt := replace(old_stmt, 'ASM451P', 'ASM452P');
action := dbms_logstdby.skip_action_replace;
exception
when others then
action := dbms_logstdby.skip_action_error;
new_stmt := null;
end handle_tbs_ddl;
/
Now stop the logical apply process
SQL> alter database stop logical standby apply;
SQL> execute dbms_logstdby.skip(stmt => 'TABLESPACE', proc_name => 'sys.handle_tbs_ddl');
SQL> alter database start logical standby apply immediate;
Check if logical standby database is applied.
SQL> select * from dba_logstdby_progress;
READ_SCN READ_THREAD# READ_SEQUENCE# READ_TIME NEWEST_SCN NEWEST_THREAD# NEWEST_SEQUENCE#
------------------------------ ------------------------------ ------------------------------ --------- ------------------------------ ------------------------------ ------------------------------ --------- ------------------------------ ------------------------------ ------------------------------
NEWEST_TI
---------
79088719872 1 7906 30-JUL-12 79088718344 1 7906 30-JUL-12 79088719872 1 7906
30-JUL-12
To see why the logical standby database is not applying anymore
SQL> select * from logstdby_events
order by 1 desc
To skip a failed transaction.
SQL>alter database start logical standby apply immediate skip failed transaction;
When the logical standby database is running behind you can speed a things.
SQL>ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> execute dbms_logstdby.apply_set('MAX_SGA',500);
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
SQL>ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL>EXECUTE DBMS_LOGSTDBY.APPLY_SET(‘MAX_SERVERS’,15);
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Temporary tables can't be created on a logical standby. To prevent this
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL>EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'GINO', object_name => 'DBMS_TABCOMP_TEMP_UNCMP', proc_name => null);
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
To skip that the AUD$ get filled on the logical standby database
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> TRUNCATE TABLE SYS.AUD$;
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> exec dbms_logstdby.skip('DML','SYS','AUD$');
SQL> exec dbms_logstdby.skip('SCHEMA_DDL','SYS','AUD$');
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
To temporary set off the logical standby database
On the primary database ASM451P1
SQL> alter system set log_archive_dest_state_2 = 'DEFER' scope = both;
SQL> alter system set log_archive_dest_2 = '' scope = both;
To put it on again
On the primary database ASM451P1
SQL>SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=ASM452P LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ASM452P REGISTER NET_TIMEOUT=30' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'ENABLE' SCOPE=BOTH;
Thats it.
Subscribe to:
Posts (Atom)