[접속]
[전체백업 받기]
RMAN> backup as compressed backupset database
2> format '/data/backup/open/rman/%U_%T_full';
Starting backup at 10-APR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/app/oracle/oradata/testdb/system01.dbf
input datafile file number=00002 name=/app/oracle/oradata/testdb/sysaux01.dbf
input datafile file number=00003 name=/app/oracle/oradata/testdb/undotbs01.dbf
input datafile file number=00005 name=/app/oracle/oradata/testdb/example01.dbf
input datafile file number=00004 name=/app/oracle/oradata/testdb/users01.dbf
input datafile file number=00006 name=/app/oracle/oradata/testdb/test04.dbf
input datafile file number=00007 name=/app/oracle/oradata/testdb/test001.dbf
channel ORA_DISK_1: starting piece 1 at 10-APR-15
channel ORA_DISK_1: finished piece 1 at 10-APR-15
piece handle=/data/backup/open/rman/1aq40om3_1_1_20150410_full tag=TAG20150410T054019 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:17
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-APR-15
channel ORA_DISK_1: finished piece 1 at 10-APR-15
piece handle=/data/backup/open/rman/1bq40oqc_1_1_20150410_full tag=TAG20150410T054019 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 10-APR-15
[예제 테이블 생성하기 ]
[oracle@server113 rman]$ sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 10 05:55:11 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
SQL> create table scott.gogak2
2 (no number,
3 name varchar2(10),
4 addr varchar2(20));
Table created.
SQL> insert into scott.gogak2 values(1,'하민성', '경기성남');
1 row created.
SQL> insert into scott.gogak2 values(2,'김태희', '경기부천');
1 row created.
SQL> insert into scott.gogak2 values(3,'한가인', '부산');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scott.gogak2;
NO NAME ADDR
---------- ---------- --------------------
1 하민성 경기성남
2 김태희 경기부천
3 한가인 부산
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select to_char(sysdate, 'YYYY-MM-DD-HH24:MI:SS')from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2015-04-10-06:00:26
SQL> drop table scott.gogak purge;
[clone DB 만들기]
[oracle@server113 dbs]$ cp inittestdb.ora ./initrman.ora
[oracle@server113 dbs]$ vi initrman.ora
rman.__pga_aggregate_target=171966464
rman.__sga_target=251658240
rman.__shared_io_pool_size=0
rman.__shared_pool_size=83886080
rman.__streams_pool_size=0
*.audit_file_dest='/app/oracle/admin/rman/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data2/rman/clone/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='rman'
*.diagnostic_dest='/app/oracle'
*.log_archive_dest_1='location=/data2/arc1'
*.log_archive_dest_2='location=/data2/arc2'
*.log_archive_format='%s_%t_%r.arc'
#*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
db_file_name_convert=('/data2/rman/testdb','/data2/rman/clone')
log_file_name_convert=('/data2/rman/testdb',/data2/rman/clone')
※ inittestdb.ora가 원본이면 안됨 ....
[clonedb용 디렉토리 설정]
[oracle@server113 data2]$ mkdir -p rman/clone
[oracle@server113 rmanclone]$ mkdir -p /app/oracle/admin/rman
[oracle@server113 rmanclone]$ mkdir -p /app/oracle/admin/rman/adump
[oracle@server113 rmanclone]$ mkdir -p /app/oracle/admin/rman/dpdump
[archive log 복사 → ramn 있는 곳으로]
[oracle@server113 rman]$ cp -av /data2/arc1/* /data/backup/open/rman/
[]
[oracle@server113 dbs]$ export ORACLE_SID=rman
[oracle@server113 dbs]$ export NLS_FORMAT='YYYY-MM-DD-HH24:MI:SS'
[oracle@server113 admin]$ sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 10 07:04:37 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
만일 접속안되면 : [oracle@server113 dbs]$ cp orapwtestdb ./orapwrmanclone
[rman으로 들어가서]
[oracle@server113 admin]$ rman auxiliary sys/oracle
[실행하기 알아서 복구됨? ] 실패
duplicate database to 'rman'
pfile='/app/oracle/product/11g/dbs/initrman.ora'
nofilenamecheck
backup location '/data/backup/open/rman'
until time '2015-04-10-06:00:26';
ORA-01127: database name 'rmanclone' exceeds size limit of 8 characters
SQL> select status from v$instance;
select status from v$instance
*
[현재 시스템 조회]
col platform_name for a20
col endian_format for a20
select d.platform_name, endian_format
from v$transportable_platform tp , v$database d
where tp.platform_name=d.platform_name;
[RHEL4 에서 작업하기]
SQL> create tablespace conv_test
2 datafile '/home/oracle/oradata/testdb/conv_test01.dbf' size 10M;
Tablespace created.
SQL> create table scott.ctest1(no number) tablespace conv_test;
Table created.
SQL> insert into scott.ctest1 values(1);
1 row created.
SQL> insert into scott.ctest1 values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scott.ctest1;
NO
----------
1
2
[점검]
exec dbms_tts.transport_set_check('CONV_TEST',true);
SYS>select * from transport_set_violations;
no rows selected
[datapump 용 작업]
[oracle@server10g112 ~]$ mkdir /home/oracle/dp_conv
SQL> create directory dp_conv as '/home/oracle/dp_conv';
Directory created.
SQL> grant read ,write on directory dp_conv to system;
Grant succeeded.
[read only 로 바꾼다음 expdp 수행]
SQL> alter tablespace conv_test read only;
Tablespace altered.
[oracle@server10g112 ~]$ expdp system/oracle dumpfile=conv.dmp directory=dp_conv transport_tablespaces=conv_test logfile=conv.log
Export: Release 10.2.0.4.0 - Production on Monday, 27 April, 2015 16:34:37
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=conv.dmp directory=dp_conv transport_tablespaces=conv_test logfile=conv.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/dp_conv/conv.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:35:44
[oracle@server113 dp_conv]$ expdp system/oracle dumpfile=conv_meta.dmp directory=dp_conv full=y include=user,role,role_grant,profile content=metadata_only
Export: Release 11.2.0.1.0 - Production on Fri Apr 10 13:06:55 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=conv_meta.dmp directory=dp_conv full=y include=user,role,role_grant,profile content=metadata_only
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/data/dp_conv/conv_meta.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 13:08:04
[옮길 플랫폼 확인하기 target 에서 확인하기]
[oracle@server113 ~]$ sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 10 11:41:25 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
col platform_name for a20
SQL> col endian_format for a20
select d.platform_name, endian_format
from v$transportable_platform tp , v$database d
3 where tp.platform_name=d.platform_name;
PLATFORM_NAME ENDIAN_FORMAT
-------------------- --------------------
Linux IA (32-bit) Little
[변환 작업하기(보내는 쪽에서)]
Cause : RMAN버그
Action :
10g 에서는 RMAN에 버그가 있어서 원래경로에 있는 RMAN을 실행되는 경로로 복사해줘야 합니다.
[oracle@database ~]$ which rman
/usr/X11R6/bin/rman
[oracle@database ~]$ su -
Password:
[root@database ~]# cp /home/oracle/product/10g/bin/rman /usr/X11R6/bin/rman
cp: overwrite `/usr/X11R6/bin/rman'? y
[oracle@database ~]$ rman target /
RMAN> convert tablespace 'CONV_TEST' to platform 'Linux IA (32-bit)'
format '/home/oracle/dp_conv/%U'2> ;
Starting backup at 27-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/home/oracle/oradata/testdb/conv_test01.dbf
converted datafile=/home/oracle/dp_conv/data_D-TESTDB_I-2661467944_TS-CONV_TEST_FNO-6_01q5eq82
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 27-APR-15
[데이터 전송받기]
[oracle@server113 dp_conv]$ scp 172.16.65.140:/home/oracle/dp_conv/* /data/dp_conv/
oracle@172.16.65.140's password:
conv.dmp 100% 68KB 68.0KB/s 00:00
conv.log 100% 958 0.9KB/s 00:00
conv_meta.dmp 100% 208KB 208.0KB/s 00:00
data_D-TESTDB_I-2661467944_TS-CONV_TEST_FNO-6 100% 10MB 10.0MB/s 00:01
export.log
[data pump 작업 target 에서]
[oracle@server113 dp_conv]$ sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 10 12:11:09 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option
SQL> create directory dp_conv as '/data/dp_conv';
Directory created.
SQL> grant read, write on directory dp_conv to system;
Grant succeeded.
[oracle@server113 dp_conv]$ rman target sys/oracle
RMAN> convert datafile '/data/dp_conv/data_D-TESTDB_I-2661467944_TS-CONV_TEST_FNO-6_01q5eq82'
format "/app/oracle/oradata/testdb/conv_test01.dbf";2>
Starting conversion at target at 10-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/data/dp_conv/data_D-TESTDB_I-2661467944_TS-CONV_TEST_FNO-6_01q5eq82
converted datafile=/app/oracle/oradata/testdb/conv_test01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished conversion at target at 10-APR-15