How to Put Oracle database to Archivelog Mode

You can check log_mode using following command 

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG
 

 

Step01

If not shutdown database,  you have to shutdown the database

                      SQL> shutdown immediate ;

Step02

stratup database mount stage

                   SQL> startup mount;

Step03

Alter database archivelog mode

                SQL> alter database archivelog;

Step04

Database open

                SQL>alter database open;

check again log_mode ,

SQL> select log_mode from v$database;

LOG_MODE
------------

ARCHIVELOG

RMAN Duplicate to New Database

I have installed two virtual machine on my PC (Red Hat 6) ,and installed oracle (11.2.0) binary both machine and created database on one machine name DB11G, Now I want to duplicate DB11G database as DB11G2 to my 2nd machine .

Step 1: I have created database backup using RMAN on DB11G database ,

             rman target=/

      RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

      RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Step 2: Login to 2nd machine and created the oracle password file in dbs folder

       orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwDB11G2 password=password entries=10

Step 3:Also added to tns entry in "tnsnames.ora" file in the 

              "$ORACLE_HOME/network/admin" directory

              

             DB11G=(DESCRIPTION =

              (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =1stMachine-IP )(PORT = 1521))

              )

              (CONNECT_DATA =(ORACLE_SID = DB11G)

              )

             )

Step 4: Also created the PFILE on 2nd machine "initDB11G2.ora" 

          DB_NAME=DB11G2

          # Convert file names to allow for different directory structure if necessary.

 #DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DB11G/','/u01/app/oracle/oradata/DB11G2/'

#LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DB11G/','/u02/app/oracle/oradata/DB11G2/'

Step 5: Create any directories necessary for start the duplicate database.

           mkdir -p /u01/app/oracle/oradata/DB11G

      mkdir -p /u01/app/oracle/fast_recovery_area/DB11G

      mkdir -p /u01/app/oracle/admin/DB11G/adump

Step 6: Copy all the backup files from 1st machine to 2nd machine.

          scp -r /u01/app/oracle/fast_recovery_area/DB11G/archivelog                oracle@2ndMachine-IP:/u01/app/oracle/fast_recovery_area/DB11G

    scp -r /u01/app/oracle/fast_recovery_area/DB11G/backupset 

oracle@2ndMachine-IP:/u01/app/oracle/fast_recovery_area/DB11G

    scp -r /u01/app/oracle/fast_recovery_area/DB11G/autobackup

oracle@2ndMachine-IP:/u01/app/oracle/fast_recovery_area/DB11G

Setp 7:Log in to 2nd machine 

          export ORACLE_SID=DB11G2

          #this is which you have installed oracle version 

          *.compatible='11.2.0.4.0' 

       

          sqlplus / as sysdba

     SQL> startup nomount

     SQL> exit

Step 8:Login RMAN as auxiliary database

          rman auxiliary /

      RMAN>

Step 9: Enter duplicate command as bellow 

      

      RMAN>DUPLICATE DATABASE DB11G TO DB11G2 BACKUP LOCATION '/u01/app/oracle/fast_recovery_area/DB11G' nofilenamecheck;

How to Put Oracle database to Archivelog Mode

Step01
If not shutdown database,  you have to shutdown the database
                      SQL> shutdown immediate ;

Step02
stratup database mount stage

                   SQL> startup mount;

Step03

Alter database archivelog mode

                SQL> alter database archivelog;

Step04

Database open

                SQL>alter database open;

RMAN Backup Re installation with duplicate database

Step -1
                          login to sqlplus
Step-2
                   shutdown immediate;
Step-3
                   startup mount exclusive restrict;
Setp-4
                   drop database;
Step-5
 Then exit from sqlplus

Step-6
                  Again log into sqlplus and enter below command
                  startup nomount pfile='$ORACLE_HOME/dbs/initTEST.ora';

Note:  Your oracle home path


Step-7
         Then exit from sqlplus and log in to RMAN using below command
         
         rman auxiliary /

Step-8
            To start databse duplication enter below command , before that copy rman backup files to the correct path and give permission to oracle.

         duplicate database to 'NEWDBNAME' backup location '/backup location path /' nofilenamecheck;


Then start the rman re installation .

Oracle Index Types

There are many index types within Oracle:

•B*Tree Indexes

- These are the common indexes in Oracle. They are simular construct to a binary tree,they provide fast access by key, to an individual row or range of rows, normally requiring very fewreads to find the correct row. The B*Tree index has several subtypes

oIndex Organised Tables

- A table stored in a B*Tree structure (see Oracle Index organisedTables.doc)

oB*Tree Cluster Indexes

- They are used to index the cluster keys (see Oracle Cluster Indexes.doc)

oReverse Key Indexes

- The bytes in the key are reversed.This is used to stop sequencal keys being on the same block like 999001, 999002, 999003 would be reversed to 100999, 200999,300999 thus these would be located on different blocks.

oDescending Indexes

- They allow data to be sorted from big to small (decending) instead of small to big (ascending).

•Bitmap Indexes

- With a bitmap index , a single index entry uses a bitmap to point to many rowssimultaneously, they are used with low data that is mostly read-only. They should not be used withOLTP systems.

•Function Based Indexes

- These are B*Tree or bitmap indexes that store the computed result of afunction on a row(s) - not the column data itself.

•Application Domain Indexes

- These are indexes you build and store yuorself, either in Oracle or outside of Oracle

•interMedia Text Indexes

- This is a specialised index built into Oracle to allow for keyword searchingof large bodies of text.

When to use a

B*Tree Index

