Oracle dbid что это
This article is a bit different from other recovery and backup case studies. The solution to this scenario has given by my fellow DBA Sham. Being a DBA we all are must aware of some different conditions that can occur in different conditions.
In this post, we are going to learn about the steps which we use to recover database when we don’t have the knowledge of DBID and DBNAME. As we know what is DBID and DBNAME in Oracle for new DBA we have mentioned about the technical definition of DBID and DBNAME.
DBID stands for database identifier, which is a unique identifier for each Oracle database running. It is found in control files as well as datafile header.
DBNAME stands for the database name.
Let’s start Recover the Database using RMAN Backup without having DBID or DBName
I have RMAN backup set under /u02/bkp directory, but nothing I have about these BACKUP SETS. I am trying to find the way to recover the database from these backup sets.
Let’s simulate the scenario step by step.
we have already orcldb Instance is running on the Server. This SID of orcldb is useful only to connect to the rman utility.
Finally, I found the DB_NAME and DBID. Database name is TESCO and DBID is 298331064. Now I am going to recover the database using minimum parameters.
Now export the ORACLE_SID and nomount the database.
Now we can restore the controlfile from backup.
As we got the above error of AUTOBACKUP, we need to try restore control file from availabale backup sets one by one.
Successfully we have restored controlfile from the backup. Control file has been restored $ORACLE_HOME/dbs, Later we will relocate this control file. Now controlfile is available so that we can MOUNT the Database Instance.
RMAN does NOT aware about these backup sets so that we need to register these RMAN backup files to the controlfile using following RMAN CATALOG command.
Now we can restore the database.
Here RMAN is trying to restore datafiles but path is NOT available on the TARGET Server.Create appropriate directory on the Target Server.
Now I am trying to execute same command. Let’s see ..
Wow, something looks good! Now easily we can do recovery!
Nothing we can ignore above warning because is asking to supply SEQUENCE 6! In our case we have recovered up to 5 th SEQUENCE. Now we can open the database in resetlog.
Now we can restrore the spfile from backup
Now we can read the spfile to make directory structure
Create required directory structure
Shutdown the database
Existing controlfile in $ORACLE_HOME/dbs
Restoring the controlfile as per SPFILE
Simply startup the Database Instance!
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles s end us the Invitation or Follow us:
Anuradha’s LinkedIn: Anuradha’s Profile
The DBID is a very important part for Oracle databases. It is an internal, uniquely generated number that differentiates databases. Oracle creates this number automatically as soon as you create the database.
During normal operation, it is quite easy to find your DBID. Whenever you start your RMAN session, it displays the DBID.
Or you can just simply select it from your v$database view.
But what happens in case you have a restore/recovery scenario where you lost your database. In the NOMOUNT state, it is not possible to retrieve the DBID.
You can take a look into the alert.log or any other trace file in your DIAG destination, but you will not find a DBID there.
So, if the only thing that you have left is your RMAN Catalog, and your datafile copies in your FRA + Archivelogs, then you need the DBID beforehand, before you can restore/recover your database correctly.
There are three possibilities to get your DBID
- You could check your RMAN backup log files, if you have set it up correctly
- You could connect to your RMAN catalog and query the “DB” table from the catalog owner. Be careful, there might be more than one entry for your DB name, and then it might become difficult to get the correct one. In my example, I have only one entry
- And as the last resort, you can startup nomount (either with a backup pfile or with the RMAN dummy instance), and afterwards you can dump out the header of your datafile copies in your FRA
Dumping out the first block is usually enough, and besides that, you are not limited to the SYSTEM datafile. You can use any of your datafile copies in your FRA (like SYSAUX, USERS and so on) to dump out the block, like shown in the following example:
As soon as you have your DBID, it is straight forward to do the rest. Connect to your target and RMAN catalog, set the DBID and then run your restore, recovery scripts.
Conclusion
Don’t forget to save your DBID with your RMAN backup jobs somewhere. Recovering a database at 3 o’clock in the morning with a missing DBID might become a nightmare.
Cheers,
William
DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database.
This chapter contains the following sections:
Step 3. Make the changes
I won’t go into all the details of the package. Instead, here is PL/SQL block you can run to make the change:
Changing Only the Database Name
The following steps describe how to change the database name without changing the DBID.
-
Ensure that you have a recoverable whole database backup. Ensure that the target database is mounted but not open, and that it was shut down consistently prior to mounting. For example: Invoke the utility on the command line, specifying a valid user with the SYSDBA privilege. You must specify both the DBNAME and SETNAME parameters. This example changes the name to test_db2 :
DBNEWID performs validations in the headers of the control files (not the datafiles) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, and exits. After DBNEWID completes successfully, the database is left mounted but is not yet usable.
If validation is not successful, then DBNEWID terminates and leaves the target database intact. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing the database name.
22.2 Ramifications of Changing the DBID and DBNAME
Describes the ramifications of changing the DBID and DBNAME of a database.
Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. This is similar to creating a database except that the data is already in the data files. After you change the DBID, backups and archive logs that were created before the change can no longer be used because they still have the original DBID, which does not match the current DBID. You must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1. Consequently, you should make a backup of the whole database immediately after changing the DBID.
Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. You must change the DB_NAME initialization parameter after a database name change to reflect the new name. Also, you may have to re-create the Oracle password file. If you restore an old backup of the control file (before the name change), then you should use the initialization parameter file and password file from before the database name change.
Do not change the DBID or DBNAME of a database if you are using a capture process to capture changes to the database.
If you are dealing with a database in a distributed database system, then each database should have a unique global database name.
Parent topic: DBNEWID Utility
22.2.1 Considerations for Global Database Names
If you are dealing with a database in a distributed database system, then each database should have a unique global database name.
The DBNEWID utility does not change global database names. This can only be done with the SQL ALTER DATABASE statement, for which the syntax is as follows:
The global database name is made up of a database name and a domain, which are determined by the DB_NAME and DB_DOMAIN initialization parameters when the database is first created.
The following example changes the database name to sales in the domain us . example . com :
You would do this after you finished using DBNEWID to change the database name.
Oracle Database Administrator's Guide for more information about global database names
Conclusion
I probably have the coolest Database Name and ID in the world now:
But if you think about it, I also changed some information in the datafile, even though the database was opened in read-only mode. Interesting?
DBNEWID Syntax
The following diagrams show the syntax for the DBNEWID utility.
Parameters
Table 14-1 describes the parameters in the DBNEWID syntax.
Table 14-1 Parameters for the DBNEWID Utility
Specifies the username and password used to connect to the database. The user must have the SYSDBA privilege. If you are using operating system authentication, then you can connect with the slash ( / ). If the $ORACLE_HOME and $ORACLE_SID variables are not set correctly in the environment, then you can specify a secure (IPC or BEQ) service to connect to the target database. A target database must be specified in all invocations of the DBNEWID utility.
Specify YES to indicate that a failed change of DBID should be reverted (default is NO ). The utility signals an error if no change DBID operation is in progress on the target database. A successfully completed change of DBID cannot be reverted. REVERT=YES is only valid when a DBID change failed.
Changes the database name of the database. You can change the DBID and the DBNAME of a database at the same time. To change only the DBNAME, also specify the SETNAME parameter.
Specify YES to indicate that DBNEWID should change the database name of the database but should not change the DBID (default is NO ). When you specify SETNAME=YES , the utility only writes to the target database control files.
Specifies that DBNEWID should write its messages to the specified file. By default the utility overwrites the previous log. If you specify a log file, then DBNEWID does not prompt for confirmation.
Specify YES to append log output to the existing log file (default is NO ).
Specify YES to print a list of the DBNEWID syntax options (default is NO ).
Changing the DBID and DBNAME of a Database
This section contains these topics:
22.3 DBNEWID Considerations for CDBs and PDBs
The DBNEWID parameter PDB allows you to change the DBID on pluggable databases (PDBs).
By default, when you run the DBNEWID utility on a container database (CDB) it changes the DBID of only the CDB; the DBIDs of the pluggable databases (PDBs) comprising the CDB remain the same. This could cause problems with duplicate DBIDs for PDBs in some cases, such as when a CDB is cloned.
As of Oracle Database 12 c Release 2 (12.2), you can change the DBID on the PDBs by using the new DBNEWID PDB parameter. You cannot specify a particular PDB; either all of them or none of them will have new DBIDs. The PDB parameter is applicable only in a CDB environment. It has the following format:
If you specify ALL , then in addition to the DBID for the CDB changing, the DBIDs for all PDBs comprising the CDB are also changed.
Specifying NONE (the default) leaves the PDB DBIDs the same, even if the CDB DBID is changed.
Oracle recommends that you use PDB=ALL , but PDB=NONE is the default for backward compatibility reasons.
Parent topic: DBNEWID Utility
Ramifications of Changing the DBID and DBNAME
Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. After you change the DBID, you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1 (see the Oracle9i Database Administrator's Guide). Consequently, you should make a backup of the whole database immediately after changing the DBID.
Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. You must change the DB_NAME initialization parameter after a database name change to reflect the new name. Also, you may have to re-create the Oracle password file. If you restore an old backup of the control file (before the name change), then you should use the initialization parameter file and password file from before the database name change.
22.1 What Is the DBNEWID Utility?
The DBNEWID utility enables you to change only the DBID , DBNAME , or both the DBID and DBNAME of a database.
Before the introduction of the DBNEWID utility, you could manually create a copy of a database and give it a new database name ( DBNAME ) by recreating the control file. However, you could not give the database a new identifier ( DBID ). The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID , you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem by enabling you to change any of the following:
- Only the DBID of a database
- Only the DBNAME of a database
- Both the DBNAME and DBID of a database
Parent topic: DBNEWID Utility
Conclusion
Don’t forget to save your DBID with your RMAN backup jobs somewhere. Recovering a database at 3 o’clock in the morning with a missing DBID might become a nightmare.
Cheers,
William
DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database.
This chapter contains the following sections:
Troubleshooting a DBID Change Operation
If the DBNEWID utility succeeds in its validation stage but detects an error while changing the DBID, then the utility stops and leaves the database in the middle of the change. In this case, you cannot open the database until the DBNEWID operation is either completed or reverted. DBNEWID displays messages indicating the status of the operation.
Before continuing or reverting, fix the underlying cause of the error. Sometimes the only solution is to restore the whole database from a recent backup and perform recovery to the point in time before DBNEWID was started. This underscores the importance of having a recent backup available before running DBNEWID.
If you choose to continue the DBID change operation rather than revert it, reexecute your original command. The DBNEWID utility resumes and attempts to continue the change until all datafiles and control files have the new DBID. At this point, the database is left mounted. You should shut it down and then mount it again prior to opening it with the RESETLOGS option.
If you choose to revert a DBNEWID operation, and if the reversion succeeds, then DBNEWID reverts all performed changes and leaves the database in a mounted state.
To revert a stalled DBID change operation, run the DBNEWID utility again, specifying the REVERT keyword. For example:
What Is the DBNEWID Utility?
Prior to the introduction of the DBNEWID utility, you could manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, you could not give the database a new identifier (DBID). The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem by allowing you to change any of the following:
-
Only the DBID of a database Only the DBNAME of a database Both the DBNAME and DBID of a database
22.4 Changing the DBID and DBNAME of a Database
This section contains these topics:
Describes how to change the DBID of a database.
Describes changing the database ID without changing the database name.
Describe how to change the database name without changing the DBID.
Describes troubleshooting hints for the DBNEWID utility.
Parent topic: DBNEWID Utility
22.4.1 Changing the DBID and Database Name
Describes how to change the DBID of a database.
The following steps describe how to change the DBID of a database. Optionally, you can change the database name as well.
- Ensure that you have a recoverable whole database backup.
- Ensure that the target database is mounted but not open, and that it was shut down consistently before mounting. For example:
To change the database name in addition to the DBID, also specify the DBNAME parameter on the command line (you will be prompted for a password). The following example changes the database name to test_db :
The DBNEWID utility performs validations in the headers of the data files and control files before attempting I/O to the files. If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt), changes the DBID (and the DBNAME, if specified, as in this example) for each data file, including offline normal and read-only data files, shuts down the database, and then exits. The following is an example of what the output for this would look like:
If validation is not successful, then DBNEWID terminates and leaves the target database intact, as shown in the following sample output. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing its DBID.
Make a new database backup. Because you reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.
22.4.2 Changing Only the Database ID
Describes changing the database ID without changing the database name.
Follow the steps in Changing the DBID and Database Name, but in Step 3 do not specify the optional database name (DBNAME). The following is an example of the type of output that is generated when only the database ID is changed.
22.4.3 Changing Only the Database Name
Describe how to change the database name without changing the DBID.
Execute the following steps:
- Ensure that you have a recoverable whole database backup.
- Ensure that the target database is mounted but not open, and that it was shut down consistently before mounting. For example:
DBNEWID performs validations in the headers of the control files (not the data files) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, shuts down the database and exits. The following is an example of what the output for this would look like:
If validation is not successful, then DBNEWID terminates and leaves the target database intact. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing the database name. (For an example of what the output looks like for an unsuccessful validation, see Step 3 in Changing the DBID and Database Name.)
The DBNEWID utility does not change the server parameter file ( SPFILE ). Therefore, if you use SPFILE to start your Oracle database, then you must re-create the initialization parameter file from the server parameter file, remove the server parameter file, change the DB_NAME in the initialization parameter file, and then re-create the server parameter file.
Because you have changed only the database name, and not the database ID, it is not necessary to use the RESETLOGS option when you open the database. This means that all previous backups are still usable.
22.4.4 Troubleshooting DBNEWID
Describes troubleshooting hints for the DBNEWID utility.
If the DBNEWID utility succeeds in its validation stage but detects an error while performing the requested change, then the utility stops and leaves the database in the middle of the change. In this case, you cannot open the database until the DBNEWID operation is either completed or reverted. DBNEWID displays messages indicating the status of the operation.
Before continuing or reverting, fix the underlying cause of the error. Sometimes the only solution is to restore the whole database from a recent backup and perform recovery to the point in time before DBNEWID was started. This underscores the importance of having a recent backup available before running DBNEWID.
If you choose to continue with the change, then re-execute your original command. The DBNEWID utility resumes and attempts to continue the change until all data files and control files have the new value or values. At this point, the database is shut down. You should mount it before opening it with the RESETLOGS option.
If you choose to revert a DBNEWID operation, and if the reversion succeeds, then DBNEWID reverts all performed changes and leaves the database in a mounted state.
If DBNEWID is run against a release 10.1 or later Oracle database, then a summary of the operation is written to the alert file. For example, for a change of database name and database ID, you might see something similar to the following:
For a change of just the database name, the alert file might show something similar to the following:
You can choose a DBID when you rename your Oracle database. This is probably a bad, unsupported, and useless idea. I assume this hidden feature can help you to mess up all your backups. So my advice would be: “don’t use it.”
I performed this test with Oracle 11.1.0.7 on Linux x86. It consists in using dbms_backup_restore instead of nid to rename the database. You’ll find below the few steps require to get to it.
Examples of Using DBNEWID
Changing Only the DBID
The following example connects with operating system authentication and changes only the DBID:
Changing the DBID and Database Name
The following example connects as user SYS and changes the DBID and also changes the database name to test2 :
Changing Only the Database Name
The following example connects as user SYSTEM and changes only the database name, and also specifies a log file for the output:
DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database.
See the following topics:
The DBNEWID utility enables you to change only the DBID , DBNAME , or both the DBID and DBNAME of a database.
Describes the ramifications of changing the DBID and DBNAME of a database.
The DBNEWID parameter PDB allows you to change the DBID on pluggable databases (PDBs).
This section contains these topics:
Describes DBNEWID syntax.
Parent topic: Other Utilities
Author
Grégory Guillou
22 Comments. Leave new
Good Article! Anyway I have no idea why? I should to change DBID.
Perhaps that make easy to remember or support.
My idea.. I don’t interest yet…
If it can help when I mess up all my backups. I think that’s very good.
Good stuff, but why not use just nid ?
Did you have any problem with that ?
I had been using dbms_backup_recovery from time to time when I had problems with RMAN and due to lack of catalog command in 8i and 9i.
Marcin, I can hardly more agree with you: use nid!
As far as I can see, the above approach is the only way to set the new DBID to a value you want. This is not possible with “nid” (a new value might be generated, but not *your* value)
Excellent script! Was asked to restore a DB from some backupset files – no controlfile backups, no documentation. I had to create a dummy database and set dbid just to catalog the backupsets – so your script came in very handy.
Thanks, hope this helps me fix my DG configuration.
I am getting errors in Step 4. I am using Oracle 10g and Windows 7 OS. Can anyone help me.
Can’t able to run the script
!cat initBLACK.ora | \
sed “s/db_name=’BLACK’/db_name=’FRANCE’/” \
> initFRANCE.ora
i am using Oracle 10g and Windows 7 OS and running the script command prompt.
Please help me out on this.
Thanks in advance.
Sagar: You can’t run the above command because it is a unix command.
What the script is trying to do is rename a parm in init.ora
from
db_name=’BLACK’
TO
db_name=’FRANCE
====
You can do the same thing in notepad.
What this step does basically is create a file name initFRANCE.ora from initBLACK.ora by
– copying all the parameters except for db_name
– replacing db_name value from BLACK to FRANCE
You can probably automate that on Windows with some kind of VBScript/Powertools but I have no clue how.
SQL> startup mount pfile=’/u01/app/oracle/product/11.1.0/db_5/dbs/initCORP2.ora’;
ORA-01081: cannot start already-running ORACLE – shut it down first
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
what to do..
Very Usefull ! ;D i Just set physical standby by rman duplicate with SKIP TABLESPACE .. then i’ve change the DBID to identical as primary .. an then .. it works :D physical standby with different structure :D
This is an excellent script, we have 45 TB of DB and took FRA save point for flash back for multiple time,but DBID is changed for a reason using NID, then realized we need to flashback the DB ,after changing the DBID, we can’t flash back since all FRA logs not useful.
using this script we changed back to original DBID and able to flashback the database.
Step 1. Open the database in read-only mode
First, stop the instance with an immediate shutdown. If we were to use nid , we would mount the instance, but with dbms_backup_restore , we need to access the package. For this reason, we have to open the database in read-only mode. Here are the commands I ran:
Changing the DBID and Database Name
The following steps describe how to change the DBID of a database. Optionally, you can change the database name as well.
-
Ensure that you have a recoverable whole database backup. Ensure that the target database is mounted but not open, and that it was shut down consistently prior to mounting. For example: Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege. For example:
To change the database name in addition to the DBID, specify the DBNAME parameter. This example changes the name to test_db2 :
The DBNEWID utility performs validations in the headers of the datafiles and control files before attempting I/O to the files. If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt), changes the DBID for each datafile (including offline normal and read-only datafiles), and then exits. The database is left mounted but is not yet usable. For example:
If validation is not successful, then DBNEWID terminates and leaves the target database intact. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing its DBID.
Make a new database backup. Because you reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.
Step 2. Get the old values; set the new ones…
Once we can access the database, we can check its NAME and DBID . The script below does these checks and prompts the user for the new NAME and DBID . To that result, it queries V$DATABASE :
Troubleshooting a Database Name Change Operation
If you specify that only the database name should be changed (and not the DBID), then the validation process is the same as for a DBID change except that DBNEWID checks only the control files. It does not read the datafiles. If the validation encounters a problem, then the database is left mounted.
It is possible for validation to succeed, but for the actual database name change to fail. The possible failure scenarios depend on how many control files are in the database, as follows:
-
If you have one or more control files and DBNEWID fails on the first control file, then the database name is not changed in the control file. You can either try the operation again or open the database and resume normal database use. If you have more than one control file and DBNEWID fails on the second control file or on any one thereafter, then some control files will have the old DBNAME and some will have the new DBNAME. In this case, you must either manually copy the first changed control file to all CONTROL_FILES locations, or revert by copying the unchanged control files to all CONTROL_FILES locations.
Step 4. Change db_name and open the database
Before you can open the database, you have to change the db_name parameter in the spfile. Once you’ve done so, you should be able to open it with resetlogs. That’s the script I ran to get to that result:
Restrictions and Usage Notes
The DBNEWID utility has the following restrictions:
-
The utility is available only on the UNIX and Windows NT operating systems. The nid executable file should be owned and run by the Oracle owner because it needs direct access to the datafiles and control files. If another user runs the utility, then set the user ID to the owner of the datafiles and control files. The DBNEWID utility must access the datafiles of the database directly through a local connection. Although DBNEWID can accept a net service name, it cannot change the DBID of a nonlocal database. To change the DBID of a database, the database must be mounted and must have been shut down consistently prior to mounting. In the case of an Oracle Real Application Clusters database, the database must be mounted in NOPARALLEL mode. You must open the database with the RESETLOGS option after changing the DBID. Note that you do not have to open with the RESETLOGS option after changing only the database name. No other process should be running against the database when DBNEWID is executing. If another session shuts down and starts the database, then DBNEWID aborts. All online datafiles should be consistent without needing recovery. Normal offline datafiles should be accessible and writable. If this is not the case, you must drop these files before invoking the DBNEWID utility. All read-only tablespaces must be accessible and made writable at the operating system level prior to invoking DBNEWID. If these tablespaces cannot be made writable (for example, they are on a CD-ROM), then you must unplug the tablespaces using the transportable tablespace feature and then plug them back in the database before invoking the DBNEWID utility (see the Oracle9i Database Administrator's Guide). You can only specify REVERT when changing only the DBID.
Читайте также: