AWR 데이터 백업 및 복구
AWR 백업
- awrextr.sql 스크립트를 수행하면, 데이터 펌프를 이용하여 AWR 데이터를 덤프 파일로 저장.
SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will extract the AWR data for a range of snapshots ~
~ into a dump file. The script will prompt users for the ~
~ following information: ~
~ (1) database id ~
~ (2) snapshot range to extract ~
~ (3) name of directory object ~
~ (4) name of dump file ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id DB Name
----------- ------------
1490765173 ORATEMP
Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id DB Name Host
------------ ------------ ------------
* 1490765173 ORATEMP db1
The default database id is the local one: '1490765173'. To use this
database id, press <return> to continue, otherwise enter an alternative.
Enter value for dbid: 1490765173
Using 1490765173 for Database ID
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
DB Name Snap Id Snap Started
------------ --------- ------------------
ORATEMP 38641 14 Dec 2012 00:00
38642 14 Dec 2012 00:20
38643 14 Dec 2012 00:40
38644 14 Dec 2012 01:00
...
DB Name Snap Id Snap Started
------------ --------- ------------------
ORATEMP 38674 14 Dec 2012 11:00
38675 14 Dec 2012 11:20
38676 14 Dec 2012 11:40
38677 14 Dec 2012 12:00
38678 14 Dec 2012 12:20
38679 14 Dec 2012 12:40
38680 14 Dec 2012 13:00
38681 14 Dec 2012 13:20
38682 14 Dec 2012 13:40
38683 14 Dec 2012 14:00
38684 14 Dec 2012 14:20
DB Name Snap Id Snap Started
------------ --------- ------------------
ORATEMP 38685 14 Dec 2012 14:40
38686 14 Dec 2012 15:00
...
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 38674
Begin Snapshot Id specified: 38674
Enter value for end_snap: 38684
End Snapshot Id specified: 38684
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------
-------------------------------------------------
DATA_PUMP_DIR /usr1/oracle/112/rdbms/log/
EXP_DIR /usr1/oracle/DBA/dump_dir
ORACLE_OCM_CONFIG_DIR /usr1/oracle/112/ccr/state
Choose a Directory Name from the above list (case-sensitive).
Enter value for directory_name: EXP_DIR
Using the dump directory: EXP_DIR
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_38674_38684.
To use this name, press <return> to continue, otherwise enter
an alternative.
Enter value for file_name: awr_dump_38674_38684.dmp
Using the dump file prefix: awr_dump_38674_38684.dmp
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /usr1/oracle/DBA/dump_dir
| awr_dump_38674_38684.dmp.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Extract Started ...
|
| This operation will take a few moments. The
| progress of the AWR extract operation can be
| monitored in the following directory/file:
| /usr1/oracle/DBA/dump_dir
| awr_dump_38674_38684.dmp.log
|
End of AWR Extract
SQL>
SQL> exit
- 오라클 디렉토리와 데이터 펌프
- 오라클 디렉토리는 os 파일 스스템 디렉토리와 오라클을 연결하는 역활
- exp pump 시에는 지정된 디렉토리에 펌프 파일 저장하며,
imp pump 시에는 지정된 디렉토리에서 파일을 읽어 데이터베이스에 적재.
- 오라클 디렉토리를 생성하기 위해서는
- 오라클의 CREATE ANY DIRECTORY 권한
- 오라클 OS 유저가 지정된 OS 디렉토리에 읽기와 쓰기를 할 수 있는 권한
- 생성
CREATE OR REPLACE DIRECTORY PUMP_AWR AS '/PUMP';
- 생성된 오라클 디렉토리를 공용 디렉토리로 사용하기 위한 권한 부여
GRANT READ, WRITE ON DIRECTORY PUMP_AWR TO PUBLIC;
- 삭제
DROP DIRECTORY PUMP_AWR;*
AWR 데이터 복구
- awrload.sql 스크립트를 사용하여 적재.
- 백업된 AWR 데이터가 저장되어 있던 데이터베이스 ID와 동일한 데이터베이스 ID를 가지는 데이터베이스에 적재할 수 없다.
SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will load the AWR data from a dump file. The ~
~ script will prompt users for the following information: ~
~ (1) name of directory object ~
~ (2) name of dump file ~
~ (3) staging schema name to load AWR data into ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------
-------------------------------------------------
DATA_PUMP_DIR /usr1/oracle/112/rdbms/log/
EXP_DIR /usr1/oracle/DBA/dump_dir
ORACLE_OCM_CONFIG_DIR /usr1/oracle/112/ccr/state
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: EXP_DIR
Using the dump directory: EXP_DIR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: awr_dump_38674_38684
Loading from the file name: awr_dump_38674_38684.dmp
Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.
The default staging schema name is AWR_STAGE.
To use this name, press <return> to continue, otherwise enter
an alternative.
Enter value for schema_name:
Using the staging schema name: AWR_STAGE
Choose the Default tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE users's default tablespace. This is the
tablespace in which the AWR data will be staged.
TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
------------------------------ --------- ------------------
TS_PERF PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT
Pressing <return> will result in the recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: USERS
Using tablespace USERS as the default tablespace for the AWR_STAGE
Choose the Temporary tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporary tablespace.
TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
------------------------------ --------- -----------------------
TEMP TEMPORARY *
Pressing <return> will result in the database's default temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: TEMP
Using tablespace TEMP as the temporary tablespace for AWR_STAGE
... Creating AWR_STAGE user
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /usr1/oracle/DBA/dump_dir
| awr_dump_38674_38684.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Load Started ...
|
| This operation will take a few moments. The
| progress of the AWR load operation can be
| monitored in the following directory/file:
| /usr1/oracle/DBA/dump_dir
| awr_dump_38674_38684.log
|
... Dropping AWR_STAGE user
End of AWR Load
-- AWR_STAGE 유저로 임포트 펌프를 실행 후, 적재된 소유자를 SYS유저로 변경.
-- 작업이 완료되면 AWR_STAGE유저와 해당 유저가 가지고 있는 모든 오브젝트를 삭제.
-- AWR 백업 수행 데이터베이스 ID와 동일한 데이터베이스 ID를 가지는 데이터베이스에 적재 작업을 수행할 경우
-- 임포트 펌프 수행 후 적재된 데이터를 SYS 소유로 변경하는 과정에서 다음과 같은 에러 발생
ERROR at line 1:
ORA-20105: unable to move AWR data to SYS
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1749
ORA-30107: not allowed to move AWR data for local dbid
ORA-06512: at line 3
-- 가장 마지막 단계에서 AWR_STAGE 유저를 삭제 하므로 SYS 유저로 소유자 변경이 안 된 적재된 데이터는 모두 삭제.
-- 즉 awrload.sql 스크립트 수행 결과로 적재된 모든 데이터는 삭제된다.