ORACLE BackupRestore

RMAN Backup

Shell

rman target / 

RMAN

backup database;

• バックアップはdb_recovery_file_destの設定に影響があるので必ず下記を注意すること
  • db_recovery_file_dest_size = 10TB
    • サイズがDBデータサイズを超えるとエラーになってバックアップが止まる
  • db_recovery_file_dest_size
    • バックアップしたディスクの容量及びディスクのIOが余裕があることを確認すること

alter system set db_recovery_file_dest_size = 12800G scope=both;
alter system set db_recovery_file_dest = '/data3/fast_recovery_area/' scope=both;

RMAN Restore

Shell

rman target / 

RMAN

startup mount
restore database;
recover database;

Using Data PUMP (expdp/impdp)

• It must execute config backup dir SQL before execute data pump

  • https://qiita.com/toshihirock/items/86931e3c52dc47287dd2

• Set export directory on sqlplus

create or replace directory BAK_DIR as '/data/bak';

• export schema

expdp admin/pass@orcl schemas=servie01 directory=BAK_DIR dumpfile=service01_20210301.dmp logfile=exp.log

• Delete job table if you see error message when execute expdp or impdp

With the Partitioning, OLAP and Data Mining options
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

select 'drop table ' || owner_name || '.' || job_name || ';'
from dba_datapump_jobs
where state = 'NOT RUNNING';

• https://www.twblogs.net/a/5c237063bd9eee16b3db454f

Using Export/Import (exp/imp)

redo log file

select * from v$log;
select * from v$logfile;

-- Drop logfile group
ALTER DATABASE DROP LOGFILE GROUP 4;

-- Add logfile group
ALTER DATABASE ADD LOGFILE GROUP 7;

-- add logfile to group
ALTER DATABASE ADD LOGFILE MEMBER '/mnt/sdb1/redolog/redo06.log' TO GROUP 6;

-- rename and change path log file
ALTER DATABASE 
  RENAME FILE '/app/oracle/flash_recovery_area/SMARTDB/onlinelog/o1_mf_6_h3hzqkx2_.log'
           TO '/mnt/sdb1/redolog/redo06.log';

Ref Documents

• Oracle backup and restore, duplicate, replica planning(jp)
  • http://cosol.jp/techdb/2019/12/
• Cumulate differential backup and restore(jp)
  • https://www.atmarkit.co.jp/ait/articles/0611/02/news126_2.html

Official Documents

• バックアップおよびリカバリ・ユーザーズ・ガイド
• Oracle stream (jp)
  • https://blogs.oracle.com/supportjp/oracle-database-oracle-streams
• 高速リカバリ領域の構成
• Oracle stream(replication) official documentation
  • https://docs.oracle.com/cd/E16338_01/server.112/b61351/toc.htm
• Cumulate differential backup and restore(jp)
  • https://www.oracle.com/technetwork/jp/database/articles/kusakabe/kusakabe-8-5172663-ja.html

Similar Documents

• Active Duplicate

Index

• https://github.com/LowyShin/KnowledgeBase/wiki

• OrgFile

  • https://github.com/LowyShin/KnowledgeBase/wiki/ORACLE-BackupRestore