Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

DBID (nid) Utility *A Powerfull Weapon in DBA’s Arsenal*

Posted by FatDBA on March 15, 2013

We should be thankful to that almighty that we are living an age where we have Oracle Database 10g and further releases available, If we’re not then changing some of the internal identifiers like DBNAME & DBID would be a real pain in the ass. Before Oracle 10g assigning a new DBNAME was only possible by manually re-creating the Control File and with no alternate or method to change DBID.

* DBNAME: Name of your database and can be comapred with your name e.g Prashant …
* DBID: Unique number (Identifier) assigned to your database in order to identify it among other DB’s and can be easily compared it with your house address. e.g 9/90-

DBNEWID (NID is same) is an oracle Database utlity/program which sits in $ORACLE_HOME/bin directory and can change the internal database identifier number or DBID and Database Name.

Alright, after that basic idea about DBNAME & DBID let’s jump to Oracle’s NID Utlity (Introduced in 10g).

DBID is a unique identifier for a database. During the years oracle released several versions of their database packages along with newer utilities and with many advancenments and re-imaged so many existing tools. RMAN (Recovery Manager) is one of the tool which along with time turned out to be a very powerful weapon in any DBA’a armoury for Backup and Recovery activities.
Up to 9i you could not register a Primary Database and a Clone Database in the same RMAN Repository or RMAN Catalog. DBID resolved this problem by changing DBID or DBNAME.

With NID Utility you can change:
– Only DBNAME
– Only DBID
– DBNAME and DBID Both

Points to be keep in mind when altering DBID of a database:
* After changing the DBID of database you must have to open your DB with RESETLOG option.
* After changing DBID of a database all old backups and archive logs becomes unsuable.
* Full Backup of your database soon after changing the DBID.

Points to be keep in mind when altering DBNAME of a database:
* This does not require to open database with RESETLOG option (No re-creation of online redo logs will happen & no sequence reset)
* Must change DB_NAME entry in your Parameter File to reflect new name.
* May have to create a new Password File.

Options available with NID Utility:

[oracle@localhost ~]$ nid help=y

DBNEWID: Release 11.2.0.1.0 – Production on Fri Mar 15 17:39:20 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
—————————————————-
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

CHANGING DBNAME & DBID Both

SQL> select name, dbid from v$database;

NAME            DBID
——— ———-
ORCL      1310899424

* Always recommended that you should take a full backup of your database before you go for DBID & DBNAME change.
– Mount your database after a graceful SHUTDOWN
– Change DBNAME & DBID
– SHUTDOWN IMMEDIATE
– STARTUP MOUNT (Could receive error like this: ORA-01103: database name ‘ANT’ in control file is not ‘ORCL’) ** Ignore
– Alter DB_NAME parameter in your pfile/spfile.
– SHUTDOWN IMMEDIATE
– Start your database with RESETLOGS option (SQL> alter database open resetlogs;)
– Verify both DBID & DBNAME.

[oracle@prashant ~]$ nid TARGET=sys/oracle90 DBNAME=ant
DBNEWID: Release 11.2.0.1.0 – Production on Fri Mar 15 13:04:03 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to database ORCL (DBID=1310899424)
Connected to server version 11.2.0

Control Files in database:
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Change database ID and database name ORCL to ANT? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1310899424 to 124308932
Changing database name from ORCL to ANT
Control File /u01/app/oracle/oradata/orcl/control01.ctl – modified
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – modified
Datafile /u01/app/oracle/oradata/orcl/system01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/sysaux01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/undotbs01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/users01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/example01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/rmanrep/rman01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/temp01.db – dbid changed, wrote new name
Control File /u01/app/oracle/oradata/orcl/control01.ctl – dbid changed, wrote new name
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – dbid changed, wrote new name
Instance shut down

Database name changed to ANT.
Modify parameter file and generate a new password file before restarting.
Database ID for database ANT changed to 124308932.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.

SQL> select name, dbid from v$database;

NAME            DBID
——— ———-
ANT        124308932

