BLOG

Useful Query To check Some Privileges

Posted on by Osama Mustafa in Scripts
Check for whether user has full database export/import privilege 


Query : 



SQL>SET lines 100 
COL privilege FOR a50 
SELECT grantee, granted_role, default_role 
FROM dba_role_privs 
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE') 
ORDER BY 1,2;

Check for user has create table or create session privilege.


Query  

SQL>SET lines 100 pages 100
COL privilege FOR a40
SELECT grantee, privilege 
  FROM dba_sys_privs 
 WHERE (grantee IN ('TEST', 'PUBLIC') 
        OR grantee IN (SELECT granted_role FROM dba_role_privs 
                        WHERE grantee IN ('TEST', 'PUBLIC'))) 
   AND privilege IN ('CREATE SESSION', 'CREATE TABLE') 
 ORDER BY 1,2;

Check for granted privileges on directory objects.


Query : 

SET lines 110
COL privilege FOR a12
COL grantee FOR a25
COL owner FOR a25
SELECT p.grantee, p.privilege, p.owner, d.directory_name 
  FROM dba_tab_privs p, dba_directories d 
 WHERE p.table_name=d.directory_name 
   AND (grantee IN ('TEST', 'PUBLIC') 
        OR grantee IN (SELECT granted_role FROM dba_role_privs 
                        WHERE grantee IN ('TEST', 'PUBLIC'))) 
 ORDER BY 4,3,2;

Thank You
osama Mustafa

RW-50015 : Installation Error On Oracle EBS R12

Posted on by Osama Mustafa in Application
RW-50015: Error: – HTTP Listener is not responding. The service might not have started on the port yet. Please check the service and use the retry button.

the Rapid Install window will show many errors for HTTP.

Checking status of OPMN managed Oracle HTTP Server (OHS) instance …

Processes in Instance: PROD_ebs.ebs.sandiego.com

——————-+——————–+———+———
ias-component | process-type | pid | status
——————-+——————–+———+———
OC4J | oafm | 21168 | Alive
OC4J | forms | 21078 | Alive
OC4J | oacore | 20943 | Alive
HTTP_Server | HTTP_Server | N/A | Down

Download and apply the patch 6078836 from OracleMetaLink to fix an issue with the Oracle HTTP Server bundled with the E-Business Suite technology stack. We unzip the patch:

[oracle@ebs oracle]$ unzip p6078836_101330_LINUX.zip
Archive: p6078836_101330_LINUX.zip
creating: 6078836/
inflating: 6078836/libdb.so.2
inflating: 6078836/README.txt

[oracle@ebs oracle]$ cd 6078836/
[oracle@ebs 6078836]$ ls
libdb.so.2 README.txt

Then we copy to /usr/lib.

[root@ebs 6078836] $ cp libdb.so.2 /usr/lib

[root@ebs lib]# cd /usr/lib

[root@ebs lib]# pwd
/usr/lib

[root@ebs lib]# ls libdb.so*
libdb.so libdb.so.2
 

Processes in Instance: PROD_ebs.ebs.sandiego.com

——————-+——————–+———+———
ias-component | process-type | pid | status
——————-+——————–+———+———
OC4J | oafm | 21168 | Alive
OC4J | forms | 21078 | Alive
OC4J | oacore | 20943 | Alive
HTTP_Server | HTTP_Server | 28519 | Alive 




Thank You
 Osama mustafa

LEARN HOW OPEN RESETLOGS WITHOUT RECOVERY

Posted on by Osama Mustafa in Database
Amazing Oracle Trick I found today while i am Browsing OTN , I see to post it here .

What is Open resetlog mode : 
 
Online redo logs are re-created . The log sequence is reset to 1.
If the databaseis running in archive log mode, the archived redo logs should then be deleted. Otherwise, chances are, that Oracle will eventually try to create an archived redo log whose filename already exists. 

First i will show its Normal Database this mean not in recovery mode :

sqlplus / as sysdba
Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             213910792 bytes
Database Buffers          616562688 bytes
Redo Buffers                6299648 bytes
Database mounted.
Database opened.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit  

Now Lets Open it :

