Oracle 10g onwards, The SYSAUX an auxiliary tablespace to the SYSTEM
tablespace created in your database.
Some database components that formerly created and used separate tablespaces
now occupy the SYSAUX tablespace.
Below table shows the components that uses the SYSAUX tablespace as their default tablespace and
the tablespace in which they were stored in earlier releases.
Component
Using SYSAUX
Tablespace in
Earlier Releases
Analytical Workspace
Object Table
SYSTEM
Enterprise Manager
Repository
...
Sunday, 30 March 2014
Saturday, 22 February 2014
Impdp does not created user/schema
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/********...
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...