Re: 백업과 복구 이수자 평가1번 문제

[22기_정민수]

# 사전 정보 확인
## oracle 12c
[sql]
SELECT tablespace_name, status FROM dba_tablespaces WHERE TABLESPACE_NAME='TS20000';


-- CHARACTER SET 확인
select *
from database_properties
where property_name='NLS_CHARACTERSET';

select *
from database_properties
where property_name='NLS_NCHAR_CHARACTERSET';


# ts20000 없을시
CREATE TABLESPACE ts20000 DATAFILE '/u01/app/oracle/oradata/ora12/ts20000.dbf'
ALTER TABLESPACE ts20000 READ ONLY;

[sh]
# DDL + 데이터 + 인덱스 이관
expdp directory=datapump_dir \
dumpfile=ts20000.dmp \
transport_tablespace=y \
tablespaces=ts20000


# META 정보 이관(SEQEUNCE,VIEW,PROCEDURE,FUNCTION,PACKAGE)
expdp directory=datapump_dir \
dumpfile=ts20000_meta.dmp \
content=metadata_only \
schemas=SH \
EXCLUDE=TABLE,INDEX,CONSTRAINT


# 데이터 파일 이관
scp /u01/app/oracle/oradata/ora12/ts20000.dbf oracle@192.168.23.175:/home/oracle/oradata/

# 덤프 파일 이관
scp /home/oracle/pump_ora12c/ts20000* oracle@192.168.23.175:/home/oracle/pump_ora19c/




## oracle 19c 
[sql]
-- 계정생성 및 권한 부여
drop user sh2 cascade;

CREATE USER sh2
IDENTIFIED BY sh2
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT connect, resource TO sh2;

GRANT create view TO sh2;

GRANT create sequence TO sh2;

GRANT CREATE DATABASE LINK TO sh2;

# IMPORT 
[sh]

impdp transport_datafiles='/home/oracle/oradata/ts20000.dbf' directory=data_pump_dir dumpfile=ts20000.dmp remap_schema=sh:sh2

impdp directory=data_pump_dir dumpfile=ts20000_meta.dmp remap_schema=sh:sh2



[sql]
-- 검수
-- sys
CREATE DATABASE LINK sh_link CONNECT TO sh IDENTIFIED BY sh USING '192.168.23.225/ora12';


-- sh2
SELECT object_name, object_type FROM user_objects@sh_link WHERE object_type != 'DATABASE LINK'
MINUS
SELECT object_name, object_type FROM user_objects WHERE object_type != 'DATABASE LINK';

SELECT object_name, object_type FROM user_objects WHERE object_type != 'DATABASE LINK' AND OBJECT_NAME NOT LIKE 'SYS%'
MINUS
SELECT object_name, object_type FROM user_objects@sh_link WHERE object_type != 'DATABASE LINK'AND OBJECT_NAME NOT LIKE 'SYS%';



SELECT constraint_name FROM user_constraints@sh_link
MINUS 
SELECT constraint_name FROM user_constraints;


SELECT constraint_name FROM user_constraints
MINUS 
SELECT constraint_name FROM user_constraints@sh_link;

SELECT 'channels'                   AS tbl, COUNT(*) AS cnt FROM channels
UNION ALL
SELECT 'countries'                  , COUNT(*) FROM countries
UNION ALL
SELECT 'products'                   , COUNT(*) FROM products
UNION ALL
SELECT 'times'                      , COUNT(*) FROM times
UNION ALL
SELECT 'promotions'                 , COUNT(*) FROM promotions
UNION ALL
SELECT 'customers'                  , COUNT(*) FROM customers
UNION ALL
SELECT 'costs'                      , COUNT(*) FROM costs
UNION ALL
SELECT 'sales'                      , COUNT(*) FROM sales
UNION ALL
SELECT 'supplementary_demographics' , COUNT(*) FROM supplementary_demographics;

TBL                               CNT  
-------------------------- ----------  -------------------------- ----------
channels                            5  channels                            5
countries                          35  countries                          35
products                           72  products                           72
times                            1826  times                            1826
promotions                        503  promotions                        503
customers                       55500  customers                       55500
costs                           82112  costs                           82112
sales                          918843  sales                          918843
supplementary_demographics       4500  supplementary_demographics       4500


SELECT 'channels'                   AS tbl, COUNT(*) AS cnt FROM channels@sh_link
UNION ALL
SELECT 'countries'                  , COUNT(*) FROM countries@sh_link
UNION ALL
SELECT 'products'                   , COUNT(*) FROM products@sh_link
UNION ALL
SELECT 'times'                      , COUNT(*) FROM times@sh_link
UNION ALL
SELECT 'promotions'                 , COUNT(*) FROM promotions@sh_link
UNION ALL
SELECT 'customers'                  , COUNT(*) FROM customers@sh_link
UNION ALL
SELECT 'costs'                      , COUNT(*) FROM costs@sh_link
UNION ALL
SELECT 'sales'                      , COUNT(*) FROM sales@sh_link
UNION ALL
SELECT 'supplementary_demographics' , COUNT(*) FROM supplementary_demographics@sh_link;
TBL                               CNT


TBL                               CNT
-------------------------- ----------
channels                            5
countries                          35
products                           72
times                            1826
promotions                        503
customers                       55500
costs                           82112
sales                          918843
supplementary_demographics       4500