Posts Tagged ‘Oracle’

« Older Entries
Newer Entries »

Oracle : Size Of Database

March 31st, 2015, posted in Oracle Queries
Share

An Oracle Database consists of data files, redo log files, control files, temporary files.Oracle : Size Of Database,Oracle,Size Of Database,Database,data files, redo log files, control files, temporary files,Oracle data files,Oracle redo log files,Oracle control files,Oracle temporary files
Whenever you say the size of the database this actually means the summation of these files.

 

 

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select sum(bytes)/1024/1024 data_size from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) d;

-------------------------------------------------------------
For Round Figure For MB :

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select round( sum(bytes)/1024/1024 ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes)/1024/1024 ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 ) controlfile_size from v$controlfile) d;

-------------------------------------------------------------

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from 
( select sum(bytes/1024/1024/1024) data_size from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) b,
( select sum(bytes/1024/1024/1024) redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) d;

-------------------------------------------------------------
For Round Figure GB :


select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from 
( select round( sum(bytes/1024/1024/1024) ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes/1024/1024/1024) ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 ) controlfile_size from v$controlfile) d;



----------------------------------------------------------

Get database size from v$datafile:

select round((sum(bytes)/1048576/1024),2) from v$datafile;

------------------------------------------------------------
Get Oracle Database size from dba_data_files:

select "Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
from(
select 
(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",
(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual
);

-----------------------------------------------------------------
-

 

 

here
a is megabytes allocated to ALL datafiles
b is megabytes allocated to ALL TEMP files
c is megabytes allocated to ALL redo-logs
d is megabytes allocated to ALL control files

Share
read more no comments

Oracle LSNRCTL – Listener Shutdown and Startup Procedures

February 17th, 2015, posted in Linux OS, Oracle
Share

Starting up and shutting down the oracle listener is a routine task for a database administrator. However a Linux system Oracle LSNRCTL – Listener Shutdown and Startup Procedures,Oracle LSNRCTL,Listener Shutdown and Startup Procedures,Listener Shutdown and Startup, Procedures,Listener Shutdown ,Listener Startup, oracle lsnrctl ,Oracle,command,oracle commandadministrator or programmer may end-up doing some basic DBA operations on development database. It is critical for non-DBAs to understand the basic database admin activities.

In this article, let us review how to start, stop, check status of an oracle listener using Oracle listener control utility LSNRCTL.

 

How To Start, Stop and Restart Oracle Listener

1. Display Oracle Listener Status

Before starting, stopping or restarting make sure to execute lsnrctl status command to check the oracle listener status as shown below. Apart from letting us know whether the listener is up or down, you can also find the following valuable information from the lsnrctl status command output.

  • Listner Start Date and Time.
  • Uptime of listner – How long the listener has been up and running.
  • Listener Parameter File – Location of the listener.ora file. Typically located under $ORACLE_HOME/network/admin
  • Listener Log File – Location of the listener log file. i.e log.xml

If the Oracle listener is not running, you’ll get the following message.

lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-APR-2009 16:27:39

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory

If the Oracle listener is running, you’ll get the following message.

$ lsnrctl status

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-APR-2009 16:27:02

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                29-APR-2009 18:43:13
Uptime                    6 days 21 hr. 43 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.1.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/devdb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "devdb" has 1 instance(s).
  Instance "devdb", status UNKNOWN, has 1 handler(s) for this service...
Service "devdb.thegeekstuff.com" has 1 instance(s).
  Instance "devdb", status READY, has 1 handler(s) for this service...
Service "devdbXDB.thegeekstuff.com" has 1 instance(s).
  Instance "devdb", status READY, has 1 handler(s) for this service...
Service "devdb_XPT.thegeekstuff.com" has 1 instance(s).
  Instance "devdb", status READY, has 1 handler(s) for this service...
The command completed successfully

2. Start Oracle Listener

If the Oracle listener is not running, start the listener as shown below. This will start all the listeners. If you want to start a specific listener, specify the listener name next to start. i.e lsnrctl start [listener-name]

$ lsnrctl start

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-APR-2009 16:27:42

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.1.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /u01/app/oracle/product/11.1.0/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/devdb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date                04-APR-2009 16:27:42
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.1.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/devdb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "devdb" has 1 instance(s).
  Instance "devdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

3. Stop Oracle Listener

If the Oracle listener is running, stop the listener as shown below. This will stop all the listeners. If you want to stop a specific listener, specify the listener name next to stop. i.e lsnrctl stop [listener-name]

$ lsnrctl stop

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-APR-2009 16:27:37

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
The command completed successfully

4. Restart Oracle Listener

To restart the listener use lsnrctl reload as shown below instead of lsnrctl stop and lsnrctl start. realod will read the listener.ora file for new setting without stop and start of the Oracle listener.

$ lsnrctl reload

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-APR-2009 17:03:31

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.2)(PORT=1521)))
The command completed successfully

