Until now my understanding for EXPDP/IMPDP was that
if the user performing import has 'IMPORT FULL DATABASE' privilege, it'll
create the users/schemas in the target database. I needed to export (expdp)
schema from one machine and import (impdp) it to another machine. I just wanted impdp to create schema on target database.
While doing import i
encountered error
$ impdp system/oracle123
directory=TEST_DIR dumpfile=test.dmp logfile=impdptest.log
.......
Starting
"SYSTEM"."SYS_IMPORT_FULL_01": system/********...
Saturday, 22 February 2014
Identify the Tablespace names from EXPDP dump file
I got request from development team to
import some old data in UAT region.The expdp dump file received from backup team was without log file and i have no access to the export file's source DB. I wanted to import that
dumpfile into UAT DB and i do not know the tablespace names required to do
so.
Solution:
Use the sqlfile option present in impdp utility. This
option will write all the SQL DDL to a specified file instead of executing
them.
$ impdp scott/tiger directory=exp_dir
dumpfile=scott.dmp sqlfile=script.sql
Import:...
Wednesday, 29 January 2014
ORA-01031: insufficient privileges to sys@standby as sysdba
The
ORA-01031 is very generic error and normally users will receive this error when
users don’t have appropriate privilege.
$oerr ora 01031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
// without the appropriate
privilege. This error also occurs if
// attempting to install a
database without the necessary operating
// system privileges.
// ...
Saturday, 25 January 2014
Creating Redo Log Groups and Members
Create all required groups and
members of redo log files during database creation. However, there are
situations where you might want to create additional groups or members.
Creating Redo Log Groups
To create new redo log groups and
members, you must have the ALTER DATABASE system privilege.
To create a new group of redo log
files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause.
The following statement adds a new
group of redo logs to the database:
ALTER DATABASE
ADD LOGFILE
('/oracle/dbs/log1c.rdo',...
Multiplex & Move Oracle control files to different directory
1, shutdown database.
shutdown immediate;
2, edit Oracle init.ora file, e.g., C:\Oracle\10g\database\inittest.ora,
find the line with control file location, change the location from old
directory to new directory, e.g.:
OLD:
control_files=("H:\Oradata\test\control01.ctl")
NEW:
control_files=("I:\Oradata\test\control01.ctl", "J:\Oradata\test\control02.ctl", "K:\Oradata\test\control03.ctl")
save init.ora...
Saturday, 18 January 2014
Automate AWR Report Generation
By default snapshots of the relevant data are taken every hour and retained for 7 days. Most of the times database running in default AWR settings. DBA's generates the awr report when something happened in their database or periodically to check if anything is wrong in databases
Some times databases are running smooth without any problems. Due to the default retention of AWR snapshot setting we may loose some good information about database.When problem occurs in the database we may need to compare the bad time report (when problem...
Monday, 23 December 2013
Restoring an RMAN Backup to Another Node /Clone Database
In certain circumstances, it may be desirable to restore a
database from an RMAN backup onto a machine other than the original
host.
For example,Restore and recover data at a given point in time for UAT/Testing, or to
duplicate a production instance.
The example assumes:
the target database is on host A
the database is to be restored onto host B
the directory structure of host B is different to host A
the ORACLE_SID will not change for the restored database
a recovery catalog is being used
the backups were carried out to disk (for...
Thursday, 19 December 2013
Full Database Recovery or Disaster Recovery.
You use the RESTORE and RECOVER commands to restore and recover the database.RMAN checks last good backup set and restore the datafiles to the state they were in when that backup set was created. When restoring database files with RMAN, it reads the datafile header and makes the determination as to whether the file needs to be restored.
If you cannot restore datafiles to their default locations, then you must update the control file to reflect the new locations of the datafiles. Use the RMAN SET NEWNAME command within a RUN command to specify...