Posts

Showing posts from 2015

REMAP_DATA Parameter of Oracle Data Pump Export (expdp)/ Import (impdp) - New Feature Oracle Database 11g

Image
   REMAP_DATA Default: There is no default Purpose The REMAP_DATA parameter allows you to specify a remap function that takes as a source the original value of the designated column and returns a remapped value that will replace the original value in the dump file. A common use for this option is to mask data when moving from a production system to a test system. For example, a column of sensitive customer data such as credit card numbers could be replaced with numbers generated by a REMAP_DATA function. This would allow the data to retain its essential formatting and processing characteristics without exposing private data to unauthorized personnel. The same function can be applied to multiple columns being dumped. This is useful when you want to guarantee consistency in remapping both the child and parent column in a referential constraint. Syntax and Description REMAP_DATA=[ schema .] tablename . column_name :[ schema .] pkg . function The description of

Table Compression Enhancement in Oracle Database 11g R2 - With New Compression Names

Image
Table Compression: Overview Oracle Database supports three methods of table compression: • Basic table compression • OLTP table compression Oracle Corporation recommends to compress all data to reduce storage costs. The Oracle database can use table compression to eliminate duplicate values in a data block. For tables with highly redundant data, compression saves disk space and reduces memory use in the database buffer cache. Table compression is transparent to database applications. • The table_compression clause is valid only for heap-organized tables. The COMPRESS keyword enables table compression. The NOCOMPRESS keyword disables table compression. NOCOMPRESS is the default. • With basic compression, the Oracle database compresses data at the time of performing bulk load using operations such as direct loads or CREATE TABLE AS SELECT . • With COMPRESS FOR OLTP , the Oracle database compresses data during all DML operations on the table. 

Creating Tables Without Segments - Oracle 11g Release 2 New Feature

Image
Understand Deferred Segment Creation Beginning with Oracle Database 11 g Release 2, when you create heap-organized tables in a locally managed tablespace, the database defers table segment creation until the first row is inserted. In addition, segment creation is deferred for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table. Note: In Release 11.2.0.1, deferred segment creation is not supported for partitioned tables. This restriction is removed in release 11.2.0.2 and later. The advantages of this space allocation method are the following: It saves a significant amount of disk space in applications that create hundreds or thousands of tables upon installation, many of which might never be populated. It reduces application installation time. There is a small performance penalty when the first row is inserted, because the new segment must be created at that

Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)

Image
Purpose of RMAN TSPITR Recovery Manager (RMAN) TSPITR enables quick recovery of one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database. RMAN TSPITR is most useful for the following situations: To recover a logical database to a point different from the rest of the physical database, when multiple logical databases exist in separate tablespaces of one physical database. For example, you maintain logical databases in the orders and personnel tablespaces. An incorrect batch job or data manipulation language (DML) statement corrupts the data in only one tablespace. To recover data lost after data definition language (DDL) operations that change the structure of tables. You cannot use Flashback Table to rewind a table to before the point of a structural change such as a truncate table operation. To recover a table after it has been dropped with the PURGE option. To recover from the logi

Rman Auxiliary Duplicate,Clone,Sandbox Database Without Any Backup

Image
RMAN has the ability to duplicate, or clone, a database from an active database. I t is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure.

Step by Step - How to Create And Configure RMAN Recovery Catalog Database

Image
Purpose of the Recovery Catalog A recovery catalog is a database schema used by RMAN to store metadata about one or more Oracle databases. Typically, you store the catalog in a dedicated database. A recovery catalog provides the following benefits: A recovery catalog creates redundancy for the RMAN repository stored in the control file of each target database . The recovery catalog serves as a secondary metadata repository. If the target control file and all backups are lost, then the RMAN metadata still exists in the recovery catalog. A recovery catalog centralizes metadata for all your target databases. Storing the metadata in a single place makes reporting and administration tasks easier to perform. A recovery catalog can store metadata history much longer than the control file. This capability is useful if you must do a recovery that goes further back in time than the history in the control file. The added complexity of managing a recovery catal

