Move/rename datafiles in Oracle

Moving datafiles of a database: The datafiles reside under /home/oracle/ORACLE_HOME/database/OLD_LOCATION and have go to /home/oracle/ORACLE_HOME/database/NEW_LOCATION/.


SQL> select tablespace_name, substr(file_name,1,70) from dba_data_files;

TABLESPACE_NAME SUBSTR(FILE_NAME,1,70)

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

SYSTEM /home/oracle/ORACLE_HOME/database/OLD_LOCATION/system.dbf

UNDO /home/oracle/ORACLE_HOME/database/OLD_LOCATION/undo.dbf

DATA /home/oracle/ORACLE_HOME/database/OLD_LOCATION/data.dbf

SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo1.ora
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo2.ora
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo3.ora


SQL> select name from v$controlfile;


NAME
--------------------------------------------------------------------------------
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_1.ora
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_2.ora
/home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_3.ora


Now, as the files to be moved are known, the database can be shut down:

SQL> shutdown

The files can be copied to their destination:


$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/system.dbf /home/oracle/ORACLE_HOME/database/NEW_LOCATION/system.dbf
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/undo.dbf /home/oracle/ORACLE_HOME/database/NEW_LOCATION/undo.dbf
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/data.dbf /home/oracle/ORACLE_HOME/database/NEW_LOCATION/data.dbf
$
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo1.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo1.ora
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo2.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo2.ora
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo3.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo3.ora
$
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_1.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_1.ora
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_2.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_2.ora
$ cp /home/oracle/ORACLE_HOME/database/OLD_LOCATION/ctl_3.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_3.ora


The init.ora file is also copied because it references the control files. I name the copied file just init.ora because it is not in a standard place anymore and it will have to be named explicitely anyway when the database is started up.


$ cp /home/oracle/ORACLE_HOME/dbs/initOLD.ora /home/oracle/ORACLE_HOME/database/NEW_LOCATION/initNEW.ora


The new location for the control files must be written into the (copied) init.ora file:
/home/oracle/ORACLE_HOME/database/NEW_LOCATION/init.ora


control_files = (/home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_1.ora,
                        /home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_2.ora,
                       /home/oracle/ORACLE_HOME/database/NEW_LOCATION/ctl_3.ora)

$ sqlplus "/ as sysdba"

SQL> startup exclusive mount pfile=/home/oracle/ORACLE_HOME/database/NEW_LOCATION/init.ora


SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/system.dbf' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/system.dbf';
SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/undo.dbf' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/undo.dbf';
SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/data.dbf' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/data.dbf';


SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo1.ora' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo1.ora';
SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo2.ora' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo2.ora';
SQL> alter database rename file '/home/oracle/ORACLE_HOME/database/OLD_LOCATION/redo3.ora' to '/home/oracle/ORACLE_HOME/database/NEW_LOCATION/redo3.ora';

SQL> shutdown

SQL> startup pfile=/home/oracle/ORACLE_HOME/database/NEW_LOCATION/init.ora


Thanks :) 

Please share your really important  comments and views  for improvement......

Why is excessive redo generated during an Online/Hot Backup in Oracle

       There is not excessive redo generated, there is additional information

logged into the online redo log during a hot backup the first time a block is

modified in a tablespace that is in hot backup mode.

In hot backup mode only 2 things are different:

No 1->               The first time a block is changed in a datafile that is in hot backup mode,

                   the ENTIRE BLOCK is written to the redo log files, not just the changed bytes.

                   Normally only the changed bytes (a redo vector) is written. In hot backup mode,

                   the entire block is logged the FIRST TIME. This is because you can get into a

                   situation where the process copying the datafile and DBWR are working on the

                   same block simultaneously.

and 2nd is :->

         The datafile headers which contain the SCN of the last completed checkpoint

          are NOT updated while a file is in hot backup mode. This lets the recovery

         process understand what archive redo log files might be needed to fully recover

         this file.

  To limit the effect of this additional logging, you should ensure you only place one tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon as you have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible.

