scp, ssh, and rsync no prompting solution

Source host: oratest-sg29
Destination host: oradev01

1. On oratest-sg29, run this command as scpuser or whoever runs the scp in shell script.
$ ssh-keygen -t rsa
Press enter, then it will generate a public key and a private key.
Don’t share the private key.
The public key should have been saved on
/.ssh/id_rsa.pub
=====sample output=======
[oradev03 2] [portald] ~ > ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/export/home/oracle/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /export/home/oracle/.ssh/id_rsa.
Your public key has been saved in /export/home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
58:07:9d:ae:69:48:b3:8b:2a:65:2b:3a:8f:ff:ea:80 oracle@oradev03
[15:18:57]
[oradev03 3] [portald] ~ >


2. Scp id_rsa.pub from source/oratest-sg29 to destination/oradev01.


3. On destination server, login as the remote user which you plan to use scp.


4. Copy the contents of id_rsa.pub to ~/.ssh/authorized_keys
===========sample============
$ cat id_rsa.pub >>~/.ssh/authorized_keys
$ chmod 700 ~/.ssh/authorized_keys


5. Done. Enjoy the no prompt scp!

How External Table Works in StandBy Database

Standby database/Data Guard is highly used for reporting purposes. However, when you want to write some log data for your reporting purpose, you might encounter an issue that you are not allowed to do so on the standby instance.

In this case, using External Table is an option. You can create a stored procedure and use it to write os file for the external table.

Here is a sample code.

PROCEDURE wrt_transaction_log (p_job_id IN NUMBER,
p_programname IN VARCHAR2,
p_filename IN VARCHAR2,
p_full IN VARCHAR2,
p_feedstartts IN DATE,
p_feedendts IN DATE,
p_status IN VARCHAR2,
p_message IN VARCHAR2)
IS
v_output_file UTL_FILE.file_type;
filename VARCHAR2 (100);
v_string VARCHAR2 (2000);
nodata EXCEPTION;
BEGIN
EXECUTE IMMEDIATE ('ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS''');

filename := 'at_genpdfile_pkg_log_ext.txt';
v_output_file := UTL_FILE.fopen ('XTABLES', filename, 'A');

v_string :=
at_genpdfile_pkg_log_seq.NEXTVAL
|| '|'
|| p_job_id
|| '|'
|| USER
|| '|'
|| p_programname
|| '|'
|| p_filename
|| '|'
|| p_full
|| '|'
|| p_feedstartts
|| '|'
|| p_feedendts
|| '|'
|| p_status
|| '|'
|| p_message
|| '|'
|| SYSDATE;
UTL_FILE.put_line (v_output_file, v_string);
UTL_FILE.fclose_all;
END;

How To Configure APEX Not To Prompt For User/Password

When I installed APEX for our clinical research using EPG, we got prompted to enter anonymous username password everytime. Try google...no luck. Oracle support just refuse my ticket because they just don't support 10gR2 using EPG....so I have to figure it out by my own in meta link.

Here is the solution:

If anonymous access to XML DB repository data via HTTP is not required, then you do not have to perform this step.

If anonymous access to XML DB repository data via HTTP is required, then you must provide correct configuration information, as described in this section. The administrator must carefully consider whether anonymous access is to be allowed, given the inherent security risks.

If you wish to have anonymous access to XML DB repository data via HTTP, you must change the XML DB configuration file (located at /xdbconfig.xml in the repository) by adding an additional element "allow-repository-anonymous-access" and set this new element to true, in addition to unlocking the ANONYMOUS user account.



connect sys/password@tns_alias AS SYSDBA

-- enable anonymous access to XDB repository

SET SERVEROUTPUT ON
DECLARE
l_cfgxml XMLTYPE;
l_value VARCHAR2(5) := 'true'; -- (true/false)
BEGIN
l_cfgxml := DBMS_XDB.cfg_get();

IF l_cfgxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
-- Add missing element.
SELECT insertChildXML
(
l_cfgxml,
'/xdbconfig/sysconfig/protocolconfig/httpconfig',
'allow-repository-anonymous-access',
XMLType('' ||
l_value ||
''),
'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
)
INTO l_cfgxml
FROM dual;

DBMS_OUTPUT.put_line('Element inserted.');
ELSE
-- Update existing element.
SELECT updateXML
(
DBMS_XDB.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()',
l_value,
'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
)
INTO l_cfgxml
FROM dual;

DBMS_OUTPUT.put_line('Element updated.');
END IF;

DBMS_XDB.cfg_update(l_cfgxml);
DBMS_XDB.cfg_refresh;
END;
/




Once the "" element is set to "true", anonymous access to the XML DB repository, but not web services, is enabled by unlocking the anonymous database account.



connect sys/password@tns_alias AS SYSDBA

