Overlap of group of scripts

You can do most of this by using a login.sql. login.sql is executed during - surprising - login and is loaded from your SQLPATH or current directory. For the examples you gave, your really chose the worst case.

Problem is the sqlterminator. Whatever you put in there, the forward slash is maintained as a free sqlterminator. Next to that, sqlplus first scans for the sqlterminator and does this before scanning to the string terminator. A bug if you ask me. The forward slash can be used in a string as long as it is not alone on a seperate line. As soon as sqlplus finds the character specified as sqlterminator, it ignores everything else and stops reading.

The forward slash can be handled, as long as it is not alone on a line.

login.sql contains:

prompt run login.sql
show sqlterminator
show sqlblanklines
set sqlblanklines on
set sqlterminator ';'
show sqlterminator
show sqlblanklines
prompt ready login.sql
set echo on

leigh.sql contains:

INSERT INTO t1 VALUES ('fail bc semicolon
a;a
/
'); 

INSERT INTO t1 VALUES ('fail bc solo /


aa
/
');

INSERT INTO t1 VALUES ('ok / not solo


aa
/a
');

DELETE FROM t1 WHERE a = 'c


a/
';

run the script:

sqlplus leigh/leigh@orcl @leigh
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 9 22:36:20 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

run login.sql
sqlterminator ";" (hex 3b)
sqlblanklines OFF
sqlterminator ";" (hex 3b)
sqlblanklines ON
ready login.sql
SQL> INSERT INTO t1 VALUES ('fail bc semicolon
  2  a;a
  3  /
ERROR:
ORA-01756: quoted string not properly terminated


SQL> ');
SP2-0042: unknown command "')" - rest of line ignored.
SQL> 
SQL> INSERT INTO t1 VALUES ('fail bc solo /
  2  
  3  
  4  aa
  5  /
ERROR:
ORA-01756: quoted string not properly terminated


SQL> ');
SP2-0042: unknown command "')" - rest of line ignored.
SQL> 
SQL> INSERT INTO t1 VALUES ('ok / not solo
  2  
  3  
  4  aa
  5  /a
  6  ');

1 row created.

SQL> 
SQL> DELETE FROM t1 WHERE a = 'c
  2  
  3  
  4  a/
  5  ';

0 rows deleted.

No need to fiddle with begin/end blocks. Can't handle sqlterminator inside command, no matter where it is, in a string or not, can't handle lines with forward slash alone on a line in a string.

The reason for the databa block corruption is that ther was data inserted in the table with nologging in the timespan between the begin of the backup and '01-JUN-2015 02:00:00'. So the information needed to fully restore the datafiles (by applying the archive log) is not contained in the archives log. Mainly there are two possibilities for you:

1) you try to avoid corrupt blocks
2) you repair the tables

1) To avoid corrupt blocks you can
1.1) schedule you duplicate so, that there is no nologging operation in the critical time interval
1.2) you can change the nologging operation to an operation with loggin
1.3) reduce the size of the critical time interval by doing an incremental backup before the until-point-of-time, e.g. '01-JUN-2015 02:00:00'. (but this has to be done on the production database)

2) you can truncate the affected tablels after the duplicate. If you don't need this tables on the your duplicate then you are done. Otherwise you have to fill them with the correct data , e.g. by loading the table with the same data as the production table. Of course this kind of repairing a table depends of the special situation.