Your production database has semiannual or annual purging programs which generate huge redo. Due to this requirement, your undo tablespace grows rapidly and occupies most of the space on the file system. The purging process is run only a few times a year. So would not like to keep the huge undo datafile in your database throughout the year. You don't want to buy additional disks unnecessarily. You have created an undo tablespace with datafiles as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid the … [Read more...] about How to Shrink the datafile of Undo Tablespace in Oracle Database
How to Drop Undo Tablespace in Oracle Database
This post provides the steps to drop an Undo tablespace. The Undo tablespace can be dropped if it is not the default Undo tablespace of the database and all the Undo segments in it are OFFLINE. 1. check whether the Undo tablespace to be dropped is the default Undo tablespace: sql> show parameter undo The tablespace name mentioned by the parameter UNDO_TABLESPACE is the current default Undo tablespace. 2. If the UNDO_TABLESPACE is set to the one to be dropped, create a new Undo … [Read more...] about How to Drop Undo Tablespace in Oracle Database
Unable to Drop Undo tablespace Since Undo Segment is in Needs Recovery
The Problem New Undo tablespace was created and a attempt is made to drop old undo tablespace. Dropping the old Undo tablespace give message: ORA-01548: active rollback segment Or Undo segment shows status as needs recovery The Solution The issue could happen if the datafile on which the undo segments reside is offline and the transaction cannot be rolled backed since the file is offline or this could also happen if there is any issue in the Undo segment itself. Check Undo … [Read more...] about Unable to Drop Undo tablespace Since Undo Segment is in Needs Recovery
How to Switch to a New Undo Tablespace in Oracle Database
This post explains the steps to switch to a new Undo tablespace. 1. Check your current default Undo tablespace: SQL> show parameter undo_tablespace 2. Determine the size of the datafile(s) for your current undo tablespace: sql> select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name=[tablespace_name from the above output]; For example: sql> select tablespace_name, file_id, file_name,round (bytes / (1024 * … [Read more...] about How to Switch to a New Undo Tablespace in Oracle Database
When to Use Startup/Shutdown Database and Alter Database Command in Oracle 12c
Before 12c there are only few commands available for startup and shutdown but if we follow that we may end up starting and stopping the PDBs and CDB as well. shutdown normal shutdown immediate shutdown abort startup startup nomount startup mount If we use one of these traditional shutdown methods at the container database (CDB) layer, we will bring down the CDB and the associated PDBs under it. This is something to be aware of when looking to reboot a container database. Let's take a … [Read more...] about When to Use Startup/Shutdown Database and Alter Database Command in Oracle 12c
How to Startup/Shutdown PDB’s in Oracle Database 12c
Traditional startup/shutdown methods prior to 12c Traditional startup/shutdown command prior to 12c as as shown below. Shutdown Command SHUTDOWN NORMAL SHUTDOWN IMMEDIATE SHUTDOWN ABORT Startup Command STARTUP STARTUP NOMOUNT STARTUP MOUNT What happen when we execute shutdown immediate at CDB? If we use one of these traditional shutdown methods at the container database (CDB) layer, we will bring down the CDB and the associated PDBs under it. $ sqlplus / as sysdba SQL*Plus: … [Read more...] about How to Startup/Shutdown PDB’s in Oracle Database 12c
How to Remove/ Disable Bash shell Command History on Linux
This post will help you to remove/ disable Bash shell commands history on Linux. 1. Disable history for a current shell. # set +o history 2. Clean command history. # history -c 3. Permanently disable bash history. # echo 'set +o history' >> ~/.bashrc Next time when user login to the shell it will not store any commands to a history file .bash_history. To apply this settings immediately for the current shell session execute the .bashrc file: # . ~/.bashrc 4. Disable a … [Read more...] about How to Remove/ Disable Bash shell Command History on Linux
Understanding Oracle Database Recyclebin Features and How to Disable it
About Recyclebin In order to have FLASHBACK DROP functionality a recyclebin is provided to every oracle user. SQL> desc recyclebin Name Null? Type ----------------------------------------- -------- ------------ OBJECT_NAME NOT NULL VARCHAR2(30) ORIGINAL_NAME VARCHAR2(32) OPERATION VARCHAR2(9) TYPE … [Read more...] about Understanding Oracle Database Recyclebin Features and How to Disable it
Oracle Database: How to catalog tape backup pieces
Starting with 10g, it's possible to use the RMAN CATALOG command to add backuppieces stored in the disk to the RMAN repository. You may need to catalog backup pieces in the following situations: You copy or move a backup piece with an operating system utility and want it to be usable by RMAN. The RMAN metadata for the backup piece was removed, but the backup piece still exists. This situation can occur if you ran the DELETE command on a backup piece that was only temporarily … [Read more...] about Oracle Database: How to catalog tape backup pieces
Understanding Flashback Table Feature in Oracle Database
Flashback Query concept was introduced in Oracle9i. But it can't flash back DDL operations such as dropping a table. Flashback Table feature in Oracle 10g makes the revival of a dropped table as easy as the execution of few statements. Let's see how Flashback Table Works. Let's first create a test table named "TESTFLASH" and insert some data into it. SQL> Create table TESTFLASH (id number); SQL> Insert into TESTFLASH values (1); SQL> Insert into TESTFLASH values (2); SQL> Insert into … [Read more...] about Understanding Flashback Table Feature in Oracle Database