– Only DBNAME
To change DBNAME only you have to use both DBNAME and SETNAME parameters in nid string. DBNEWID performs validations in controlfiles headers only not datafiles. Change DB_NAME parameter soon after you modify DBNAME using nid utility in pfile/spfile.

Change DBNAME
SHUTDOWN IMMEDIATE
STARTUP MOUNT (Could recieve error like this: ORA-01103: database name ‘REDANT’ in control file is not ‘ANT’) ** Ignore
Alter DB_NAME parameter in your pfile/spfile.
SHUTDOWN IMMEDIATE
Verify both DBNAME.

[oracle@prashant ~]$ nid TARGET=sys/oracle90 DBNAME=redant SETNAME=YES

DBNEWID: Release 11.2.0.1.0 – Production on Fri Mar 15 13:18:51 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to database ANT (DBID=124308932)
Connected to server version 11.2.0

Control Files in database:
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Change database name of database ANT to REDANT? (Y/[N]) => Y

Proceeding with operation
Changing database name from ANT to REDANT
Control File /u01/app/oracle/oradata/orcl/control01.ctl – modified
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – modified
Datafile /u01/app/oracle/oradata/orcl/system01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/sysaux01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/undotbs01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/users01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/example01.db – wrote new name
Datafile /u01/app/oracle/oradata/rmanrep/rman01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/temp01.db – wrote new name
Control File /u01/app/oracle/oradata/orcl/control01.ctl – wrote new name
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – wrote new name
Instance shut down

Database name changed to REDANT.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID – Completed succesfully.

– Only DBID
If want to change DBID only, do not give DBNAME parameter in nid sting. This requires Opening of database with RESETLOG option.

[oracle@prashant ~]$ nid TARGET=SYS/oracle90
Connected to database REDANT (DBID=124308932)

Database ID for database REDANT changed to 629811920.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID – Completed succesfully.

To revert back changes use REVERT keyword.
[oracle@prashant ~]$ nid TARGET=SYS/oracle90 REVERT=YES

Posted in Advanced | Tagged: | Leave a Comment »

Questions ??

Posted by FatDBA on March 7, 2013

Let’s discover more about Data Guard 11g and discuss some of the topics out of the  ocean named Data Guard.

Query Scn in case of Active Data Guard ?
Fast-Start when using Maximum Performance Model … ?
Split-Brain Scenarios in DG 11g and how Fast-Start resolves the problem ?

Posted in Advanced | Tagged: , | Leave a Comment »

Database Migration vs Upgrade ?

Posted by FatDBA on February 25, 2013

While talking with one of the new DBA found him uncomfortable & perplexed when started discussing Database Migration and Upgrade activities. Well i’ve seen that there are so many confusions revolves in any DBA’s mind who is very new to the Administration.

Alright, so today i would like to distinguish between the two activities which could be easily considered as one of the major activity of any DBA.

Okay.

Terms like Migration and Upgrade are used to convert an existing database to a new software release. One of the major difference that i’ve discovered is,

In case of Upgrade, you shut down the database, start it up with the new software release, and then execute provided SQL and PL/SQL scripts to update Oracle’s internal dictionary tables and other objects. Oracle also provides ‘downgrade’ scripts which can be run to revert back all changes that happened to the database during the Upgrade process.
In broader terms Upgrade is a new release for existing system.

Example: DB Version change from Oracle 7.3.3 to Oracle 7.4.4 is an Upgrade example. e.g. 8.1.6->8.1.7 or 8.1.7.0->8.1.7.4.

Migration:
In some cases Migration may describe the process of moving data from a non-Oracle database into an Oracle database. A Migration is generally displacing a database from —

* From one destionation/location to new one.
* One RDBMS to another (Hetrogenous), example: Sybase to Oracle.
* One major release to another (Homogenous), example: Oracle 9i to Oracle 10g.
Example: Going from Oracle 9i to Oracle 10g is a Migration example. e.g. 7->8, 8->9.

Posted in Advanced | Tagged: , | 1 Comment »