sqlplus / as sysdba

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             213910792 bytes
Database Buffers          616562688 bytes
Redo Buffers                6299648 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
 
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> 
 
 
 

 
 
 

The difference between NOT IN and NOT EXISTS

Posted on by Osama Mustafa in Others
There is a popular misconception that NOT IN and NOT EXISTS are two ways to filter out rows present in one table and not in another table, with both methods being usable interchangeably. Popular wisdom is that the difference between the two is only in terms of performance (being on the basis of whether the larger table is the “outer” or the “inner” in the query) but that the results are the same.

However, we must remember that such an operation being an Anti-Join fails when NULLs are involved. In Oracle a NULL cannot be compared to any other value, not even another NULL. Therefore, a NOT IN operation would fail if the result set being probed returns a NULL. In such a case, the results of a NOT IN query is 0 rows while a NOT EXISTS query would still show the rows present in the one table but not in the other table.

Here is a simple demonstration :


SQL> -- create the two test tables
SQL>
SQL> drop table results_table purge;

Table dropped.

SQL> drop table query_table purge;

Table dropped.

SQL>
SQL> create table results_table
 2  as select owner,object_name,object_type from dba_objects where owner in ('HEMANT','DBSNMP','OUTLN');

Table created.

SQL> create table query_table
 2  as select owner,object_name,object_type from dba_objects where owner in ('DBSNMP','OUTLN');

Table created.

SQL>
SQL> -- a NOT IN query
SQL> select r.owner, r.object_name from results_table r
 2  where r.owner not in (select q.owner from query_table q)
 3  order by 1,2;

OWNER                          OBJECT_NAME                                                                                         
------------------------------ ------------------------------                                                                      
HEMANT                         DUPDB                                                                                               
HEMANT                         MY_T_A                                                                                              
HEMANT                         RESULTS_TABLE                                                                                       
HEMANT                         SOURCE_TABLE                                                                                        
HEMANT                         TEST_APPEND                                                                                         
HEMANT                         TRACE_USER_SESSIONS                                                                                 
HEMANT                         TRACE_USER_SESSIONS_BEGIN                                                                           
HEMANT                         TRACE_USER_SESSIONS_END                                                                             

8 rows selected.

SQL>
SQL> -- a NOT EXISTS query
SQL> select r.owner, r.object_name from results_table r
 2  where not exists (select '1' from  query_table q where r.owner=q.owner)
 3  order by 1,2;

OWNER                          OBJECT_NAME                                                                                         
------------------------------ ------------------------------                                                                      
HEMANT                         DUPDB                                                                                               
HEMANT                         MY_T_A                                                                                              
HEMANT                         RESULTS_TABLE                                                                                       
HEMANT                         SOURCE_TABLE                                                                                        
HEMANT                         TEST_APPEND                                                                                         
HEMANT                         TRACE_USER_SESSIONS                                                                                 
HEMANT                         TRACE_USER_SESSIONS_BEGIN                                                                           
HEMANT                         TRACE_USER_SESSIONS_END 



8 rows selected.

SQL>
SQL> ---
SQL> REM  So far, NOT IN  and  NOT EXISTS  have presented the same results
SQL>
SQL> REM  What happens if there is a row with a NULL value ?
SQL>
SQL> insert into query_table values (NULL,'ABCDEFGH','TABLE');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> -- retry the NOT IN query
SQL> select r.owner, r.object_name from results_table r
 2  where r.owner not in (select q.owner from query_table q)
 3  order by 1,2;

no rows selected

SQL>
SQL> -- retry the NOT EXISTS query
SQL> select r.owner, r.object_name from results_table r
 2  where not exists (select '1' from  query_table q where r.owner=q.owner)
 3  order by 1,2;

OWNER                          OBJECT_NAME                                                                                         
------------------------------ ------------------------------                                                                      
HEMANT                         DUPDB                                                                                               
HEMANT                         MY_T_A                                                                                              
HEMANT                         RESULTS_TABLE                                                                                       
HEMANT                         SOURCE_TABLE                                                                                        
HEMANT                         TEST_APPEND                                                                                         
HEMANT                         TRACE_USER_SESSIONS                                                                                 
HEMANT                         TRACE_USER_SESSIONS_BEGIN                                                                           
HEMANT                         TRACE_USER_SESSIONS_END                                                                             

