how to execute same SQL script in all Oracle databases in Linux server [single shot]

Linux shell script: sample

[orcl@qact /home/oracle/adt]$ more all_db.sh 

#!/bin/bash

for I in GTD1 PTD1 FTD1 CTD1 MTD1 MSD1 # List of DB names
do

  echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"

  echo "executing scripts...."

  export ORACLE_SID=$I

  echo "DB Name: "$ORACLE_SID

  sqlplus -S -L  / as sysdba < /home/oracle/adt/adt.sql

  echo "Done."

done

[orcl@qact /home/oracle/adt]$ more adt.sql 

spool /home/oracle/adt/DB_result_$ORACLE_SID.log
set line 280
select name, open_mode, log_mode from v$database;
.
.
.
spool off;
exit;

[orcl@qact /home/oracle/adt]$ ./all_db.sh

[orcl@qact /home/oracle/adt]$ ll

total 8308

-rwxr--r-- 1 oracle dba    2567 Nov 15 12:38 adt.sql

-rwxr--r-- 1 oracle dba     314 Nov 15 12:02 all_db.sh

-rw-r--r-- 1 oracle dba   96709 Nov 15 12:39 DB_result_GTD1.log

-rw-r--r-- 1 oracle dba   90588 Nov 15 12:39 DB_result_PTD1.log

-rw-r--r-- 1 oracle dba   64756 Nov 15 12:39 DB_result_FTD1.log

-rw-r--r-- 1 oracle dba   92605 Nov 15 12:39 DB_result_CTD1.log

-rw-r--r-- 1 oracle dba   92408 Nov 15 12:39 DB_result_MTD1.log

-rw-r--r-- 1 oracle dba   92408 Nov 15 12:39 DB_result_MSD1.log

Note: " sqlplus -S -L " 

-S silent.
-L try login only once. (else 3 times)

ORA-00119 ORA-00132

during startup, faced below errors..?

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'LISTENER_AZE'

Cause: non-default listener port number.

Solution: add an entry in tnsnames.ora mentioning non-default port. (blue text below)

 Note: Please avoid using port 0-1023 [ IANA range]

FLASHBACK ON / OFF [11g]

########## Flashback enable: [11g] ##########

[ reboot of database Not required for 11g]

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   NO

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=3G;
SQL> alter system set DB_RECOVERY_FILE_DEST='D:\recovery_area';
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=120;
SQL> alter database flashback on;
SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES





########## Flashback disable: [11g] ##########

SQL> alter database flashback off;

SQL> select log_mode,flashback_on from v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   NO

FLASHBACK ON / OFF [10g]

############## Flashback enable: [10g] ########################


SQL> select open_mode, log_mode, flashback_on from v$database;

OPEN_MODE            LOG_MODE     FLASHBACK_ON
-------------------- ------------ -------------
READ WRITE           ARCHIVELOG   NO

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=3G;
SQL> alter system set DB_RECOVERY_FILE_DEST='D:\app\Administrator\recovery_area';
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=120;
SQL> show parameter DB_RECOVERY_FILE_DEST
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
SQL> select * from v$bgprocess where name='RVWR';
SQL> alter database open;



############## Flashback disable: [10g]  ######################


SQL> select open_mode, log_mode, flashback_on from v$database;

OPEN_MODE            LOG_MODE     FLASHBACK_ON
-------------------- ------------ -------------
READ WRITE           ARCHIVELOG   YES

SQL> alter database flashback off;

RMAN backup script [tested] Windows, Linux

*Works on Oracle 10g, 11g

###############################################################

## For Windows with separate log for every day of the week.

###############################################################

-----------------------rman configuration-----------------------

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default PARALLELISM CAN BE > 1 ONLY FOR ENTERPRISE EDITION
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\SNCFBSCGH.ORA'; # default

-----------------------rman script------------------------------

file name: rman_backup.sql

connect target 'system/password'
run
{
allocate channel d1 type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
backup AS COMPRESSED BACKUPSET tag Full_database_open format 'E:\dbbackup_rman\db_%t_%s_p%p.dbf' database;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup AS COMPRESSED BACKUPSET archivelog all format 'E:\dbbackup_rman\al_%t_%s_p%p.dbf' delete all input;
delete noprompt obsolete redundancy = 1;
backup AS COMPRESSED BACKUPSET current controlfile tag =cf1 format 'E:\dbbackup_rman\cf_%t_%s_p%p.dbf';
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
}

-----------------------rman.bat---------------------------------
e:
cd E:\ora10g\BIN
rman @E:\dbbackup_rman\rman_backup.sql > E:\dbbackup_rman\log\rman_backup_%date:~0,3%.LOG

----------------------------------------------------------------

How to create ".bat" file?

----------------------------------------------------------------




###############################################################

## For Linux with separate log for every day of the week.

###############################################################


#!/bin/bash
export ORACLE_SID=msats
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
cd /u01/app/oracle/product/10.2.0/db_1/bin
./rman  target 'system/password' log=/u03/rmanbackup/rmanlog.`date '+%a'` << EOF
run
{
allocate channel d1 type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
backup AS COMPRESSED BACKUPSET tag whole_database_open format '/u03/rmanbackup/db_%t_%s_p%p' database;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/u03/rmanbackup/al_%t_%s_p%p' delete all input;
delete noprompt obsolete redundancy=1;
backup current controlfile tag =cf1 format '/u03/rmanbackup/cf_%t_%s_p%p';
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;

}
#exit;
EOF


----------------------- END -----------------------------------

Oracle patch numbers

Quick Reference to Patchset Patch Numbers [ID 753736.1]