Please share your really important  comments and views  for improvement......   Thanks :)

Step by Step MMR Replication Setup

Oracle 8i  Replication Step By Step

1. PRE REPLICATION ENVOIRNMENT SETUP

BACKUP PRIMARY/SECONDARY SITE DB

1. Backup all the DB Links and synonyms for SECONDARY Environment.

2. Shutdown the Existing secondary Database Normal.

[ ] Shutdown

3. Take the cold backup from the existing SECONDARY Database Server (O/s Level).

4. Clean up SECONDARY Data base Environment.

(Remove all the SECONDARY DB from different mount point for refresh new DB from PRIMARY)

5. Cross Check the Mount points on the SECONDARY database server have sufficient space

6. Take the control file Backup on existing PRIMARY database As :

Alter database backup controlfile to trace;

(This will create a back script of the controlfile at the destination: user_dump_dest with some name with .trc extension, kindly find it with the latest timestamp and rename it as ‘create_control_file.sql’)

Note: This file has information about PRIMARY Environment.

7. Take all the DB Links and synonyms Backup.

8. List All the Data files.

Select name from dba_data_files OR select * from v$datafile

9. Shutdown the Existing PRIMARY Database Normal.

  

            [ ] Shutdown

10. Take a Cold Backup of PRIMARY Database.

2. REPLICATION ENVOIRNMENT SETUP

DB Refresh & Replication Setup Step By Step with:

1. Restore the backup to the new database server to the relevant mount points.

Copy the backup controlfile to the new server $ORACLE_HOME/dbs directory.

     Note: copy the redo.log files and multiplex the files in different mount points.

2.  Copy the pfile from the source Primary database server to  the location

Change the modification of path of:

- Controlfile location. (The control files must be multiplex)

- user_dump_dest.

- core_dump_dest.

- background_dump_dest.

3.   Login to new Primary database server as sys user :

$ sqlplus ‘ / as sysdba’

      Note : it will connect  to the oracle ideal instance.

4. Startup the database in nomount mode .

[ ] startup nomount  pfile=’pfile_path/pfilename’. ( it set the parameter that are set in the                 parameterfile)

I.e # cd $ORACLE_HOME/dbs/

5.  Create new Control file .

             Run the script ‘create_control_file.sql’ that is created in the step-8

[ ] @create_control_file.sql;

Note: Make changes according to SECONDARY Environment

I.e. change in path of Datafiles as below

6. Take on the DATABASE into mount state

Alter database mount;

7.  Take on the DATABASE into open state

Alter database open resetlogs;

8.  To check the database is in open state

[ ] select name,open_mode From v$database;

9. To check the database is refreshed properly

[ ] select *  from V$RECOVER_FILE;

10. Configure following Step in each Environment ( Primary & Secondary)

1.      Connect SECONDARY QA DB as sysdba as follows

$ ORACLE_SID=SECONDARY

$ export ORACLE_SID

$ sqlplus '/ as sysdba'

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Aug 10 07:16:17 2010

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

With the Partitioning option

Jserver Release 8.1.7.4.0 - Production

SQL>

2.      Create replication administrator / propagator / receiver

CREATE USER REPADMIN

             IDENTIFIED BY REPADMIN

             DEFAULT TABLESPACE USERS

             TEMPORARY TABLESPACE TEMP

             PROFILE DEFAULT

             ACCOUNT UNLOCK;

GRANT CONNECT TO REPADMIN;

  GRANT DBA TO REPADMIN;

  GRANT RESOURCE TO REPADMIN;

  GRANT SELECT_CATALOG_ROLE TO REPADMIN;

  ALTER USER REPADMIN DEFAULT ROLE ALL;

  -- 45 System Privileges for REPADMIN

  GRANT ALTER ANY CLUSTER TO REPADMIN;

  GRANT ALTER ANY INDEX TO REPADMIN;

  GRANT ALTER ANY PROCEDURE TO REPADMIN;

  GRANT ALTER ANY SEQUENCE TO REPADMIN;

  GRANT ALTER ANY SNAPSHOT TO REPADMIN;

  GRANT ALTER ANY TABLE TO REPADMIN;

  GRANT ALTER ANY TRIGGER TO REPADMIN;

  GRANT ALTER SESSION TO REPADMIN;

  GRANT COMMENT ANY TABLE TO REPADMIN;

3.      Grant privs to the propagator, to propagate changes to remote sites

               EXECUTE Dbms_Defer_Sys.Register_Propagator(username=>'REPADMIN');

4.      Grant privs to the receiver to apply deferred transactions

               GRANT EXECUTE ANY PROCEDURE TO repadmin;

5.      Authorize the administrator to administer replication groups and schemas

               EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Repgroup('REPADMIN');

               EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Schema (username => 'REPADMIN');

6.      Authorize the administrator to lock and comment tables

               GRANT LOCK ANY TABLE TO repadmin;

               GRANT COMMENT ANY TABLE TO repadmin;

7.       Connect to the replication administrator

               CONNECT repadmin/repadmin

8.      Create private db links for all repadmin users

Create database link "IAS.QA.SECONDARY.COM"

Using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=10.277.93.169)(Port=1521)))(CONNECT_DATA=(SID=SECONDARY)))';

Create database link " IAS.QA.PRIMARY.COM"

Using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=10.237.93.141)(Port=1521)))(CONNECT_DATA=(SID=SECONDARY)))';

9.      Schedule job to push transactions to QA master sites with appropriate intervals

EXECUTE Dbms_Defer_Sys.Schedule_Push(        -

        Destination   => ' IAS.QA.PRIMARY.COM ',       -

        Interval      => 'sysdate+1/24/60',  -

        Next_date     => sysdate+1/24/60,    -

        Stop_on_error => FALSE,              -

        Delay_seconds => 0,                  -

        Parallelism   => 1);

10.  Schedule job to push transactions to INT master sites with appropriate intervals

EXECUTE Dbms_Defer_Sys.Schedule_Push(        -

        Destination   => ' IAS.QA.SECONDARY.COM ',       -

        Interval      => 'sysdate+1/24/60',  -

        Next_date     => sysdate+1/24/60,    -

        Stop_on_error => FALSE,              -

        Delay_seconds => 0,                  -

        Parallelism   => 1);

11.  Schedule job to delete successfully replicated transactions

EXECUTE Dbms_Defer_Sys.Schedule_Purge(       -

        Next_date     => sysdate+1/24,       -

        Interval      => 'sysdate+1/24');

CONNECT repadmin/repadmin

12.  Create replication group for QA site

EXECUTE Dbms_Repcat.Create_Master_Repgroup('IREPLICATION');

13.  Add master destination sites

EXECUTE Dbms_Repcat.Add_Master_Database('REPLICATION', 'IAS.QA.RENNES.EQUANT.COM');

14.   Wait until IAS_REPLICATION appears in the DBA_REPSITES view\

SELECT * FROM dba_repsites WHERE gname = ‘REPLICATION';

15.  Register objects within the group

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'TEST'
oname => 'NEW_CHECKPOINT'
type => 'TABLE',
min_communication => TRUE);
END;

/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'TEST'
oname => 'NEW_CORRELATION'
type => 'TABLE',
min_communication => TRUE);
END;

1.2.2 Start/Stop Replication  (PRIMARY & SECONDARY)

Once replication support has been generated for all objects relevant objects replication can be started or stopped as follows:

-- Start Replication

EXECUTE Dbms_Repcat.Resume_Master_Activity(gname => 'REPLICATION');

-- Stop Replication

EXECUTE Dbms_Repcat.Suspend_Master_Activity(gname => 'REPLICATION');

1.2.3 Check  Replication Status  (PRIMARY & SECONDARY)

Select gname, status from dba_repgroup where gname ='REPLICATION' ;











Thanks :)







Please share your really important  comments and views  for improvement......