Question: We have mistakenly create a datafile with special charatcers. Also we get error ORA-01516 when we try to rename it using the normal method: sql> alter database rename file 'old_file_name' to 'new_file_name'; The Solution 1. Bring the Tablespace offline. sql> alter tablespace [tablespace name] offline; 2. Copy/Move the File at the OS level specifying the correct file name. 3. If there is no special character then the solution is: sql> alter database rename file … [Read more...] about Oracle Database – How to Rename a Datafile with Special Characters Created by Mistake
Oracle 10g
How to Rename or Move Datafiles and Logfiles in Oracle Database
How to Rename or Move Datafiles and Logfiles In many situations, a datafile or logfile must be renamed inside Oracle. Whereas the contents of the file remain valid, you need to define a new physical name or location for it. For example: You want to move a database file to a different disk for performance or maintenance reasons. You have restored a datafile from backup, but the disk where it should normally be placed has crashed and you need to relocate it to a different disk. You have … [Read more...] about How to Rename or Move Datafiles and Logfiles in Oracle Database
How To Size UNDO Tablespace For Automatic Undo Management
Sizing an UNDO tablespace requires three pieces of data. (UR) UNDO_RETENTION in seconds (UPS) Number of undo data blocks generated per second (DBS) Overhead varies based on extent and file size (db_block_size) The undo space needed is calculated as: UndoSpace = UR * (UPS * DBS) Two of the pieces of information can be obtained from the instance configuration: UNDO_RETENTION and DB_BLOCK_SIZE. The third piece of the formula requires a query being run against the database. The maximum … [Read more...] about How To Size UNDO Tablespace For Automatic Undo Management
How to duplicate a Oracle Database to a previous Incarnation
NOTE: In 11.2, there is a new option to duplicate syntax, INCARNATION, which allows you to duplicate the database to a previous incarnation using the RMAN duplicate command. For example: run { allocate auxiliary channel ch1 type 'SBT_TAPE' parms'ENV=(NB_ORA_CLIENT=xxx.com,NB_ORA_SERV=xxx.com)'; allocate auxiliary channel ch2 type 'SBT_TAPE' parms'ENV=(NB_ORA_CLIENT=xxx.com,NB_ORA_SERV=xxx.com)'; allocate auxiliary channel ch3 type 'SBT_TAPE' … [Read more...] about How to duplicate a Oracle Database to a previous Incarnation
How to Modify an Existing ASM Spfile in a RAC Environment
This post provides an example on how to modify an existing Automatic Storage Management (ASM) spfile in a two-node Real Application Clusters (RAC) Environment. These steps can be applied to a multiple node Real Application Clusters (RAC) Environment. Applies To: Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.1.0.7 [Release 10.2 to 11.1] If a spfile is used in Clustered ASM environments, then the spfile must be on a shared Clusterware device, i.e. shared spfile. Note: … [Read more...] about How to Modify an Existing ASM Spfile in a RAC Environment
How to list all the named events set for a database
Oracle Internal Events, both named event (eg: ERRORSTACK) as numeric events (eg 10046) can be set at the session level. This post outlines how DataBase Administrators can verify which events have been set in a specific session or in the entire system. This can be achieved through the ORADEBUG functionality. The exact syntax to use depends on the Oracle version you are using. If you want to retrieve the events set in a specific session, then use the following oradebug commands to identify the … [Read more...] about How to list all the named events set for a database
Step By Step Guide To Create Physical Standby On Normal File System For ASM Primary using RMAN
This post covers the detailed steps for creating a physical standby database on a normal file system using RMAN for a primary database in ASM. Overview Here the Standby database can be created using the following 2 methods: Normal Restore and Recovery using RMAN. Creating a Duplicate database for Standby using RMAN. Method 1 Perform the backup of the ASM primary database ( Datafiles, Controlfile for standby and Archivelogs ) Make the backup pieces available on the Standby … [Read more...] about Step By Step Guide To Create Physical Standby On Normal File System For ASM Primary using RMAN
How to Create a Physical Standby from ASM Primary
The Setup This post offers step by step procedure to create physical standby from ASM primary. Database Name: chicago Primary db_unique_name: chicago standby db_unique_name: boston Primary Hostname: prim.example.com standby Hostname: stdby.example.com Steps 1. Enable Force Logging: SQL> ALTER DATABASE FORCE LOGGING; Database altered. 2. On the primary node, create a staging directory. For example: [oracle@prim ~]$ mkdir –p /home/oracle/stage 3. Create the same exact … [Read more...] about How to Create a Physical Standby from ASM Primary
How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies in Oracle Database
The RMAN CATALOG command is used to accomplish the following: Add backup pieces and image copies on disk to the RMAN repository. Record a datafile copy as a level 0 incremental backup in the RMAN repository, which enables you to use it as part of an incremental backup strategy. Record the existence of the last user-managed datafile copies made after the final shutdown. Restrictions and Usage Notes You must be connected to the target database, which must be mounted or open. If RMAN … [Read more...] about How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies in Oracle Database
RMAN: SET NEWNAME Command Using SQL
When you must restore the database the same directory structure is not always available. If you have a database containing 1000s of datafiles it can be very tedious to setup the set newname commands for all the datafiles. Using sqlplus we can extract the information we need into a file which can then be easily modified and executed as an RMAN script to complete the task. Oracle 11g New Command - 'SET NEWNAME FOR DATABASE' Starting from 11.2, we can use SET NEWNAME FOR DATABASE clause to … [Read more...] about RMAN: SET NEWNAME Command Using SQL