Snapshot Standby ?

Posted by FatDBA on February 22, 2013

Writing …… Will Soon Update.

Posted in Advanced | Tagged: , | Leave a Comment »

Oracle Data Guard Evolution.

Posted by FatDBA on February 21, 2013

Oracle Data Guard’s Improvements from Version 8i (Introduced in same version) till now:

ORACLE 8i
————————-
* Read-Only Standby Database
* Managed recovery
* Remote archiving redo log files

ORALCE 9i
————————-
* “Zero Data Loss” Integration
* Data Guard Broker and Data Guard Manager GUI
* Swithcover and Failover operations
* Automatical synchronous
* Logical Standby Database
* Maximum Protection

ORACLE 10g
————————-
* Real-Time Apply
* Fast-Start Failover
* Asynchronous redo transfer
* Flashback Database

ORACLE 11g
————————
* Active Standby Database (Active Data Guard)
* Snapshot Standby
* Heterogeneous platform support (Production –Linux, Standby – Windows)

Posted in Advanced | Tagged: , | 1 Comment »

What are Standby Redo log Files in Data Guard Environment ?

Posted by FatDBA on February 21, 2013

What are Standby Redo Log Files and why they are important ?

Recently asked by one of my good Fried Sowmya about some internals of Standby Redo Log Files and soon i found myself in bit of soreness. So, here i decided to pt some light over this very basic thing if one has to start with Oracle Data Guard.

Standby Redo Logs: They are very similar to Online Redo but with the only difference that S’by Redo Logs are used to contain redo data from Primary DB. S’by Redo Logs are only used if you are using Log Writer as the log transport medium to Standby Site.

sbydb042
When we use Standby Redo Log Files:
*  When using Real-Time Apply service.
*  If using Maximum Protection or Maximum Availability Models.

Points To Remember:
* You should create standby redo logs on both the standby and the primary database which is helpful in case of switch-overs (But is not mandatory for Primary Database)
* The standby redo logs should be at least as big as the largest online redo log .
* You can create Standby Logs after creating Standby Environment.

* In case of disaster and complete loss of the primary database, the entire redo log worth of data will not be lost because the standby redo log file on standby site will contain some or all of the transactions contained in the lost redo log file. This has been one of the most useful enhancements in Data Guard technology, as it minimizes the loss of data while operating in MAXIMUM PERFORMANCE mode.

Example:
Creating Standby Redo log Files on
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/DB11G/standby_redo01.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/DB11G/standby_redo02.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/app/oracle/oradata/DB11G/standby_redo03.log’) SIZE 50M;

SQL> select group#, member, type from v$logfile;

GROUP#                          MEMBER                                                                            TYPE
———-    ———————————————————————————————————————————–—
4       /u01/app/oracle/oradata/DB11G/standby_redo01.log                      STANDBY
3       /u01/app/oracle/oradata/DB11G/standby_redo02.log                      STANDBY
5       /u01/app/oracle/oradata/DB11G/standby_redo03.log                      STANDBY

Here i’ve added a standby logfile on Std DB end:

ALTER DATABASE ADD LOGFILE (‘/u01/app/oracle/oradata/DB11G/online_redo01.log’) SIZE 50M;
ALTER DATABASE ADD LOGFILE (‘/u01/app/oracle/oradata/DB11G/online_redo02.log’) SIZE 50M;
ALTER DATABASE ADD LOGFILE (‘/u01/app/oracle/oradata/DB11G/online_redo03.log’) SIZE 50M;

Pic Credits: Google/Oracle.com

Posted in Advanced | Tagged: , | 1 Comment »

Enterprise Cloud Infrastructure for Dummies (Oracle Special Edition – PDF Format) E-Book

Posted by FatDBA on February 19, 2013

Get your own copy of Enterprise Cloud Infrastructure for Dummies, Oracle Special Edition.
Source: Oracle.com

Link: http://www.oracle.com/webapps/dialogue/ns/dlgwelcome.jsp?p_ext=Y&p_dlg_id=11847803&src=7618000&Act=8