In general B*Tree index would be placed on columns that were frequently used in the predicate of a query andexpect some small fraction of the data from the table to be returned. its is purely a function on how large of a percentage of the table you will need to access via the index and how hte data happens to be laid out. If you canuse the index to answer the question accessing a large percentage of the rows makes sense, since you areavoiding the extra scattered I/O to read the table. If you use the index to access the table you will need to ensureyou are processing a small percentage of the total table.

Bitmap Indexes

Bitmap indexes are structures that store pointers to many rows with a single index key entry. In a bitmap indexthere will be a very small number of index entries, each of which point to many rows. Bitmap indexes are bestused on low cardinality data, this is were the number of distinct ietms in the set of rows divided by the number of rows is a small number for example a gender column may have MF, F and NULL. If you had a table with20000 rows you would find that 3/20000 = 0.00015, this would be an ideal candidate for a bitmap index.Remember also that a single bitmap entry key points to many rows. If a session modifies the index then all of the rows that the index points to are effectively locked. Oracle cannot lock an individual bit in a bitmap indexentry; it locks the entire bitmap, this will seriously inhibit concurrency as each update will appear to lock potentially hundreds of rows preventing their bitmap columns from being concurrently updated.An Oracle bitmap index would look like




Function Based Index


Function-Based indexes give the ability to index computed columns and use theses indexes in a query, it allowsyou to have case insensitive searchs or sorts, search complex equations and to extends the SQL language byimplementing your own functions and operators and then searching on them. The main reason to use them are:

•  They are easy to implement and provide immediate value

• They can be used to speed up existing applications without changing the application code or queries.There is some work that needs to be done before you can use Function-based indexes

• You must have the privilege QUERY REWRITE to create function-based indexes on tables in your ownschema

• You must have the privilege GLOBAL QUERY REWRITE to create function-based indexes on tablesin your own schema

• Function-based indexes only use the cost based optimiser (CBO) and never the rule base optimiser (RBO)

• Use SUBSTR to constrain return values from user written functions that return VARCHAR2 or RAWtypes. Optionally hide the SUBSTR in a view (preferred).

• For the optimiser to use function-based indexes, the following session or system variables must be set:

o QUERY_REWRITE_ENABLED=TRUE (means allow optimiser to rewrite the query to usefunction-based index)

oQUERY_REWRITE_INTEGRITY=TRUSTED (means that the optimiser should trust the codeof the programmer)You can enable the above with the ALTER SESSION or at the system level via ALTER SYSTEM or by settingthem in in the init.ora file.Function-based indexes will affect the performance of inserts and updates, if you insert or update frequently thismay not be an option for you.

Application Domain Indexes

Application domain indexing gives the user the ability to supply new indexing technology. Most people willnever make use of it this particular API to build a new index type. The inetMedia set of functionality,implemented using the Application Domain indexing feature will provide indexing on text, XML documentsand images.

Hint One





if you believe that a child table is getting locked via an un-indexed foreign key and you would like to prove ityou can issue:alter table disable table lock; Now, any update or delete to the parent table that would cause the table lock will receive: ERROR at line1:ORA-00069: cannot accquire lock -- table locks disabled for

Hint Two

You will only see index blocks on the FREELIST when the index block is totally empty, only then can the block be reused. This is different to table blocks as table blocks can be on the FREELIST if they contain data

Impdp over network using Network_link

Applies to:




Oracle Version: 10G and later



Advantages:



This is an alternate and fast approach for importing database from one database to another.

This process can skip following activities:

1. Creating database directory for backup on source database.

2. Create dump file on source database.

3. Copy dump file at destination server.

4. Creating database directory for backup restoration on Destination database.

5. Expdp command to take database backup.



Solution:



Suppose we have two databases db1 and db2. We need to restore test_schema1 backup form db1 to db2.



Prerequisites:



1. Source and Destination database must be connected through the network.

2. Both databases must be in open mode.

3. Source database (db1) tns detail must exist at destination (db2).



Following are the steps:

1. Crate a public database link at db2 connecting to db1 using system user.



SQL> create public database link db1_link connect to system identified by sys using 'db1';



Database link created.



Crosscheck the db link is working.



SQL> select * from dual@ db1_link;



D

-

X



You may face following error during impdp, if db link is not public.

ORA-39001: invalid argument value

ORA-39200: Link name "fmstest" is invalid.

ORA-02019: connection description for remote database not found



2. Import using Impdp command at db2 (Destination)



[oracle@~]$ export ORACLE_SID=test01

[oracle@~]$ impdp system/sys schemas= test_schema1 network_link= db1_link



Import: Release 11.2.0.2.0 - Production on Fri Jun 17 07:00:57 2011



Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.



Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** schemas= test_schema1 network_link= db1_link

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 4 MB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

--------------------------------------------------------------------------

-------------------------------------------------------------------------

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE

Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed at 07:08:45



Detail of Impdp parameters:



network_link: This parameter will have value of db link created.



Schemas: Name of the schema need to import from source. If user doesn’t exists it will create it, otherwise it import in to existing user with one error.



Here, directory and dumpfile parameters are not required. Other parameters are same as it used to be.

DROP DATABASE

You can use to drop database DBCA if u create database using DBACA.

 Otherwise you have following one of following method

 ****************Method One**********************************
sqlplus / as sysdba
shutdown immediate;
startup mount exclusive;
Alter system enable restricted session;
drop database ;

****************Method Two**********************************
sqlplus / as sysdba
shutdown immediate;
STARTUP NOMOUNT RESTICT;
ALTER DATABASE MOUNT;
DROP DATABASE;