Saturday, March 14, 2015

Add a user account to a ora_dba group in Windows 8 !

I know this is a very silly thing, but lots of student asking this question so many times specially in Windows 8, How can they add a user account to a group.

Lacking of this ORA_DBA group privileges , you will face following error message when you will try to log in internally like
 
SQL > connect / as sysdba

ORA-01031: insufficient privileges 

so, you have add your current OS user (oracle software installation OS user)  to "ora_dba" group in windows (dba group in unix system).

1)  Open CMD prompt window the administrative privileges.

To start a command prompt window with Windows Administrator privileges:

    On your desktop, create a shortcut for the command prompt window. An icon for that shortcut appears on the desktop.

    Right-click the icon for the newly created shortcut, and specify Run as administrator.

When you open this window, the title bar reads Administrator: Command Prompt. Commands run from within this window are run with Administrator privileges.

then
2)  CMD commands to do that in Windows 8

--check the users

cmd> net user

--for checking the group

cmd> net localgroup

--add user111 to ora_dba group

cmd> net localgroup ora_dba your_oracle_user_name /add

--for help
cmd> net help localgroup

------------------------------------***--------------------------------

Saturday, February 28, 2015

Windows PowerShell script to check the website status!



Description:
This is a small PowerShell script; you can run it from your any windows machine to check your or any website’s status in every 30 seconds/ any at frequency. (you can change it to your desire time)
And if it (script) found that the specific webpage is down then it will send you an email that your web site is down.
But you need a SMTP server and internet connection. 

Use case:  

If is there any scenarios come like, your webpage is going down any how without your knowledge and you are getting so many complain about it. Then you can implement this script to monitor your site and take necessary actions. 

Here I have used web page as an "https://www.facebook.com/" and it will check the status of this page or link in every 30 seconds.  

The Script:

 Begin {  
   $web = New-Object System.Net.WebClient  
   $web.UseDefaultCredentials = $True  
   $flag = $false  
   $url = "https://www.facebook.com/"  
   }  
 Process {  
   While ($flag -eq $false) {  
     Try {  
       $web.DownloadString($url)  
       clear  
     Write-Host -ForegroundColor Green "$(Get-Date): Facebook is up!"  
     Start-Sleep -Seconds 30  
      ##  $flag = $True  
       }  
     Catch { Write-host -fore Red "$(Get-Date): Facebook is down..."   
      ## email sending----------------  
     $emailFrom = "Halim@test.com"  
     # Use commas for multiple addresses  
     $emailTo = "Halim@test.com,halim1@test.com"  
     $subject = "Facebook May be Down(Test email notification system) !!"  
     $body = "Facebook May be Down. Please Check! (This is just a test email)" # Details: $($_.Exception)"  
     $smtpServer = "192.168.10.199" # your smtp server's IP or name.   
     $smtp = new-object Net.Mail.SmtpClient($smtpServer)  
     $smtp.Send($emailFrom, $emailTo, $subject, $body)    
     $flag = $True  
     ##exit   
        ## -----------------------  
           }  
                }  
      }    
 End {  
   Write-Host -fore yellow "Email send & This process need to run again for further monitoring"  
   }  


How  to execute or run :
            1) Go to search or run and type “PowerShell”  enter  or go to the following exe,  then click
                %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe
           2)  Copy & paste the code on it, then enter.


Thanks
Halim

ORA-24247: network access denied by access control list (ACL)

Easy solution for this error....
Problem:
You may face above error, when you will use several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR)  utilities in Oracle database 11g or higher version. This is a new security policy, implemented by Oracle in latest versions. but you can pass it via following steps

1) Connect with super user (SYS user)

2) Create following " Pro_add_me_to_acl_list" procedure (This procedure is for "Drop ACL", "Create_ACL" and "Assign_ACL" with the package "DBMS_NETWORK_ACL_ADMIN" )
or
you can do same thing without creating the procedure see here  http://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1.php

(But this procedure will simplify you activity)

3) and then execute the procedure with appropriate parameters .

