[관리] Oracle 백업에 대하여 – 아랑

#### ORACLE BACKUP 절차 ######

1. 먼저 이전 DB_Server 의 환경을 검사한다.

# .bash_profile 의 환경설정 검사

# $ORACLE_HOME/dbs/파라메타(*.ora)파일 검사

  –> init<SID>.ora 은 DB_name 과 실질적인 server 환경의

      DB의 경로를 지정한다.

# $ORACLE_HOME/oradata/DB_name/*.ctl –> contralfile 검사 # $ORACLE_HOME/oradata/DB_name/*.log –> logfile 검사 # $ORACLE_HOME/oradata/DB_name/*.dbf –> databasefile 검사 # 기타 databasefile 검사

#테이블 스페이스 와 데이터  파일 정보

SVRMGR> select tablespace_name,file_name,v$datafile.status,enabled

     2> from dba_data_files, v$datafile

     3> where file_id=file#;

          

TABLESPACE_NAME                FILE_NAME                                                                        STATUS  ENABLED  

—————————— ——————————————————————————– ——- ———-

SYSTEM                         /home/oracle/oradata/auction/system01.dbf                                        SYSTEM  READ WRITE

OEM_REPOSITORY                 /home/oracle/oradata/auction/oemrep01.dbf                                        ONLINE  READ WRITE

RBS                            /home/oracle/oradata/auction/rbs01.dbf                                           ONLINE  READ WRITE

TEMP                           /home/oracle/oradata/auction/temp01.dbf                                          ONLINE  READ WRITE

USERS                          /home/oracle/oradata/auction/users01.dbf                                         ONLINE  READ WRITE

INDX                           /home/oracle/oradata/auction/indx01.dbf                                          ONLINE  READ WRITE

DRSYS                          /home/oracle/oradata/auction/drsys01.dbf                                         ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata1.dbs                                     ONLINE  READ WRITE

APIDX1                         /home/oracle/oradata/auctionplus/apidx1.dbs                                      ONLINE  READ WRITE

APRBS                          /home/oracle/oradata/auctionplus/aprbs.dbs                                       ONLINE  READ WRITE

APTEMP                         /home/oracle/oradata/auctionplus/aptemp.dbs                                      ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata2.dbs                                     ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata3.dbs                                     ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata4.dbs                                     ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata5.dbs                                     ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata6.dbs                                     ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata7.dbs                                     ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata8.dbs                                     ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata9.dbs                                     ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata10.dbs                                    ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata11.dbs                                    ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata12.dbs                                    ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata13.dbs                                    ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata14.dbs                                    ONLINE  READ WRITE

APDATA1                        /home/oracle/oradata/auctionplus/apdata15.dbs                                    ONLINE  READ WRITE

CMAIL                          /cmail/cmail_db/cmail_data.ora                                                   ONLINE  READ WRITE

#리두로그 파일 정보

SVRMGR> select v$logfile.member,v$logfile.group#,v$log.status,bytes

     2> from v$logfile,v$log

     3> where v$logfile.group#=v$log.group#;

MEMBER                                                                           GROUP#     STATUS           BYTES    

——————————————————————————– ———- —————- ———-

/home/oracle/oradata/auction/redo01.log                                                   1 INACTIVE             512000

/home/oracle/oradata/auction/redo02.log                                                   2 CURRENT              512000

#컨트롤 파일의 정보

SVRMGR> select * from v$controlfile;

STATUS  NAME                                                                            

——- ——————————————————————————–

        /home/oracle/oradata/auction/control01.ctl                                      

        /home/oracle/oradata/auction/control02.ctl                                      

#파라메타 파일의 정보

SVRMGR> show parameter;

NAME                                TYPE    VALUE

———————————– ——- ——————————

O7_DICTIONARY_ACCESSIBILITY         boolean TRUE                          

always_anti_join                    string  NESTED_LOOPS                  

always_semi_join                    string  standard                      

aq_tm_processes                     integer 0                            

audit_file_dest                     string  ?/rdbms/audit                

audit_trail                         string  NONE                          

background_core_dump                string  partial                      

background_dump_dest                string  /home/oracle/admin/auction/bdu

backup_tape_io_slaves               boolean FALSE                        

bitmap_merge_area_size              integer 1048576                      

blank_trimming                      boolean FALSE                        

buffer_pool_keep                    string                                

buffer_pool_recycle                 string                                

commit_point_strength               integer 1                            

compatible                          string  8.1.0                        

control_file_record_keep_time       integer 7                            

control_files                       string  /home/oracle/oradata/auction/c

core_dump_dest                      string  /home/oracle/admin/auction/cdu

cpu_count                           integer 2                            

create_bitmap_area_size             integer 8388608                      

cursor_space_for_time               boolean FALSE                        

db_block_buffers                    integer 8192                          

db_block_checking                   boolean FALSE                        

db_block_checksum                   boolean FALSE                        

db_block_lru_latches                integer 1                            

db_block_max_dirty_target           integer 8192                          

db_block_size                       integer 2048                          

