Nous sommes des experts des infrastructures et plateformes de donnes innovantes et efficientes. And also see (from the ORACLE-BASE Blog): Duplicate a Database Using RMAN in Oracle Database 11g Release 2. Some blogs mention to backup the controlfile and/or switch the logfile, but this is not required. John, In both cases, you would use "USING BACKUP CONTROLFILE" if you have also lost your current controlfile with the database.Also note that when you use RMAN for your BACKUP, RESTORE and RECOVER, the "USING BACKUP CONTROFILE" is hidden -- RMAN automatically recognises which controlfile it is using when doing a RECOVER.Hemant. Reset to original value by RMAN scope=spfile; sql clone alter system reset db_unique_name scope=spfile; connected to auxiliary database (not started), Total System Global Area 1152450560 bytes, Redo Buffers 9383936 bytes, sql statement: alter system set db_name = PROD comment= Reset to original value by RMAN scope=spfile, sql statement: alter system reset db_unique_name scope=spfile, sql statement: CREATE CONTROLFILE REUSE SET DATABASE PROD RESETLOGS ARCHIVELOG. A Backup controlfile can be either ofa) a controlfile that was created -- as above, so not having information in v$archived_logorb) a binary backup -- which has information only as of the time the backup was created but no information on archivelogs generated since then.Hemant, I know that scn increments every commit or rollback.I am just confused with block scn.When a commit is issued, does scn of all blocks incremenets?or only the scn of modified block increments, Anonymous.When a transaction updates a block, the block SCN is also updated -- thus different blocks in a datafile may have different block SCNs, depending on when they were updated.Hemant. Just to remember the commands we did a snapshot and copied the volumes to the target server: Then we mounted the volumes and were already able to and start the DB done The database name is also stored in the datafile headers. Incorrectly using AUTOTRACE and EXPLAIN PLAN. Asking for help, clarification, or responding to other answers. To subscribe to this RSS feed, copy and paste this URL into your RSS reader. catalog clone datafilecopy /data2/prod/sysaux01.dbf, renamed tempfile 1 to /data2/prod/temp01.dbf in control file, datafile copy file name=/data2/prod/sysaux01.dbf RECID=1 STAMP=875883671, datafile copy file name=/data2/prod/undotbs01.dbf RECID=2 STAMP=875883671, datafile copy file name=/data2/prod/users01.dbf RECID=3 STAMP=875883671, datafile copy file name=/data2/prod/example01.dbf RECID=4 STAMP=875883672, input datafile copy RECID=1 STAMP=875883671 file name=/data2/prod/sysaux01.dbf, input datafile copy RECID=2 STAMP=875883671 file name=/data2/prod/undotbs01.dbf, input datafile copy RECID=3 STAMP=875883671 file name=/data2/prod/users01.dbf, input datafile copy RECID=4 STAMP=875883672 file name=/data2/prod/example01.dbf, Everything has went well listener services were also worked fine, RMAN connectivity from auxiliary server also fine using net service name but. which are handled in my script. backup as copy current controlfile auxiliary format /data2/prod/control01.ctl; restore clone controlfile to /data2/prod/control02.ctl from, sql statement: alter system set db_name = PROD comment= Modified by RMAN duplicate scope=spfile, sql statement: alter system set db_unique_name = PROD comment= Modified by RMAN duplicate scope=spfile, Total System Global Area 1152450560 bytes, Fixed Size 2212696 bytes, Variable Size 335547560 bytes, Database Buffers 805306368 bytes, Redo Buffers 9383936 bytes, channel ORA_DISK_1: SID=76 device type=DISK, channel ORA_DISK_1: starting datafile copy, output file name=/data2/app/oracle/product/11.2.0/dbs/snapcf_prod.f tag=TAG20150401T124940 RECID=4 STAMP=875882981, channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03, channel ORA_AUX_DISK_1: copied control file copy, input datafile file number=00001 name=/data2/prod/system01.dbf, output file name=/data2/prod/system01.dbf tag=TAG20150401T124955, channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15, input datafile file number=00002 name=/data2/prod/sysaux01.dbf, output file name=/data2/prod/sysaux01.dbf tag=TAG20150401T124955, channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05, input datafile file number=00005 name=/data2/prod/example01.dbf, output file name=/data2/prod/example01.dbf tag=TAG20150401T124955, channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15, input datafile file number=00003 name=/data2/prod/undotbs01.dbf, output file name=/data2/prod/undotbs01.dbf tag=TAG20150401T124955, channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07, input datafile file number=00004 name=/data2/prod/users01.dbf, output file name=/data2/prod/users01.dbf tag=TAG20150401T124955, channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01, sql statement: alter system archive log current, archivelog like /backup/archive/1_116_872701561.dbf auxiliary format. /backup/archive/1_116_872701561.dbf ; catalog clone archivelog /backup/archive/1_116_872701561.dbf; channel ORA_DISK_1: starting archived log copy, input archived log thread=1 sequence=116 RECID=112 STAMP=875883160, output file name=/backup/archive/1_116_872701561.dbf RECID=0 STAMP=0, channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03, archived log file name=/backup/archive/1_116_872701561.dbf RECID=112 STAMP=875883644, input datafile copy RECID=4 STAMP=875883644 file name=/data2/prod/system01.dbf, input datafile copy RECID=5 STAMP=875883644 file name=/data2/prod/sysaux01.dbf, input datafile copy RECID=6 STAMP=875883645 file name=/data2/prod/undotbs01.dbf, input datafile copy RECID=7 STAMP=875883645 file name=/data2/prod/users01.dbf, input datafile copy RECID=8 STAMP=875883645 file name=/data2/prod/example01.dbf, archived log for thread 1 with sequence 116 is already on disk as file /backup/archive/1_116_872701561.dbf, archived log file name=/backup/archive/1_116_872701561.dbf thread=1 sequence=116, media recovery complete, elapsed time: 00:00:02. In your environment you may have more volumes. 5 Reasons Why you Should Run your Oracle Database on Oracle Exadata, COVID-19: A New Threat to Enterprise Cyber Security, All You Need To Know About Oracle Autonomous Health Framework Execution, 10 Easy Steps To Patch Oracle Exadata X8M RoCE Switch, 5 Reasons Why Exadata is Important for Your Business Continuity, 7 Easy Steps to Verify RoCE Cabling on Oracle Exadata X8M, How to Restore Compute node from Snapshot Backup for Oracle Exadata, Step-by-step Guide of Exadata Snapshot Based Backup of Compute Node to NFS Share, Comparing Oracle Database Appliance X8-2 Model Family. US to Canada by car with an enhanced driver's license, no passport? [oracle@Nsm-linux01 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin, (ORACLE_HOME = /data2/app/oracle/product/11.2.0), (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)), Here global_name is nothing but service name and sid_name is instance name, Now reload the listener and check the services, LSNRCTL for Linux: Version 11.2.0.1.0 Production on 01-APR-2015 12:46:41. Use the ALTER DATABASE RENAME FILE 'oldlocation' TO 'newlocation' command for each datafile being so renamed/relocated , with the database in MOUNT mode, not OPEN (because you will be renaming SYSTEM as well ! I have Oracle database and I know SYS password and service name. We did a snapshot clone with our fancy new PureStorage. or controlfile is older than redo, etc..so that I dont need open resetlogs.I can compare controlfile and datafile with v$datafile and v$datafile_header.How about redo? /u01/app/oracle/admin/$DB_UNIQUE_NAME/xdb_wallet or I would set it to at least 10.1 or 10.2 (e.g. By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. db-crawler, a database search utility for Documentum, Power BI Active Directory data integration. Was there a Russian safe haven city for politicians and scientists? 10.2.0.1 or 10.2.0.4)Changing the init.ora file doesn't change the database name. Most blogs tell you ALTER SYSTEM SET DB_NAME=T01B; and start your DB . So why should we need to rename a DB? It is not "need current controlfile" it is "use current controlfile". Cannot Get Optimal Solution with 16 nodes of VRP with Time Windows. After a snapshot clone, (see my recent blog) you want to run it with new name. Netsoftmate is an Oracle Gold Partner and an expert service provider for Oracle Engineered Systems, Databases and Cyber Security. Nous proposons nos clients des solutions adaptes et sur mesure grce nos consultant.e.s dont les comptences et connaissances voluent constamment grce la formation continue. Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide, Which Oracle version? Yes, of course. That information can be inferred by Oracle based on a backup controlfile or a created controlfile plus the first archivelog you apply and the log_archive_dest and log_archive_format OR your db_recovery_file_dest (ie your FRA)Hemant. Thanks a lot Hemant,So in other words, if I dont use until command, my database will not be opened automatically with resetlogs. SQL> create pfile=/backup/rman_backup/initprod.ora from spfile; [oracle@Snsm-linux02 dbs]$orapwd file=orapwprod password=oracle, Copy the password file and init file to destination $ORACLE_HOME/dbs directory using scp or ftp, scp orapwprod oracle@172.16.110.18:/data2/app/oracle/product/11.2.0/dbs/, scp /backup/rman_backup/initprod.ora oracle@172.16.110.18:/data2/app/oracle/product/11.2.0/dbs/. For completeness I would like to mention directories containing the $DB_UNIQUE_NAME like In which schema do temporary tables go in PostgreSQL? Thanks sir.When I issue "recover database using backup controlfile" , the information in controlfile is not deleted, am I right?I notice that v$archived_log has same records. Modified by RMAN duplicate scope=spfile; sql clone alter system set db_unique_name =. When you cloned a DB, you probably started it with a new name and cloning (duplicating) with RMAN provided a new DBID, right? Why dont second unit directors tend to become full-fledged directors? This is possible because the controlfile identifies the Log Sequence Number of the Online Redo Logs (ie, the CURRENT Redo Log) and Oracle can then do a Recovery to that point. This was in response to a forums.oracle.com thread (URL at the beginning of my post).A non-RMAN backup of a production database has been copied to development. GROUP 2 ( /data2/prod/redo02.log ) SIZE 50 M REUSE, GROUP 3 ( /data2/prod/redo03.log ) SIZE 50 M REUSE. Quaresma,If you have run an RMAN DUPLICATE DATABASE command you should see an "set until scn." in the "contents of Memory Script" that RMAN creates.That is how it does incomplete recovery and subsequently does an OPEN RESETLOGS.Hemant K Chitale. Generate a CREATE CONTROLFILE script (using ALTER DATABASE BACKUP CONTROLFILE TO TRACE), shutdown the database, rename datafiles at the OS level (using "move" or "mv" or "ren" commands), edit the CREATE CONTROLFILE script to specify the new locations and then run it so that the controlfile reads and sets the new path names of the datafiles.2. )Setting new locations for trace files simply requires creating the new trace file target directories and then updating your instance parameter file (initSID.ora or spfileSID.ora) for "background_dump_dest", "user_dump_dest", "core_dump_dest" etc etc) for all the "destination" directories.Hemant K Chitale. without specifying BACKUP CONTROLFILE and/or UNTIL ) is an instruction to do a COMPLETE Recovery. Note: From here on I will use SRC for the original, the source DB-name and mv fails with "No space left on device" when the destination has 31 GB of space remaining. rename the path/filenames on OS-level we can modify path as well as filenames in one command. do not forget to save your result and check all files and the FRA. TK,The controlfile has *information* about Checkpoints and SCNs.It, itself, doesn't need to be compared.However, if say, the highest available SCN recorded by the controlfile (which you would see in v$DATAFILE and V$LOG) is lower than the SCN in datafile headers, Oracle would "know" that the controlfile is older than the datafiles -- that it is a "backup" controlfile. All rights reserved. TGT for the new, the target DB-name. And it tells us that all the archives and backups are obsolete. There may be several reasons: rename all DB-files, including TEMP-files and redo-logs. In 12c (enterprise edition) you can clone a pluggable database through SQL only. Now we have executed again, this time we get different error, check below. Copyright (c) 1982, 2009, Oracle and/or its affiliates. (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521)), (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521)), Both target and destination server should have tns entries, Static listener registration on auxiliary site, Service should be register on aux listener, Sqlnet.ora should have correct parameters, Target database should be running through spfile, Check connectivity between target and destination server, Put tns entry of auxiliary database into target $ORACLE_HOME/network/admin directory, [oracle@Snsm-linux02 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin, [oracle@Snsm-linux02 admin]$ vi tnsnames.ora, TNS Ping Utility for Linux: Version 11.2.0.1.0 Production on 01-APR-2015 12:34:19. /u01/app/oracle/diag/rdbms/$DB_UNIQUE_NAME/ rev2022.7.21.42639. We are headquartered in India with offices spread across North America, Middle-east and SEA. Autonomous Database on Oracle Exadata Cloud@Customer -vs- Exadata Cloud@Customer, How to Setup Autonomous Database On Exadata Cloud@Customer. For a fresh clone, we will need as well the spfile and orapw from either the ADMIN- or ORACLE_HOME-directory and the sqlnet configuration files. RMAN-03002: failure of Duplicate Db command at 03/31/2015 16:23:02, RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/31/2015 16:23:02, ORA-17628: Oracle error 19505 returned by remote Oracle server. In the above article, we have learned that Active Duplication using Rman utility with same database name and same directory structure, and we have faced some errors and their work around. These steps and code snippets cover the tasks for a standalone. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.110.16)(PORT=1521))), Instance prod, status UNKNOWN, has 1 handler(s) for this service, DEDICATED established:0 refused:0 state:ready, Put Tns entry for target (source) database in $ORACLE_HOME/network/admin directory, [oracle@Nsm-linux01 admin]$ vi tnsnames.ora, TNS Ping Utility for Linux: Version 11.2.0.1.0 Production on 01-APR-2015 12:49:28, Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED). Oracle TNS: net service name is incorrecly specified. Put static listener entry into listener.ora file in $ORACLE_HOME/network/admin directory. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. If one or more of the datafile headers is much older, media recovery requires ArchiveLogs.Hemant K Chitale. This email has been checked for viruses by Avast antivirus software. Copyright (c) 1982, 2009, Oracle. That is assuming that you also have the Online Redo Log(s) !Note : You don't use the current controlfile because "it has information about archivelogs". RMAN has the ability to clone a database. I have already copied accross backupsets which includes archivelogs.RMAN> run{DUPLICATE TARGET DATABASE TO TESTLOGFILE1GROUP 1 ('F:\ORACLE\ORADATA\TEST\REDOLOGS\TEST_REDO1A.LOG','G:\ORACLE\ORADATA\TEST\REDOLOGS\TEST_REDO1B.ORA') SIZE 50m REUSE,GROUP 2 ('F:\ORACLE\ORADATA\TEST\REDOLOGS\TEST_REDO2A.LOG', 'G:\ORACLE\ORADATA\TEST\REDOLOGS\TEST_REDO2B.ORA') SIZE 50m REUSE,GROUP 3 ('F:\ORACLE\ORADATA\TEST\REDOLOGS\TEST_REDO3A.LOG', 'G:\ORACLE\ORADATA\TEST\REDOLOGS\TEST_REDO3B.ORA') SIZE 50m REUSENOFILENAMECHECK;}. So simply startup and If the Recovery is satisfied by the Online Redo Logs, it is Instance Recovery. Making statements based on opinion; back them up with references or personal experience. Site design / logo 2022 Stack Exchange Inc; user contributions licensed under CC BY-SA. Hi Hemant;Is there a way to change the dbname with changing the filenames in os level as well?I mean, suppose I want to change orcl to orcl2 and suppose bdump is in:/oracle/app/product/orcl/bdumpit should be:/oracle/app/product/orcl2/bdumpSame for datafile locations.Is that possible automatically? Anonymous,The headers of the datafiles tell Oracle what is the last SCN applied to each datafile and whether the datafile was closed cleanly.Before attempting an OPEN, Oracle reads the headers. Welcome to LSNRCTL, type help for information. Issue the command to duplicate the database. . Oracle Recovery Manager (RMAN) provides a comprehensive foundation for efficiently backing up and recovering the Oracle databases, it provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems, automates administration of your backup strategies. To learn more, see our tips on writing great answers. Finally, you should be able to start the cloned DB with its new namee. John,A RECOVER DATABASE would be enough.Hemant. Announcing the Stacks Editor Beta release! SQL> select value from v$parameter where name=spfile; /data2/app/oracle/product/11.2.0/dbs/spfileprod.ora, NAME TYPE VALUE, spfile string /data2/app/oracle/product/11.2. Copyright (c) 1991, 2009, Oracle. SQLNET.AUTHENTICATION_SERVICES = (NTS,NONE), #NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT).
We just need to remove the lines, containing the old 1: and new: 1: , (here $filesys1 and $filesys2 are our volumes, /u0*/oradata), adapt tnsnames.ora and append a tns-entry for the new DB: (I tend to leave the old one unchanged), (I tend to leave the old one and remove it later).