TABLE. Backup & Recovery

select status, instance_name from v$instance;
STATUS        INSTANCE_NAME
------------- ----------------
OPEN          ora11g


# DataFile Tablespace
SELECT a.file#, a.name file_name, b.name tbs_name, a.status, a.checkpoint_change#
FROM v$datafile a, v$tablespace b
WHERE a.ts# = b.ts#;
     FILE# FILE_NAME                                          TBS_NAME   STATUS        CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ---------- ------------- ------------------
         1 /u01/app/oracle/oradata/ora11g/system01.dbf        SYSTEM     SYSTEM                   2172194


# 객체의 테이블스페이스 정보
SELECT e.segment_name,f.tablespace_name, f.file_name
FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id
AND e.owner = 'HR' AND e.segment_name = 'EMP_60';
SEGMENT_NAME            TABLESPACE_NAME FILE_NAME
----------------------- --------------- --------------------------------------------------
EMPLOYEES               EXAMPLE         /u01/app/oracle/oradata/ora11g/example01.dbf


# 테이블이 어떤 데이터파일에 속해있는가
SELECT e.segment_name,f.tablespace_name, f.file_name, count(*)
FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id
AND e.owner = 'HR' and f.tablespace_name = 'DATA_TBS'
group by e.segment_name,f.tablespace_name,f.file_name;
SEGMENT_NAME            TABLESPACE_NAME FILE_NAME                                            COUNT(*)
----------------------- --------------- -------------------------------------------------- ----------
EMP_20240125            DATA_TBS        /u01/app/oracle/oradata/ora11g/data02.dbf                   3


# DataFile Online Backup
SELECT a.file#,a.name, a.checkpoint_change#,b.status,b.change#,b.time
FROM v$datafile a, v$backup b
WHERE a.file# = b.file#;
     FILE# NAME                                          CHECKPOINT_CHANGE# STATUS           CHANGE# TIME
---------- --------------------------------------------- ------------------ ------------- ---------- ---------
         1 /u01/app/oracle/oradata/ora11g/system01.dbf              2172194 NOT ACTIVE             0


# 테이블 용량
select bytes/1024/1024 mb
from dba_segments where segment_name = 'COPY_EMP' and owner = 'HR';
        MB
----------
     .0625



# Redo Log
SELECT thread#,sequence#,archived,status,first_change#,first_time,next_change#,next_time
FROM v$log;
   THREAD#  SEQUENCE# ARCHIVED  STATUS        FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
---------- ---------- --------- ------------- ------------- ----------- ------------ ---------
         1         16 YES       INACTIVE            2172191 24-JAN-24        2172194 24-JAN-24


# Archive Log
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log;
NAME                                           SEQUENCE# FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
--------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_50_1154915502.arc           50       1146954 15-JAN-24        1155615 16-JAN-24


# 아카이브로그 : 삭제내역 제외
SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log
where name is not null
order by next_time;
NAME                                           SEQUENCE# FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
--------------------------------------------- ---------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_50_1154915502.arc           50       1146954 15-JAN-24        1155615 16-JAN-24




ls /home/oracle/arch1

! ls /home/oracle/arch1

ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER SYSTEM SWITCH LOGFILE;


# Redo Log History
SELECT * FROM v$log_history;
     RECID      STAMP    THREAD#  SEQUENCE# FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- ---------- ------------- ----------- ------------ ----------------- ---------
         1 1154915606          1          1        635002 07-DEC-23         664028            635002 07-DEC-2


# Redo Log File, Status
SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
    b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b
WHERE a.group# = b.group#
ORDER BY 1;
    GROUP#  SEQUENCE# MEMBER                                                     MB ARCHIVED  STATUS
---------- ---------- -------------------------------------------------- ---------- --------- -------------
         1         16 /u01/app/oracle/oradata/ora11g/redo01.log                  50 YES       INACTIVE


# Resetlogs
select incarnation#,resetlogs_change#,resetlogs_time,status,resetlogs_id,flashback_database_allowed
from v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS        RESETLOGS_ID FLASHBACK_DATABASE_ALLOWED
------------ ----------------- -------------- ------------- ------------ --------------------------
           1           1275812 29-JAN-24      PARENT          1159525055 NO



# Undo Segment
SELECT segment_id, segment_name, owner, tablespace_name, status
FROM dba_rollback_segs;


# 사용중인 Undo Segment
SELECT s.username,s.sid,s.serial#,r.name,t.xidusn,t.ubafil,t.ubablk,t.used_ublk
FROM v$session s, v$transaction t, v$rollname r
WHERE s.taddr = t.addr
AND t.xidusn = r.usn;
SEGMENT_ID SEGMENT_NAME            OWNER      TABLESPACE_NAME STATUS
---------- ----------------------- ---------- --------------- -------------
         0 SYSTEM                  SYS        SYSTEM          ONLINE
         1 _SYSSMU1_886085095$     PUBLIC     UNDOTBS         ONLINE


#
SELECT * FROM v$recover_file;



SELECT name FROM v$controlfile;
NAME
---------------------------------------------
/u01/app/oracle/oradata/ora11g/control01.ctl


SELECT * FROM v$tempfile;


SELECT * FROM v$logfile;
    GROUP# STATUS        TYPE    MEMBER                                             IS_RECOVERY_DEST_FILE
---------- ------------- ------- -------------------------------------------------- ----------------------
         1               ONLINE  /u01/app/oracle/oradata/ora11g/redo01.log          NO


SELECT * FROM dba_tablespaces;


SELECT * FROM v$tablespace;
       TS# NAME                                          INC BIGFILE        FLA ENC
---------- --------------------------------------------- --- -------------- --- ---
         0 SYSTEM                                        YES NO             YES


SELECT file#, name, status, checkpoint_change# FROM v$datafile;
     FILE# NAME                                          STATUS        CHECKPOINT_CHANGE#
---------- --------------------------------------------- ------------- ------------------
         1 /u01/app/oracle/oradata/ora11g/system01.dbf   SYSTEM                   2172194


SELECT tablespace_name,file_name,bytes/1024/1024 mb FROM dba_data_files;
TABLESPACE_NAME FILE_NAME                                                  MB
--------------- -------------------------------------------------- ----------
HRM_TBS         /u01/app/oracle/oradata/ora11g/hrm_tbs01.dbf               10