Create Database using DBCA silent mode

DBCA is great tool to create database using GUI , but sometimes when we don’t have access to GUI, we can still create database using DBCA silent mode creation where you need to pass silent option with dbca and some parameter values to create database. In following example we have used template General_Purpose ,   […]

Read More

RMAN maxpiecesize command

Use of RMAN maxpiecesize command To avoid large size of backupset we can set maxpiecesize of rman backup piece. I have taken following backup without setting any parameter : RMAN> backup database; Starting backup at 29-DEC-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=8 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) […]

Read More

ORA-16179 incremental changes to “log_archive_dest_10” not allowed with SPFILE

You might encounter ORA-16179 error while changing the archive destination. SQL> alter system set log_archive_dest_10=’d:\oracle\arc\dbtest\’; alter system set log_archive_dest_10=’d:\oracle\arc\dbtest\’ * ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-16179: incremental changes to “log_archive_dest_10” not allowed with SPFILE There is syntax error in above command . It should be like log_archive_dest_n [n is a integer […]

Read More

Database Cloning without RMAN on windows

CREATE INITSRDB.ORA PFILE IN D:\DATABASE\APP FOLDER *.db_name=’srdb2′ *.control_files=’D:\DATABASE\APP\SHRIRAM\REDOLOGS\srdb2\CONTROLFILE\O1_srdb2.CTL’ 2. CREATE DESTINATION FOLDER DATAFILES FOLDER ONLINE REDOLOGS FOLDER CONTROLFILE FOLDER 3. CREATE SERVICE USING ORADIM ORADIM -NEW -SID SRDB2 NOTE: CHECK WHETHER THE SERVICE IS RUNNING OR NOT. 4. COPY ALL THE DATAFILES, REDOLOGS TO DESTINATION FOLDER AS IT IS THE CASE OF DATABASE CLONING WITHOUT […]

Read More

Connecting PDB in Oracle 12c

To connect as sys or other elevated local users, use the same methods as below, but use ‘as sysdba‘ where necessary Method 1: alter session set container = pdbName; Method 2:For other methods, you need to know the service-name for the PDB: Get the service name for the PDB: SELECT name || ‘ ‘|| pdb […]

Read More

Recover a datafile from copy

Let’s recover if some datafile is unavailable. Let’s delete users01.dbf datafile which is in USERS tablespace. [oracle@node214 DB11G]$ pwd /u01/app/oracle/oradata/DB11G [oracle@node214 DB11G]$ [oracle@node214 DB11G]$ mv users01.dbf users01.dbf.bak #as backup file. [oracle@node214 DB11G]$ Let’s try to create a table name A in tablespace Users. SQL> create table a(id number) tablespace USERS * ERROR at line 1: […]

Read More

Recover controlfile

Controlfiles saved at least in 2 locations. If one of them is corrupted then you can copy from other one after shutdown database. In this case I will show you how to recover from autobackup via RMAN. So you need CONFIGURE CONTROLFILE AUTOBACKUP ON; in RMAN and you should have full backup database. It can […]

Read More

Difference between .bashrc and .bash_profile

Difference Between .bashrc and .bash_profile : We use .bashrc and .bash_profile frequently to set environment for Oracle. Here is the difference between both files ,which file is used when . location of .bashrc : /home/username/.bashrc location of .bash_profile : /home/oracle/.bash_profile .bashrc : This file is executed for non-login shells . .bash_profile : This file is executed […]

Read More

ESTIMATE in Oracle Expdp

The value for parameter ESTIMATE  is either BLOCKS (default) or STATISTICS. BLOCKS: The estimated space used is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes. It is  the least accurate because of: The table was created with a much bigger initial extent size than was needed for […]

Read More