SSH Configuration between servers

SSH Configuration between linux servers without giving password

SSH can give you authenticated and encrypted connections to remote computers. If you set up keys you can make these connections without passwords.

I test this for RHEL5 64 bit OS

Step1: Generate a public and private key pair on the machine from where you want to login to other machines.

For SSH1:

# ssh-keygen -b 1024 -t rsa

For SSH2 (Recommended):

# ssh-keygen -b 1024 -t dsa

• You will be prompted for a file in which the key and a passphrase will be saved. 
• You may press Enter through each of these prompts. 
• If you do so, the key generation program will assume that you wish to use the default file name of id_dsa and your private key will not be protected by a password.
• Once this is done, you will see id_rsa and id_rsa.pub file in the .ssh directory in your home directory if you have not supplied a file name as I mentioned above.

Step 2: Copy the content of id_rsa.pub file to authorized_keys2 file available in .ssh directory of the remote host(s)

• Copy the public keys (id_rsa.pub) to the remote host. That is the content of id_rsa.pub file to the file called  authorized_keys2 available in .ssh directory of the host(s) where you wanted to connect without password. 
• Be aware that .ssh directory will be available in the home directory as hidden file so make sure it availability by ls -ltr command in the home directory.

# scp ~/.ssh/id_rsa.pub remote_host:/

Step 3: Login to the remote host and check if you already have authorized_keys2 file in .ssh dir. If this doesn’t exist, create it with the following commands.

# touch ~/.ssh/authorized_keys2

Step 4: On the remote host where you are in step 3, copy your public key to the authorized_keys2 file, with the following command.

# cat ~/ id_rsa.pub >> ~/.ssh/authorized_keys2

Step 5: You are set to use SSH authentication without password now. If you want you can delete or move id_rsa.pub file.

You can follow steps 2 – 5 for all other hosts where you want to have public key authentication.

Recompiling Invalid Objects

STEP 1: Use the below query to find INVALID Objects



SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS='INVALID';
Export the result of the above query to xls.

STEP 2: Identify the Object Type of each invalid object.

STEP 3: If the object type is synonym

Use the below query for generating a query to create or replace invalid synonyms

SELECT  'CREATE OR REPLACE PUBLIC SYNONYM ' ||OBJECT_NAME ||' FOR '
||OBJECT_NAME||';' FROM DBA_OBJECTS where status='INVALID' AND OBJECT_TYPE='SYNONYM';

Use the below query for generating a query to grant privileges to synonyms



SELECT 'GRANT ALL ON ' ||OBJECT_NAME ||' TO PUBLIC||;' FROM DBA_OBJECTS where status='INVALID' AND OBJECT_TYPE='SYNONYM';

STEP 4: Copy and paste the result set of both the above queries in separate file


Now execute the first result set

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME1 FOR OBJECT_NAME1;

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME2 FOR OBJECT_NAME2;


CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME3 FOR OBJECT_NAME3;

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME4 FOR OBJECT_NAME4;

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME5 FOR OBJECT_NAME5;
CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME6 FOR OBJECT_NAME6;

CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.

Now execute the second result set

GRANT ALL ON SYNONYM_NAME1 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME2 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME3 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME4 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME5 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME6 TO PUBLIC;

GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.

Use the below query to check whether the objects has been recompiled and the status is VALID

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS
WHERE STATUS='VALID' AND OBJECT_TYPE='SYNONYM' AND OBJECT_NAME IN ('OBJECT_NAME1','OBJECT_NAME2','OBJECT_NAME3','OBJECT_NAME4',
'OBJECT_NAME5';

STEP 5: If the object type is not synonym


Recompile other objects as the show below

Uploading CSV in SQLLDR

Normal CSV Upload



Step 1: Before loading data you need two files control file and csv file.
Step 2: Change the following in the control file

•   Path of the csv file.
•   Table name.
•   Column names for which the data has to be inserted.

Step 3: Execute the command sqlldr control=c:\ctrl3, log=c:\tablename.log userid=username/password@connectionname
    


Blob and Date Column CSV Upload via SQLLDR
  
Step 1: Before loading data you need two files control file and csv file .

This is different from normal upload since it has date field to be loaded and XML_BLOB field that need to loaded.

Prerequisite: Char_Set_ID will be given
  
Step 2: Export the Char_Set  table (for the Char_Set_ID given) excluding the char_set_xml column as csv.

Step 3: Now export the char_set_xml column for each of the given Char_Set_ID as single dat file.
  
Step 4: Change the following in the csv file

•  In the XML_BLOB (char_Set_xml) column mention the path of the dat file XML_BLOB (char_Set_xml).
•  Also check for the date format of the date fields as mentioned in the control file.

Step 5: Change the following in the control file

•  Path of the csv file.
•  Table name.
•  Column names for which the data has to be inserted.
• Give the correct format of the date fields. For Example insert_date Date "MM/DD/YYYY HH24:MI:SS",
• XML_BLOB column has to be mentioned as below in the control file. For Example lob file filler char,CHAR_SET_XML LOBFILE(lob_file) TERMINATED BY EOF
  

Step 4: Execute the command sqlldr control=c:\ctrl, log=c:\tablename.log userid=suvin/welcome@connectionname

 
Control File Content


LOAD DATA

INFILE "c:\char_set.csv"
Append INTO TABLE CHAR_SET
FIELDS TERMINATED BY ','
(
CHAR_SET_ID,
CHAR_SET_ID,
CHAR_SET_NAME,
QUERY_TABLE_ID,
lob_file filler char,
CHAR_SET_XML LOBFILE(lob_file) TERMINATED BY EOF,
domain_name,
insert_user,
insert_date Date "MM/DD/YYYY HH24:MI:SS",
update_user,
update_date Date "MM/DD/YYYY HH24:MI:SS"
)

Sample

Microsoft Windows XP [Version 5.1.2600]

(C) Copyright 1985-2001 Microsoft Corp.


C:\Documents and Settings\;sqlldr userid=suvin/welcome@tmstest control=c:/CTRL4, log=tablename_04_NOV.log


SQL*Loader: Release 10.1.0.2.0 - Production on Wed Nov 3 17:13:44 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.


Commit point reached - logical record count 53


C:\Documents and Settings\sqlldr userid=suvin/welcome@tmsprod1 control=c:/CTRL4, log=tablename_04_NOV.log


SQL*Loader: Release 10.1.0.2.0 - Production on Wed Nov 3 17:16:01 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.


Commit point reached - logical record count 53


C:\Documents and Settings\;