Dev's Weblog

Reading time: 1 - 2 minutes

#!/bin/bash
primary=`sqlplus -s sys/passwd@primary as sysdba << EOF
SET HEADING OFF
select
max(sequence#) current_seq
from v\\$log;
EOF`

echo "---------------------------------------"
echo "Current Redo Log Sequence of Primary DB"
echo "---------------------------------------"
echo $primary

standby=`sqlplus -s sys/passwd@standby as sysdba << EOF
SET HEADING OFF
select
max(applied_seq#) last_seq
from
v\\$archive_dest_status;
EOF`

echo "--------------------------------------"
echo "Last applied Log Sequence of Standby DB"
echo "--------------------------------------"
echo $standby

gap=`expr $primary - $standby`
echo "-------------------------------------------------"
echo "Redo Log Gap Between Primary and Standby Database"
echo "-------------------------------------------------"
echo "$gap"
if [ $gap -le 3 ] ; then
echo "-------------------------------------------------"
echo "Standby Database is in sync with Primary database"
echo "-------------------------------------------------"
else
echo "---------------------------------------------------------"
echo "Standby database running behind by $gap redo log sequence"
echo "---------------------------------------------------------"
fi

 

Result of script would look like,

---------------------------------------Current Redo Log Sequence of Primary DB---------------------------------------79--------------------------------------Last applied Log Sequnce of Standby DB--------------------------------------77-------------------------------------------------Redo Log Gap Between Primary and Standby Database-------------------------------------------------2-------------------------------------------------Standby Database is in sync with Primary database-------------------------------------------------

Reading time: 3 - 4 minutes

Calculate UNDO_RETENTION for given UNDO Tablespace

You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time!
Actual Undo Size

SELECT SUM(a.bytes) UNDO_SIZE FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#;

UNDO_SIZE
----------
209715200

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) UNDO_BLOCK_PER_SEC FROM v$undostat;

UNDO_BLOCK_PER_SEC
------------------
3.12166667

DB Block Size

SELECT TO_NUMBER(value) DB_BLOCK_SIZE [KByte] FROM v$parameter WHERE name = 'db_block_size';

DB_BLOCK_SIZE [Byte]
--------------------
4096

Optimal Undo Retention

209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]