Step by Step - How to turn on Password Protection for Oracle Database Listener Control (LSNRCTL) Command Line Utility

Image
Step 1:- open administrator cmd Step 2:- C:\lsnrctl Step 3:- lsnrctl> change_password Step 4:- lsnrctl> old password : (keep this blank) Step 5:- lsnrctl> new password : (set your password here) Step 6:- lsnrctl> retype new password : (retype your password here) Step 7:- lsnrctl> save_config  Step 8:- lsnrctl> exit Step 9:- Open listener.ora file from ORACLE_HOME\NETWORK\ADMIN\ Step 10:- Add this line - LOCAL_OS_AUTHENTICATION_LISTENER = OFF step 11:- lsnrctl> stop step 12:- lsnrctl> start <-- this is required to reload the listener.ora file step 13:- lsnrctl> exit Step 14:- from administrator cmd open lsnrctl Step 15:- lsnrctl>stop The above command will give an error like Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=B-3)(PORT=1521))) TNS-01169: The listener has not recognized the password Step 16:- LSNRCTL> set password unisoft <--- your password set above. Step 17:- lsnrctl> stop <--- n

Step by Step - Creating RMAN Virtual Private Catalog - Oracle Database 11g New Feature

Image
About Virtual Private Catalogs By default, all of the users of an RMAN recovery catalog have full privileges to insert, update, and delete any metadata in the catalog. For example, if the administrators of two unrelated databases share the same recovery catalog, each administrator could, whether inadvertently or maliciously, destroy catalog data for the other's database. In many enterprises, this situation is tolerated because the same people manage many different databases and also manage the recovery catalog. But in other enterprises where clear separation of duty exists between administrators of various databases, as well as between the DBA and the administrator of the recovery catalog, you may desire to restrict each database administrator to modify only backup metadata belonging to those databases that they are responsible for, while still keeping the benefits of a single, centrally-managed, RMAN recovery catalog. This goal can be achieved by implementing v

Oracle Database 11g New Feature - Backup-Based Duplication Without a Target Connection or Recovery Catalog Connection

Image
Oracle 11g New Feature - Duplicating a Oracle Database Using 11g RMAN Backup Location Option but Without Connection to Target or  Recovery Catalog We have Target Database name as ORCL And we are creating a duplicate database using RMAN Auxiliary Instance with name as AUXDB by using the backup location but without making connection to target database and recovery catalog database while the duplication process is running. Note :- This practical need same directory structures and it is performed on same server. Just the database name is different so we are converting the path with respect to dbname only.  Image Source - docs.oracle.com Your Target Database can be in any log mode. Take RMAN Backup based on your Database Log Mode. A) Archivelog Mode - Take a Whole Hot Backup C:\set ORACLE_SID=orcl C:\rman target / RMAN> backup database plus archivelog; B) Noarchivelog Mode - Take a Whole Cold Backup C:\set ORACLE_SID=orcl C:\rman target / RM

Oracle 11g New Feature - Duplicating a Database Using 11g RMAN From Active Database Option without ANY BACKUP

Image
Oracle 11g New Feature - Duplicating a Database Using 11g RMAN From Active Database Option without ANY BACKUP We have Target Database name as ORCL And we are creating a Auxiliary database with name as AUXDB    Image Source - docs.oracle.com Check log_mode of Target Database. Target must be in archivelog mode. Otherwise put the database in archivelog mode. shutdown immediate startup mount alter database archivelog; alter database open; Create Pfile from Target Database Spfile C:\set ORACLE_SID=orcl C:\sqlplus / as sysdba SQL> create pfile='C:\app\Administrator\product\11.2.0\dbhome_1\database\initauxdb.ora' from spfile; Open pfile with notepad and replace (Ctrl + H) all orcl to auxdb except for parameter local_listener. SQL> host notepad C:\app\Administrator\product\11.2.0\dbhome_1\database\initauxdb.ora On Windows Platform You need to Create a new Instance for auxdb which also creates a password file for the same. SQL> host