db_domain                           string                                

db_file_direct_io_count             integer 64                            

db_file_multiblock_read_count       integer 8                            

db_file_name_convert                string                                

db_files                            integer 200                          

db_name                             string  auction                      

db_writer_processes                 integer 1                            

dblink_encrypt_login                boolean FALSE                        

always_anti_join                    string  NESTED_LOOPS                  

always_semi_join                    string  standard                      

aq_tm_processes                     integer 0                            

audit_file_dest                     string  ?/rdbms/audit                

audit_trail                         string  NONE                          

background_core_dump                string  partial                      

background_dump_dest                string  /home/oracle/admin/auction/bdu

backup_tape_io_slaves               boolean FALSE                        

bitmap_merge_area_size              integer 1048576                      

blank_trimming                      boolean FALSE                        

buffer_pool_keep                    string                                

buffer_pool_recycle                 string                                

commit_point_strength               integer 1                            

compatible                          string  8.1.0                        

control_file_record_keep_time       integer 7                            

control_files                       string  /home/oracle/oradata/auction/c

core_dump_dest                      string  /home/oracle/admin/auction/cdu

cpu_count                           integer 2                            

create_bitmap_area_size             integer 8388608                      

cursor_space_for_time               boolean FALSE                        

db_block_buffers                    integer 8192                          

db_block_checking                   boolean FALSE                        

db_block_checksum                   boolean FALSE                        

db_block_lru_latches                integer 1                            

db_block_max_dirty_target           integer 8192                          

db_block_size                       integer 2048                          

db_domain                           string                                

db_file_direct_io_count             integer 64                            

db_file_multiblock_read_count       integer 8                            

db_file_name_convert                string                                

db_files                            integer 200                          

db_name                             string  auction                      

db_writer_processes                 integer 1                            

dblink_encrypt_login                boolean FALSE                        

dbwr_io_slaves                      integer 0                            

disk_asynch_io                      boolean TRUE                          

distributed_transactions            integer 10                            

dml_locks                           integer 264                          

enqueue_resources                   integer 484                          

ent_domain_name                     string                                

event                               string                                

fast_start_io_target                integer 0                            

fast_start_parallel_rollback        string  LOW                          

fixed_date                          string                                

gc_defer_time                       integer 10                            

gc_files_to_locks                   string                                

gc_releasable_locks                 integer 0                            

gc_rollback_locks                   string                                

global_names                        boolean FALSE                        

hash_area_size                      integer 131072                        

hash_join_enabled                   boolean TRUE                          

hash_multiblock_io_count            integer 0                            

hi_shared_memory_address            integer 0                            

hs_autoregister                     boolean TRUE                          

ifile                               file                                  

instance_groups                     string                                

instance_name                       string  ORCL                          

instance_number                     integer 0                            

java_max_sessionspace_size          integer 0                            

java_pool_size                      string  20971520                      

java_soft_sessionspace_limit        integer 0                            

job_queue_interval                  integer 60                            

job_queue_processes                 integer 0                            

large_pool_size                     string  0                            

license_max_sessions                integer 0                            

license_max_users                   integer 0                            

license_sessions_warning            integer 0                            

lm_locks                            integer 12000                        

lm_procs                            integer 127                          

lm_ress                             integer 6000                          

local_listener                      string                                

lock_name_space                     string                                

lock_sga                            boolean FALSE                        

log_archive_dest                    string                                

log_archive_dest_1                  string                                

log_archive_dest_2                  string                                

log_archive_dest_3                  string                                

log_archive_dest_4                  string                                

log_archive_dest_5                  string                                

log_archive_dest_state_1            string  enable                        

log_archive_dest_state_2            string  enable                        

log_archive_dest_state_3            string  enable                        

log_archive_dest_state_4            string  enable                        

log_archive_dest_state_5            string  enable                        

log_archive_duplex_dest             string                                

log_archive_format                  string  %t_%s.dbf                    

log_archive_max_processes           integer 1                            

log_archive_min_succeed_dest        integer 1                            

log_archive_start                   boolean FALSE                        

log_buffer                          integer 163840                        

log_checkpoint_interval             integer 10000                        

log_checkpoint_timeout              integer 1800                          

log_checkpoints_to_alert            boolean FALSE                        

log_file_name_convert               string                                

max_commit_propagation_delay        integer 700                          

max_dump_file_size                  string  10000                        

max_enabled_roles                   integer 20                            

max_rollback_segments               integer 30                            