ALTER USER anonymous ACCOUNT UNLOCK;
Removing anonymous access to the XML DB repository, not web services, can be accomplished by locking the anonymous database account, or setting the "" element back to "false".

If you need to remove the "" element entirely, it can be accomplished using the following code:

connect sys/password@tns_alias AS SYSDBA

SET SERVEROUTPUT ON
DECLARE
l_cfgxml XMLTYPE;
BEGIN
l_cfgxml := DBMS_XDB.cfg_get();

IF l_cfgxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') != 0 THEN
SELECT deleteXML
(
l_cfgxml,
'/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access',
'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"'
)
INTO l_cfgxml
FROM dual;

DBMS_XDB.cfg_update(l_cfgxml);
DBMS_XDB.cfg_refresh;

DBMS_OUTPUT.put_line('Element deleted.');
END IF;
END;
/

How to audit failed login attempts in SQL Server

For sql server, it is easier.

How to audit failed login attempts in Oracle

I just copy the article from Mr Messin's blog.

You must set the audit_trail=DB in the init.ora/spfile
then you must audit with audit session whenever not successful ;
Here is a complete walk through on the setup and a script that will help query the audit trail for the failed login attempts. Be sure to clean up you audit trail so it does not grow out of control.

The Example Walk Through for Setup:

SQL> connect sys as sysdba
Enter password: ******
Connected.
SQL> alter system set audit_trail=DB scope=spfile ;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 234883972 bytes
Database Buffers 369098752 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> audit session whenever not successful ;

Audit succeeded.

SQL> connect dummy/dummy
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect sys as sysdba
Enter password: ******
Connected.
SQL> select os_username,
username,
userhost,
to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
returncode
from dba_audit_session
where action_name = 'LOGON'
and returncode > 0
order by timestamp ;

OS_USERNAME
--------------------------------------------------------------------------------
USERNAME
------------------------------
USERHOST
--------------------------------------------------------------------------------TIMESTAMP RETURNCODE
------------------- ----------------
MRMESSIN\Mike Messina
DUMMYWORKGROUP\MRMESSIN
11/08/2007 09:07:54 1017
SQL>

Here is a script that will show you the failed login attempts made to an Oracle Database after your setup.

-----------------------------------------------
-- see_failed_login_attempts.sql
--
-- Michael Messina
--
-- query the Oracle Audit Trail and
-- will write a log file of the failed
-- login attempts for the database.
--
-- Requires:
-- audit_trail=DB in init.ora/spfile
-- audit session whenever not successful ;
-----------------------------------------------
set pagesize 200
set linesize 150
column os_username format a15
column username format a15
column userhost format a40
column timestamp format a20
column returncode format 9999999999

spool failed_login_attempts.log

select os_username,
username,
userhost,
to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
-- action_name,
returncode
from dba_audit_session
where action_name = 'LOGON'
and returncode > 0
order by timestamp ;

spool off

How to spool to a single file in SQL Plus

The team wants my scripts to spool to one log file so it is easier to verify. I found the following from Oracle Forum.

For UNIX or Linux.

spool temp.txt
your_sql;
spool off
host cat temp.txt >> your_file.txt
host rm temp.txt

For Windows.

spool temp.txt
your_sql;
spool off
host type temp.txt >> your_file.txt
host del temp.txt

Finally I can see some data

It is real fun that when you see the tool shows you some data after putting some efforts into the site. That is my way to test out the sites visibility.

To improve the visibility of your website, there are several points you have to verify:

First of all, the on page factors. These are basic stuffs that you have to look at. If you optimize them, you have a good foundation. However, that doesn't means you will rank high by doing these basic things. Because most of the websites have done that already. If you don't do, you won't be ranked even with tons of other efforts.

Secondly, you have to pick good keywords, that can drive you traffic and profits. That sounds easy, but not that simply as you thought. High traffic keywords always have higher competitions. What are you going to do?

Last but not the least, back links from other reputation sites. The way google beat yahoo is that google use back links to evaluate a site. So you know how important they are.

In conclusion, we need to check quite some elements to ensure our sites' visibility on the internet.

partitioning in SQL Server 2005

I am working on an assignment of migrating a database from SQL 2000 to 2005. That is not a big deal. But at the mean time, there is a huge table contains orders in the database which I want to partition it. In oracle, we can use redefine package to partition an existing table, this won't be an issue. However, this is SQL Server...so I have to determine what is the best practice...
1)migrate the whole db, create a new partitioned order_new table, move the data to the order_new, rename order to order_old, rename order_new to order.
2)migrate the whole db, use scripts(if there is any) to partition the existing order table.
3)migrate the whole db except the order table, create a partition one on the new server, import the data.(the migration might encounter constraint issues, not a good approach)
4)any other ideas?

my new Site for quality link building

I think the team should focus on building quality links.

About being a good DBA

A set of useful DBA Script is a must have tool.