(1) LOCK ACCOUNT
$ vi lockAccount.sql
"lockAccount.sql" [Read only] 12 lines, 358 characters
set echo on
spool /u01/app/oracle/admin/scripts/lockAccount.log
BEGIN
FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
'SYS','SYSTEM') )
LOOP
dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
execute immediate 'alter user ' || item.USERNAME || ' password expire account lock' ;
END LOOP;
END;
/
spool off
(2) CloneRmanRestore
$ vi CloneRmanRestore.sql
"CloneRmanRestore.sql" [Read only] 5 lines, 248 characters
connect "SYS"/"password" as SYSDBA
set echo on
spool /u01/app/oracle/admin/PXXX/scripts/CloneRmanRestore.log
startup nomount pfile="/u01/app/oracle/admin/PXXX/scripts/init.ora";
@/u01/app/oracle/admin/PXXX/scripts/rmanRestoreDatafiles.sql;
~
(3) RMAN - RestoreDatafiles
$ vi rmanRestoreDatafiles.sql
"rmanRestoreDatafiles.sql" [Read only] 29 lines, 1308 characters
set echo off;
set serveroutput on;
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;
variable devicename varchar2(255);
declare
omfname varchar2(512) := NULL;
done boolean;
begin
dbms_output.put_line(' ');
dbms_output.put_line(' Allocating device.... ');
dbms_output.put_line(' Specifying datafiles... ');
:devicename := dbms_backup_restore.deviceAllocate;
dbms_output.put_line(' Specifing datafiles... ');
dbms_backup_restore.restoreSetDataFile;
dbms_backup_restore.restoreDataFileTo(1, '/u10/oradata/PXXX/system01.dbf', 0, 'SYSTEM');
dbms_backup_restore.restoreDataFileTo(2, '/u10/oradata/PXXX/sysaux01.dbf', 0, 'SYSAUX');
dbms_backup_restore.restoreDataFileTo(3, '/u13/oradata/PXXX/undotbs01.dbf', 0, 'UNDOTBS1');
dbms_backup_restore.restoreDataFileTo(4, '/u10/oradata/PXXX/users01.dbf', 0, 'USERS');
dbms_output.put_line(' Restoring ... ');
dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.1.0/db_1/assistants/dbca/templates/Seed_Database.dfb', done);
if done then
dbms_output.put_line(' Restore done.');
else
dbms_output.put_line(' ORA-XXXX: Restore failed ');
end if;
dbms_backup_restore.deviceDeallocate;
end;
/
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;
(4) postDBCreation
$ vi postDBCreation.sql
"postDBCreation.sql" [Read only] 24 lines, 1491 characters
connect "SYS"/"password" as SYSDBA
set echo on
spool /u01/app/oracle/admin/PXXX/scripts/postDBCreation.log
shutdown immediate;
connect "SYS"/"Password" as SYSDBA
startup mount pfile="/u01/app/oracle/admin/PXXX/scripts/init.ora";
alter database archivelog;
alter database open;
connect "SYS"/"Password" as SYSDBA
set echo on
create spfile='/u01/app/oracle/product/11.1.0/db_1/dbs/spfilePXXX.ora' FROM pfile='/u01/app/oracle/admin/PXXX/scripts/init.ora';
shutdown immediate;
connect "SYS"/"Password" as SYSDBA
startup ;
alter user SYSMAN identified by "Password" account unlock;
alter user DBSNMP identified by "Password" account unlock;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
commit;
host /u01/app/oracle/product/11.1.0/db_1/bin/emca -config dbcontrol db -silent -DB_UNIQUE_NAME PXXX -PORT 1521 -EM_HOME /u01/app/oracle/product/11.1.0/db_1 -
LISTENER LISTENER -SERVICE_NAME Pxxx -SYS_PWD password -SID PXXX -ORACLE_HOME /u01/app/oracle/product/11.1.0/db_1 -DBSNMP_PWD Password -HOST server_name -LISTENER_OH /u01/app/oracle/product/11.1.0/db_1 -LOG_FILE /u01/app/oracle/admin/PXXX/scripts/emConfig.log -SYSMAN_PWD Password;
connect "SYS"/"Password" as SYSDBA
spool /u01/app/oracle/admin/PXXX/scripts/postDBCreation.log
(5)
$ vi pxxx-xx5-PXXX-backup.rcv
"pxxx-xx5-PXXX-backup.rcv" 24 lines, 1000 characters
connect target rman_dba/password@PXXX
connect catalog rman_ctlg/password@PYYY1
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 35 DAYS;
configure device type sbt parallelism 1;
allocate channel for maintenance device type 'SBT_TAPE' send
#'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=pxx-xx5,NSR_GROUP=DB_Oracle2_VTL)';
'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=listenerVIP,NSR_GROUP=DB_Oracle2_VTL)';
crosscheck backup;
CROSSCHECK COPY;
delete NOPROMPT obsolete device type SBT_TAPE;
release channel;
run
{
allocate channel t1 type 'SBT_TAPE' send
#'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=pxxx-xx5,NSR_GROUP=DB_Oracle2_VTL)';
'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=listenerVIP,NSR_GROUP=DB_Oracle2_VTL)';
sql "alter system archive log current";
backup current controlfile format '/ctl_%d_%s_%U/';
backup full format '/db_%d_%s_%U/' (database);
backup archivelog all format '/arc_%d_%s_%U/' delete input;
release channel t1;
}
exit
(6)
$ vi postScripts.sql
"postScripts.sql" [Read only] 14 lines, 557 characters
connect "SYS"/"Password" as SYSDBA
set echo on
spool /u01/app/oracle/admin/PXXX/scripts/postScripts.log
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/dbmssml.sql;
execute dbms_datapump_utl.replace_default_dir;
commit;
connect "SYS"/"Password" as SYSDBA
alter session set current_schema=ORDSYS;
@/u01/app/oracle/product/11.1.0/db_1/ord/im/admin/ordlib.sql;
alter session set current_schema=SYS;
connect "SYS"/"Password" as SYSDBA
connect "SYS"/"Password" as SYSDBA
execute ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
spool off
(7)
$ vi pxxx-xx5-PXXX5-cumulative1.rcv
"pxxx-xx5-PXXX5-cumulative1.rcv" 12 lines, 676 characters
connect target rman_dba/password@pxxx5
connect catalog rman_ctlg/password@pyyy1
configure controlfile autobackup on;
#configure channel device type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=pxx-xx5,NSR_GROUP=DB_Oracle2_VTL)';
configure channel device type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=listenerSibVIP,NSR_GROUP=DB_Oracle2_VTL)';
configure device type sbt parallelism 1;
configure default device type to sbt;
#backup incremental level 1 database;
backup incremental level 1 format '/db_%d_%s_%U/' (database);
backup current controlfile format '/ctl_%d_%s_%U/';
backup archivelog all format '/arc_%d_%s_%U/' delete input;
exit;
(8)
$ vi lockAccount.sql
"lockAccount.sql" [Read only] 12 lines, 358 characters
set echo on
spool /u01/app/oracle/admin/scripts/lockAccount.log
BEGIN
FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
'SYS','SYSTEM') )
LOOP
dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
execute immediate 'alter user ' || item.USERNAME || ' password expire account lock' ;
END LOOP;
END;
/
spool off
(2) CloneRmanRestore
$ vi CloneRmanRestore.sql
"CloneRmanRestore.sql" [Read only] 5 lines, 248 characters
connect "SYS"/"password" as SYSDBA
set echo on
spool /u01/app/oracle/admin/PXXX/scripts/CloneRmanRestore.log
startup nomount pfile="/u01/app/oracle/admin/PXXX/scripts/init.ora";
@/u01/app/oracle/admin/PXXX/scripts/rmanRestoreDatafiles.sql;
~
(3) RMAN - RestoreDatafiles
$ vi rmanRestoreDatafiles.sql
"rmanRestoreDatafiles.sql" [Read only] 29 lines, 1308 characters
set echo off;
set serveroutput on;
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;
variable devicename varchar2(255);
declare
omfname varchar2(512) := NULL;
done boolean;
begin
dbms_output.put_line(' ');
dbms_output.put_line(' Allocating device.... ');
dbms_output.put_line(' Specifying datafiles... ');
:devicename := dbms_backup_restore.deviceAllocate;
dbms_output.put_line(' Specifing datafiles... ');
dbms_backup_restore.restoreSetDataFile;
dbms_backup_restore.restoreDataFileTo(1, '/u10/oradata/PXXX/system01.dbf', 0, 'SYSTEM');
dbms_backup_restore.restoreDataFileTo(2, '/u10/oradata/PXXX/sysaux01.dbf', 0, 'SYSAUX');
dbms_backup_restore.restoreDataFileTo(3, '/u13/oradata/PXXX/undotbs01.dbf', 0, 'UNDOTBS1');
dbms_backup_restore.restoreDataFileTo(4, '/u10/oradata/PXXX/users01.dbf', 0, 'USERS');
dbms_output.put_line(' Restoring ... ');
dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.1.0/db_1/assistants/dbca/templates/Seed_Database.dfb', done);
if done then
dbms_output.put_line(' Restore done.');
else
dbms_output.put_line(' ORA-XXXX: Restore failed ');
end if;
dbms_backup_restore.deviceDeallocate;
end;
/
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;
(4) postDBCreation
$ vi postDBCreation.sql
"postDBCreation.sql" [Read only] 24 lines, 1491 characters
connect "SYS"/"password" as SYSDBA
set echo on
spool /u01/app/oracle/admin/PXXX/scripts/postDBCreation.log
shutdown immediate;
connect "SYS"/"Password" as SYSDBA
startup mount pfile="/u01/app/oracle/admin/PXXX/scripts/init.ora";
alter database archivelog;
alter database open;
connect "SYS"/"Password" as SYSDBA
set echo on
create spfile='/u01/app/oracle/product/11.1.0/db_1/dbs/spfilePXXX.ora' FROM pfile='/u01/app/oracle/admin/PXXX/scripts/init.ora';
shutdown immediate;
connect "SYS"/"Password" as SYSDBA
startup ;
alter user SYSMAN identified by "Password" account unlock;
alter user DBSNMP identified by "Password" account unlock;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
commit;
host /u01/app/oracle/product/11.1.0/db_1/bin/emca -config dbcontrol db -silent -DB_UNIQUE_NAME PXXX -PORT 1521 -EM_HOME /u01/app/oracle/product/11.1.0/db_1 -
LISTENER LISTENER -SERVICE_NAME Pxxx -SYS_PWD password -SID PXXX -ORACLE_HOME /u01/app/oracle/product/11.1.0/db_1 -DBSNMP_PWD Password -HOST server_name -LISTENER_OH /u01/app/oracle/product/11.1.0/db_1 -LOG_FILE /u01/app/oracle/admin/PXXX/scripts/emConfig.log -SYSMAN_PWD Password;
connect "SYS"/"Password" as SYSDBA
spool /u01/app/oracle/admin/PXXX/scripts/postDBCreation.log
(5)
$ vi pxxx-xx5-PXXX-backup.rcv
"pxxx-xx5-PXXX-backup.rcv" 24 lines, 1000 characters
connect target rman_dba/password@PXXX
connect catalog rman_ctlg/password@PYYY1
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 35 DAYS;
configure device type sbt parallelism 1;
allocate channel for maintenance device type 'SBT_TAPE' send
#'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=pxx-xx5,NSR_GROUP=DB_Oracle2_VTL)';
'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=listenerVIP,NSR_GROUP=DB_Oracle2_VTL)';
crosscheck backup;
CROSSCHECK COPY;
delete NOPROMPT obsolete device type SBT_TAPE;
release channel;
run
{
allocate channel t1 type 'SBT_TAPE' send
#'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=pxxx-xx5,NSR_GROUP=DB_Oracle2_VTL)';
'NSR_ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=listenerVIP,NSR_GROUP=DB_Oracle2_VTL)';
sql "alter system archive log current";
backup current controlfile format '/ctl_%d_%s_%U/';
backup full format '/db_%d_%s_%U/' (database);
backup archivelog all format '/arc_%d_%s_%U/' delete input;
release channel t1;
}
exit
(6)
$ vi postScripts.sql
"postScripts.sql" [Read only] 14 lines, 557 characters
connect "SYS"/"Password" as SYSDBA
set echo on
spool /u01/app/oracle/admin/PXXX/scripts/postScripts.log
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/dbmssml.sql;
execute dbms_datapump_utl.replace_default_dir;
commit;
connect "SYS"/"Password" as SYSDBA
alter session set current_schema=ORDSYS;
@/u01/app/oracle/product/11.1.0/db_1/ord/im/admin/ordlib.sql;
alter session set current_schema=SYS;
connect "SYS"/"Password" as SYSDBA
connect "SYS"/"Password" as SYSDBA
execute ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
spool off
(7)
$ vi pxxx-xx5-PXXX5-cumulative1.rcv
"pxxx-xx5-PXXX5-cumulative1.rcv" 12 lines, 676 characters
connect target rman_dba/password@pxxx5
connect catalog rman_ctlg/password@pyyy1
configure controlfile autobackup on;
#configure channel device type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=pxx-xx5,NSR_GROUP=DB_Oracle2_VTL)';
configure channel device type 'SBT_TAPE' parms 'ENV=(NSR_SERVER=nyoshelgtdb01,NSR_CLIENT=listenerSibVIP,NSR_GROUP=DB_Oracle2_VTL)';
configure device type sbt parallelism 1;
configure default device type to sbt;
#backup incremental level 1 database;
backup incremental level 1 format '/db_%d_%s_%U/' (database);
backup current controlfile format '/ctl_%d_%s_%U/';
backup archivelog all format '/arc_%d_%s_%U/' delete input;
exit;
(8)