Oracle Listener Help

1. View Available Listener Commands

lsnrctl help command will display all available listener commands. In Oracle 11g following are the available listener commands.

  • start – Start the Oracle listener
  • stop – Stop the Oracle listener
  • status – Display the current status of the Oracle listener
  • services – Retrieve the listener services information
  • version – Display the oracle listener version information
  • reload – This will reload the oracle listener SID and parameter files. This is equivalent to lsnrctl stop and lsnrctl start.
  • save_config – This will save the current settings to the listener.ora file and also take a backup of the listener.ora file before overwriting it. If there are no changes, it will display the message “No changes to save for LISTENER”
  • trace – Enable the tracing at the listener level. The available options are ‘trace OFF’, ‘trace USER’, ‘trace ADMIN’ or ‘trace SUPPORT’
  • spawn – Spawns a new with the program with the spawn_alias mentioned in the listener.ora file
  • change_password – Set the new password to the oracle listener (or) change the existing listener password.
  • show – Display log files and other relevant listener information.
$ lsnrctl help

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-APR-2009 16:12:09

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

The following operations are available
An asterisk (*) denotes a modifier or extended command:

start               stop                status
services            version             reload
save_config         trace               spawn
change_password     quit                exit
set*                show*

2. Get More help on Specific Listener Command

You can get detailed help on a specific oracle listener command as shown below. In the following example, it gives all the available arguments/parameters that can be passed to the lsnrctl show command.

$ lsnrctl help show

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-APR-2009 16:22:28

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

The following operations are available after show
An asterisk (*) denotes a modifier or extended command:

rawmode                     displaymode
rules                       trc_file
trc_directory               trc_level
log_file                    log_directory
log_status                  current_listener
inbound_connect_timeout     startup_waittime
snmp_visible                save_config_on_stop
dynamic_registration
Share
read more no comments

EBS — Datapump expdp error ORA-33272 , ZPB, DBMS_AW, dba_aws

January 31st, 2015, posted in Oracle Queries
Share

EBS -- Datapump expdp error,EBS ,Datapump expdp error,Datapump error,ORA-33272 ,Oracle,Oracle 9i,Oracle 10g,Oracle 11g,Ora Error,Oracle Error,Error In Oracle Export

While exporting an EBS 11i database using datapump , you can get ORA-33272 error on Analytical Workspaces..
This errors are mostly due to the versions of aw objects..
Suppose you have upgraded your Ebs database from 9i to 10g and the AW objects belong to 9i version of Oracle Database have remained in your database..  In such a situation, you can encounter ORA-33272 error on your future data pump exports..

An example for these kind of errors are below;

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_AW_EXP.schema_info_exp(‘ZPB’,1,1,’10.02.00.00.00′,newblock)
ORA-33272: Analytic workspace ZPB.ZPBCODE cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [], [], []
ORA-06512: at “SYS.DBMS_AW”, line 67
ORA-06512: at “SYS.DBMS_AW”, line 131
ORA-06512: at “SYS.DBMS_AW”, line 792
ORA-06512: at “SYS.DBMS_AW”, line 1142
ORA-06512: at “SYS.DBMS_AW_EXP”, line 517
ORA-06512: at line 1
ORA-06512: at “SYS.DB.