8 rows selected.

SQL>
SQL> ---
SQL> REM  Surprise ?!   The NOT IN returned 0 rows !
SQL> REM  Why ?  Because of the presence of a NULL in the query_table !
SQL> REM
SQL> REM  REMEMBER : A "NOT IN"  anti-join fails because a NULL returned cannot be compared !
SQL>
SQL> --
SQL> REM One "workaround" is to filter out rows which contain NULLs
SQL> REM     .... but think carefully before you do so. Are you sure you want to exclude them ?
SQL>
SQL> REM In the ideal world, such columns should be defined as NOT NULL columns !
SQL> REM That would be the right schema design !
SQL>
SQL> -- test the suggested workaround
SQL> select r.owner, r.object_name from results_table r
 2  where r.owner not in (select q.owner from query_table q WHERE OWNER IS NOT NULL)
 3  order by 1,2;


OWNER                          OBJECT_NAME                                                                                         
------------------------------ ------------------------------                                                                      
HEMANT                         DUPDB                                                                                               
HEMANT                         MY_T_A                                                                                              
HEMANT                         RESULTS_TABLE                                                                                       
HEMANT                         SOURCE_TABLE                                                                                        
HEMANT                         TEST_APPEND                                                                                         
HEMANT                         TRACE_USER_SESSIONS                                                                                 
HEMANT                         TRACE_USER_SESSIONS_BEGIN                                                                           
HEMANT                         TRACE_USER_SESSIONS_END                                                                             

8 rows selected.

SQL> 
 
 
 
Finally I would thank Hemant for this amazing Article .
I posted to make it more popular and useful for the people who wants to learn
something new .
 
Osama mustafa

tns-12560 message 12560 not found

Posted on by Osama Mustafa in Database
Sometimes Users Cannot access to Database You Need To check The following :

Sqlplus / as sysdba

Show parameter local_listener ;

You have to see output like this :


local_listener            (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=you-ip-address)(PORT=1521))))

  If you see Null Value Do the Following :

Alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.250.25)(PORT=1521))))‘ scope=memory ;

Or For Real Application :

Alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.250.25)(PORT=1521))))‘ scope=memory sid=’you-node-name’;

Scope = memory For Test Purpose

Sql > Alter system register ;

Now You should see database name service on listener ;

to Apply these Changes all we have to do is scope=spfile like the following :

Alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.250.25)(PORT=1521))))‘ scope=spfile ;

Or For Real Application :

Alter system set local_listener=’(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.250.25)(PORT=1521))))‘ scope=spfile sid=’you-node-name’;

Thank you
Osama Mustafa

How to change SGA in Oracle Rac

Posted on by Osama Mustafa in Database
This Article for newbe Oracle DBA Since If you trying to change sga on rac with wrong way , spfile maybe be corrupted . 

Node One : ORCL1
Node Two : ORCL2

Note : You Need To Check Memory Parameter On Database , if its Have Available Size .

  • Connect To Node One :

alter system set sga_max_size=16g scope=spfile sid = 'ORCL1';
alter system set sga_target=12g scope=spfile sid = 'ORCL1';
alter system set sga_max_size=16g scope=spfile sid = 'ORCL2';
alter system set sga_target=12g scope=spfile sid = 'ORCL2';

Or In Another Way :
sql>alter system set sga_target=12G scope=spfile sid=’*’;
sql>alter system set sga_max_size=16G scope=spfile sid=’*’;
sql>alter system set sga_max_size=16G scope=spfile ;
sql>alter system set sga_target=12G scope=spfile;

 PGA :

sql>alter system set pga_aggregate_target=4G scope =spfile sid=’*’;
sql>alter system set pga_aggregate_target=4G scope=both;

  • shutdown database PROD (two instances should be shut down)

 $>srvctl stop database -d PROD

$>srvctl START database -d PROD

Check The New Size For Both Instance By :

Show parameter sga ;

Thank you
Osama mustafa 

Automatic Storage Management/ASM Part 2

Posted on by Osama Mustafa in Database

Templates:

Templates are named groups of attributes that can be applied to the files within a disk group

