データベース複製(同一サーバ別名)

• MySQL

(8.0.22)

mysqldump -u root -p --single-transaction --triggers --routines --events test > test.sql

create database test2;
use test2
source test.sql

• Oracle

(19c)

https://qiita.com/tlokweng/items/a041394e1011434eca06
https://blogs.oracle.com/otnjp/shibacho-057

(1)非CDBの場合
※CDBの場合も同様に実行すればよい。(ただし、複製先pfileにenable_pluggable_database=trueの追加必要)

TARGET接続無し + CATALOG接続無しで、取得済みバックアップを使ってデータベースを複製
orcl→orcl2

-- 前提
OS: CentOS7
メモリ: 4G

-- 1. 複製元DB作成

非CDBのシングルDB作成

FRA有効化
mkdir /u01/app/oracle/oradata/orcl/fra

alter system set db_recovery_file_dest='/u01/app/oracle/oradata/orcl/fra' scope=spfile;
alter system set db_recovery_file_dest_size=500M scope=spfile;

アーカイブログモード
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;

-- 2. rmanバックアップの取得

mkdir /home/oracle/bkup

export NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS'

rman target /

show all;
-- 紛らわしいので一時的無効化
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
show all;

backup device type disk format '/home/oracle/bkup/%U' database;
backup device type disk format '/home/oracle/bkup/%U' current controlfile;
backup device type disk format '/home/oracle/bkup/%U' archivelog all;

list backup summary;
list backup of archivelog all by file;

list backupset 1;
list backupset 2;
list backupset 3;
list backupset 4;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

-- 3. 複製先DB作成

mkdir -p /u01/app/oracle/oradata/orcl2/fra

vim $ORACLE_HOME/dbs/initorcl2.ora
db_name='orcl2'
memory_target=800M
db_domain='example.com'
control_files = ('/u01/app/oracle/oradata/orcl2/control01.ctl','/u01/app/oracle/oradata/orcl2/control02.ctl')
compatible=19.0.0
db_block_size=8192
db_recovery_file_dest='/u01/app/oracle/oradata/orcl2/fra'
db_recovery_file_dest_size=500M
remote_login_passwordfile='EXCLUSIVE'
diagnostic_dest='/u01/app/oracle'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl2/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl2/'

※duplicateコマンド実行によりspfileが生成されるため、pfileを変更して再実行する場合は、spfileの削除必要

export ORACLE_SID=orcl2
sqlplus / as sysdba
startup nomount;

rman auxiliary /

DUPLICATE DATABASE TO orcl2 BACKUP LOCATION '/home/oracle/bkup';

(2)PDBの場合

select * from V$PDBS;
select * from cdb_pdbs;

alter session set PDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCL/pdb1/', '/u01/app/oracle/oradata/ORCL/pdb12';

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;

• PostgreSQL

(13)
https://qiita.com/tatataiki/items/e6208ab36d35356f1f55

create database test2 template test;

• SQL Server

(2019)

use test;

backup database test
to disk = 'C:\bkup\test.bak'
with format,
medianame = 'test',
name = 'full backup of test',
stats =10,
checksum;

restore database test2
from disk = 'C:\bkup\test.bak'
with recovery,
move 'test' to 'C:\test2\test.mdf',
move 'tbs1' to 'C:\test2\tbs1.ndf',
move 'test_log' to 'C:\test2\test_log.ldf';