For the solution, use following query to list the version for the object in question;
select * from all_aws where owner=’ZPB’;  (ZPB is the schema name of failing objects in the example above)SQL> select * from dba_aws;
Example output:
OWNER AW_NUMBER AW_NAME AW_V PAGESPACES GENERATIONS FROZEN
———- ———- ——————– —- ———- ———– ——
ZPB 1000 ZPBCODE 9.1 292 2
SYS 129 AWREPORT 10.2 9 2
SYS 128 AWXML 10.2 64 2
ZPB 1001 ZPBANNOT 9.1 7 1
ZPB 1002 ZPBDATA 9.1 7 1
SYS 125 AWMD 10.2 363 2
SYS 124 EXPRESS 10.2 46 2
SYS 126 AWCREATE 10.2 21 2
SYS 127 AWCREATE10G 10.2 9 2
Analyze the output of the query and if you have older versions, delete them..
For the example above: the ZPB Aws are 9i and they are need to be deleted.

You can use dbms_aw.execute to do that..

exec dbms_aw.execute(‘aw delete zpb.zpbcode’);
exec dbms_aw.execute(‘aw delete zpb.zpbannot’);
exec dbms_aw.execute(‘aw delete zpb.zpbdata’);

Log in AS ZPB
SQL> set serverout on
SQL> call dbms_aw.execute(‘AW DELETE ZPBCODE’);
SQL> call dbms_aw.execute(‘AW DELETE ZPBANNOT’);
SQL> call dbms_aw.execute(‘AW DELETE ZPBDATA’);
If you receive errors deleting the AWs, drop the associated AW$ tables :
AS SYS:
SQL> drop table AW$ZPBCODE;
SQL> drop table AW$ZPBANNOT;
SQL> drop table AW$ZPBDATA;select * from dba_aws;SYS 125 AWREPORT 9.1 9 2
SYS 124 AWXML 9.1 50 2
SYS 123 AWCREATE10G 9.1 9 2
SYS 122 AWCREATE 9.1 18 2
SYS 121 AWMD 9.1 205 2
SYS 120 EXPRESS 9.1 38 2


Also if you have all 9i ‘s in awr$ table, then you may be need to -> Remove OLAP, Clean up Left Over Objects And Add It back (Doc ID 1560841.1)
———————————————————————————————————————————————————————————————————————-
Note : Before applying any of this. Do understand your problem and concern with some-else as well.
———————————————————————————————————————————————————————————————————————
Share
read more no comments

Batman Team

October 27th, 2014, posted in COMiCS, DC Comic And Movies
Share

batman, batman comic, batman joker, batman joker jason todd, batman under the hood, batman under the red hood, Batman Under the Red Hood Cast Jensen Ackles, Batman Under the Red Hood Comic, jason todd, Jensen, Jensen Ackles, joker, red-hood, under the hood, under the red hood,night wing,oracle,robbin,batman team,baman and robbin,batman and red hood,batman and orcale,batman and batgirl,batgirl

Share
read more no comments

WARNING: Could not find all the required OS utilities in the $PATH. Please review the checks above

September 21st, 2014, posted in Oracle, Solaris
Share

WARNING: Could not find all the required OS utilities in the $PATH.
Please review the checks above

Oracle,Oracle Clonning,Oracle,Warning,Cannot Find Path, perl adcfgclone.pl appsTier,perl adcfgclone.pl dbTier,Clone warning,Oracle help,Oracle Issue,Oracle Warning,

Issue :

bash-3.00$ perl adcfgclone.pl dbTier

Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA

Oracle Applications Rapid Clone

Version 12.0.0

adcfgclone Version 120.20.12000000.12

Enter the APPS password :

Checking for make…
Unable to locate ‘make’ utility in path
Checking for ld…
Unable to locate ‘ld’ utility in path
Checking for ar…
Unable to locate ‘ar’ utility in path
Unable to locate all utilities with system path.
PATH = /clone/apps/apps_st/comn/clone/bin/../jre/bin:/usr/bin:

WARNING: Could not find all the required OS utilities in the $PATH. Please review the checks above


Solution:
Please set perl and perl5lib than use below command and retry issue
export PATH=/usr/ccs/bin:$PATH

Share
read more 1 comment
« Older Entries
Newer Entries »