Examples :

 

-- Create a new template.
ALTER DISKGROUP disk_group_1 ADD TEMPLATE my_template ATTRIBUTES (MIRROR FINE);

-- Modify template.
ALTER DISKGROUP disk_group_1 ALTER TEMPLATE my_template ATTRIBUTES (COARSE);

-- Drop template.
ALTER DISKGROUP disk_group_1 DROP TEMPLATE my_template;

Available attributes include:

  • UNPROTECTED – No mirroring or striping regardless of the redundancy setting.
  • MIRROR – Two-way mirroring for normal redundancy and three-way mirroring for high redundancy. This attribute cannot be set for external redundancy.
  • COARSE – Specifies lower granuality for striping. This attribute cannot be set for external redundancy.
  • FINE – Specifies higher granularity for striping. This attribute cannot be set for external redundancy.

 

 

Directories

A directory heirarchy can be defined using the ALTER DISKGROUP statement to support ASM file aliasing

Examples :

-- Create a directory.
ALTER DISKGROUP disk_group_1 ADD DIRECTORY '+disk_group_1/my_dir';

-- Rename a directory.
ALTER DISKGROUP disk_group_1 RENAME DIRECTORY '+disk_group_1/my_dir' TO '+disk_group_1/my_dir_2';

-- Delete a directory and all its contents.
ALTER DISKGROUP disk_group_1 DROP DIRECTORY '+disk_group_1/my_dir_2' FORCE;

 

Aliases

Aliases allow you to reference ASM files using user-friendly names

-- Create an alias using the fully qualified filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
  FOR '+disk_group_1/mydb/datafile/my_ts.342.3';

-- Create an alias using the numeric form filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
  FOR '+disk_group_1.342.3';

-- Rename an alias.
ALTER DISKGROUP disk_group_1 RENAME ALIAS '+disk_group_1/my_dir/my_file.dbf'
  TO '+disk_group_1/my_dir/my_file2.dbf';

-- Delete an alias.
ALTER DISKGROUP disk_group_1 DELETE ALIAS

Files

Files are not deleted automatically if they are created using aliases, as they are not Oracle Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created. For these circumstances it is necessary to manually delete the files, as shown below.

 

-- Drop file using an alias.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';

-- Drop file using a numeric form filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.342.3';

-- Drop file using a fully qualified filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/mydb/datafile/my_ts.342.3';

ASM Views:

Migrating to ASM Using RMAN:

  • Disable change tracking (only available in Enterprise Edition) if it is currently being used.
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
  • Shutdown the database.
    SQL> SHUTDOWN IMMEDIATE 
    • Modify the parameter file of the target database as follows:
      Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
      Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.
  • Start the database in nomount mode.

                            RMAN> STARTUP NOMOUNT

 

  • Restore the controlfile into the new location from the old location.

 RMAN> RESTORE CONTROLFILE FROM ‘old_control_file_name’;

  • Mount the database.
  • Copy the database into the ASM disk group.
    RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';

  • Switch all datafile to the new ASM location.

    RMAN> SWITCH DATABASE TO COPY;
  • Open the database.

    RMAN> ALTER DATABASE OPEN;
  • Create new redo logs in ASM and delete the old ones.
  • Enable change tracking if it was being used.

    SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING; 

rman-06054 media recovery requesting

Posted on by Osama Mustafa in Database 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/13/2011 14:51:58
RMAN-06054: media recovery requesting unknown log: thread 1 seq 2 lowscn 547146
 
 
This Error Happened Sometimes When you try to restore rman backup from Real Application
Cluster To single Node 
 
Solution  

 RMAN > recover database noredo;
 

Thank you
Osama mustafa
 
 
Hope This is useful .

RMAN-10003 ORA-12154

Posted on by Osama Mustafa in Database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/15/2011 12:54:22
RMAN-12001: could not open channel ORA_DISK_1
RMAN-10008: could not create channel context
RMAN-10003: unable to connect to target database
ORA-12154: TNS:could not resolve the connect identifier specified

Solution :

RMAN> run {
2> allocate channel ch00 type disk;
3> restore database;
4>}

Thank You
osama Mustafa