1. cd /data/vendor/oracle/refresh_prod_db/refresh_db/BPIIP 2. mkdir ddmmyyyy 3 cd ddmmyyyy 4. copy the production version of control file to this directory cp ../crt_file_BPIIP . cp ../crt_file_BPIIP_from_production.bk .
5. cd ../ 6. cp check_file_readiness.sh 11Sep2008/. 7. ensure the control file bpiip_ctrl.sql 8. check_file_readiness_in_staging.sh BPII 9 edit file_list_BPIIT for crt file purpse 10. modify create_cntl_bpiit_29aug2008_instaging.sql with the correct directory on initiBPIIT.ora 11. sqlplus '/ as sysdba' @create_cntl_bpiit_29aug2008_instaging.sql ALTER DATABASE OPEN resetlogs;
recover database using backup controlfile until cancel
/dbrefresh/data1/BPIIP/BPIIP_redo_005b.rdo
use rdo log if required to be recoveried
12
ALTER SYSTEM switch logfile;
13. ALTER TABLESPACE TEMP_INV ADD TEMPFILE '/dbrefresh/data1/BPIIP/BPIIP_TEMP_INV_001.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 8192M;
14. select * from dba_temp_files;
======================================== get the new name:
awk -F/ '{ print $1 "/dbrefresh/data1/" $5 }' list_file_name
VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 8'); # Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate. RECOVER DATABASE # All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL; # Database can now be opened normally. ALTER DATABASE OPEN resetlogs; • select * from v$datafile where name like '%MISS%'=============================================================
shrinkinig BPI ========================================================== sqlplus '/ as sysdba' 1. @cloning-create-BPI-tablespaces_11Sep2008.sql 2 @cloning-generate-BPI-scripts.sql 3. drop_table.sql 4. create bring ts read write script.sql . @bring_ts_online.sql 5. move_tables.sql move all the previous tablespace to move_event_data 6. move_lob.sql 7 move_index.sql 8.@../fix_EVENT_ACT_BPI_CATCHALL_SET_T.sql 9.
==================================
restore BPAOT ==================== VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 8'); -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/bpaop-arch-04/archivelog/BPAOP/arch_BPAOP_1_542476800_1.arc'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL; -- Database can now be opened normally. ALTER DATABASE OPEN ; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE AO_TEMP ADD TEMPFILE '/dbrefresh/data1/BPAOP/BPAOP_AO_TEMP_001.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 16384M; -- End of tempfile additions.awk -F/ '{ print $1 "/dbrefresh/data1/BPAOP/" $5 }' new_file_list.check
==================================
shrinking AO ============================== TABLESPACE_NAME ------------------------------ AO_TEMP CRM_AUDIT_DATA_TEMP CRM_AUDIT_INX_TEMP ORDERS_AUDIT_DATA_TEMP ORDERS_AUDIT_INX_TEMP ORDERS_AUDIT_DATA1_TEMP ORDERS_AUDIT_INX1_TEMP ORDERS_AUDIT_INX2_TEMP ORDERS_AUDIT_DATA2_TEMP