ORACLE_HOME Permissions Required to discover our DB Listener in Enterprise Manager(EM)

Today We have given the database details to EM Team to discover our database & listener.They have discovered the database,but EM had shown DB Listener as down even-though it is up.So when we troubleshooting, we found em agent need  permission for few lib  & mesg files.


$ chmod 775 $ORACLE_HOME/network/mesg
$ cd $ORACLE_HOME/network/mesg
$ chmod 666 *
$ chmod 775 $ORACLE_HOME/network/lib
$ cd $ORACLE_HOME/network/lib
$ chmod 666 *
$ cd $ORACLE_HOME/lib
$  chmod  644 *


P.S:Please make sure we are using proper  umask (umask 022 )when we install oracle_home.so that we can avoid this issue.

Import Schema from one Tablespace to another Tablespace

We had a requirement to move schema from one tablespace to another.We can achieve this using expdp & impdp.

Current tablespace       New Tablespace

IAM_LOB                    JAY_LOB
IAM_TABLE               JAY_TABLE
IAM_INDEX               JAY_INDEX

EXPORT SCHEMA USING EXPDP:

Create a parameter file with below parameters
oracle:JAYDEV> more para.par
DIRECTORY=JAY_DIR
SCHEMAS=JAY
DUMPFILE=JAY_EXPORT_%U.dmp
FILESIZE=8G
LOGFILE=EXPORT_JAY.log
JOB_NAME=JAY_EXPORT


expdp "'/as sysdba'" parfile=para.par




SQL> select tablespace_name from dba_ts_quotas where username='JAY';

TABLESPACE_NAME
------------------------------
IAM_LOB
IAM_TABLE
IAM_INDEX

select count(*) from dba_objects where owner='JAY';

 COUNT(*)
----------
       846

Now drop the user jay & tablespaces.

drop user jay cascade;
drop the old tablespaces.


SQL> select count(*) from dba_tables where tablespace_name like 'IAM%';

  COUNT(*)
----------
         0

SQL> select count(*) from dba_segments where tablespace_name like 'IAM%';

  COUNT(*)
----------
         0

SQL>  select count(*) from dba_indexes  where tablespace_name like 'IAM%';

  COUNT(*)
----------

         0

SQL> DROP TABLESPACE IAM_INDEX INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE IAM_LOB INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE IAM_TABLE INCLUDING CONTENTS AND DATAFILES;


Tablespace dropped.


IMPORT SCHEMA USING IMPDP (remap_tablespace):

Create a parameter file with below parameters .


oracle:JAYDEV> more imp.par
DIRECTORY=JAY_DIR
DUMPFILE=JAY_EXPORT_%U.dmp
REMAP_TABLESPACE=IAM_TABLE:JAY_TABLE,IAM_INDEX:JAY_INDEX,IAM_LOB:JAY_LOB
LOGFILE=IMPORT_VEMTEST.log
JOB_NAME=JAY_IMPORT

impdp "'/as sysdba'" parfile=imp.par

verify the object_count 

Revoking RESOURCE/DBA role removes UNLIMITED TABLESPACE (Grant)

if you grant a user RESOURCE or DBA role, the user then also has the UNLIMITED TABLESPACE privilege.  OK, that's fine.  However, if you revoke either RESOURCE or DBA, the UNLIMITED TABLESPACE priv is also revoked, even if it was given indepently in a grant statement.

SQL> ALTER USER JAY QUOTA UNLIMITED ON JAY_INDEX;

User altered.

SQL> select tablespace_name from dba_ts_quotas where username='JAY';

TABLESPACE_NAME
------------------------------
JAY_INDEX

SQL> grant dba to JAY;  

Grant succeeded.

SQL> revoke dba from JAY;

Revoke succeeded.

SQL> select tablespace_name from dba_ts_quotas where username='JAY';

no rows selected

How to send email from APEX?

Command to Send EMail:

DECLARE
 l_id number;
BEGIN
    l_id := APEX_MAIL.SEND (p_to        => 'srm.jay@gmail.edu',  
                            p_from      => 'srm.jay@stanford.edu',  
                            p_subj      => 'APEX_MAIL from imguat',
                            p_body      => 'Please review the attachment.',
                            p_body_html => '<b>Please</b> review the attachment');    
COMMIT;
END;
/

To send a mail immediately from the apex mail queue:

