Saturday, July 14, 2012

Copying/Cloning Database Oracle

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue:

sql> alter database backup controlfile to trace;

This will put the create database syntax in the trace file directory.

The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

Trong thư mục user_dump_dest file kinhdoanh_ora_11774.trc vừa dump ra
/u01/app/oracle/admin/kinhdoanh/udump

[oracle@c4is-billing-s kinhdoanh]$ls
adump   bdump   cdump   dpdump  pfile   udump

- Tìm và thay thế tất cả kinhdoanh thành utility
- Sửa lại câu
CREATE CONTROLFILE REUSE DATABASE "KINHDOAN" NORESETLOGS  ARCHIVELOG
thành 
CREATE CONTROLFILE SET DATABASE "UTILITY" RESETLOGS  NOARCHIVELOG

VD: File chuẩn db_create_controlfile.sql

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "UTILITY" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/utility/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/utility/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/utility/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u02/oradata/utility/system01.dbf',
  '/u02/oradata/utility/undotbs01.dbf',
  '/u02/oradata/utility/sysaux01.dbf',
  '/u02/oradata/utility/users01.dbf',
  '/u02/oradata/utility/data01.dbf',
  '/u02/oradata/utility/data02.dbf',
  '/u02/oradata/utility/data03.dbf',
  '/u02/oradata/utility/data04.dbf',
  '/u02/oradata/utility/data05.dbf',
  '/u02/oradata/utility/data06.dbf',
  '/u02/oradata/utility/data07.dbf',
  '/u02/oradata/utility/data08.dbf',
  '/u02/oradata/utility/data09.dbf',
  '/u02/oradata/utility/data10.dbf',
  '/u02/oradata/utility/data11.dbf',
  '/u02/oradata/utility/data12.dbf',
  '/u02/oradata/utility/data13.dbf',
  '/u02/oradata/utility/data14.dbf',
  '/u02/oradata/utility/data15.dbf',
  '/u02/oradata/utility/data_ivr_01.dbf',
  '/u02/oradata/utility/data_ivr_02.dbf',
  '/u02/oradata/utility/data_ivr_03.dbf',
  '/u02/oradata/utility/data_ivr_04.dbf',
  '/u02/oradata/utility/data16.dbf',
  '/u02/oradata/utility/data17.dbf',
  '/u02/oradata/utility/data18.dbf',
  '/u02/oradata/utility/data19.dbf',
  '/u02/oradata/utility/data20.dbf',
  '/u02/oradata/utility/data21.dbf',
  '/u02/oradata/utility/system02.dbf'
CHARACTER SET AL32UTF8
;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/utility/temp1_02.dbf'
     SIZE 4096M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/utility/temp1_01.dbf'
     SIZE 2048M REUSE AUTOEXTEND OFF;


STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

