Monday, February 28, 2011

ORACLE DBA: hot backup cloninig

ORACLE DBA: hot backup cloninig: "Verfication Steps============1)Find the name of the source database SQL> select name from v$database; NAME---------IRFAN Now IRFAN is so..."
Posted by at No comments:

Wednesday, December 8, 2010

hot backup cloninig

Verfication Steps
============
1)Find the name of the source database

SQL> select name from v$database;

NAME
---------
IRFAN

Now IRFAN is source database and we will make a copy of this database to (ASIF) which will be target database
SOURCE=IRFAN
TARGET or DESTINATION=ASIF


2)Make sure that your source database is in archivelog mode to take the hot backup.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     64
Next log sequence to archive   66
Current log sequence           66



3) Find the location of the datafiles. You need to copy the datafiles from source to destination
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oracle/oradata/irfan/system01.dbf
/u02/oracle/oradata/irfan/undotbs01.dbf
/u02/oracle/oradata/irfan/sysaux01.dbf
/u02/oracle/oradata/irfan/users01.dbf
/u02/oracle/oradata/irfan/catalog01.dbf
/u02/oradata/IRFAN/datafile/o1_mf_test_6gyjllt3_.dbf
/u02/oracle/oradata/irfan/ahmed.dbf
/u02/oracle/oradata/irfan/far01.dbf
/u02/oracle/oradata/irfan/undo01.dbf
/u02/oracle/oradata/irfan/asif1.dbf
/u02/oracle/oradata/irfan/tech.dbf
/u02/oracle/oradata/irfan/fff.dbf


You can find all the datafiles in /u02/oracle/oradata/irfan except one which in /u02/oradata/IRFAN/datafile/
source datafiles location =
1.) /u02/oracle/oradata/irfan/
2.) /u02/oradata/IRFAN/datafile/

Target or Destination datafiles location =  /u01/oradata/ASIF
Actual Steps:
=========
1.)Put the database in begin backup mode

SQL>alter database begin backup;

2.) use os cp command to copy all the datafiles of source to target location. Give below command at sql prompt
SQL>host
$cp -pr /u02/oracle/oradata/irfan/*  /u01/oradata/ASIF/ &
$cp -pr /u02/oradata/IRFAN/datafile/*   /u01/oradata/ASIF/ &

3) Give exit and at sql prompt give end backup end
SQL>alter database end backup;

4) Take the trace of the controlfile of source database

alter database backup controlfile to trace as '/u01/oradata/asif.ctrl';

5) cp parameter file of source to target
SQL> create pfile from spfile;

File created.

SQL> host

$ cd $ORACLE_HOME/dbs

$ cp initirfan.ora initasif.ora
$ vi initasif.ora
use below command to search and global replace

:%s,/u02/oracle/oradata/irfan,/u01/oradata/ASIF,g

:%s,/u01/app/oracle/admin/irfan,/u01/app/oracle/admin/asif,g

:%s,irfan,asif,g

:wq! to save

$ mkdir -p /u01/app/oracle/admin/asif/
$ cd /u01/app/oracle/admin/asif/
mkdir cdump bdump udump adump
$ ls
adump  bdump  cdump  udump

$ cat /u01/oradata/asif.ctrl

copy the output to notepad

delete all the lines before resetlogs

replace /u02/oracle/oradata/irfan /u01/oradata/ASIF
replace /u02/oradata/IRFAN/datafile /oradata/ASIF

delete -- STANDBY LOGFILE line and empty line after that
and delete all -- lines

for line
CREATE CONTROLFILE REUSE DATABASE "IRFAN" RESETLOGS  ARCHIVELOG
change as below
CREATE CONTROLFILE SET DATABASE "asif" RESETLOGS  ARCHIVELOG
and save the file  back to linux


The sample should look like below

vi /u01/oradata/asif.ctrl
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "asif" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/ASIF/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/oradata/ASIF/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/oradata/ASIF/redo03.log'  SIZE 50M
DATAFILE
  '/u01/oradata/ASIF/system01.dbf',
  '/u01/oradata/ASIF/undotbs01.dbf',
  '/u01/oradata/ASIF/sysaux01.dbf',
  '/u01/oradata/ASIF/users01.dbf',
  '/u01/oradata/ASIF/catalog01.dbf',
  /u01/oradata/ASIF/o1_mf_test_6gyjllt3_.dbf',
  '/u01/oradata/ASIF/ahmed.dbf',
  '/u01/oradata/ASIF/far01.dbf',
  '/u01/oradata/ASIF/undo01.dbf',
  '/u01/oradata/ASIF/asif1.dbf',
  '/u01/oradata/ASIF/fff.dbf'
CHARACTER SET WE8ISO8859P1
;

$ export ORACLE_SID=asif
$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 3 11:20:00 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.
SQL> @/u01/oradata/asif.ctrl
ORACLE instance started.

Total System Global Area  281018368 bytes
Fixed Size                  1218968 bytes
Variable Size             100664936 bytes
Database Buffers          176160768 bytes
Redo Buffers                2973696 bytes

Control file created.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 868332 generated at 12/03/2010 10:42:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ASIF/archivelog/2010_12_03/o1_mf_1_66_%u_.ar
c
ORA-00280: change 868332 for thread 1 is in sequence #66


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oradata/ASIF/system01.dbf'


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 868332 generated at 12/03/2010 10:42:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ASIF/archivelog/2010_12_03/o1_mf_1_66_%u_.ar
c
ORA-00280: change 868332 for thread 1 is in sequence #66


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/IRFAN/archivelog/2010_12_03/o1_mf_1_66_6hk1lkv8_.arc
ORA-00279: change 870189 generated at 12/03/2010 11:25:29 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ASIF/archivelog/2010_12_03/o1_mf_1_67_%u_.ar
c
ORA-00280: change 870189 for thread 1 is in sequence #67
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/IRFAN/archivelog/2010_12_03/o1_mf_1_66_6hk1
lkv8_.arc' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/ASIF/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;  2

Tablespace altered.


=================

on source check for archive destination

first try to find

show parameter archi
or try
show parameter reco

alter system switch archivelog;








Posted by at No comments:
Subscribe to: Posts (Atom)