Oracle DB 생성, 백업, 복구

명령어 정리

drop user apa55353 cascade;
-- 유저 삭제 후 새로 시작
create user apa55353 identified by  "tiger" default tablespace apa55353_ts temporary tablespace temp;
select * from dba_users;
grant connect, resource to apa55353;
grant imp_full_database, exp_full_database to apa55353;
alter user apa55353 quota 30G on apa55353_ts;
alter user apa55353 quota 5G on system ;

-- 터미널 sqlplus '/as sysdba'에서만 가능,
GRANT SELECT ON V_$SESSION TO apa55353;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO apa55353;
GRANT SELECT ON V_$SQL TO apa55353;


-- 생성 확인용
SELECT * FROM all_TABLES where owner='APA55353';
select * from user_tables;
select * from dba_tablespaces;
SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'APA55353';
SELECT COUNT(*) FROM APA55353.CUST;
SELECT COUNT(*) FROM APA55353.SALES_ORDER;

-- 테이블 생성 예시
create table tb_a 
(id number,
nam varchar2(100)
);
SELECT COUNT(*) FROM APA55353.TB_A;

터미널에서 SFTP 통신으로 파일 복사하기

////// cmd ///////
C:\Users\user>cd apa55353.dmp 경로

C:\Users\user\apa55353.dmp 경로>sftp -o port=22 root@아이피 주소

sftp> pwd
Remote working directory: /root
sftp> cd /oradata
sftp> pwd
Remote working directory: /oradata
sftp> put apa55353.dmp
Uploading apa55353.dmp to /oradata/apa55353.dmp
apa55353.dmp                                             100%  385MB  28.3MB/s   00:13
sftp>
C:\Users\user\Desktop\211213>

TS 생성, 백업, 복구

※ 원격파일전송			sftp -o port=22 username@000.000.000.000
				
						bi	필수 모드 변경
						mget	서버에서 현재 접속 클라이언트 PC로 파일 가져오기
						mput	클라이언트(현재 접속 Win PC)에서 서버로 올리기
						ls	서버의 폴더 및 파일 
						!dir	클라이언트의 폴더 및 파일 정보


[OS Prompt] sqlplus '/as sysdba'

--------------------------------
-- TableSpace 생성
--------------------------------
cd /ordata/
mkdir apa55353

SQL> create tablespace apa55353_ts  datafile '/oradata/apa55353/apa55353_data.dbf' size 1500m;

[OS Prompt] cd /oradata/apa55353
[OS Prompt] pwd
[OS Prompt] du -sm ./apa55353*.*

SQL> select TABLESPACE_NAME , file_name, bytes/1024/1024 mb 
     from   dba_data_files 
     where  tablespace_name = 'APA55353_TS';
     
SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT 
     from    dba_tablespaces;

--------------------------------
-- TableSpace 크기 확대
--------------------------------


SQL> alter tablespace apa55353_ts add datafile '/oradata/apa55353/apa55353_data1.dbf' size 1G;
SQL> alter database datafile '/oradata/apa55353/apa55353_data.dbf' resize 2G;


-----------------------------------------------------------------------------------------------------------------------
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-----------------------------------------------------------------------------------------------------------------------
--------------------------------
--Oracle User 삭제/초기화
--------------------------------
[OS Prompt] sqlplus '/as sysdba'
SQL> drop user apa55353 cascade; ← ■ 필요시 수행

--------------------------------
--Oracle User 생성
--------------------------------
[OS Prompt] sqlplus '/as sysdba'

SQL> create user apa55353 identified by  "tiger" default tablespace apa55353_ts temporary tablespace temp ;

SQL> grant connect, resource to apa55353;			--기본 접속, 오브젝트 권한

SQL> GRANT SELECT ON V_$SESSION TO apa55353;			--성능 권한
SQL> GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO apa55353;
SQL> GRANT SELECT ON V_$SQL TO apa55353;

SQL> grant imp_full_database, exp_full_database to apa55353;	--exp, imp 권한 

SQL> alter user apa55353 quota 30G on apa55353_ts ;		--용량 권한
SQL> alter user apa55353 quota 5G on system ;


SQL> set pagesize 50
SQL> col username for a20
SQL> col account_status for a17
SQL> col default_tablespace for a20
SQL> col temporary_tablespace for a15
SQL> select username,  default_tablespace from dba_users where username = 'APA55353';

--------------------------------
--Oracle User 확인
--------------------------------
SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT"
SQL>

	ETC) 사용자에게 부여된 ROLE 확인
		SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'APA55353';

	Etc)  ROLE에 부여된 시스템 권한 확인
		SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'RESOURCE' ;
		SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'CONNECT' ;
		SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'EXP_FULL_DATABASE' ;
		SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'IMP_FULL_DATABASE' ;

	ETC) 사용자에게 부여된 시스템 권한 확인
		SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'APA55353' ;

	ETC) 타 사용자에게 부여한 객체(테이블등) 권한 확인
		SELECT * FROM DBA_TAB_PRIVS
		WHERE OWNER = '테이블소유자명' ;
		또는,
		SELECT * FROM DBA_TAB_PRIVS
		WHERE GRANTEE = '권한부여자명' ;




--------------------------------
--실험 기본 데이터 생성/복구
--------------------------------
[prompt] su - oracle
[prompt] cd /oradata/imp_exp
[prompt] whoami
[prompt] imp apa55353/tiger file=apa55353.dmp log=apa55353_imp.log.log buffer=102400000  commit=n fromuser=apa55353 touser=apa55353 ignore=yes
	[prompt] imp apa55353/xxxxx file=apa55353.dmp log=apa55353_imp.log.log buffer=102400000  commit=n fromuser=apa55353 touser=apa55353 ignore=yes tables=ACCOUNT
	[prompt] imp apa55353/xxxxx file=apa55353.dmp log=apa55353_imp.log.log buffer=102400000  commit=n fromuser=apa55353 touser=apa55353 ignore=yes tables=Dept
	[prompt] imp apa55353/xxxxx file=apa55353.dmp log=apa55353_imp.log.log buffer=102400000  commit=n fromuser=apa55353 touser=apa55353 ignore=yes tables=Emp  
	[prompt] imp apa55353/xxxxx file=apa55353.dmp log=apa55353_imp.log.log buffer=102400000           fromuser=apa55353 touser=apa55353 rows=no	← ■ 오브젝트만 추가시

--------------------------------
--실험 기본 데이터 백업 
--------------------------------
exp apa55353/tiger owner=apa55353 log=apa55353_exp.log file=apa55353_new.dmp direct=y statistics=none  ← ■ 필요시 수행