Создание копии базы данных Oracle из бекапа с помощью RMAN на том же сервере
Делалось:
17.08.2015
Имеется 1 сервер Oracle 12c, установленный как здесь
Создаю password file для нового instance COPY12
$ cd $ORACLE_HOME/dbs/
$ cp orapworcl12 orapwcopy12
Прописываю параметры подключения к создаваемому instance COPY12
$ cd /u01/oracle/database/12.1/network/admin/
$ vi tnsnames.ora
COPY12 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = moscow.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = copy12)
)
)
Стартую instance COPY12 в режиме nomount
$ export ORACLE_SID=COPY12
$ cd /u01/oracle/database/12.1/dbs/
$ vi init${ORACLE_SID}.ora
db_name=copy12
COMPATIBLE=12.1.0.2.0
$ sqlplus / as sysdba
SQL> startup nomount pfile=$ORACLE_HOME/dbs/init${ORACLE_SID}.ora
SQL> select status from v$instance;
STATUS
------------------------------------
STARTED
$ ps -ef | grep copy12
oracle12 25954 1 0 09:31 ? 00:00:00 ora_pmon_copy12
oracle12 25956 1 0 09:31 ? 00:00:00 ora_psp0_copy12
oracle12 25958 1 3 09:31 ? 00:00:01 ora_vktm_copy12
oracle12 25962 1 0 09:31 ? 00:00:00 ora_gen0_copy12
oracle12 25964 1 0 09:31 ? 00:00:00 ora_mman_copy12
oracle12 25968 1 0 09:31 ? 00:00:00 ora_diag_copy12
oracle12 25970 1 0 09:31 ? 00:00:00 ora_dbrm_copy12
oracle12 25972 1 0 09:31 ? 00:00:00 ora_vkrm_copy12
oracle12 25974 1 0 09:31 ? 00:00:00 ora_dia0_copy12
oracle12 25976 1 0 09:31 ? 00:00:00 ora_dbw0_copy12
oracle12 25978 1 0 09:31 ? 00:00:00 ora_lgwr_copy12
oracle12 25980 1 0 09:31 ? 00:00:00 ora_ckpt_copy12
oracle12 25982 1 0 09:31 ? 00:00:00 ora_smon_copy12
oracle12 25984 1 0 09:31 ? 00:00:00 ora_reco_copy12
oracle12 25986 1 0 09:31 ? 00:00:00 ora_lreg_copy12
oracle12 25988 1 0 09:31 ? 00:00:00 ora_pxmn_copy12
oracle12 25990 1 0 09:31 ? 00:00:00 ora_mmon_copy12
oracle12 25992 1 0 09:31 ? 00:00:00 ora_mmnl_copy12
oracle12 26005 15234 0 09:31 pts/0 00:00:00 grep copy12
Настройка Listener для создания нового сервиса COPY12
$ cd $GRID_HOME/network/admin
$ cp listener.ora listener.ora.bkp
$ vi listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=moscow.localdomain)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ORCL12)
(ORACLE_HOME=/u01/oracle/database/12.1)
(SID_NAME=orcl12)
)
(SID_DESC=
(GLOBAL_DBNAME=COPY12)
(ORACLE_HOME=/u01/oracle/database/12.1)
(SID_NAME=copy12)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET
$ lsnrctl reload;
$ lsnrctl services
***
Service "COPY12" has 2 instance(s).
Instance "copy12", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "copy12", status BLOCKED, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "ORCL12" has 2 instance(s).
Instance "orcl12", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "orcl12", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
***
$ lsnrctl status;
***
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 17-AUG-2015 06:02:18
Uptime 3 days 3 hr. 45 min. 59 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/grid/12.1/network/admin/listener.ora
Listener Log File /u01/oracle/diag/tnslsnr/moscow/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=moscow.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=moscow.localdomain)(PORT=5500))(Security=(my_wallet_directory=/u01/oracle/admin/orcl12/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "COPY12" has 2 instance(s).
Instance "copy12", status UNKNOWN, has 1 handler(s) for this service...
Instance "copy12", status BLOCKED, has 1 handler(s) for this service...
Service "ORCL12" has 2 instance(s).
Instance "orcl12", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl12", status READY, has 1 handler(s) for this service...
Service "orcl12XDB" has 1 instance(s).
Instance "orcl12", status READY, has 1 handler(s) for this service...
The command completed successfully
Подготовка RMAN скрипта для создания копии сервера
$ export ORACLE_SID=orcl12
$ echo $ORACLE_SID
orcl12
Значения размера файлов журналов, которые нужно будет указать в скрипте.
SQL> select max (bytes), count (1) from v$log;
MAX(BYTES) COUNT(1)
---------- ----------
52428800 3
$ rman target /
Если используется Oracle Catalog
$ rman target / catalog rman/rman123@rman12
RMAN> report schema;
starting full resync of recovery catalog
full resync complete
Report of database schema for database with db_unique_name ORCL12
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 810 SYSTEM YES +DATA/ORCL12/DATAFILE/system.258.887923593
3 780 SYSAUX NO +DATA/ORCL12/DATAFILE/sysaux.257.887923497
4 135 UNDOTBS1 YES +DATA/ORCL12/DATAFILE/undotbs1.260.887923711
6 5 USERS NO +DATA/ORCL12/DATAFILE/users.259.887923707
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 197 TEMP 32767 +DATA/ORCL12/TEMPFILE/temp.265.887923853
set lin 180
col MEMBER for a60
col status for a8
select a.GROUP#, a.THREAD#, a.BYTES/1024/1024 as MB, a.status, b.member
from v$log a, v$logfile b
where a.GROUP#=b.GROUP#;
GROUP# THREAD# MB STATUS MEMBER
---------- ---------- ---------- -------- ------------------------------------------------------------
3 1 50 CURRENT +DATA/ORCL12/ONLINELOG/group_3.264.888049601
3 1 50 CURRENT +ARCH/ORCL12/ONLINELOG/group_3.259.888049605
2 1 50 INACTIVE +DATA/ORCL12/ONLINELOG/group_2.263.888049597
2 1 50 INACTIVE +ARCH/ORCL12/ONLINELOG/group_2.258.888049599
1 1 50 INACTIVE +DATA/ORCL12/ONLINELOG/group_1.262.888049593
1 1 50 INACTIVE +ARCH/ORCL12/ONLINELOG/group_1.257.888049595
Создание каталогов в ASM
$ cd ~/ . asm.sh
$ asmcmd
$ cd DATA
ASMCMD> mkdir COPY12
ASMCMD> cd COPY12
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir TEMPFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> cd ../../
ASMCMD> cd ARCH
ASMCMD> mkdir COPY12
ASMCMD> cd COPY12
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir ARCHIVELOG
ASMCMD> exit
$ source ~/.bash_profile
$ cd $ORACLE_HOME/scripts
$ vi duplicate-rman-script.rman
Данные задаются в соответствии запроса report shema; Идентификатор файлов данных (1,3,4….) см. в report shema;
RUN {
set NEWNAME for DATAFILE 1 to '+DATA/COPY12/DATAFILE/system.258.887923593';
set NEWNAME for DATAFILE 3 to '+DATA/COPY12/DATAFILE/sysaux.257.887923497';
set NEWNAME for DATAFILE 4 to '+DATA/COPY12/DATAFILE/undotbs1.260.887923711';
set NEWNAME for DATAFILE 6 to '+DATA/COPY12/DATAFILE/users.259.887923707';
set NEWNAME for TEMPFILE 1 to '+DATA/COPY12/TEMPFILE/temp.265.887923853';
DUPLICATE TARGET DATABASE TO COPY12
# SKIP TABLESPACE DATA
LOGFILE
GROUP 1 ('+ARCH/COPY12/ONLINELOG/group_1.257.888049595', '+DATA/COPY12/ONLINELOG/group_1.262.888049593') SIZE 52428800 REUSE,
GROUP 2 ('+ARCH/COPY12/ONLINELOG/group_2.258.888049599', '+DATA/COPY12/ONLINELOG/group_2.263.888049597') SIZE 52428800 REUSE,
GROUP 3 ('+ARCH/COPY12/ONLINELOG/group_3.259.888049605', '+DATA/COPY12/ONLINELOG/group_3.264.888049601') SIZE 52428800 REUSE;
}
Проверка синтаксиса созданного файла сценария
$ rman CHECKSYNTAX @duplicate-rman-script.rman
Выполнение скрипта резервного копирования
$ rman target sys/manager@orcl12 auxiliary sys/manager@copy12 @duplicate-rman-script.rman
Если используется Oracle Catalog, команда может выглядеть следующим образом
$ rman catalog rman/rman123@rman12
$ connect target sys/manager
$ connect auxiliary sys/manager@copy12
$ @duplicate-rman-script.rman
Завершилось без ошибок (с 3-го раза).
Проверяем результаты копирования
$ export ORACLE_SID=COPY12
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
copy12
SQL> select status from v$instance;
STATUS
------------
OPEN