Here it is in live example :

 SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 27 15:14:58 2015  
 Copyright (c) 1982, 2010, Oracle. All rights reserved.  
            STEP-#1   
 SQL>  
 SQL> conn sys@orcl_dev_linux as sysdba  
 Enter password:  
 Connected.  
 SQL>  
 SQL>  
 SQL> show user  
 USER is "SYS"  
 SQL>  
            STEP-#2   
 SQL>  
 SQL> CREATE OR REPLACE PROCEDURE Pro_add_me_to_acl_list (aacl     VARCHAR2,  
  2                           acomment   VARCHAR2,  
  3                           aprincipal  VARCHAR2,  
  4                           aisgrant   BOOLEAN,  
  5                           aprivilege  VARCHAR2,  
  6                           aserver    VARCHAR2,  
  7                           aport     NUMBER)  
  8 IS  
  9 BEGIN  
  10   BEGIN  
  11    DBMS_NETWORK_ACL_ADMIN.DROP_ACL (aacl);  
  12    DBMS_OUTPUT.put_line ('ACL dropped.....');  
  13   EXCEPTION  
  14    WHEN OTHERS  
  15    THEN  
  16      DBMS_OUTPUT.put_line ('Error dropping ACL: ' || aacl);  
  17      DBMS_OUTPUT.put_line (SQLERRM);  
  18   END;  
  19  
  20   BEGIN  
  21    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (aacl,  
  22                      acomment,  
  23                      aprincipal,  
  24                      aisgrant,  
  25                      aprivilege);  
  26    DBMS_OUTPUT.put_line ('ACL created.....');  
  27   EXCEPTION  
  28    WHEN OTHERS  
  29    THEN  
  30      DBMS_OUTPUT.put_line ('Error creating ACL: ' || aacl);  
  31      DBMS_OUTPUT.put_line (SQLERRM);  
  32   END;  
  33  
  34   BEGIN  
  35    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (aacl, aserver, aport);  
  36    DBMS_OUTPUT.put_line ('ACL assigned.....');  
  37   EXCEPTION  
  38    WHEN OTHERS  
  39    THEN  
  40      DBMS_OUTPUT.put_line ('Error assigning ACL: ' || aacl);  
  41      DBMS_OUTPUT.put_line (SQLERRM);  
  42   END;  
  43  
  44   COMMIT;  
  45   DBMS_OUTPUT.put_line ('ACL commited.....');  
  46 END;  
  47  
  48 /  
 Procedure created.  
 SQL>  
 SQL>  
 SQL>  
 SQL> COLUMN host FORMAT A30  
 SQL> COLUMN acl FORMAT A30  
 SQL>  
 SQL> SELECT host, lower_port, upper_port, acl  
  2 FROM  dba_network_acls;  
 HOST              LOWER_PORT UPPER_PORT  
 ------------------------------ ---------- ----------  
 ACL  
 ------------------------------  
 <smtp_server>             25     25  
 /sys/acls/smtp-gate-permission  
 s.xml  
 your_smtp_server_name_or_ip             25     25  
 /sys/acls/mailserver_acl.xml  
                
                       STEP-#3  
 SQL>  
 SQL>  
 SQL> BEGIN  
  2   Pro_add_me_to_acl_list ('mailserver_acl.xml',  
  3               'ACL for used Email Server to connect',  
  4               'HR',            --(user name/schema)  
  5               TRUE,  
  6               'connect',  
  7               'your_smtp_server_name_or_ip', --(your exchange mail server name)  
  8               25);             --default port number  
  9 END;  
  10  
  11 /  
 PL/SQL procedure successfully completed.  
 SQL>  
 SQL>  


Thanks
Halim

Friday, January 30, 2015

ORA-00845: MEMORY_TARGET not supported on this system in Redhat Linux 6 in oracle database 11g/12c



Easy solution for this error is below....


When creating a database with DBCA, you are not able to give more memory to database.
Because of ORA-00845: MEMORY_TARGET not supported on this system

Or

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system


Cause: 
 Your shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET. So Oracle can’t able to setup MEMORY_TARGET (AMM- automatic memory management) parameter. Though your shmmax (\etc\sysctl.conf) have enough value.

You can see it with below commands

Root# df –h




Solution:

So we need to change the value of below parameter in /etc/fstab with root user then reboot the server.

tmpfs /dev/shm tmpfs size=7G 0 0

After changing the size=7g (with root user) in /etc/fstab  (was defaults) Automatically memory_target parameters value changed from 0 (zero) to (sga_target + pga_aggregate_target) .
And I am also able to set up memory_target (AMM- automatic memory management)







Cheers.... thanks Halim. 

Tuesday, December 23, 2014

Recreate or reconfigure the Enterprise Manager (EM) console or Database Control in oracle 11g

If you use EM for managing your Oracle databases, you may face lots of problem with EM like sometimes EM will not starting up, sometimes it will start but not able to connect with database and listener and agent etc.

So, here are some basic things related to EM, you can reduce your EM related problem by following these.

Sample URL for EM in 11g R2 : https://your_host_name:1158/em  (just an idea)


Prerequisite for EM:



1) Make sure the "/etc/hosts" file contains at least the loopback address and preferably the machine definition also.

    127.0.0.1       localhost.localdomain  localhost
    192.168.1.101  your_server_name.localdomain   your_server_name


2) make sure the ORACLE_HOSTNAME and ORACLE_UNQNAME environment variables are set correctly.

    ----in windows

    cmd> set ORACLE_HOSTNAME=your_server_name.localdomain;
    cmd> set ORACLE_UNQNAME=orcl;

3) Check the listener is running correctly with the same IP/your_server_name and port ;


    cmd> lsnrctl status  ---run cmd as a adminstrator

B)  Enterprise Manager service check/start/stop .



        cmd> set ORACLE_HOSTNAME=your_server_name; --your host_name

        cmd> set ORACLE_UNQNAME=orcl;  --your database name or sid name

        cmd> emctl status dbconsole

        cmd> emctl start dbconsole

        cmd> emctl stop dbconsole


C) If EM fails to start or running but not working properly then you can recreate it with EMCA utility .



         [note: you need following information's before recreate

         1. Listener should be up, running and registerd with database.
         2. Database SID name
         3. Listener port number: 1521
         4. Password for SYS user: 
         5. Password for DBSNMP user: 
         6. Password for SYSMAN user:   
         ]
Then run following commands

       cmd> set ORACLE_HOSTNAME=your_server_name; --your host_name

       cmd> set ORACLE_UNQNAME=orcl;  --your database name or sid name

       cmd> emca -config dbcontrol db -repos recreate


D) if above recreate command fails, you drop the EM first and create by following commands



        cmd> set ORACLE_HOSTNAME=your_server_name; -----your host_name

        cmd> set ORACLE_UNQNAME=orcl;  -----your database name or sid name

        cmd> emca -deconfig dbcontrol db -repos drop

        cmd> emca -config dbcontrol db -repos create


---I hope it will reduce your EM related problem. cheers!!