Export tables with the expdp command and a parfile

Parfile name export.par
userid=system/password
SCHEMAS=UAT_2016_XXX
DIRECTORY=FULL_BACKUPS
DUMPFILE=UAT_2016_XXX_%U.dmp
LOGFILE=UAT_2016_XXX.log
INCLUDE=TABLE:"IN('AAA','BBB','CCC','DDD','EEE','FFF')"

COMPRESSION=ALL

PARALLEL=8

Command
expdp parfile=export.par

To see more options
expdp help=y

DROP and Recreate EM repository

Stop dbconsole, if running and the following process

ps -ef | grep console;                                            

ps -ef| grep emwd;

ps -ef | grep emagent;

ps -ef |grep java;

Logon SQLPLUS as sysdba or sys and execute below

drop user sysman cascade;

drop role MGMT_USER;

drop user MGMT_VIEW cascade;

drop public synonym MGMT_TARGET_BLACKOUTS;

drop public synonym SETEMVIEWUSERCONTEXT; 

drop public synonym MGMT_target_blackouts;                   

drop public synonym mgmt_severity_array;

drop public synonym mgmt_guid_obj;  

Verify any leftover object owned by SYSMAN

SELECT owner,TABLE_NAME, synonym_name name FROM dba_synonyms WHERE table_owner = 'SYSMAN';

if you notice any public synonym or any rows returned by the above query .

DECLARE

CURSOR c1 IS

SELECT owner, synonym_name name

FROM dba_synonyms

WHERE table_owner = 'SYSMAN';

BEGIN

FOR r1 IN c1 LOOP

IF r1.owner = 'PUBLIC' THEN

EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;

ELSE

EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;      

END IF;

END LOOP;

END;

/

emca -deconfig dbcontrol db -repos drop

emca -config dbcontrol db -repos create

MEMORY_TARGET (SGA_TARGET) or HugePages – which to use?

MEMORY_TARGET (SGA_TARGET) or HugePages – which to use?
Oracle 10g introduced the SGA_TARGET and SGA_MAX_SIZE parameter which dynamically re-sized many SGA components on demand. With 11g Oracle developed this feature further to include the PGA as well – the feature is now called “Automatic Memory Management” (AMM) which is enabled by setting the parameter MEMORY_TARGET.

Unfortunately using MEMORY_TARGET or MEMORY_MAX_SIZE together with Huge Pages is not supported. You have to choose either Automatic Memory Management or HugePages. In this post i´d like to discuss AMM and Huge Pages.

Query available snapshots and Removing a snapshot

To query the available snapshot you can use this query:

select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by snap_id;

To remove a snapshot:

exec dbms_workload_repository.drop_snapshot_range  (low_snap_id=>1, high_snap_id=>10);

Manually creating a snapshot

Manually creating a snapshot
You can of course create a snapshot manually by executing:

exec dbms_workload_repository.create_snapshot;

SCP as a background process without using password

SCP as a background process without using password

To execute any linux command in background we use nohup as follows:

$ nohup SOME_COMMAND &
But the problem with scp command is that it prompts for the password (if password authentication is used). So to make scp execute as a background process do this:

$ nohup scp file_to_copy user@server:/path/to/copy/the/file > nohup.out 2>&1
Then press ctrl + z which will temporarily suspend the command, then enter the command:

$ bg
This will start executing the command in backgroud

Changing the AWR settings

Changing the snapshot interval and retention time
For changing the snapshot interval and/or the retention time use the following syntax:

exec dbms_workload_repository.modify_snapshot_settings(interval => 60, retention => 525600);

The example printed above changes the retention time to one year (60 minutes per hour * 24 hours a day * 365 days per year = 525600). It does not alter the snapshot interval (60 minutes is the default). If you want to you can alter this as well but keep in mind you need additional storage to do so. Setting the interval value to zero completely disables data collection.