cp /u03/oracle/oradata/kinhdoanh/* /u03/oracle/oradata/utility

cp /u02/oracle/oradata/kinhdoanh/* /u02/oracle/oradata/utility

cp /u01/app/oracle/oradata/kinhdoanh/* /u01/app/oracle/oradata/utility


STEP 4: Create the bdump, udump and cdump directories

cd $DBA/admin
cd kinhdoanh

mkdir cdump
mkdir dpdump
mkdir pfile
mkdir adump
mkdir udump
mkdir bdump

STEP 8: Copy-over the old init.ora file

rcp $ORACLE_BASE/admin/kinhdoanh/pfile/*.ora newhost:/u01/oracle/admin/utility/pfile

Step 5: Global replace kinhdoanh to utility in *.ora

STEP 6: Start the new database

sql> startup nomount;

sql> @db_create_controlfile.sql

sql> RECOVER DATABASE


sql> ALTER DATABASE OPEN;


Trong trường hợp shutdown copy data file( system02, undotbs01,sysaux01,users01 ) ở bước 3 xong, copy start database lên => copy tiếp các datafile còn lại sẽ bị mất đồng bộ

Cách xữ lý :

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01152: file 5 was not restored from a sufficiently old backup
ORA-01110: data file 5: '/u02/oradata/utility/data01.dbf'

Copy hết cái archivelog mới qua server mới

SQL> recover database using backup controlfile;
ORA-00279: change 10911249216749 generated at 07/14/2012 10:10:01 needed for
thread 1
ORA-00289: suggestion : /u02/archivelog_10g/utility/1_6955_747570143.dbf
ORA-00280: change 10911249216749 for thread 1 is in sequence #6955

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO


Chọn AUTO để tiến hành lấy lấy các dữ liệu thời điểm bị mất
Trong trường hợp thiếu archivelog

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10911342402108 generated at 07/14/2012 14:24:11 needed for
thread 1
ORA-00289: suggestion : /u02/archivelog_10g/utility/1_6965_747570143.dbf
ORA-00280: change 10911342402108 for thread 1 is in sequence #6965

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.


SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.


=====================================
bash-3.00$ id
uid=100(oracle) gid=100(oinstall)
bash-3.00$ . ~/profile_11g.sh
start
/tmp
/u01/app/oracle/product/11.2.0.3/db_1/dg4msql/driver/lib:/u01/app/oracle/product/11.2.0.3/db_1/lib
TT4-SMS-S
/u01/app/oracle/product/11.2.0.3/db_1
sms
bash-3.00$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 17 16:05:54 2012

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

Connected to an idle instance.

SQL> startup pfile='/tmp/initsms.ora'
ORACLE instance started.

Total System Global Area 2038276096 bytes
Fixed Size                  2160776 bytes
Variable Size            1157629816 bytes
Database Buffers          855638016 bytes
Redo Buffers               22847488 bytes
Database mounted.
Database opened.
SQL> alter database backup controlfile to trace;

Database altered.

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/sms
                                                 /sms/trace

bash-3.00$ cd /u01/app/oracle/diag/rdbms/sms/sms/trace
bash-3.00$ cp sms_ora_4466.trc /tmp/

bash-3.00$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 17 16:15:50 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

bash-3.00$ cd /tmp/
bash-3.00$ vi sms_ora_4466.trc

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "SMS" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 40
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 13175
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/sms/redo01_1.log',
    '/u01/app/oracle/oradata/sms/redo01_2.log',
    '/u01/app/oracle/oradata/sms/redo01_3.log',
    '/u01/app/oracle/oradata/sms/redo01_4.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 2 (
    '/u04/logfile/redo02_1.log',
    '/u04/logfile/redo02_2.log',
    '/u04/logfile/redo02_3.log',
    '/u04/logfile/redo02_4.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 3 (
    '/u03/logfile_sms/redo03_1.log',
    '/u03/logfile_sms/redo03_2.log',
    '/u03/logfile_sms/redo03_3.log',
    '/u03/logfile_sms/redo03_4.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 4 (
    '/u02/logfile_sms/redo04_1.log',
    '/u02/logfile_sms/redo04_2.log',
    '/u02/logfile_sms/redo04_3.log',
    '/u02/logfile_sms/redo04_4.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 5 (
    '/u03/logfile_sms/redo05_1.log',
    '/u03/logfile_sms/redo05_2.log',
    '/u03/logfile_sms/redo05_3.log',
    '/u03/logfile_sms/redo05_4.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 6 (
    '/u02/logfile_sms/redo06_1.log',
    '/u02/logfile_sms/redo06_2.log',
    '/u02/logfile_sms/redo06_3.log',
    '/u02/logfile_sms/redo06_4.log'
  ) SIZE 150M BLOCKSIZE 512,

  GROUP 7 (
    '/u04/logfile/redo07_1.log',
    '/u04/logfile/redo07_2.log',
    '/u04/logfile/redo07_3.log',
    '/u04/logfile/redo07_4.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 8 (
    '/u01/app/oracle/oradata/sms/redo08_1.log',
    '/u01/app/oracle/oradata/sms/redo08_2.log',
    '/u01/app/oracle/oradata/sms/redo08_3.log',
    '/u01/app/oracle/oradata/sms/redo08_4.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 9 (
    '/u02/logfile_sms/redo09_1.log',
    '/u02/logfile_sms/redo09_2.log',
    '/u02/logfile_sms/redo09_3.log',
    '/u02/logfile_sms/redo09_4.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 10 (
    '/u02/logfile_sms/redo10_1.log',
    '/u02/logfile_sms/redo10_2.log',
    '/u02/logfile_sms/redo10_3.log',
    '/u02/logfile_sms/redo10_4.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 11 (
    '/u03/logfile_sms/redo11_1.log',
    '/u03/logfile_sms/redo11_2.log',
    '/u03/logfile_sms/redo11_3.log',
    '/u03/logfile_sms/redo11_4.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 12 '/u02/logfile_sms/redo12_1.log'  SIZE 150M BLOCKSIZE 512,
  GROUP 13 '/u02/logfile_sms/redo13_1.log'  SIZE 150M BLOCKSIZE 512,
  GROUP 14 '/u02/logfile_sms/redo14_1.log'  SIZE 150M BLOCKSIZE 512,
  GROUP 15 (
    '/u02/logfile_sms/redo15_1.log',
    '/u02/logfile_sms/redo15_2.log',
    '/u02/logfile_sms/redo15_3.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 16 (
    '/u02/logfile_sms/redo16_1.log',
    '/u02/logfile_sms/redo16_2.log',
    '/u02/logfile_sms/redo16_3.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 17 (
    '/u02/logfile_sms/redo17_1.log',
    '/u02/logfile_sms/redo17_2.log',
    '/u02/logfile_sms/redo17_3.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 18 (
    '/u02/logfile_sms/redo18_1.log',
    '/u02/logfile_sms/redo18_2.log',
    '/u02/logfile_sms/redo18_3.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 19 (
    '/u02/logfile_sms/redo19_1.log',
    '/u02/logfile_sms/redo19_2.log',
    '/u02/logfile_sms/redo19_3.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 20 (
    '/u02/logfile_sms/redo20_1.log',
    '/u02/logfile_sms/redo20_2.log',
    '/u02/logfile_sms/redo20_3.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 21 (
    '/u02/logfile_sms/redo21_1.log',
    '/u02/logfile_sms/redo21_2.log',
    '/u02/logfile_sms/redo21_3.log'
  ) SIZE 150M BLOCKSIZE 512,
  GROUP 22 (
    '/u02/logfile_sms/redo22_1.log',
    '/u02/logfile_sms/redo22_2.log',
    '/u02/logfile_sms/redo22_3.log'
  ) SIZE 150M BLOCKSIZE 512
-- STANDBY LOGFILE

DATAFILE
  '/u02/oradata/sms/system01.dbf',
  '/u02/oradata/sms/sysaux01.dbf',
  '/u02/oradata/sms/undotbs01.dbf',
  '/u02/oradata/sms/users01.dbf',
  '/u02/oradata/sms/data01.dbf',
  '/u02/oradata/sms/data02.dbf',
  '/u02/oradata/sms/data03.dbf',
  '/u02/oradata/sms/data04.dbf',
  '/u02/oradata/sms/data05.dbf',
  '/u02/oradata/sms/data06.dbf',
  '/u02/oradata/sms/data07.dbf',
  '/u02/oradata/sms/data08.dbf',
  '/u03/oradata/sms/data09.dbf',
  '/u03/oradata/sms/data10.dbf',
  '/u03/oradata/sms/data11.dbf',
  '/u03/oradata/sms/data12.dbf',
  '/u03/oradata/sms/data13.dbf',
  '/u03/oradata/sms/data14.dbf',
  '/u03/oradata/sms/data15.dbf',
  '/u03/oradata/sms/data16.dbf',
  '/u03/oradata/sms/data17.dbf',
  '/u03/oradata/sms/data18.dbf',
  '/u03/oradata/sms/data19.dbf',
  '/u03/oradata/sms/data20.dbf',
  '/u03/oradata/sms/data21.dbf',
  '/u03/oradata/sms/data22.dbf',
  '/u03/oradata/sms/data23.dbf',
  '/u03/oradata/sms/data24.dbf',
  '/u03/oradata/sms/data25.dbf',
  '/u03/oradata/sms/data26.dbf',
  '/u03/oradata/sms/data27.dbf',
  '/u03/oradata/sms/data28.dbf',
  '/u03/oradata/sms/data29.dbf',
  '/u03/oradata/sms/data30.dbf'
CHARACTER SET AL32UTF8
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/sms/temp01.dbf'
     SIZE 1040187392  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
======================

cd  /u01/app/oracle/product/11.2.0.3/db_1/dbs
bash-3.00$ scp initsms.ora oracle@10.151.120.244:/u02/app/oracle/product/11.2.0.3.0/db_1/dbs/
bash-3.2$ chmod 644 initsms.ora


audit_file_dest="/u02/app/oracle/admin/sms/adump"
audit_trail=DB
compatible=11.2.0.0.0
control_files=/u02/app/oracle/oradata/sms/control01.ctl, /u02/app/oracle/oradata/sms/control02.ctl
db_block_size=8192
db_domain=""
db_name="sms"
diagnostic_dest=/u02/app/oracle
log_archive_dest_1='LOCATION=/u02/archivelog'
log_archive_format=%t_%s_%r.dbf
memory_target=2046820352   => chú ý chỉnh ram cho vừa phải để tránh lỗi SQL> startup nomount; Segmentation Fault
open_cursors=300
processes=300
remote_login_passwordfile=EXCLUSIVE
sessions=480
undo_tablespace=UNDOTBS1
event=""

Vào thư mục oracle home
bash-3.2$ pwd
/u02/app/oracle/admin

bash-3.2$ mkdir sms
bash-3.2$ ls -ltr
total 7
drwxr-x---   8 oracle   oinstall       8 Apr  7  2012 kinhdoanh
drwxr-xr-x   8 oracle   oinstall       8 Apr  7  2012 bcclml
drwxr-xr-x   2 oracle   oinstall       2 Nov 17 18:31 sms
bash-3.2$ cd sms/

mkdir cdump
mkdir dpdump
mkdir pfile
mkdir adump
mkdir udump
mkdir bdump


bash-3.2$ cd /u02/app/oracle/oradata/
bash-3.2$ ls
bcclml     kinhdoanh
bash-3.2$ mkdir sms




bash-3.2# su - oracle
Oracle Corporation      SunOS 5.10      Generic Patch   January 2005
bcclml

$ id
uid=100(oracle) gid=100(oinstall)
$ bash

bash-3.2$ echo $ORACLE_SID
bcclml

Chý ý cần đổi tên ORACLE_SI
bash-3.2$ export ORACLE_SID=sms
bash-3.2$ echo $ORACLE_SID
sms

bash-3.2$ cd /tmp/
bash-3.2$
bash-3.2$ echo $ORACLE_SID
sms
bash-3.2$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 17 18:36:45 2012

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

Connected to an idle instance.

SQL>

bash-3.2$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 17 18:43:50 2012

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2472673280 bytes
Fixed Size                  2161824 bytes
Variable Size            1392509792 bytes
Database Buffers         1056964608 bytes
Redo Buffers               21037056 bytes
SQL> @/tmp/sms_ora_4466.trc
ORA-01081: cannot start already-running ORACLE - shut it down first

Control file created.


Database altered.


Tablespace altered.

SQL> create spfile from pfile;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 7451451392 bytes
Fixed Size                  2173776 bytes
Variable Size            3841987760 bytes
Database Buffers         3590324224 bytes
Redo Buffers               16965632 bytes
Database mounted.
Database opened.



echo $ORACLE_HOME
SQL> var OHM varchar2(100);
SQL> EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;

PL/SQL procedure successfully completed.

SQL> PRINT OHM

OHM


$ORACLE_HOME/bin>sqlplus sys/@ as sysdba
eg.
D:\10205\BIN>sqlplus sys/oracle1@testdb as sysdba



SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- --------------
remote_login_passwordfile string EXCLUSIVE

If this parameter is not set to 'EXCLUSIVE', the following command can be run:-
alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
Unix:
$ orapwd file=$ORACLE_HOME/dbs/orapw<$ORACLE_SID> password=sys entries=5
 
select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
 
$ORACLE_HOME/bin>sqlplus sys/ as sysdba
   
  

No comments: