Transporting Automatic Workload Repository Data

To extract AWR data:
1.     At the SQL prompt, enter:
        SQL> @$ORACLE_HOME/rdbms/admin/awrextr.sql
2.     Specify the database from which AWR data will be extracted:
        Enter value for db_id: 1377863381
3.     Specify the number of days for which you want to list snapshot IDs.
        Enter value for num_days: 2
4.     Define the range of snapshots for which AWR data will be extracted by specifying a beginning and ending snapshot ID:
        Enter value for begin_snap: 30
        Enter value for end_snap: 40
5.     Specify the directory object pointing to the directory where the export dump file will be stored:
        Enter value for directory_name: DATA_PUMP_DIR
6.     Specify a prefix for the name of the export dump file (the .dmp suffix will be automatically appended):
        Enter value for file_name: awrdata_30_40

To load AWR data:
1.     At the SQL prompt, enter:
        Sql> @$ORACLE_HOME/rdbms/admin/awrload.sql
2.     Specify the directory object pointing to the directory where the export dump file is located:
        Enter value for directory_name: DATA_PUMP_DIR
3.     Specify a prefix for the name of the export dump file (the .dmp suffix will be automatically appended):
        Enter value for file_name: awrdata_30_40
4.     Specify the name of the staging schema where AWR data will be loaded:
        Enter value for schema_name: AWR_STAGE
        NOTE: a staging schema namedAWR_STAGE will be created where AWR data will be loaded.
5.     Specify the default tablespace for the staging schema:
        Enter value for default_tablespace: SYSAUX
6.     Specify the temporary tablespace for the staging schema:
        Enter value for temporary_tablespace: TEMP

NOTE: A staging schema named AWR_STAGE will be created where AWR data will be loaded. After AWR data is loaded into the AWR_STAGE schema, the data will be transferred into AWR tables in the SYSschema. After AWR data is loaded, the staging schema will be dropped automatically.

Comments

Post a Comment

Popular posts from this blog

 Exadata  Oracle Exadata Smart Flash Cache * Exadata cell -> Flash LUN -> Cell Disk -> Flash Cache/Flash Log  * CELL_FLASH_CACHE - – DEFAULT (LRU), KEEP, NONE * ALTER TABLE <TABLE_NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) * ALTER INDEX <INDEX NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) Smart Flash Cache Mode: 1. Write Through Flash Cache Mode (Default):  Write on Disk -> Copy to Flash Cache  * excellent for absorbing repeated random reads  * commonly found in OLTP applications. * dcli -g cell_servers -l celladmin cellcli -e "list cell attributes flashcachemode" * Steps to enable Write-Through Mode CellCLI> ALTER FLASHCACHE ALL FLUSH CellCLI> DROP FLASHCACHE CellCLI> ALTER CELL SHUTDOWN SERVICES CELLSRV CellCLI> ALTER CELL flashCacheMode=WriteThrough CellCLI> ALTER CELL STARTUP SERVICES CELLSRV CellCLI> CREATE FLASHCACHE ALL 2. Write Back Flash Cache Mode:  Write on Flash -> Copy to Disk  * best ...
Read more

Database Network : Configuration Parameters

LSNRCTL> command [listener_name] lsnrctl @file_name  #to execute in batch mode. REM or # to identify comments in the batch script Listener.ora file parameters: ·          PASSWORDS_listener_name  #an unencrypted password ·          LSNRCTL> change_password #to establish an encrypted password ·          LSNRCTL> SAVE_CONFIG  #Any changes are stored in listener.ora ·          LSNRCTL> SET PASSWORD  #If a password is set, then issue SET PASSWORD command before changing the password. Use the command SET PASSWORD prior to privileged Listener Control utility commands ·          Reload : the database services, instances, service handlers, and listening endpoints that were dynamically registered with the listener will be unregistered and subseque...
Read more

Undo Segments : Drop Problematic Undo Segments

ERROR about to recover undo segment 98 status:6 inst:0 mark undo segment 98 as available status:6 ret:1  about to recover undo segment 46 status:6 inst:0 mark undo segment 46 as available status:6 ret:1   SQL> select * from V$FAST_START_TRANSACTIONS; USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS CON_ID ---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- 46 46 2313064 RECOVERING 505 24992423 77 5586 0 0 0 10001000684B2300 0000000000000000 1 0 98 25 1352150 RECOVERING 0 226231 78 5586 0 0 0 30001900D6A11400 0000000000000000 1 0   SQL> SELECT segment_name, tablespace_name  FROM dba_rollback_segs  WHERE segment_id IN (98, 46); SEGMENT_NAME               TABLESPACE_NAME ------------------------------ ----------------------------...
Read more