ORA-12532: TNS:invalid argument


sqlplus system@orcl

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Aug 19 13:18:56 2007

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

Enter password:
ERROR:
ORA-12532: TNS:invalid argument

In this case we can ping to remote host but not able to connect database which we have created on remote host.

Cause : Listener port is closed.May be a firewall policy make the issue.

Action : We need to open Listener port for communication .By default Listener port is 1521.

Insert Array Output into Database


Hello Friends now a days i am working on php.A few days ago i got a task from my trainer and task was to uploading a txt file in new folder and output should be in an array format using loops and after that insert that output into database and the content in the text file was 100 email id's.At that time i was new in php and no idea how to start my task and how to use loops.For that i searched on net for many times but i couldn't find the required data to complete task on single site.
So,i had to serach many sites to collect or get idea how to complete my task.Now here in this post i am going to share that program with you so that if you get similar task like this task you can get help from this,i hope it will be helpful to you.

Here the program is:
1) Using this code you can upload your file from one folder to another folder.


<?php
if (($_FILES["file"]["type"] == 'text/plain') && ($_FILES["file"]["size"] < 20000))

  {
  if ($_FILES["file"]["error"] > 0)
    {
    echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
    }
  else
    {
    echo "Upload: " . $_FILES["file"]["name"] . "<br />";
    echo "Type: " . $_FILES["file"]["type"] . "<br />";
    echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
    echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />";


    if (file_exists("nit/" . $_FILES["file"]["name"]))
      {
      echo $_FILES["file"]["name"] . " already exists. "."<br/>"."<br/>";
      }
    else
      {
      move_uploaded_file($_FILES["file"]["tmp_name"],
      "nit/" . $_FILES["file"]["name"]);
      echo "Stored in: " . "nit/" . $_FILES["file"]["name"]."<br />";
 
      }
    }
  } 
  ?>


2) Use this code after 1st code as shown above, By using this code you will get your output in array format and after that output will be insert into database, see this code how its works,



<?php
        $file = fopen("file.txt", "r");
if ($file) {
  while (!feof($file))
  {
  $aa=fgets($file);
  $explode= explode(",",$aa);
  print_r(explode(',',$aa));
  foreach ($explode as $id =>$email_id)
 {
   $sql="insert into aa (id,email_id) values ('$id','$email_id')";
   $result=mysql_query($sql);
  }
 }
 
fclose($file);
      }
?>
</body>
</html>



Note:- In this above code we are using while loop as well as explode function after that we are using insert statement to insert output into database.

I hope you will find it informative.

To install Oracle in english on non-english OS

I installed oracle on non-english OS(windows server 2008 R2) which was not feasible for me.
I got server in Germany and installed Oracle 11g. When i installed blindly then i noticed, Oracle was communicating in German language which i can't understand. After some research & discussion with my DBA friends, i found three ways to make oracle install in english on non-english machine. You can opt anyone of them as per your requirement.


#Modify the oraparam.ini file.
In all the unzipped oracle installation files directory, there is a folder called install which has the file oraparam.ini.  It is this file which tells the setup.exe how to behave.  There is this parameter  ” NLS_ENABLED “, has a default value TRUE, which tells the setup.exe to take the OS language when invoked. So, if the OS is German, the setup.exe would show the details in German.  Make it FALSE and setup.exe will not take the OS language, it will go for the default english.

NLS_ENABLED=TRUE  #setup.exe will take the OS language for granted

NLS_ENABLED=FALSE #setup.exe will go for the default english




#Change the non-english OS language to English.
There is an alternate option that we can change OS language to English and oracle will automatically use english language. Steps to change the language:
Go to control panel --> Regional and language --> you will got four tab under this option
a) Formats --> select English where ever you find any option.
b) Location --> You can select your country but i choose US.
c) Keyboard & Language --> Change Keyboard --> general --> select US under Default Language and click on US under Installed Services.
d) Administrative --> change system locale.. --> select English
Reboot your system to take language into English.

Now install oracle, it will automatically pick English language as default.