Using Inline Views, you can do all in one query!

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZEA [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status =A 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECTA MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/

ACTUAL UNDO SIZE [MByte]
------------------------
200

UNDO RETENTION [Sec]
--------------------
10800

OPTIMAL UNDO RETENTION [Sec]
----------------------------
16401

Calculate Needed UNDO Size for given Database Activity

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Again, all in one query:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/

ACTUAL UNDO SIZE [MByte]
------------------------
200

UNDO RETENTION [Sec]
--------------------
10800

NEEDED UNDO SIZE [MByte]
------------------------
131.695313

The previous query may return a "NEEDED UNDO SIZE" that is less than the "ACTUAL UNDO SIZE". If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.

Reading time: 1 - 2 minutes

You have to do this through the WordPress database directly. The most convenient way to manage the database is via the phpMyAdmin tool at your web hosting account.

Once in phpMyAdmin select the WordPress database from the drop-down menu on the left. The page will refresh and the database's tables will be displayed on it. Open the SQL tab (look at the top navigation bar).

In the text field write the following SQL query:

UPDATE `wp_users` SET `user_pass` = MD5( 'new_password_here' ) WHERE `wp_users`.`user_login` = "admin";

"new_password_here" - replace this with the new password you wish to use.
"admin_username" - replace this with the username the password should be updated for.

Once you are ready, click on the GO button to submit the query. If everything goes fine without errors, you should be able to login to WordPress with the new password.

Reading time: 7 - 12 minutes

Cloning an Oracle user has always been problematic because of all of the specific grants of privileges and data access. 

Raymond Wauben has published this script for cloning Oracle users, noting the transfer of salient system and object privileges.

This is taken from http://www.dba-oracle.com/t_cloning_oracle_user_id.htm

========================================================================================================================

Rem
Rem $Header: crusrlk.sql 03-apr-2007 $
Rem
Rem Author: raymond.wauben@gmail.com
Rem    NAME
Rem      crusrlk.sql
Rem    DESCRIPTION
Rem      Create a new database schema/user like an existing database schema/user.
Rem    RETURNS
Rem
Rem    NOTES
Rem      This script must be run while connected as a user with DBA privileges.
Rem      In case of directory users, new users reside in the same directory
Rem      information tree as existing users and database account equals to
Rem      directory account.
Rem      This script does not completely clone user SYS.
Rem      This script has been tested successfully against the following
Rem      Oracle Server version(s):
Rem        - Oracle Server 10g Release 1;
Rem        - Oracle Server 10g Release 2.
Rem    MODIFIED   (MM/DD/YY)
Rem     rwauben    04/03/07 - Creation

set define '%'
set echo off
set feedback off
set heading off
set linesize 160
set pagesize 0
set pause on
set trimspool on
set verify off

clear screen

prompt --------------------------------------------------------------------------------;
prompt Create a new schema/user like an existing schema/user.;
prompt Defaults are shown between brackets [].;
prompt --------------------------------------------------------------------------------;

accept OriginalUser prompt "Enter existing database schema/user [%_USER]: " default %_USER
accept NewUser      prompt "Enter new database schema/user [SCOTT]: " default SCOTT
accept NewPassword  prompt "Enter password for new database schema/user (Leave blank to copy): " hide

prompt ================================================================================;
prompt You entered the following information:
prompt ;

prompt Existing database schema/user     : %OriginalUser
prompt New database schema/user          : %NewUser

prompt ;
prompt If this is correct, press ENTER to generate SQL and PL/SQL statements and
prompt create the new schema/user, otherwise press CTRL+C to cancel and return to the
prompt SQL*Plus prompt.;
prompt ;
prompt Make sure you have DBA privileges before continuing!;
prompt ================================================================================;
pause

undefine v_file_name
column v_file_name new_value v_file_name

undefine v_remove_command
column v_remove_command new_value v_remove_command

set termout off

select to_char(sysdate,'YYYYMMDD_HH24MISS') || '_' || user || '.sql' as v_file_name from dual;

Rem Determine use of either Windows 'del' command or Linux/Unix 'rm' command.
select case when lower(program) like '%.exe' then 'del'
       else 'rm'
       end as v_remove_command
from v$session
where sid = (select distinct sid
from v$mystat);

set termout on

spool %v_file_name

Rem Build CREATE USER statement. --------------------------------------------------

select 'CREATE USER %NewUser ' ||
       case when password = 'EXTERNAL' then 'IDENTIFIED EXTERNALLY'
            when password = 'GLOBAL'   then 'IDENTIFIED GLOBALLY AS ''' ||
              replace (external_name,'%OriginalUser','%NewUser') || ''''
            else 'IDENTIFIED BY ' ||
              decode(upper('%NewPassword'),null,'VALUES ''' || password || '''','%NewPassword')
       end
       || ' DEFAULT TABLESPACE ' || default_tablespace ||
       ' TEMPORARY TABLESPACE ' || temporary_tablespace ||
       ' PROFILE ' || profile || ' ACCOUNT ' ||
       decode(account_status,'OPEN','UNLOCK',
                             'EXPIRED','UNLOCK PASSWORD EXPIRE',
                             'EXPIRED(GRACE)','UNLOCK',
                             'LOCKED(TIMED)','UNLOCK',
                             'LOCKED','LOCK',
                             'EXPIRED & LOCKED(TIMED)','UNLOCK PASSWORD EXPIRE',
                             'EXPIRED(GRACE) & LOCKED(TIMED)','UNLOCK',
                             'EXPIRED & LOCKED','LOCK PASSWORD EXPIRE',
                             'EXPIRED(GRACE) & LOCKED','LOCK',
                             'LOCK') || ';'
from dba_users
where username = upper('%OriginalUser');

Rem -------------------------------------------------------------------------------

Rem Check tablespace quotas and build ALTER USER statements. ----------------------

select 'ALTER USER %NewUser QUOTA ' ||
       decode(max_bytes,-1,'UNLIMITED',max_bytes) ||
       ' ON ' || tablespace_name || ';'
from sys.dba_ts_quotas
where username = upper('%OriginalUser');

Rem -------------------------------------------------------------------------------

Rem Check SYSDBA and/or SYSOPER privileges and build GRANT statement. -------------

select decode(sysdba, 'TRUE', 'GRANT SYSDBA TO %NewUser;', null) sysdba
from v$pwfile_users
where username = upper('%OriginalUser');

select decode(sysoper, 'TRUE', 'GRANT SYSOPER TO %NewUser;', null) sysoper
from v$pwfile_users
where username = upper('%OriginalUser');

Rem -------------------------------------------------------------------------------

Rem Check system privileges and build GRANT statements. ---------------------------

select 'GRANT ' || privilege || ' TO %NewUser' ||
       decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
from sys.dba_sys_privs
where grantee = upper('%OriginalUser');

Rem -------------------------------------------------------------------------------

Rem Check roles and build GRANT statements. ---------------------------------------

select 'GRANT ' || granted_role || ' TO %NewUser' ||
       decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
from sys.dba_role_privs
where grantee = upper('%OriginalUser');

Rem -------------------------------------------------------------------------------

Rem Check default roles and build ALTER USER ... DEFAULT ROLE ... statement. ------

set serveroutput on

declare
  v_default_roles varchar2(4000) := null;
begin
  for c1 in (select * from sys.dba_role_privs
             where grantee = upper('%OriginalUser')
             and default_role = 'YES')
  loop
    if length(v_default_roles) > 0 then
      v_default_roles := v_default_roles || ',' || c1.granted_role;
    else
      v_default_roles := v_default_roles || c1.granted_role;
    end if;
  end loop;

  if length(v_default_roles) > 0 then
    dbms_output.put_line('ALTER USER %NewUser DEFAULT ROLE ' || v_default_roles || ';');
  end if;
end;
/

set serveroutput off

Rem -------------------------------------------------------------------------------

Rem Check table and column privileges and build GRANT statements. -----------------

select 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name ||
       ' TO %NewUser' || decode(grantable,'YES',' WITH GRANT OPTION;',';')
from (select usrge.name grantee, usr.name owner, obj.name table_name, null column_name,
      usrgr.name grantor, tabprivmap.name privilege,
      decode(mod(objauth.option$,2), 1, 'YES', 'NO') grantable,
      decode(bitand(objauth.option$,2), 2, 'YES', 'NO') hierarchy,
      decode(obj.type#, 2, 'TABLE', 4, 'VIEW', 6, 'SEQUENCE', 7, 'PROCEDURE',
                        8, 'FUNCTION', 9, 'PACKAGE', 13, 'TYPE', 22, 'LIBRARY',
                        23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE',
                        29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE',
                        33, 'OPERATOR', 42, 'MATERIALIZED VIEW',
                        'UNDEFINED') object_type
from sys.objauth$ objauth, sys.obj$ obj, sys.user$ usr, sys.user$ usrgr, sys.user$ usrge,
     sys.table_privilege_map tabprivmap
where objauth.obj# = obj.obj#
and objauth.grantor# = usrgr.user#
and objauth.grantee# = usrge.user#
and objauth.col# is null
and objauth.privilege# = tabprivmap.privilege
and usr.user# = obj.owner#
and obj.type# in (2, 4, 6, 7, 8, 9, 13, 22, 24, 28, 29, 30, 32, 33, 42)
and usrge.name = upper('%OriginalUser')
union all
select usrge.name, usr.name, obj.name, col.name, usrgr.name, tabprivmap.name,
       decode(mod(objauth.option$,2), 1, 'YES', 'NO'),
       null hierarhy,
       decode(obj.type#, 2, 'TABLE', 4, 'VIEW', 42, 'MATERIALIZED VIEW')
from sys.objauth$ objauth, sys.obj$ obj, sys.user$ usr, sys.user$ usrgr, sys.user$ usrge,
     sys.col$ col, sys.table_privilege_map tabprivmap
where objauth.obj# = obj.obj#
and objauth.grantor# = usrgr.user#
and objauth.grantee# = usrge.user#
and objauth.obj# = col.obj#
and objauth.col# = col.col#
and objauth.col# is not null
and objauth.privilege# = tabprivmap.privilege
and usr.user# = obj.owner#
and obj.type# in (2, 4, 42)
and bitand(col.property, 32) = 0
and usrge.name = upper('%OriginalUser'));

Rem -------------------------------------------------------------------------------

Rem Check Java privileges and build PL/SQL statements. ----------------------------

set serveroutput on

declare
  i integer := 1;
begin
  for c1 in (select kind, grantee, type_schema, type_name,
             name, action, enabled, seq
             from sys.dba_java_policy
             where grantee = upper('%OriginalUser')
             order by seq)
  loop
    if i = 1 then
      dbms_output.put_line('DECLARE');
      dbms_output.put_line('KEYNUM NUMBER;');
      dbms_output.put_line('BEGIN');
      i := 2;
    end if;

    if c1.kind = 'GRANT' then
      dbms_output.put_line('SYS.DBMS_JAVA.GRANT_PERMISSION(GRANTEE => ''' ||
                                  upper('%NewUser') || ''', PERMISSION_TYPE => ''' ||
                                  c1.type_schema || ':' || c1.type_name ||
                                  ''', PERMISSION_NAME => ''' || c1.name ||
                                  ''', PERMISSION_ACTION => ''' || c1.action ||
                                  ''', KEY => KEYNUM);');
    elsif c1.kind = 'RESTRICT' then
      dbms_output.put_line('SYS.DBMS_JAVA.RESTRICT_PERMISSION(GRANTEE => ''' ||
                                  upper('%NewUser') || ''', PERMISSION_TYPE => ''' ||
                                  c1.type_schema || ':' || c1.type_name ||
                                  ''', PERMISSION_NAME => ''' || c1.name ||
                                  ''', PERMISSION_ACTION => ''' || c1.action ||
                                  ''', KEY => KEYNUM);');
    end if;
  end loop;

  if i = 2 then
    dbms_output.put_line('END;');
    dbms_output.put_line('/');
  end if;
end;
/

set serveroutput off

Rem -------------------------------------------------------------------------------

Rem Check resource group privileges and build PL/SQL statements. ------------------

set serveroutput on

declare
  i integer := 1;
  v_initial_group varchar2(30) := null;
begin
  for c1 in (select grantee,
                    granted_group,
                    decode(grant_option,'YES','TRUE','FALSE') grant_option,
                    initial_group
             from dba_rsrc_consumer_group_privs
             where grantee = upper('%OriginalUser'))
  loop
    if i = 1 then
      dbms_output.put_line('BEGIN');
      dbms_output.put_line('SYS.DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();');
      dbms_output.put_line('SYS.DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();');
      i := 2;
    end if;

    dbms_output.put_line('SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(''%NewUser'',''' || c1.granted_group || ''',' || c1.grant_option || ');');

    if c1.initial_group = 'YES' then
      v_initial_group := c1.granted_group;
    end if;
  end loop;

  if i = 2 then
    dbms_output.put_line('SYS.DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();');
    dbms_output.put_line('END;');
    dbms_output.put_line('/');
  end if;

  if v_initial_group is not null then
    dbms_output.put_line('BEGIN');
    dbms_output.put_line('SYS.DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(''%NewUser'',''' || v_initial_group || ''');');
    dbms_output.put_line('END;');
    dbms_output.put_line('/');
  end if;
end;
/

set serveroutput off

Rem -------------------------------------------------------------------------------

Rem Check proxy authentication and build ALTER USER ... statements. ---------------

set serveroutput on

declare
  v_proxy_roles varchar2(4000);
begin
  for c1 in (select distinct decode(proxy,null,'ENTERPRISE USERS',proxy) proxy,
                    client,
                    decode(authentication,'YES',' AUTHENTICATION REQUIRED') authentication
             from sys.dba_proxies
             where client = upper('%OriginalUser'))
  loop
    v_proxy_roles := null;

    for c2 in (select role
             from sys.dba_proxies
             where nvl(proxy,'ENTERPRISE USERS') = c1.proxy
             and client = c1.client
             and role is not null)
    loop
      if v_proxy_roles is null then
        v_proxy_roles := ' WITH ROLES ' || c2.role;
      else
        v_proxy_roles := v_proxy_roles || ', ' || c2.role;
      end if;
    end loop;

    dbms_output.put_line('ALTER USER %NewUser GRANT CONNECT THROUGH ' || c1.proxy || v_proxy_roles || c1.authentication || ';');
  end loop;
end;
/

set serveroutput off

Rem -------------------------------------------------------------------------------

spool off

prompt --------------------------------------------------------------------------------;
prompt Creating new database schema/user %NewUser like %OriginalUser ...;
prompt Only error messages are displayed.
prompt --------------------------------------------------------------------------------;

@%v_file_name

prompt --------------------------------------------------------------------------------;
prompt Ready.;
prompt --------------------------------------------------------------------------------;

host %v_remove_command %v_file_name           

Reading time: < 1 minute

PROBLEM:

========

I had installed Ubuntu 8.10 on my desktop yesterday and i found that it was taking lot of time when i try to SSH into my box.

SOLUTION:

=========

open /etc/ssh/sshd_config and add following entry.

UseDNS no

Restart SSH using following command.

sudo /etc/init.d/sshd restart