Hi,Chris
When i solve Oracle DataGuard physical standby database about GAP's problem,due to most of archived logs have not existed in dynamic performance view 'v$archived_log' and also existed in OS,therefore i need to register these archived logs manually,such as 'alter database register logfile '/u01/oradata/archivelog/1_90384_850739945.dbf';' and registering archived logs one by one is so difficult that i decide to use an PL/SQL code to finish.
Now,my query steps as follows,
SYS@standby> set linesize 300
SYS@standby> set pagesize 300
SYS@standby> col name for a60
SYS@standby> select name,sequence#,applied from v$archived_log order by 2;
NAME SEQUENCE# APPLIED
------------------------------------------------------------ ---------- ------------------
<b>/u01/oradata/archivelog/1_90384_850739945.dbf 90384 NO
/u01/oradata/archivelog/1_90436_850739945.dbf 90436 NO</b>
/u01/oradata/archivelog/1_90437_850739945.dbf 90437 NO
/u01/oradata/archivelog/1_90438_850739945.dbf 90438 NO
/u01/oradata/archivelog/1_90439_850739945.dbf 90439 NO
/u01/oradata/archivelog/1_90440_850739945.dbf 90440 NO
/u01/oradata/archivelog/1_90441_850739945.dbf 90441 NO
/u01/oradata/archivelog/1_90442_850739945.dbf 90442 NO
<b>......</b>
At the same time,i also found archived logs on OS(CentOS 6.6 x86_64),
[oracle@oracledg2 archivelog]$ ls -lrth |grep 90384
-rw-r----- 1 oracle oinstall 36M Aug 25 14:02 1_90384_850739945.dbf
[oracle@oracledg2 archivelog]$ ls -lrth |grep 90435
-rw-r----- 1 oracle oinstall 27M Aug 25 14:02 1_90435_850739945.dbf
My PL/SQL code as follows,execute it on SQL*Plus and has some error,
SYS@standby> begin
2 for sequence in 90385 .. 90435
3 loop
4 execute immediate 'alter database register logfile '/u01/oradata/archivelog/1_' || sequence || '_850739945.dbf'';
5 end loop;
6 end;
7 /
execute immediate 'alter database register logfile '/u01/oradata/archivelog/1_' || sequence || '_850739945.dbf'';
*
ERROR at line 4:
ORA-06550: line 4, column 80:
PLS-00103: Encountered the symbol "_" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || bulk member
submultiset
Please give me some advices,thanks!
Best Regards
Quanwen Zhao
You need to escape the quotes, eg
execute immediate 'alter database register logfile ''/u01/oradata/archivelog/1_' || sequence || '_850739945.dbf''';
^ ^ ^
normal escaped escaped followed by normal