#Change the NLS_LANG.
After installing Oracle in non-english language use NLS_LANG.

NLS_LANG is an environment variable used to define language, territory and character set properties. You can use as follow.

Windows (in cmd window):
set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Unix/ Linux:
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1


Here are some valid NLS_LANG settings:
->   French_France.WE8ISO8895P1
->   French_Switzerland.WE8ISO8895P1
->   Norwegian_Norway.NDK7DEC
->   Norwegian_Norway.WE8ISO8895P1
->   Japanese_Japan.JA16SJIS
->   Arabic_Egypt.AR8MSWIN1256
->   American_America.AR8MSWIN1256
->   American_America.WE8ISO8859P1
->   Latvian_Latvia.BLT8CP921

Note: Third option(Change the NLS_LANG.) is not permanent setting. It will work until command prompt is not closed. Rest both options are permanent.



Your suggestions and queries are always warm welcomed at 
navneetsingh89@gmail.com

Reclaiming Unused Space in Index.

We can see Reclaimable space in Schema by using below query.

SELECT'Task Name : ' || f.task_name || CHR(10) ||'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||'Segment Name : ' || o.attr2 || CHR(10) ||'Segment Type : ' || o.type || CHR(10) ||'Partition Name : ' || o.attr3 || CHR(10) ||'Message : ' || f.message || CHR(10) ||'More Info : ' || f.more_info || CHR(10) ||
'------------------------------------------------------' Advice FROM dba_advisor_findings f,dba_advisor_objects o,dba_advisor_executions e WHERE o.task_id = f.task_id AND o.object_id = f.object_id AND f.task_id = e.task_id AND e. execution_start > sysdate - 1 AND e.advisor_name = 'Segment Advisor' ORDER BY f.task_name;

Solution

There are a couple of effective methods for freeing up unused space associated with an index:




•Rebuilding the index
•Shrinking the index


Before you perform either of these operations, first check
USER_SEGMENTS to verify that the amount of space used corresponds with the Segment Advisor’s advice. In this example, the segment name is F_REGS_IDX1

SQL> select bytes from user_segments where segment_name = 'F_REGS_IDX1';
BYTES----------
166723584

This example uses the
ALTER INDEX...REBUILD

statement to re-organize and compact the space usedby an index:

SQL> alter index f_regs_idx1 rebuild;
Alternatively, use the
ALTER INDEX...SHRINK SPACE statement to free up unused space in an index—for example:

SQL> alter index f_regs_idx1 shrink space;

Now query

USER_SEGMENTS

again to verify that the space has been de-allocated. Here is the output forthis example:

BYTES----------
524288

The space consumed by the index has considerably decreased.

I prefer export and import is the another useful method to reclaim free space.

" Bharat Bandh " Is it reality or just a show off.

Is the bandh is the only way to protest, what type of bandh is this where people are forced to stay home and shut down their shops. All anti social elements are having a great time. A bandh should be something where people protest by themselves. Policitcan dont their work in Parliament and does not allow us to work too. What have they gained by calling this bandh we lost one day's pay.




 Its just a show off. If really the cry needs to be heard, there is no point in destroying public properties or blocking roads. Think! cover all petrol pumps, don't allow anyone to fill petrol tanks(allow only people who are in emergency i.e. hospital , ambulance, traveling etc). Cover up govt. offices, don't allow anyone to go inside. When govt. takes a hit, then they think about it. Rt now , general public is getting a hit, how does it affect the govt?

Enable flashback in Oracle database

Before enable the flashback we need to take our database in archive log mode. To 
enable the archive log click here 
After enabling the archive logs now we need to set 3 paramaters for flashback are
1 db_recovery_file_dest 
2 db_recovery_file_dest_size
3 db_flashback_retention_target 
  