Perform Incomplete Recovery Until Sequence # and Thread # Using Rman in 10g and 11g

Image
www.unisoftindia.org First Database in Archivelog Mode. Take Rman Backup. Rman > Backup Database; Switch Log files. Delete Some Archive logs. I am doing complete recovery that time i have got an error. Show error message. RMAN-06025: no backup of log thread 1 seq 16 lowscn 834655 found to restore RMAN-06025: no backup of log thread 1 seq 15 lowscn 834304 found to restore Some Archivelog is missing that time incomplete recovery is done. Go to Rman Window and run the following command. run { Shutdown Immediate Startup Mount Set Until Sequence 15 Thread 1; Restore Database; Recover Database; Alter Database Open Resetlogs; } Database only Restore sequence number 15 and after 15 and 16 sequence no all Archivelogs are available but these Archivelogs are not recoverable these all data are lost.  Incomplete recovery is very Dangerous.

Perform Time Base Incomplete recovery Using Rman in Oracle 10g & 11g

Image
www.unisoftindia.org Incomplete Recovery Using Rman (Time Base ) 1)     First database in archivelog mode. 2)     Tack a Rman Backup (Rman> backup Database; ) command 3)     Take a current Timestamp first time 4)     select CURRENT_TIMESTAMP from dual; 5)     Create parag to parag9 tables and switch log file after 3 tables. 6)     Take a current scn no Second time 7)     select CURRENT_TIMESTAMP from dual; 8)     Drop table parag5. 9)     Create parag10 to parag13 tables and switch log file after 3 tables. 10)   After some time I want to these drop parag5 table back this is the important table; 11)   These table backup and archivelog is not available that time incomplete recovery is doing. 12)   Go to rman window rman> 13)   Run these command 14)   RMAN> 15)   run { Shutdown Immediate Startup Mount Set Until Time= ’02-Mar-2015 02:23:15’; Restore Database; Recover Database;

Perform Scn Base Incomplete recovery Using Rman in Oracle 10g & 11g

Image
www.unisoftindia.org Incomplete Recovery Using Rman ( Scn No ) 1.       First database in Archivelog mode. 2.       Tack a Rman Backup (Rman> backup Database; ) command 3.       Take a current scn no first time 4.       select timestamp_TO_SCN(CURRENT_TIMESTAMP) from dual; 5.       Create parag to parag9 tables and switch log file after 3 tables. 6.       Take a current scn no Second time 7.       select timestamp_TO_SCN(CURRENT_TIMESTAMP) from dual; 8.       Drop table parag5. 9.       Create parag10 to parag13 tables and switch log file after 3 tables. 10.   After some time I want to these drop parag5 table back this is the important table; 11.   These table backup and archivelog is not available that time incomplete recovery is doing. 12.   Go to rman window rman> 13.   Run these command 14.   RMAN> 15.   run { Shutdown Immediate Startup Mount Set Until Scn < Scn_No >; Restore D

Step By Step Creating Oracle Duplicate Database 10g on Same Server Using RMAN Auxiliary Instance and RMAN Backup

Image
Duplicating Target Database Using RMAN Auxiliary Instance on Oracle 10g                                                   or Creating Duplicate Database on the Same Server Using RMAN Backup. We have Target Database name as ORCL And we are creating a Auxiliary database with name as AUXDB  Fig :- Backup-Based Duplication with a Target Connection Image Source - docs.oracle.com 1.Take Whole Hot/Cold Backup of Target Database RMAN> backup database plus archivelog; 2.Create Pfile from Target Database Spfile SQL> create pfile='C:\ORACLE\PRODUCT\10.2.0\DB_1\Database\initauxdb.ora' from spfile; 3.Open pfile with notepad and replace (Ctrl + H) all orcl to auxdb SQL> host notepad C:\ORACLE\PRODUCT\10.2.0\DB_1\Database\initauxdb.ora 4.On Windows Platform You need to Create a new Instance for auxdb which also creates an password file for the same. SQL> host oradim -new -sid auxdb -syspwd admin 5.Create All the folders in Oracle Folde