i changed the set until time (to the time when arch backup was completed excluding synch time) and recovered the database. I had all the backup pieces for that days RMAN backup. SQL "ALTER DATABASE RENAME FILE ''E:\WIND9\ORADATA\WIND9\REDO01.LOG'' TO ''E:\ptc\Windchill_9.0\ocu\ORADATA\WIND9\REDO01.LOG'' "; Change the database name using nid We will now shutdown the database and then mount it. I n this post I will explain how to refresh test/dev oracle database from production backup. SET NEWNAME FOR DATAFILE 5 TO 'E:\ptc\Windchill_9.0\ocu\ORADATA\WIND9\TOOLS01.DBF'; This fixed the issue. 84:59 ora_pmon_DEVE72 [oracle@DEVE72 DEVE72]$ echo $ORACLE_SID DEVE72 [oracle@DEVE72 DEVE72]$ sqlplus sys as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 17 15:36:22 2010 Copyright (c) 1982, 2007, Oracle. That will contain all the Backuppiece names for that backup. Pls follow the below mentioned steps to do so. 229 B F A DISK 24-FEB-09 1 1 NO TAG20090224T182832 The client takes a level 0 (full) backup of DB every day. _pe.subscribe(); Fill in your details below or click an icon to log in: You are commenting using your WordPress.com account. In my case, I hadnt noticed that the 10g ORACLE_HOME versions of Production and Test were different. The backuppieces with Archives are not listed here and I am not sure (yet) how to do it. The redo log files have to be renamed in the controlfile. Shutdown oracle on test server.2. (/backup01. '/uatdb/app/oradata/log1c.dbf'; alter database rename file

Do let me know your experiences with RMAN too. 2. connected to target database (not started) RMAN> @rman_recovery.rcv Note: At this stage, we can continue to recover the database and keep it in sync with the source production database by manually applying the archive log files which are copied from the production server to the log archive destination of the test database on the target server. '/oradb_2/oracle/product/TJTG/db/apps_st/data/log1c.dbf' to SQL> startup mount ORACLE instance started. But this datafile is already been restored last time. oracle database from production backup . Important RMAN commands for Oracle database. SQL> alter database rename file /data03/TEST/logfiles/redo01PROD.log to /data01/TEST/logfiles/redo01PROD.log; SQL> alter database rename file /data03/TEST/logfiles/redo02PROD.log to /data01/TEST/logfiles/redo01PROD.log; SQL> alter database rename file /data03/TEST/logfiles/redo03PROD.log to /data01/TEST/logfiles/redo01PROD.log; SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/data01/TEST/temp00.dbf' size 500m; SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/data01/TEST/temp01.dbf' size 500m; SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/data01/TEST/temp02.dbf' size 500m; SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/data01/TEST/temp03.dbf' size 500m; SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/data01/TEST/temp04.dbf' size 500m; SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/data01/TEST/temp01.dbf' size 500m reuse; https://sajidoracledba.blogspot.com/2019/09/12c-multitenant-container-and-pluggable-RMAN-Backup.html. SET NEWNAME FOR DATAFILE 2 TO /backup /TEST/ undo_APPS_UNDOTS5.dbf; SET NEWNAME FOR DATAFILE 3 TO /backup /TEST/ prod_data01PROD.dbf; SET NEWNAME FOR DATAFILE 4 TO /backup /TEST/ prod_data02PROD.dbf; SET NEWNAME FOR DATAFILE 5 TO /backup /TEST/ prod_data03PROD.dbf; SET NEWNAME FOR DATAFILE 6 TO /backup /TEST/ prod_data04PROD.dbf; SET NEWNAME FOR DATAFILE 7 TO /backup /TEST/ prod_data05PROD.dbf; SET NEWNAME FOR DATAFILE 8 TO /backup /TEST/ prod_data06PROD.dbf; SET NEWNAME FOR DATAFILE 9 TO /backup /TEST/ prod_data07PROD.dbf; SET NEWNAME FOR DATAFILE 10 TO /backup /TEST/ prod_data08PROD.dbf; SET NEWNAME FOR DATAFILE 11 TO /backup /TEST/ prod_data09PROD.dbf; SET NEWNAME FOR DATAFILE 12 TO /backup /TEST/ prod_data10PROD.dbf; SET NEWNAME FOR DATAFILE 13 TO /backup /TEST/ prod_data11PROD.dbf; SET NEWNAME FOR DATAFILE 14 TO /backup /TEST/ prod_data12PROD.dbf; SET NEWNAME FOR DATAFILE 15 TO /backup /TEST/ prod_data13PROD.dbf; SET NEWNAME FOR DATAFILE 16 TO /backup /TEST/ prod_data14PROD.dbf; SET NEWNAME FOR DATAFILE 17 TO /backup /TEST/ prod_data15PROD.dbf; SET NEWNAME FOR DATAFILE 18 TO /backup /TEST/ prod_data16PROD.dbf; SET NEWNAME FOR DATAFILE 19 TO /backup /TEST/ prod_ndx01PROD.dbf. Database ID for database ASR changed to run { Look for the string "List of archived logs" RMAN > list backup of archivelog all List of Archived Logs in backup set 69 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 79 7970987 16-JUN-10 7973402 16-JUN-10 In this case, the last archivelog backed up belongs to sequence number 79. TechnologyAdvice does not include all companies or all types of products available in the marketplace. ORA-48108: invalid value given for the But since the requirement is to restore the datafiles to a different location than what is in the Production, I use SET NEWNAME FOR DATAFILE command. RMAN>sql alter database open resetlogs; Now we need to check the redo log file location if it dont match then we will rename it as below. '/uatdb/app/oradata/log1b.dbf'; alter database rename file SET NEWNAME FOR DATAFILE 14 TO 'E:\ptc\Windchill_9.0\ocu\ORADATA\WIND9\BLOBS03.DBF'; Oracle database monitoring scripts for daily,weekly,monthly and quarterly DBA checklist activity, Importance of executing root.sh and orainstroot.sh in oracle, Recovery of Container Database (CDB) and Pluggable database (PDB), How To restore RMAN backup from PROD to TEST database. [ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files. List of Backups set until time to_date(08-nov-10 16:26:43,dd-mon-yy hh24:mi:ss); meta name=viewport content=width=device-width, initial-scale=1 Temporary Tablespace Reconfiguration After the restore, we will note that the temporary tablespace files are still pointing to the source production database as these tempfiles have not been renamed when we renamed all the database data files in an earlier step. Change), You are commenting using your Facebook account. alter database rename file (LogOut/ Prepare the RMAN restore and recover database script [oracle@DEVE72 DEVE72]$ cat rman_head rename_datafiles.lst rename_logfiles.lst rman_tail > rman_recovery.rcv Connect to the target database via RMAN and execute the rman_recovery.rcv script [oracle@DEVE72 dbs]$ rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jun 21 13:04:04 2010 Copyright (c) 1982, 2007, Oracle. SQL> conn /as sysdba The situation here is we have taken an RMAN backup on PROD (Source) and we need to restore those backup pieces on different server as TEST (target) database. Target Sytem Hostname (virtual or normal) [tjpsb008] : Target System Database Server Node [tjpsb008] : recover database; The restore in itself is simple. TJPSB007. RMAN> list backuppiece tag='TAG20090224T180002'; List of Backup Pieces check out the. In order to let RMAN know about the backup in'/backup01/rman_backup' backupwe use theCATALOG command. RMAN it is TJTG). TJTG (not mounted). diagnostic_dest init.ora parameter, ORA-48140: the specified ADR Base '/uatdb/app/oradata/log2b.dbf'; alter database rename file ## SWITCH DATAFILE ALL; Target System Database Domain Name [ebs.in] : Target System Base Directory : /uatapp/app/applascp, Target System Tools ORACLE_HOME Directory Steps to configure Manual Data Guard Oracle 11g Steps to configure Oracle 11g Data Guard Physical Getting started with RMAN Overview of RMAN, Star Online Move Datafiles in Oracle 12c R1 without get How to relocate/move oracle datafiles in Oracle 11g. Source bash profile with Oracle_Sid as I shall remember your advice on keeping my posts short (and sweet!). Running autoconfig on application Tier: [ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files. Add a new tempfile for the refreshed database in the appropriate location- SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u03/oradata/DEVE72/temp01.dbf' size 2G; Tablespace altered. '/oradb/oracle/product/TJTG/db/apps_st/data/log1a.dbf' to '/uatdb/app/oradata/log1a.dbf'; alter database rename file Provide the values required for creation of the new after restore switch datafile all has also been executed successfully. (adsbygoogle = window.adsbygoogle || []).push({}); Thanks for reading till end. Apologies for the late response. I was looking at the 24-FEB-09 backup to be restored from and the output below is trimmed to show only that days backup. &. SET NEWNAME FOR DATAFILE 7 TO 'E:\ptc\Windchill_9.0\ocu\ORADATA\WIND9\INDX02.DBF'; database with RESETLOGS option. SQL> @generate_logfiles.sql It will create a file called rename_logfiles.lst Edit the rename_logfiles.lst file and change values of prd22 to DEVE72 SQL "ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo01a.log'' to ''/u04/oradata/DEVE72/redo01a.log''" ; SQL "ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo01b.log'' to ''/u05/oradata/DEVE72/redo01b.log''" ; SQL "ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo02a.log'' to ''/u04/oradata/DEVE72/redo02a.log''" ; SQL "ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo02b.log'' to ''/u05/oradata/DEVE72/redo02b.log''" ; SQL "ALTER DATABASE RENAME FILE ''/u04/oradata/prd22/redo03a.log'' to ''/u04/oradata/DEVE72/redo03a.log''" ; SQL "ALTER DATABASE RENAME FILE ''/u05/oradata/prd22/redo03b.log'' to ''/u05/oradata/DEVE72/redo03b.log''" ; Specify the archive log sequence until which recovery will be performed Edit the rman_tail file and change the line with the words >> SET UNTIL SEQUENCE 80 to include the appropriate archive log sequence which was noted in an earlier step. Succesfully changed database name and [/uatapp/app/applascp/apps/tech_st/10.1.2, Target System Web ORACLE_HOME Directory How to automate/schedule shell script for rman backup in oracle database through crontab. RMAN>startup nomount pfile='/uatdb/app/oraascp/db/11.2.0/dbs/initTJTG.ora'; connected to target database: Copy all the backup piecies (datafile,controlfile and spfile) of source(PROD) database to target(TEST) server using scp. All rights reserved. How to run Autoconfig On DB Tier and Apps Tier on R12. bjbjmm . f7 d h h h h | i Z 2 i i i i i j j j $ h j j j j j i i =~ =~ =~ j J i i =~ j =~ =~ : , i i n h x %