Oracle Application Express logs successfully submitted message in the table APEX_MAIL_LOG with the timestamp reflecting your server's local time. 

begin
APEX_MAIL.PUSH_QUEUE('localhost','25');
end;
/

Please check the apex_mail_log table for any errors or warnings.

select mail_send_error from APEX_MAIL_LOG ; 

How to Export DB_LINKS

We used to refresh our DEV database from production.so we need to preserve the DEV dblinks before we start refresh . we need to restore after refresh.

We can export the all the dblinks by using expdp.

Please create a para.par file with below commands.

full=y
INCLUDE=DB_LINK:"IN(SELECT db_link FROM dba_db_links)"

Then execute below command to export

expdp "'/as sysdba'"  directory=DATA_PUMP_DIR dumpfile=dblink-restore_JAYDEV.dmp logfile=dblink-restore_JAYDEV.log parfile=para.par


After Refresh,Please execute below impdp command to recreate dblinks.

Note: DBLINK we are going to import should not exist in the target database(DEV).please drop dblinks if it already exists.

impdp "'/as sysdba'" directory=DATA_PUMP_DIR dumpfile=dblink-restore_JAYDEV.dmp logfile=dblink_restore_JAYDEV.log


ALTERNATE METHOD:

Before Refresh,We can take ddl of dblinks  by using below query and spool it. Then we can connect to each schema and recreate the dblinks after refresh.

set long 100000 head off
spool exp_dblinks.dat
SELECT DBMS_METADATA.GET_dDL('DB_LINK', DB_LINK,'PUBLIC') FROM DBA_DB_LINKS WHERE OWNER = 'PUBLIC';


SELECT DBMS_METADATA.GET_dDL('DB_LINK', DB_LINK,'XXRA') FROM DBA_DB_LINKS WHERE OWNER = 'JAY';

Steps to Create Incident Rule in Oracle EM For Linux Host Monitoring.

In our environment,Already we had a  default template which will be applied to all the unix hosts.so currently As a DBA,we have to know the server status (up or down),High cpu & memory alerts,disk space alerts.so for that,we need to create rule.

Note:I haven't provided steps on how  to create template and apply to the target.I have just given steps to apply a rule for the servers where metrics has been collected by a template.In future,I will provide the steps to create a template as well.


Setup -> Incidents -> Incident Rules
Create Rule Set
Name: JAY_DEV_HOST_MONITORING
Applies to: Targets
Targets tab:
Specific targets radio button selected
Add Groups Click on +Add icon
Target type: Group
Target Name: g_jay_dev_host_unix

under Rules Tab
click on Create
Select: Incoming events and updates to events
Checkbox: Type: Metric Alert
All events of type Metric Alert
Advanced Selection Options
checkbox: Severity In Critical;Warning;
Next
Create New Rule - Add Actions
Click Add
Leave everything to default
Under Advanced Notifications
Select checkbox for
provide email address
Continue
Click Next
Name:METRIC_RULE_ALERT
Click Next
Click Continue
Click OK
Click Save


under Rules Tab
click on Create
Select: Incoming events and updates to events
Checkbox: Type: Target Availablity
Specific events of type Target Availability
click on Add
select Target type as Host
choose Agent unreachable,Agent Back Up from Down,Down
click OK
Next
Create New Rule - Add Actions
Click Add
Leave everything to default
Under Advanced Notifications
Select checkbox for
provide email address
Under Repeat Notifications
Mark the checkbox for Send Repeat notifications
Continue
Click Next
Name:Target_Availablity_Status
Click Next
Click Continue
Click OK
Click Save

Exception in thread "main" java.lang.OutOfMemoryError while applying weblogic psu patch (bsu.sh)

When applying weblogic patch using smart update (bsu.sh),  through command line i received an error

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded       
 at java.lang.reflect.Method.copy(Method.java:151)        
 at java.lang.reflect.ReflectAccess.copyMethod(ReflectAccess.java:136)        
 at sun.reflect.ReflectionFactory.copyMethod(ReflectionFactory.java:300)         
at java.lang.Class.searchMethods(Class.java:2799)         
at java.lang.Class.getMethod0(Class.java:2813)         
at java.lang.Class.getMethod(Class.java:1663)      



Now, open bsu.sh alter the values of Xms and Xmx (from default 256 to 1024) and rerun.