mts_dispatchers                     string  (ADDRESS=(PARTIAL=YES)(PROTOCO

mts_listener_address                string                                

mts_max_dispatchers                 integer 5                            

mts_max_servers                     integer 20                            

mts_multiple_listeners              boolean FALSE                        

mts_servers                         integer 1                            

mts_service                         string  auction                      

nls_calendar                        string                                

nls_comp                            string                                

nls_currency                        string                                

nls_date_format                     string                                

nls_date_language                   string                                

nls_dual_currency                   string                                

nls_iso_currency                    string                                

nls_language                        string  AMERICAN                      

nls_numeric_characters              string                                

nls_sort                            string                                

nls_territory                       string  AMERICA                      

nls_time_format                     string                                

nls_time_tz_format                  string                                

nls_timestamp_format                string                                

nls_timestamp_tz_format             string                                

object_cache_max_size_percent       integer 10                            

object_cache_optimal_size           integer 102400                        

open_cursors                        integer 50                            

open_links                          integer 4                            

open_links_per_instance             integer 4                            

optimizer_features_enable           string  8.1.5                        

optimizer_index_caching             integer 0                            

optimizer_index_cost_adj            integer 100                          

optimizer_max_permutations          integer 80000                        

optimizer_mode                      string  CHOOSE                        

optimizer_percent_parallel          integer 0                            

optimizer_search_limit              integer 5                            

oracle_trace_collection_name        string                                

oracle_trace_collection_path        string  ?/otrace/admin/cdf            

oracle_trace_collection_size        integer 5242880                      

oracle_trace_enable                 boolean FALSE                        

oracle_trace_facility_name          string  oracled                      

oracle_trace_facility_path          string  ?/otrace/admin/fdf            

os_authent_prefix                   string                                

os_roles                            boolean FALSE                        

parallel_adaptive_multi_user        boolean FALSE                        

parallel_automatic_tuning           boolean FALSE                        

parallel_broadcast_enabled          boolean FALSE                        

parallel_execution_message_size     integer 2148                          

parallel_instance_group             string                                

parallel_max_servers                integer 0                            

parallel_min_percent                integer 0                            

parallel_min_servers                integer 0                            

parallel_server                     boolean FALSE                        

parallel_server_instances           integer 1                            

parallel_threads_per_cpu            integer 2                            

partition_view_enabled              boolean FALSE                        

plsql_load_without_compile          boolean FALSE                        

plsql_v2_compatibility              boolean FALSE                        

pre_page_sga                        boolean FALSE                        

processes                           integer 50                            

query_rewrite_enabled               boolean FALSE                        

query_rewrite_integrity             string  enforced                      

rdbms_server_dn                     string                                

read_only_open_delayed              boolean FALSE                        

recovery_parallelism                integer 0                            

remote_dependencies_mode            string  TIMESTAMP                    

remote_login_passwordfile           string  EXCLUSIVE                    

remote_os_authent                   boolean FALSE                        

remote_os_roles                     boolean FALSE                        

replication_dependency_tracking     boolean TRUE                          

resource_limit                      boolean FALSE                        

resource_manager_plan               string                                

rollback_segments                   string  aprbs1, aprbs2, aprbs3, aprbs4

row_locking                         string  always                        

serial_reuse                        string  DISABLE                      

serializable                        boolean FALSE                        

service_names                       string  auction                      

session_cached_cursors              integer 0                            

session_max_open_files              integer 10                            

sessions                            integer 60                            

shadow_core_dump                    string  partial                      

shared_memory_address               integer 0                            

shared_pool_reserved_size           string  2621440                      

shared_pool_size                    string  52428800                      

sort_area_retained_size             integer 0                            

sort_area_size                      integer 65536                        

sort_multiblock_read_count          integer 2                            

sql92_security                      boolean FALSE                        

sql_trace                           boolean FALSE                        

standby_archive_dest                string  ?/dbs/arch                    

star_transformation_enabled         string  FALSE                        

tape_asynch_io                      boolean TRUE                          

text_enable                         boolean FALSE                        

thread                              integer 0                            

timed_os_statistics                 integer 0                            

timed_statistics                    boolean FALSE                        

transaction_auditing                boolean TRUE                          

transactions                        integer 66                            

transactions_per_rollback_segment   integer 5                            

use_indirect_data_buffers           boolean FALSE                        

user_dump_dest                      string  /home/oracle/admin/auction/udu

utl_file_dir                        string                                

2. 오라클 백업 (홀백업)

# cd $ORACLE_HOME

# mkdir fullbackup

# cp $ORACLE_HOME/dbs/*.ora $ORACLE_HOME/fullbackup # cp $ORACLE_HOME/oradata/DB_NAME/*.ctl $ORACLE_HOME/fullbackup # cp $ORACLE_HOME/oradata/DB_NAME/*.log $ORACLE_HOME/fullbackup # cp $ORACLE_HOME/oradata/DB_NAME/*.dbf $ORACLE_HOME/fullbackup # cp $ORACLE_HOME/oradata/기타DB_NAME/DB_file $ORACLE_HOME/fullbackup

# 가장 손쉽고 확실한 방법은 백업에 관련된 디렉토리를 통으로 복사하는방법

$ORACLE_HOME/dbs/    $ORACLE_HOME/oradata/DB_NAME/

3. 오라클 설치

4. 오라클 복구 (완전복구)

# 데이타파일을 원래 경로에 복사한다.

# svrmgrl

# SVRMGR> startup mount;

# SVRMGR> recover database;

   media recovery complete

# SVRMGR> alter database open;