The below script should restart aborted GG processes.
The script is capable of restarting Manager, Extract and Replicat Processes.
The logic is it just looks at info all command (of ggsci) and attempts to restart any process which it sees as not RUNNING .
A non-running process can be either in STOPPED or ABORTED status.
It is intelligent enough to find out whether the stopped process is an extract, replicat or manager.
Please note I have used a script call ed /usr/local/bin/sid to set the env parameters. This script can be replaced by your own script or by setting env variables manually.
#!/bin/ksh
. /usr/local/bin/sid xxx
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export GG_HOME=/oracle/software/goldengate/11.1.1.1.2
export PATH=$PATH:.
MSGFILE="/tmp/mail.txt"
EMAILFILE="/tmp/email.txt"
SERVERNAME=`hostname`
rm -rf $EMAILFILE 2>/dev/null
cd $GG_HOME
ggsci <<EOF >$MSGFILE
info all
exit
EOF
ERROR_CTR=`cat $MSGFILE|grep -v -i "running"|wc -l`
#following loop is for manager process restart
if [ $ERROR_CTR -ne 0 ] ; then
cat $MSGFILE|grep -v -i "running"|grep -i "manager"|while read LINE ;do
echo "Manager has aborted ">>$EMAILFILE
echo "Attempting to restart" >>$EMAILFILE
cd $GG_HOME
ggsci <<EOF >>$EMAILFILE
start mgr
info all
exit
EOF
done
fi
#following loop is for extract process restart
if [ $ERROR_CTR -ne 0 ] ; then
cat $MSGFILE|grep -v -i "running"|grep -i "extract"|awk ' {print $3 } '|while read LINE ;do
echo "EXTRACT $LINE has aborted ">>$EMAILFILE
echo "attempting to restart" >>$EMAILFILE
cd $GG_HOME
ggsci <<EOF >>$EMAILFILE
start extract $LINE
info all
exit
EOF
done
fi
#following loop is for replicat process restart
if [ $ERROR_CTR -ne 0 ] ; then
cat $MSGFILE|grep -v -i "running"|grep -i "replicat"|awk ' {print $3 } '|while read LINE ;do
echo "REPLICAT $LINE has aborted ">>$EMAILFILE
echo "Attempting to restart" >>$EMAILFILE
cd $GG_HOME
ggsci <<EOF >>$EMAILFILE
start replicat $LINE
info all
exit
EOF
done
fi
[ -f $EMAILFILE ] && cat $EMAILFILE|mailx -s "Extract died on $SERVERNAME" gautha@xxx.xxx
Friday, June 8, 2012
Accurately predicting the redo log sequence needed for a Golden Gate Restart
Bounded Recovery is set to 20 Minutes.
1. Started an active transaction which runs for 5 hours at 8.11AM.
The log sequence at the start of transaction is 16230 (please see below logfile).
jupiter<oracle>/tmp> cat test.sh
#!/bin/ksh
. /usr/local/bin/sid athenadv
sqlplus ops\$gchandra/test123 << EOF >test.log
select max(sequence#) from v\$log;
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
drop table testtab;
create table testtab (col1 number);
insert into testtab values(1);
execute dbms_lock.sleep(18000);
commit;
exit
EOF
Run the above script(in the background)
nohup ./test.sh &
Now check the spool file created.....
jupiter<oracle>/tmp> cat test.log
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 8 08:11:22 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
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
SQL> SQL>
MAX(SEQUENCE#)
--------------
16230
SQL>
TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
08-jun-2012 08:11:22
SQL>
Table dropped.
SQL>
Table created.
SQL>
1 row created.
2.
At around 10am, I stop the extract process.
GGSCI (jupiter) 2> stop ext xtathdv
Sending STOP request to EXTRACT XTATHDV ...
Request processed.
3. Check the BR checkpoint from ggserr.log and also from BR directory.
It hapenned at around 9.42am.
2012-06-08 09:42:24 INFO OGG-01738 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p28822_extr: start=SeqNo: 16293, RBA: 75422736, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000, end=SeqNo: 16293, RBA: 75423744, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
jupiter<oracle>/oracle/software/goldengate/11.1.1.1.2/BR/XTATHDV> ls -lt
total 88
-rw-rw---- 1 oracle dba 65536 Jun 8 09:42 CP.XTATHDV.000000039
-rw-rw---- 1 oracle dba 65536 Jun 8 09:22 PO.XTATHDV.0000007.0000000
-rw-rw---- 1 oracle dba 65536 Jun 8 08:42 PO.XTATHDV.0000006.0000000
drwxrwxr-x 2 oracle dba 96 Jun 7 15:54 stale
4.Check the archive log sequence at around 9.42am.
select sequence# from v$archived_log
where to_date('08-jun-2012 09:42:00','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;
SQL> select sequence# from v$archived_log
2 where to_date('08-jun-2012 09:42:00','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;
SEQUENCE#
----------
16293
This means I need only archive log from sequence 16293 when I restart GG extract process.
I should not need from sequence 16230(log sequence # from start of transaction).
5.Perform a few log switches and delete all archive files from RMAN.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
Run the following from rman -
run {
allocate channel c1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/oracle/admin/backup/config/jupiter_tdpo.opt)';
backup
filesperset = 4
format 'al_%d%s%t_%U'
archivelog until time 'SYSDATE'
delete all input;
sql 'alter system archive log current';
release channel c1;
}
6.Kill the test.sh script.
jupiter<oracle>/oracle/arch/athenadv/arch> ps -ef|grep test.sh
oracle 5072 1 0 08:11:22 ? 0:00 /bin/ksh ./test.sh
oracle 15014 26448 0 10:16:01 pts/6 0:00 grep test.sh
jupiter<oracle>/oracle/arch/athenadv/arch> ps -ef|grep sqlplus
oracle 5077 5072 0 08:11:22 ? 0:00 sqlplus ops$gchandra/test123
oracle 15018 26448 1 10:16:08 pts/6 0:00 grep sqlplus
jupiter<oracle>/oracle/arch/athenadv/arch> kill -9 5077
jupiter<oracle>/oracle/arch/athenadv/arch>
jupiter<oracle>/oracle/arch/athenadv/arch> ps -ef|grep test.sh
oracle 16879 26448 1 10:16:27 pts/6 0:00 grep test.sh
jupiter<oracle>/oracle/arch/athenadv/arch>
7.
GGSCI (jupiter) 2> start ext xtathdv
Sending START request to MANAGER ...
EXTRACT XTATHDV starting
2012-06-08 10:44:42 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start ext xtathdv.
2012-06-08 10:44:42 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 10.64.17.19 (START EXTRACT XTATHDV ).
2012-06-08 10:44:42 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT XTATHDV starting.
2012-06-08 10:44:42 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, xtathdv.prm: EXTRACT XTATHDV starting.
2012-06-08 10:44:43 INFO OGG-01639 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: ACTIVE: for object pool 1: p28822_extr.
2012-06-08 10:44:43 INFO OGG-01640 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start XID: 0.0.0.
2012-06-08 10:44:43 INFO OGG-01641 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start position: SeqNo: 16293, RBA: 75422736, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:44:43 INFO OGG-01642 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery end position: SeqNo: 16293, RBA: 75423744, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:44:43 INFO OGG-01579 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: VALID BCP: CP.XTATHDV.000000039.
2012-06-08 10:44:43 INFO OGG-01629 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: 2.
2012-06-08 10:44:45 INFO OGG-01513 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Positioning to Sequence 16293, RBA 75422736.
2012-06-08 10:46:12 ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Could not find archived log for sequence 16293 thread 1 under default destinations SQL <SELECT name FROM v$archived_log WHERE sequence# = :ora_seq_no AND thread# = :ora_thread AND resetlogs_id = :ora_resetlog_id AND archived = 'YES' AND deleted = 'NO>, error retrieving redo file name for sequence 16293, archived = 1, use_alternate = 0Not able to establish initial position for sequence 16293, rba 75422736.
2012-06-08 10:46:12 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, xtathdv.prm: PROCESS ABENDING.
The above log extract says that it reads from 16293. That is as per my expectation.
Since 16293 is not present in archive destination, the GG process abends as expected.
8.Restore the archive log from rman
RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 16293;
Starting restore at 08-Jun-2012
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=112 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.2.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=594 device type=DISK
archived log for thread 1 with sequence 16348 is already on disk as file /oracle/arch/athenadv/arch/1_16348_783838261.dbf
archived log for thread 1 with sequence 16349 is already on disk as file /oracle/arch/athenadv/arch/1_16349_783838261.dbf
..
..
..
9. Restart the GG extract process and observe the logfile
GGSCI (jupiter) 3> start ext xtathdv
Sending START request to MANAGER ...
EXTRACT XTATHDV starting
Observe the logfile
2012-06-08 10:51:46 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT XTATHDV starting.
2012-06-08 10:51:46 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, xtathdv.prm: EXTRACT XTATHDV starting.
2012-06-08 10:51:46 INFO OGG-01639 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: ACTIVE: for object pool 1: p28822_extr.
2012-06-08 10:51:46 INFO OGG-01640 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start XID: 0.0.0.
2012-06-08 10:51:46 INFO OGG-01641 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start position: SeqNo: 16293, RBA: 75422736, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:51:46 INFO OGG-01642 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery end position: SeqNo: 16293, RBA: 75423744, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:51:46 INFO OGG-01579 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: VALID BCP: CP.XTATHDV.000000039.
2012-06-08 10:51:46 INFO OGG-01629 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: 2.
2012-06-08 10:51:49 INFO OGG-01513 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Positioning to Sequence 16293, RBA 75422736.
2012-06-08 10:51:49 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Positioned to Sequence 16293, RBA 75422736, Jun 8, 2012 9:42:23 AM.
2012-06-08 10:51:49 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, xtathdv.prm: EXTRACT XTATHDV started.
2012-06-08 10:51:49 INFO OGG-01055 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery initialization completed for target file /oracle/goldengatedata/dirdat/at000011, at RBA 32117725.
2012-06-08 10:51:49 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Output file /oracle/goldengatedata/dirdat/at is using format RELEASE 10.4/11.1.
2012-06-08 10:51:49 INFO OGG-01026 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Rolling over remote file /oracle/goldengatedata/dirdat/at000011.
2012-06-08 10:51:49 INFO OGG-01053 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery completed for target file /oracle/goldengatedata/dirdat/at000012, at RBA 1032.
2012-06-08 10:51:49 INFO OGG-01057 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery completed for all targets.
2012-06-08 10:51:50 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Position of first record processed Sequence 16293, RBA 75422736, SCN 1932.1174983725, Jun 8, 2012 9:42:23 AM.
2012-06-08 10:51:50 INFO OGG-01644 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: COMPLETE: for object pool 1: p28822_extr at SeqNo: 16293, RBA: 75423760, SCN: 1932.1174983729 (8299051799601).
2012-06-08 10:52:37 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, xtathdv.prm: No unique key is defined for table DV3_STATUS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Looks like GG extract is chuggling along.
Hope you find this useful.
1. Started an active transaction which runs for 5 hours at 8.11AM.
The log sequence at the start of transaction is 16230 (please see below logfile).
jupiter<oracle>/tmp> cat test.sh
#!/bin/ksh
. /usr/local/bin/sid athenadv
sqlplus ops\$gchandra/test123 << EOF >test.log
select max(sequence#) from v\$log;
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
drop table testtab;
create table testtab (col1 number);
insert into testtab values(1);
execute dbms_lock.sleep(18000);
commit;
exit
EOF
Run the above script(in the background)
nohup ./test.sh &
Now check the spool file created.....
jupiter<oracle>/tmp> cat test.log
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 8 08:11:22 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
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
SQL> SQL>
MAX(SEQUENCE#)
--------------
16230
SQL>
TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
08-jun-2012 08:11:22
SQL>
Table dropped.
SQL>
Table created.
SQL>
1 row created.
2.
At around 10am, I stop the extract process.
GGSCI (jupiter) 2> stop ext xtathdv
Sending STOP request to EXTRACT XTATHDV ...
Request processed.
3. Check the BR checkpoint from ggserr.log and also from BR directory.
It hapenned at around 9.42am.
2012-06-08 09:42:24 INFO OGG-01738 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p28822_extr: start=SeqNo: 16293, RBA: 75422736, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000, end=SeqNo: 16293, RBA: 75423744, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
jupiter<oracle>/oracle/software/goldengate/11.1.1.1.2/BR/XTATHDV> ls -lt
total 88
-rw-rw---- 1 oracle dba 65536 Jun 8 09:42 CP.XTATHDV.000000039
-rw-rw---- 1 oracle dba 65536 Jun 8 09:22 PO.XTATHDV.0000007.0000000
-rw-rw---- 1 oracle dba 65536 Jun 8 08:42 PO.XTATHDV.0000006.0000000
drwxrwxr-x 2 oracle dba 96 Jun 7 15:54 stale
4.Check the archive log sequence at around 9.42am.
select sequence# from v$archived_log
where to_date('08-jun-2012 09:42:00','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;
SQL> select sequence# from v$archived_log
2 where to_date('08-jun-2012 09:42:00','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;
SEQUENCE#
----------
16293
This means I need only archive log from sequence 16293 when I restart GG extract process.
I should not need from sequence 16230(log sequence # from start of transaction).
5.Perform a few log switches and delete all archive files from RMAN.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
Run the following from rman -
run {
allocate channel c1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/oracle/admin/backup/config/jupiter_tdpo.opt)';
backup
filesperset = 4
format 'al_%d%s%t_%U'
archivelog until time 'SYSDATE'
delete all input;
sql 'alter system archive log current';
release channel c1;
}
6.Kill the test.sh script.
jupiter<oracle>/oracle/arch/athenadv/arch> ps -ef|grep test.sh
oracle 5072 1 0 08:11:22 ? 0:00 /bin/ksh ./test.sh
oracle 15014 26448 0 10:16:01 pts/6 0:00 grep test.sh
jupiter<oracle>/oracle/arch/athenadv/arch> ps -ef|grep sqlplus
oracle 5077 5072 0 08:11:22 ? 0:00 sqlplus ops$gchandra/test123
oracle 15018 26448 1 10:16:08 pts/6 0:00 grep sqlplus
jupiter<oracle>/oracle/arch/athenadv/arch> kill -9 5077
jupiter<oracle>/oracle/arch/athenadv/arch>
jupiter<oracle>/oracle/arch/athenadv/arch> ps -ef|grep test.sh
oracle 16879 26448 1 10:16:27 pts/6 0:00 grep test.sh
jupiter<oracle>/oracle/arch/athenadv/arch>
7.
GGSCI (jupiter) 2> start ext xtathdv
Sending START request to MANAGER ...
EXTRACT XTATHDV starting
2012-06-08 10:44:42 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start ext xtathdv.
2012-06-08 10:44:42 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 10.64.17.19 (START EXTRACT XTATHDV ).
2012-06-08 10:44:42 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT XTATHDV starting.
2012-06-08 10:44:42 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, xtathdv.prm: EXTRACT XTATHDV starting.
2012-06-08 10:44:43 INFO OGG-01639 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: ACTIVE: for object pool 1: p28822_extr.
2012-06-08 10:44:43 INFO OGG-01640 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start XID: 0.0.0.
2012-06-08 10:44:43 INFO OGG-01641 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start position: SeqNo: 16293, RBA: 75422736, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:44:43 INFO OGG-01642 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery end position: SeqNo: 16293, RBA: 75423744, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:44:43 INFO OGG-01579 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: VALID BCP: CP.XTATHDV.000000039.
2012-06-08 10:44:43 INFO OGG-01629 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: 2.
2012-06-08 10:44:45 INFO OGG-01513 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Positioning to Sequence 16293, RBA 75422736.
2012-06-08 10:46:12 ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Could not find archived log for sequence 16293 thread 1 under default destinations SQL <SELECT name FROM v$archived_log WHERE sequence# = :ora_seq_no AND thread# = :ora_thread AND resetlogs_id = :ora_resetlog_id AND archived = 'YES' AND deleted = 'NO>, error retrieving redo file name for sequence 16293, archived = 1, use_alternate = 0Not able to establish initial position for sequence 16293, rba 75422736.
2012-06-08 10:46:12 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, xtathdv.prm: PROCESS ABENDING.
The above log extract says that it reads from 16293. That is as per my expectation.
Since 16293 is not present in archive destination, the GG process abends as expected.
8.Restore the archive log from rman
RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 16293;
Starting restore at 08-Jun-2012
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=112 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.2.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=594 device type=DISK
archived log for thread 1 with sequence 16348 is already on disk as file /oracle/arch/athenadv/arch/1_16348_783838261.dbf
archived log for thread 1 with sequence 16349 is already on disk as file /oracle/arch/athenadv/arch/1_16349_783838261.dbf
..
..
..
9. Restart the GG extract process and observe the logfile
GGSCI (jupiter) 3> start ext xtathdv
Sending START request to MANAGER ...
EXTRACT XTATHDV starting
Observe the logfile
2012-06-08 10:51:46 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT XTATHDV starting.
2012-06-08 10:51:46 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, xtathdv.prm: EXTRACT XTATHDV starting.
2012-06-08 10:51:46 INFO OGG-01639 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: ACTIVE: for object pool 1: p28822_extr.
2012-06-08 10:51:46 INFO OGG-01640 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start XID: 0.0.0.
2012-06-08 10:51:46 INFO OGG-01641 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start position: SeqNo: 16293, RBA: 75422736, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:51:46 INFO OGG-01642 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery end position: SeqNo: 16293, RBA: 75423744, SCN: 1932.1174983728 (8299051799600), Timestamp: 2012-06-08 09:42:23.000000.
2012-06-08 10:51:46 INFO OGG-01579 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: VALID BCP: CP.XTATHDV.000000039.
2012-06-08 10:51:46 INFO OGG-01629 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: 2.
2012-06-08 10:51:49 INFO OGG-01513 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Positioning to Sequence 16293, RBA 75422736.
2012-06-08 10:51:49 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Positioned to Sequence 16293, RBA 75422736, Jun 8, 2012 9:42:23 AM.
2012-06-08 10:51:49 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, xtathdv.prm: EXTRACT XTATHDV started.
2012-06-08 10:51:49 INFO OGG-01055 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery initialization completed for target file /oracle/goldengatedata/dirdat/at000011, at RBA 32117725.
2012-06-08 10:51:49 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Output file /oracle/goldengatedata/dirdat/at is using format RELEASE 10.4/11.1.
2012-06-08 10:51:49 INFO OGG-01026 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Rolling over remote file /oracle/goldengatedata/dirdat/at000011.
2012-06-08 10:51:49 INFO OGG-01053 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery completed for target file /oracle/goldengatedata/dirdat/at000012, at RBA 1032.
2012-06-08 10:51:49 INFO OGG-01057 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery completed for all targets.
2012-06-08 10:51:50 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Position of first record processed Sequence 16293, RBA 75422736, SCN 1932.1174983725, Jun 8, 2012 9:42:23 AM.
2012-06-08 10:51:50 INFO OGG-01644 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: COMPLETE: for object pool 1: p28822_extr at SeqNo: 16293, RBA: 75423760, SCN: 1932.1174983729 (8299051799601).
2012-06-08 10:52:37 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, xtathdv.prm: No unique key is defined for table DV3_STATUS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Looks like GG extract is chuggling along.
Hope you find this useful.
Thursday, June 7, 2012
Golden Gate Bounded Recovery ( BR) Process
I created a sample script called test.sh and introduced a sleeptime of 5 hours(or 18,000 seconds).
Make sure below user has DBA privs so that he can query from v$log(to check the log sequence).
++++++Contents of test.sh +++++++++++++++++++++++++
#!/bin/ksh
sqlplus ops\$gchandra/test123 << EOF >test.log
select max(sequence#) from v\$log;
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
drop table testtab;
create table testtab (col1 number);
insert into testtab values(1);
execute dbms_lock.sleep(18000);
commit;
exit
EOF
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
I run the script in background with no interruption.
jupiter<oracle>/tmp> nohup ./test.sh &
[1] 28917
jupiter<oracle>/oracle/arch/athenadv/arch> cat /tmp/test.log
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 7 20:29:25 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
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
SQL> SQL>
MAX(SEQUENCE#)
--------------
15625
SQL>
TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
07-jun-2012 20:29:26
SQL>
Table dropped.
SQL>
Table created.
SQL>
1 row created.
The point to note is if the GG process is stopped or aborts at this point of time and restarted later, it needs
access to redo log from sequence 15625 . This is because uncomitted transactions are never written to the trail file
by golden gate. Only commited transactions are written.
There is something called bounded recovery which is basically a time period i.e 4 hours by default.
After every BR time interval , GG looks at the active transactions which qualify as old transactions ( i.e
they have been active for more than BR time period) and writes it to a temp location GG HOME/BR/<extract name> .
For example if the BR is set to 4 hours(default), and say a BR flush happens at 9am. At that time it scans all the active transactions
which started earlier than 5am (9am -4 hrs).
Say there were say 2 active transactions A and B .Transaction A started at 5.01am and transaction B started at
3.59am. Therefore transaction B is flushed to the BR location and transaction A is ignored as it has not been running for 4 hours.
Therefore if the golden gate crashed just a few seconds before the next flushout i.e.1pm , then transaction A which started
at 5.01am is lost.Therefore when you restart the GG it needs archive logs generated from 5.01am.
So in this worst case scenario , it needs access to log files(or archive files) from 2 times BR interval.
So prepare to retain archivelogs (or have capacity to restore archive logs) for 2 X (BR Interval period).
Now via RMAN, I delete the archivelog 15625.
Please note that the transaction is still active but now if GG is stopped and restarted, it will not have access to archive sequence 15625
as we have deleted it via rman using a simple command
backup
filesperset = 4
format 'al_%d%s%t_%U'
archivelog until time 'sysdate'
delete all input;
Now let us see if bounded recovery comes to our rescue.
I have on purpose set BR to 20 minutes and will patiently wait for the BR to happen.
Then I will note the log sequence as of the BR . Ideally after this point if GG process is stopped and restarted, it should
not need the original log sequence 15625 but should need only the log sequence during the BR flushout.
The BR flushout happens (as shown by GG log at 8.50pm)
2012-06-07 20:50:20 INFO OGG-01738 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p28822_extr: start=SeqNo: 15642, RBA: 14661648, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000, end=SeqNo: 15642, RBA: 14662656, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000.
Also see it here
jupiter<oracle>/oracle/software/goldengate/11.1.1.1.2/BR/XTATHDV> ls -lt
total 64
-rw-rw---- 1 oracle dba 65536 Jun 7 20:50 CP.XTATHDV.000000003
-rw-rw---- 1 oracle dba 65536 Jun 7 20:50 PO.XTATHDV.0000001.0000000
drwxrwxr-x 2 oracle dba 96 Jun 7 15:54 stale
SQL> select sequence# from v$archived_log
2 where to_date('07-jun-2012 20:50:00','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;
SEQUENCE#
----------
15641
SQL> select sequence# from v$archived_log
2 where to_date('07-jun-2012 20:50:59','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;
SEQUENCE#
----------
15642
So at this point if I stop GG and restart I should need only from sequence 15641 or 15642 . To be on safer side, make
sure you have archive logs from sequence 15641.
let us test this !!!!
GGSCI (jupiter) 39> stop ext xtathdv
Sending STOP request to EXTRACT XTATHDV ...
Request processed.
Restart the extract and carefully observe the ggserr.log file
2012-06-07 20:53:46 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT XTATHDV starting.
2012-06-07 20:53:46 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, xtathdv.prm: EXTRACT XTATHDV starting.
2012-06-07 20:53:47 INFO OGG-01639 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: ACTIVE: for object pool 1: p28822_extr.
2012-06-07 20:53:47 INFO OGG-01640 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start XID: 0.0.0.
2012-06-07 20:53:47 INFO OGG-01641 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start position: SeqNo: 15642, RBA: 14661648, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000.
2012-06-07 20:53:47 INFO OGG-01642 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery end position: SeqNo: 15642, RBA: 14662656, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000.
2012-06-07 20:53:47 INFO OGG-01579 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: VALID BCP: CP.XTATHDV.000000003.
2012-06-07 20:53:47 INFO OGG-01629 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: 1.
2012-06-07 20:53:50 INFO OGG-01513 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Positioning to Sequence 15642, RBA 14661648.
2012-06-07 20:53:51 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Positioned to Sequence 15642, RBA 14661648, Jun 7, 2012 8:50:19 PM.
2012-06-07 20:53:51 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, xtathdv.prm: EXTRACT XTATHDV started.
2012-06-07 20:53:51 INFO OGG-01055 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery initialization completed for target file /oracle/goldengatedata/dirdat/at000005, at RBA 2213190.
2012-06-07 20:53:51 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Output file /oracle/goldengatedata/dirdat/at is using format RELEASE 10.4/11.1.
2012-06-07 20:53:51 INFO OGG-01026 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Rolling over remote file /oracle/goldengatedata/dirdat/at000005.
2012-06-07 20:53:51 INFO OGG-01053 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery completed for target file /oracle/goldengatedata/dirdat/at000006, at RBA 1032.
2012-06-07 20:53:51 INFO OGG-01057 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery completed for all targets.
2012-06-07 20:53:51 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Position of first record processed Sequence 15642, RBA 14661648, SCN 1932.1131459784, Jun 7, 2012 8:50:19 PM.
2012-06-07 20:53:51 INFO OGG-01644 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: COMPLETE: for object pool 1: p28822_extr at SeqNo: 15642, RBA: 14662672, SCN: 1932.1131459785 (8299008275657).
2012-06-07 20:54:02 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, xtathdv.prm: No unique key is defined for table DV3_STATUS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
If you look above it starts looking at archive logs only from sequence 15642. This is indeed a big relief as some transactions
run longer than 4 hours and if i left it to default I might have to restore as much as 8 hours of archive logs.
Make sure below user has DBA privs so that he can query from v$log(to check the log sequence).
++++++Contents of test.sh +++++++++++++++++++++++++
#!/bin/ksh
sqlplus ops\$gchandra/test123 << EOF >test.log
select max(sequence#) from v\$log;
select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;
drop table testtab;
create table testtab (col1 number);
insert into testtab values(1);
execute dbms_lock.sleep(18000);
commit;
exit
EOF
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
I run the script in background with no interruption.
jupiter<oracle>/tmp> nohup ./test.sh &
[1] 28917
jupiter<oracle>/oracle/arch/athenadv/arch> cat /tmp/test.log
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 7 20:29:25 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
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
SQL> SQL>
MAX(SEQUENCE#)
--------------
15625
SQL>
TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
07-jun-2012 20:29:26
SQL>
Table dropped.
SQL>
Table created.
SQL>
1 row created.
The point to note is if the GG process is stopped or aborts at this point of time and restarted later, it needs
access to redo log from sequence 15625 . This is because uncomitted transactions are never written to the trail file
by golden gate. Only commited transactions are written.
There is something called bounded recovery which is basically a time period i.e 4 hours by default.
After every BR time interval , GG looks at the active transactions which qualify as old transactions ( i.e
they have been active for more than BR time period) and writes it to a temp location GG HOME/BR/<extract name> .
For example if the BR is set to 4 hours(default), and say a BR flush happens at 9am. At that time it scans all the active transactions
which started earlier than 5am (9am -4 hrs).
Say there were say 2 active transactions A and B .Transaction A started at 5.01am and transaction B started at
3.59am. Therefore transaction B is flushed to the BR location and transaction A is ignored as it has not been running for 4 hours.
Therefore if the golden gate crashed just a few seconds before the next flushout i.e.1pm , then transaction A which started
at 5.01am is lost.Therefore when you restart the GG it needs archive logs generated from 5.01am.
So in this worst case scenario , it needs access to log files(or archive files) from 2 times BR interval.
So prepare to retain archivelogs (or have capacity to restore archive logs) for 2 X (BR Interval period).
Now via RMAN, I delete the archivelog 15625.
Please note that the transaction is still active but now if GG is stopped and restarted, it will not have access to archive sequence 15625
as we have deleted it via rman using a simple command
backup
filesperset = 4
format 'al_%d%s%t_%U'
archivelog until time 'sysdate'
delete all input;
Now let us see if bounded recovery comes to our rescue.
I have on purpose set BR to 20 minutes and will patiently wait for the BR to happen.
Then I will note the log sequence as of the BR . Ideally after this point if GG process is stopped and restarted, it should
not need the original log sequence 15625 but should need only the log sequence during the BR flushout.
The BR flushout happens (as shown by GG log at 8.50pm)
2012-06-07 20:50:20 INFO OGG-01738 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: CHECKPOINT: for object pool 1: p28822_extr: start=SeqNo: 15642, RBA: 14661648, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000, end=SeqNo: 15642, RBA: 14662656, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000.
Also see it here
jupiter<oracle>/oracle/software/goldengate/11.1.1.1.2/BR/XTATHDV> ls -lt
total 64
-rw-rw---- 1 oracle dba 65536 Jun 7 20:50 CP.XTATHDV.000000003
-rw-rw---- 1 oracle dba 65536 Jun 7 20:50 PO.XTATHDV.0000001.0000000
drwxrwxr-x 2 oracle dba 96 Jun 7 15:54 stale
SQL> select sequence# from v$archived_log
2 where to_date('07-jun-2012 20:50:00','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;
SEQUENCE#
----------
15641
SQL> select sequence# from v$archived_log
2 where to_date('07-jun-2012 20:50:59','dd-mon-yyyy hh24:mi:ss') between first_time and next_time;
SEQUENCE#
----------
15642
So at this point if I stop GG and restart I should need only from sequence 15641 or 15642 . To be on safer side, make
sure you have archive logs from sequence 15641.
let us test this !!!!
GGSCI (jupiter) 39> stop ext xtathdv
Sending STOP request to EXTRACT XTATHDV ...
Request processed.
Restart the extract and carefully observe the ggserr.log file
2012-06-07 20:53:46 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT XTATHDV starting.
2012-06-07 20:53:46 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, xtathdv.prm: EXTRACT XTATHDV starting.
2012-06-07 20:53:47 INFO OGG-01639 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: ACTIVE: for object pool 1: p28822_extr.
2012-06-07 20:53:47 INFO OGG-01640 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start XID: 0.0.0.
2012-06-07 20:53:47 INFO OGG-01641 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery start position: SeqNo: 15642, RBA: 14661648, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000.
2012-06-07 20:53:47 INFO OGG-01642 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: recovery end position: SeqNo: 15642, RBA: 14662656, SCN: 1932.1131459784 (8299008275656), Timestamp: 2012-06-07 20:50:19.000000.
2012-06-07 20:53:47 INFO OGG-01579 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: VALID BCP: CP.XTATHDV.000000003.
2012-06-07 20:53:47 INFO OGG-01629 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: 1.
2012-06-07 20:53:50 INFO OGG-01513 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Positioning to Sequence 15642, RBA 14661648.
2012-06-07 20:53:51 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Positioned to Sequence 15642, RBA 14661648, Jun 7, 2012 8:50:19 PM.
2012-06-07 20:53:51 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, xtathdv.prm: EXTRACT XTATHDV started.
2012-06-07 20:53:51 INFO OGG-01055 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery initialization completed for target file /oracle/goldengatedata/dirdat/at000005, at RBA 2213190.
2012-06-07 20:53:51 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Output file /oracle/goldengatedata/dirdat/at is using format RELEASE 10.4/11.1.
2012-06-07 20:53:51 INFO OGG-01026 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Rolling over remote file /oracle/goldengatedata/dirdat/at000005.
2012-06-07 20:53:51 INFO OGG-01053 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery completed for target file /oracle/goldengatedata/dirdat/at000006, at RBA 1032.
2012-06-07 20:53:51 INFO OGG-01057 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Recovery completed for all targets.
2012-06-07 20:53:51 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, xtathdv.prm: Position of first record processed Sequence 15642, RBA 14661648, SCN 1932.1131459784, Jun 7, 2012 8:50:19 PM.
2012-06-07 20:53:51 INFO OGG-01644 Oracle GoldenGate Capture for Oracle, xtathdv.prm: BOUNDED RECOVERY: COMPLETE: for object pool 1: p28822_extr at SeqNo: 15642, RBA: 14662672, SCN: 1932.1131459785 (8299008275657).
2012-06-07 20:54:02 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, xtathdv.prm: No unique key is defined for table DV3_STATUS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
If you look above it starts looking at archive logs only from sequence 15642. This is indeed a big relief as some transactions
run longer than 4 hours and if i left it to default I might have to restore as much as 8 hours of archive logs.
Friday, June 1, 2012
Install XML DB on Oracle 11gR2
This note is copied from the Metalink Note and talks about removal and re-install of an XML DB on a 11gR2 Oracle Database.
This is more for my reference and sorry for duplicating ....
XDB Removal
The catnoqm.sql script drops XDB.
SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;
XDB Installation
The catqm.sql script requires the following parameters be passed to it when run:
A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior to running the script.)
C. XDB user temporary tablespace
D. SecureFiles = YES or NO
(If YES is specified, the XDB repository will use SecureFile storage.
If NO is specified, LOBS will be used.
To use SecureFiles, compatibility must be set to 11.2.
The tablespace specified for the XDB repository must be using
Automatic Segment Space Management (ASSM) for SecureFiles to be used.)
Therefore the syntax to run catqm.sql is the following:
SQL> catqm.sql A B C D
For Example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP YES
## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##
SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs> <XDB temporary tbs> <SecureFiles = YES/NO>
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
set echo on;
connect / as sysdba
set pagesize 1000
col comp_name format a36
col version format a12
col status format a8
col owner format a12
col object_name format a35
col name format a25
-- Check status of XDB
select comp_name, version, status
from dba_registry
where comp_id = 'XDB';
-- Check for invalid objects owned by XDB
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner = 'XDB';
spool off;
This is more for my reference and sorry for duplicating ....
XDB Removal
The catnoqm.sql script drops XDB.
SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;
XDB Installation
The catqm.sql script requires the following parameters be passed to it when run:
A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior to running the script.)
C. XDB user temporary tablespace
D. SecureFiles = YES or NO
(If YES is specified, the XDB repository will use SecureFile storage.
If NO is specified, LOBS will be used.
To use SecureFiles, compatibility must be set to 11.2.
The tablespace specified for the XDB repository must be using
Automatic Segment Space Management (ASSM) for SecureFiles to be used.)
Therefore the syntax to run catqm.sql is the following:
SQL> catqm.sql A B C D
For Example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP YES
## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##
SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs> <XDB temporary tbs> <SecureFiles = YES/NO>
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
Verify XDB Installation
spool xdb_status.txtset echo on;
connect / as sysdba
set pagesize 1000
col comp_name format a36
col version format a12
col status format a8
col owner format a12
col object_name format a35
col name format a25
-- Check status of XDB
select comp_name, version, status
from dba_registry
where comp_id = 'XDB';
-- Check for invalid objects owned by XDB
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner = 'XDB';
spool off;
Subscribe to:
Posts (Atom)