1653970

Posted in Advanced, Basics | Tagged: | Leave a Comment »

DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM and

Posted by FatDBA on February 8, 2013

Well there are whole lot of changes when you start comparing Oracle 10g and 11g.  DB_ULTRA_SAFE is one of the new parameter introduced in 11g which provides a mechanism to avoid block corruptions in your database. his parameter actually controls three parameters DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM & DB_LOST_WRITE_PROTECT parameters collectively.
Click on below provided links in order to read more about these three three initialization parameters.

DB_BLOCK_CHECKING

DB_BLOCK_CHECKSUM

DB_LOST_WRITE_PROTECT

Posted in Advanced | Tagged: | 2 Comments »

Image Copies VS BackupSets

Posted by FatDBA on February 7, 2013

Difference between Image Copies and BackupSets (RMAN):

Image Copies could be of two types:
1. RMAN Image Copies
2. O.S Image Copies

RMAN Image Copies
Use the RMAN copy command to create an image copy. If the original file needs to be replaced, and if the image copy is of a datafile, then you do not need to restore the copy. Instead, Oracle provides a switch command to point the control file at the copy and update the recovery catalog to indicate that the copy has been switched. Issuing the switch command in this case is equivalent to issuing the SQL statement ALTER DATABASE RENAME DATAFILE. You can then perform media recovery to make the copy current. They have an advantage of checking for Corrupt Blocks as they are being read by RMAN.

O/S Image Copies
Oracle supports image copies created by mechanisms other than RMAN, also known as O/S copies. For example, a copy of a datafile that you make with the UNIX cp command is an O/S copy. You must catalog such O/S copies with RMAN before using them with the restore or switch commands.
You can create an O/S copy when the database is open or closed. If the database is open and the datafile is not offline normal, then you must place the tablespace in hot backup mode, that is, issue the SQL statement ALTER TABLESPACE BEGIN BACKUP before creating the copy.

BackupSets:
A backup set consists of one or more files in an RMAN-specific format, known as backup pieces. By default, a backup set consists of one backup piece. When backing up datafiles to backup sets, RMAN is able to skip some datafile blocks that do not currently contain data, reducing the size of backup sets and the time required to create them. This behavior, known as unused block compression, means that backups of datafiles as backup sets are generally smaller than image copy backups and take less time to write.

Main Differences:
•RMAN supports binary compression of backup sets, where the backup set contents are compressed before being written to disk using a compression algorithm tuned for compression of datafiles and archived log files. But with Image copy no compression is done.

•A backup set never contains empty blocks. As RMAN passes through the datafiles, it simply skips blocks that have never been used. But image copy contains empty blocks. This means that backups of datafiles as backup sets are generally smaller than image copy backups and take less time to write.

•Incremental backups can’t be performed with Image copy but incremental backups can be taken over backup sets.

•RMAN can take backup of backup sets directly to tape, if you have installed the RMAN drivers for your tape library.But RMAN can’t take backup of image copies directly to tape.

Posted in Advanced | Tagged: | 4 Comments »

Shrinking Segments

Posted by FatDBA on February 7, 2013

Shrinking Segments:
The diagram in the slide describes the two phases of a table shrink operation. The first phase does the compaction. During this phase, rows are moved to the left part of the segment as much as possible. Internally, rows are moved by packets to avoid locking issues. After the rows have been moved, the second phase of the shrink operation is started. During this phase, the high-water mark (HWM) is adjusted and the unused space is released. The COMPACT clause is useful if you have long-running queries that might span the shrink operation and attempt to read from blocks that have been reclaimed. When you specify the SHRINK SPACE COMPACT clause, the progress of the shrink operation is saved in the bitmap blocks of the corresponding segment. This means that the next time a shrink operation is executed on the same segment, the Oracle database server remembers what has been done already. You can then reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.

Untitled

Posted in Advanced | Tagged: , | Leave a Comment »

« Previous Entries
Next Entries »