>>This parameter "db_recovery_file_dest" is used to set the destination where we 
want to store the location of flashback logs.  The location of flashback logs may or 
maybe be same as the location of archive logs, here i gave different location 
from archive logs. 
>>The parmeter "db_recovery_file_dest_size" is used to define the size that can be 
used by flashback directory/folder provided with above parameter. This size can be 
calculated on the basis of transactions happened in our database and if archive logs, 
only when the destination of archive logs & flashback logs are same.
>>The parameter "db_flashback_retention_target " is used to provide time limit upto 
which flashback logs should be in the memory of database, So that database can be 
flashed up back with in time limit. Time is provided in minutes, so we set retention 
time =1440 which is about a day.
 
 >>Set flashback database parameters
alter system set db_recovery_file_dest='E:\app\navneet\FRA' scope=spfile; 
alter system set db_recovery_file_dest_size=20G scope=spfile;
alter system set db_flashback_retention_target=1440 
 
 >> Turn flashback on for the database 
shutdown immediate; 
startup mount;
alter database flashback on;
alter database open;

>>Now check if flashback is enabled 
select flashback_on from v$database; 
 FLASHBACK_ON
------------------
YES
 Now Flashback is ENABLED.
 

>>To disable flashback  
alter system set db_recovery_file_dest=" scope= both sid='*';
 
 
Some related queries are as follow 
 
>> To check the flashback logs 
select * from v$flashback_database_log;

>>To check the estimated flashback size
SELECT ROUND(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024) FROM 
V$FLASHBACK_DATABASE_LOG; 
  
>>To create a guaranteed restore point
create restore point RESTORE_POINT guarantee flashback database;
select NAME, SCN, TIME from V$RESTORE_POINT;

>>To flashback the database to specific time
flashback database to timestamp to_timestamp('dd/mm/yy HH24:MI:SS', 
'DD/MM/YYYY HH24:MI:SS');

>>To drop a restore point
select NAME, SCN, TIME from V$RESTORE_POINT; 
 drop restore point RESTORE_POINT;


The V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views can help to find out the current location, disk quota, space in use, space reclaimable by deleting files,total number of files, the percentage of the total disk quota used by different types of files



Your suggestions and queries are always warm welcomed at navneetsingh89@gmail.com.

Enabling Archivelog Mode in Oracle 11g

To set archive log, always login through SYSDBA


Now, lets check the current log mode either in "Archivelog" or "Noarchivelog":

C:\Documents and Settings\navneet>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 27 11:19:53 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2296
Current log sequence 2299


OR


SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

We have checked that our Database is in "NOARCHIVE LOG". Below query will set the location where to save the archive logs.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=E:\ARCHIVE_LOG' SCOPE=SPFILE;

System altered.


SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 385876432 bytes
Database Buffers 142606336 bytes
Redo Buffers 5804032 bytes
Database mounted.

The below query will make database into archivelog.
SQL> ALTER DATABASE ARCHIVELOG;

Database altered.



SQL> ALTER DATABASE OPEN;

Database altered.


SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
ARCHIVELOG


OR


SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\ARCHIVE_LOG
Oldest online log sequence 2296
Next log sequence to archive 2299
Current log sequence 2299


SQL> alter system switch logfile;


Now database is in Archivelog mode, We have verified by checking archivelogs in directory.


E:\>dir
Volume in drive E has no label.
Volume Serial Number is AA1B-C0B9

Directory of E:\

12/09/2011 09:15 AM app
03/27/2012 11:43 AM 13,952,512 ARCHIVE_LOG2ARC0000002299_0769427405.0001
03/27/2012 11:46 AM 1,536 ARCHIVE_LOG2ARC0000002300_0769427405.0001
03/28/2012 05:39 AM 50,630,144 ARCHIVE_LOG2ARC0000002301_0769427405.0001
03/29/2012 05:38 AM 40,771,072 ARCHIVE_LOG2ARC0000002302_0769427405.0001
03/29/2012 06:30 PM 40,671,232 ARCHIVE_LOG2ARC0000002303_0769427405.0001
02/17/2009 11:33 AM 23,516,968 SkypeSetupFull.exe
6 File(s) 169,543,464 bytes
1 Dir(s) 38,746,923,008 bytes free


To Enable Flashback click here



Your suggestions and queries are always warm welcomed at navneetsingh89@gmail.com. 

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More