rlwrap installation

/* To Install */
# download rlwrap-0.30.tar.gz (search the net or from http://www.ahmedbaraka.com/download/oracle/rlwrap-0.30.tar.gz )
# unzip the file and install as Root User...
#readling package needed
rpm -qa | grep readline

readline-5.1-3.el5

readline-5.1-3.el5
readline-devel-5.1-3.el5
readline-devel-5.1-3.el5

su -
gunzip rlwrap-0.30.tar.gz
tar -xvf rlwrap-0.30.tar
cd rlwrap-0.30
./configure
make
make install
# for rpm version:
# can be downloaded from:
# http://ivan.kartik.sk/oracle/download_from.php?site_id=5
# http://ivan.kartik.sk/oracle/download_from.php?site_id=9
rpm -ivh rlwrap*.rpm
#
echo "alias rman2='rlwrap rman'" >> /home/oracle/.bashrc========================
rlwrap installation error


I download the zip file from here: http://utopia.knoware.nl/~hlub/uck/rlwrap/

...and take the following installation steps which result in error.

root ~# gunzip rlwrap-0.30.tar.gz
root ~# tar -xvf rlwrap-0.30.tar
root ~# cd rlwrap-0.30
root rlwrap-0.30# ./configure
.........
........
OPENPTY
configure: checking for pty ranges...
checking for tgetent... no
checking for tgetent in -lcurses... no
checking for tgetent in -lncurses... no
checking for tgetent in -ltermcap... no
configure: WARNING: No termcap nor curses library found
checking for readline in -lreadline... no
configure: error:
You need the GNU readline library(ftp://ftp.gnu.org/gnu/readline/ ) to build
this program!

ah, the package is called 'readline', thus

yum install readline

should be sufficient

If it gives trouble, install the devel package belonging to readline too:

yum install readline-devel

If you need to find the package to install for a certain file, use 'provides':

yum provides */libreadline*

I already had readline, but needed readline-devel.
orto make it even more easy, rlwrap is included in the EPEL repository: https://fedoraproject.org/wiki/EPEL

include the epel repository:

rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-3.noarch.rpm

then install rlwrap:

yum install rlwrap

MEMORY_TARGET not supported on this system

MEMORY_TARGET not supported on this system 
Oracle9i introduced pga_aggregate_target, you can adjust the PGA automatically; Oracle10 introduction sga_target, can automatically adjust to the SGA. Oracle11g then the two parts are integrated, the introduction of memory_target, can automatically adjust all of the memory, which is newly introduced automatic memory management features. 
SQL> alter system set memory_target = 200m scope = spfile; 

System altered. 

SQL> alter system set memory_target = 200m scope = spfile; 

System altered. 

SQL> alter system set sga_target = 0 scope = spfile; 

System altered. 

SQL> alter system set pga_aggregate_target = 0 scope = spfile; 

System altered. 

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

SQL> startup 
ORACLE instance started. 

Total System Global Area 209235968 bytes 
Fixed Size 1298920 bytes 
Variable Size 150998552 bytes 
Database Buffers 54525952 bytes 
Redo Buffers 2412544 bytes 
Database mounted. 
Database opened. 

Set memory_target parameters, in fact Oracle will automatically set and adjust the two double-underscore at the beginning of the implicit parameter to the allocation of SGA and PGA memory, which, and Oracle10g is automatically set sga_target are assigned db_cache_size and shared_pool_size mechanism is the same as 
SQL> select a.ksppinm name, b.ksppstvl value 
2 from x $ ksppi a, x $ ksppcv b 
3 where a.indx = b.indx 
4 and (a.ksppinm like '% sga_target%' 
5 or a.ksppinm like '% pga_aggregate_target%'); 

NAME VALUE 
---------------------------------------- ---------- -------------------- 
sga_target 0 
__sga_target 125829120 
pga_aggregate_target 0 
__pga_aggregate_target 79691776 

If memory_max_target / memory_target set too large, may result in instance not start, reported that ORA-00845 error 
SQL> alter system set memory_max_target = 300m scope = spfile; 

System altered. 

SQL> shutdown immediate; 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 
SQL> startup 
ORA-00845: MEMORY_TARGET not supported on this system 

This error is a bit misleading, in fact does not mean that the platform does not support the version of AMM features, but more than the system set up memory_max_target set share memory (/ dev / shm) only. In Oracle11g for linux seems to use a new mechanism to manage the shared memory segment, rather than the traditional sys v shm had. The alert.ora can be found in a more accurate description of the error 

Starting ORACLE instance (normal) 
WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the / dev / shm file system to be mounted for at least 314572800 bytes. / Dev / shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 209715200 and used is 0 bytes. 
memory_target needs larger / dev / shm 

Therefore, this error there are two solutions. The first is to reduce the natural memory_max_target / memory_target. The second is to increase the / dev / shm 
[root @ localhost ~] # df-k / dev / shm 
Filesystem 1K-blocks Used Available Use% Mounted on 
shmfs 204800 0 204800 0% / dev / shm 

[root @ localhost ~] # umount / dev / shm 
[root @ localhost ~] # mount-t tmpfs shmfs-o size = 300m / dev / shm 

[root @ localhost ~] # df-k / dev / shm 
Filesystem 1K-blocks Used Available Use% Mounted on 
shmfs 358400 0 358400 0% / dev / shm 

[root @ localhost trace] # ls-l / dev / shm 
total 0 

Increase / dev / shm and re-start the instance 
SQL> startup 
ORACLE instance started. 

Total System Global Area 313860096 bytes 
Fixed Size 1299624 bytes 
Variable Size 255855448 bytes 
Database Buffers 50331648 bytes 
Redo Buffers 6373376 bytes 
Database mounted. 
Database opened. 

Let's look at / dev / shm the contents of the 
[root @ localhost trace] # df-k / dev / shm 
Filesystem 1K-blocks Used Available Use% Mounted on 
shmfs 307200 122792 184408 40% / dev / shm 
[root @ localhost trace] # ls-l / dev / shm 
total 122792 
-rw-r ----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_0 
-rw-r ----- 1 oracle oinstall 4194304 Sep 10 21:15 ora_ning_1179659_1 
-rw-r ----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_10 
-rw-r ----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_11
-rw-r ----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_12 
-rw-r ----- 1 oracle oinstall 0 Sep 10 21:15 ora_ning_1179659_13 
...... 

Attached: / dev / shm to use: 

Using tmpfs speed up your linux server uses virtual disks to store squid cache files and php the seesion. Fast and a lot of Oh! 

By default the system will load the / dev / shm, it is the so-called tmpfs, Some say that with the ramdisk (virtual disk), but not the same. The same as the virtual disk, tmpfs can use your RAM, but it can also use your swap partition to store. And the traditional virtual disk is a block device, and the need for a sort of command mkfs can really use it, tmpfs is a file system, rather than a block device; you just install it, it can use. 

tmpfs has the following advantages: 
1. Dynamic file system size, 
2. tmpfs Another major advantage is its lightning speed. Because a typical tmpfs file system will be fully reside in RAM, the read and write could be almost instantaneous. 
3. tmpfs data not be retained after you restart, because virtual memory is volatile in nature. It is necessary to do some scripting to do, such as loading, binding operations. 

Well talk about a number of major principles, we are tired of looking at it, I still talk about the application of it:) 

First in the / dev / shm to build a tmp folder, and then with the actual / tmp binding 

mkdir / dev / shm / tmp 
chmod 1777 / dev / shm / tmp 
mount - bind / dev / shm / tmp / tmp 

1. Squid cache directory settings 

vi / etc / squid / squid.conf 

Modified to 
cache_dir ufs / tmp 256 16 256 
The first one here, 256 that the use 256M memory, I think http://www.linuxaid.com.cn/articles/4/4 / 441672019.shtml where the method does not use the ramdisk as the direct use of tmpfs, at least every time you start do not mkfs , you can also dynamically change the size. This time, / tmp is actually the / dev / shm / tmp 

Then restart about service, ok, now all of the squid cache files are saved inside inverted tmpfs file system, and soon oh. 

2. For php performance optimization 

For a visit to a large quantity of apache + php website may tmp will be under a lot of temporary files, such as seesion or some cache file, then you can save it to a tmpfs file. 

Save seesion method is very simple as long as a modified php.ini on the list, as I have already / dev / stm / tmp and / tmp binding, it is not rewritten are also OK, as php program generated cache file, it will only change their own php program has:) 

As for the tmpfs other applications, I think we may be inspired by this article will.

Oracle 11Gr2 Linux RPM Package Required

2.3.2 Kernel Requirements

The following are the kernel requirements for Oracle Database 11g Release 2 (11.2):

• On Asianux 2, Oracle Enterprise Linux 4, and Red Hat Enterprise Linux 4:
  

  2.6.9 or later
  

• On Asianux 3, Oracle Enterprise Linux 5, and Red Hat Enterprise Linux 5:
  

  2.6.18 or later
  

• On SUSE Linux Enterprise Server 10:
  

  2.6.16.21 or later
  

• On SUSE Linux Enterprise Server 11:
  

  2.6.27.19 or later
  

To determine whether the required kernel is installed, enter the following command:

# uname -r

The following is a sample output displayed by running this command on an Oracle Enterprise Linux 5 system:

2.6.18-128.el5PAE

In this example, the output shows the kernel version (2.6.18) and errata level (-128.el5PAE) on the system.

If the kernel version does not meet the requirement, then contact the operating system vendor for information about obtaining and installing kernel updates.

2.3.3 Package Requirements

The following are the list of packages required for Oracle Database 11g Release 2 (11.2):

Note:

• Oracle recommends that you install your Linux operating system with the default software packages (RPMs), unless you specifically intend to perform a minimal installation, and follow the directions for performing such an installation to ensure that you have all required packages for Oracle software.
  
• Oracle recommends that you do not customize RPMs during a default operating system installation. A default installation includes most required packages, and will help you to limit manual checks of package dependencies.
  
• If you did not perform a default Linux installation, you intend to use LDAP, and you want to use the scripts odisrvreg, oidca, or schemasync, then install the Korn shell RPM for the Linux distribution.
  
• You must install the packages (or later versions) listed in the following table. Also, ensure that the list of RPMs and all of the prerequisites for these RPMs are installed.
  

On Linux x86:

Operating System		Requirement
Asianux 2, Oracle Enterprise Linux 4, and Red Hat Enterprise Linux 4		The following packages (or later versions) must be installed: binutils-2.15.92.0.2 compat-libstdc++-33-3.2.3 elfutils-libelf-0.97 elfutils-libelf-devel-0.97 gcc-3.4.6 gcc-c++-3.4.6 glibc-2.3.4-2.41 glibc-common-2.3.4 glibc-devel-2.3.4 glibc-headers-2.3.4 libaio-devel-0.3.105 libaio-0.3.105 libgcc-3.4.6 libstdc++-3.4.6 libstdc++-devel-3.4.6 make-3.80 numactl-0.6.4.i386 pdksh-5.2.14 sysstat-5.0.5 unixODBC-2.2.11 unixODBC-devel-2.2.11
Asianux 3, Oracle Enterprise Linux 5, and Red Hat Enterprise Linux 5	The following packages (or later versions) must be installed: binutils-2.17.50.0.6 compat-libstdc++-33-3.2.3 elfutils-libelf-0.125 elfutils-libelf-devel-0.125 elfutils-libelf-devel-static-0.125 gcc-4.1.2 gcc-c++-4.1.2 glibc-2.5-24 glibc-common-2.5 glibc-devel-2.5 glibc-headers-2.5 kernel-headers-2.6.18 ksh-20060214 libaio-0.3.106 libaio-devel-0.3.106 libgcc-4.1.2 libgomp-4.1.2 libstdc++-4.1.2 libstdc++-devel-4.1.2 make-3.81 numactl-devel-0.9.8.i386 sysstat-7.0.2 unixODBC-2.2.11 unixODBC-devel-2.2.11
SUSE Linux Enterprise Server 10	The following packages (or later versions) must be installed: binutils-2.16.91.0.5 compat-libstdc++-5.0.7 gcc-4.1.2 gcc-c++-4.1.2 glibc-2.4-31.63 glibc-devel-2.4-31.63 ksh-93r-12.9 libaio-0.3.104 libaio-devel-0.3.104 libelf-0.8.5 libgcc-4.1.2 libstdc++-4.1.2 libstdc++-devel-4.1.2 make-3.80 sysstat-8.0.4
SUSE Linux Enterprise Server 11	The following packages (or later versions) must be installed: binutils-2.19 gcc-4.3 gcc-c++-4.3 glibc-2.9 glibc-devel-2.9 ksh-93t libstdc++33-3.3.3 libstdc++43-4.3.3_20081022 libstdc++43-devel-4.3.3_20081022 libaio-0.3.104 libaio-devel-0.3.104 libgcc43-4.3.3_20081022 libstdc++-devel-4.3 make-3.81 sysstat-8.1.5

On Linux x86-64:

Operating System	Requirement
Asianux 2, Oracle Enterprise Linux 4, and Red Hat Enterprise Linux 4	The following packages (or later versions) must be installed: binutils-2.15.92.0.2 compat-libstdc++-33-3.2.3 compat-libstdc++-33-3.2.3 (32 bit) elfutils-libelf-0.97 elfutils-libelf-devel-0.97 expat-1.95.7 gcc-3.4.6 gcc-c++-3.4.6 glibc-2.3.4-2.41 glibc-2.3.4-2.41 (32 bit) glibc-common-2.3.4 glibc-devel-2.3.4 glibc-headers-2.3.4 libaio-0.3.105 libaio-0.3.105 (32 bit) libaio-devel-0.3.105 libaio-devel-0.3.105 (32 bit) libgcc-3.4.6 libgcc-3.4.6 (32-bit) libstdc++-3.4.6 libstdc++-3.4.6 (32 bit) libstdc++-devel 3.4.6 make-3.80 numactl-0.6.4.x86_64 pdksh-5.2.14 sysstat-5.0.5 unixODBC-2.2.11 unixODBC-2.2.11 (32 bit) unixODBC-devel-2.2.11 unixODBC-devel-2.2.11 (32 bit)
Asianux 3, Oracle Enterprise Linux 5, and Red Hat Enterprise Linux 5	The following packages (or later versions) must be installed: binutils-2.17.50.0.6 compat-libstdc++-33-3.2.3 compat-libstdc++-33-3.2.3 (32 bit) elfutils-libelf-0.125 elfutils-libelf-devel-0.125 gcc-4.1.2 gcc-c++-4.1.2 glibc-2.5-24 glibc-2.5-24 (32 bit) glibc-common-2.5 glibc-devel-2.5 glibc-devel-2.5 (32 bit) glibc-headers-2.5 ksh-20060214 libaio-0.3.106 libaio-0.3.106 (32 bit) libaio-devel-0.3.106 libaio-devel-0.3.106 (32 bit) libgcc-4.1.2 libgcc-4.1.2 (32 bit) libstdc++-4.1.2 libstdc++-4.1.2 (32 bit) libstdc++-devel 4.1.2 make-3.81 numactl-devel-0.9.8.x86_64 sysstat-7.0.2 unixODBC-2.2.11 unixODBC-2.2.11 (32 bit) unixODBC-devel-2.2.11 unixODBC-devel-2.2.11 (32 bit)
SUSE Linux Enterprise Server 10	The following packages (or later versions) must be installed: binutils-2.16.91.0.5 compat-libstdc++-5.0.7 gcc-4.1.0 gcc-c++-4.1.2 glibc-2.4-31.63 glibc-devel-2.4-31.63 glibc-devel-32bit-2.4-31.63 ksh-93r-12.9 libaio-0.3.104 libaio-32bit-0.3.104 libaio-devel-0.3.104 libaio-devel-32bit-0.3.104 libelf-0.8.5 libgcc-4.1.2 libstdc++-4.1.2 libstdc++-devel-4.1.2 make-3.80 numactl-0.9.6.x86_64 sysstat-8.0.4
SUSE Linux Enterprise Server 11	The following packages (or later versions) must be installed: binutils-2.19 gcc-4.3 gcc-32bit-4.3 gcc-c++-4.3 glibc-2.9 glibc-32bit-2.9 glibc-devel-2.9 glibc-devel-32bit-2.9 ksh-93t libaio-0.3.104 libaio-32bit-0.3.104 libaio-devel-0.3.104 libaio-devel-32bit-0.3.104 libstdc++33-3.3.3 libstdc++33-32bit-3.3.3 libstdc++43-4.3.3_20081022 libstdc++43-32bit-4.3.3_20081022 libstdc++43-devel-4.3.3_20081022 libstdc++43-devel-32bit-4.3.3_20081022 libgcc43-4.3.3_20081022 libstdc++-devel-4.3 make-3.81 sysstat-8.1.5

Note:

The numa package link for Linux x86 is /usr/lib and Linux x86-64 is /usr/lib64/.

To determine whether the required packages are installed, enter commands similar to the following:

# rpm -q package_name

If a package is not installed, then install it from the Linux distribution media or download the required package version from the Linux vendor's Web site.

rpm -ivh elfutils-libelf-0.137-3.el5 elfutils-libelf-devel-0.137-3.el5.i386.rpm elfutils-libelf-devel-static-0.137-3.el5.i386.rpm

rpm -ivh gcc-4.1.2-46.el5.i386.rpm gcc-c++-4.1.2-46.el5.i386.rpm glibc-devel-2.5-42.i386.rpm libgomp-4.4.0-6.el5.i386.rpm libstdc++-devel-4.1.2-46.el5.i386.rpm glibc-headers-2.5-42.i386.rpm kernel-headers-2.6.18-164.el5.i386.rpm

List of Database Users with Database Roles

1. Create the table DBROLES using below script in any database
   

   
   

CREATE TABLE DBROLES

( DBName sysname not null,

UserName sysname not null,

db_owner varchar(3) not null,

db_accessadmin varchar(3) not null,

db_securityadmin varchar(3) not null,

db_ddladmin varchar(3) not null,

db_datareader varchar(3) not null,

db_datawriter varchar(3) not null,

db_denydatareader varchar(3) not null,

db_denydatawriter varchar(3) not null,

Cur_Date datetime not null default getdate()

)

GO

Please include all the user defined database roles in above table as Column Name.

1. Create the Stored Procedure sp_GetListOfDBroles using the below script in the same database where you have created the table DBROLES.
   

Create procedure sp_GetListOfDBroles

as

declare @dbname varchar(200)

declare @mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR

select name

from master.dbo.sysdatabases

where name not in ('mssecurity','tempdb')

Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

Set @mSQL1 = ' Insert into DBROLES ( DBName, UserName, db_owner, db_accessadmin,

db_securityadmin, db_ddladmin, db_datareader, db_datawriter,

db_denydatareader, db_denydatawriter )

SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '

Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,

Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,

Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,

Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,

Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,

Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,

Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,

Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter

from (

select b.name as USERName, c.name as RoleName

from ' + @dbName+'.dbo.sysmembers a '+char(13)+

' join '+ @dbName+'.dbo.sysusers b '+char(13)+

' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c

on a.groupuid = c.uid )s

Group by USERName

order by UserName'

--Print @mSql1

Execute (@mSql1)

FETCH NEXT FROM DBName_Cursor INTO @dbname

END

CLOSE DBName_Cursor

DEALLOCATE DBName_Cursor

Go

Please include the additional column in the above scripts also.

1. Please Execute the Stored Procedure sp_GetListOfDBroles
   
2. Now you can get the list of all user with roles as below
   

Select * from DBROLES

DBName UserName db_owner db_accessadmin db_securityadmin...

------------- ---------------- ------------ -------------- ----------------...

UserDB1 User1 No No No ...

UserDB1 User_RO No No No ...

UserDB1 User_RW Yes No No ...

UserDB1 Server1\USER_DB No No No ...

UserDB1 dbo Yes No No ...

UserDB2 User1 No No No ...

UserDB2 User_RO No No No ...

UserDB2 User_RW Yes No No ...

UserDB2 Server1\USER_DB No No No ...

UserDB2 dbo Yes No No ...

UserDB3 User1 No No No ...

UserDB3 User_RO No No No ...

UserDB3 User_RW Yes No No ...

UserDB3 Server1\USER_DB No No No ...

UserDB3 dbo Yes No No ...

PS: I have included the few column in result due to row size limitation.

1. To get the list of rights for a specific user or database, please use the where clause as
   

Select * from DBROLES where DBName = 'Userdb1'

Select * from DBROLES where UserName = 'User1'

1. You can schedule the above SP through Job to execute every week or month as per the requirement. Doing this we'll able to find out when we assigned any rights to a user.
   

As always, your suggestion and comments are most welcome.

IMPDP ORA-39083: Object type TRIGGER failed

One can use the Oracle data pump import tool (IMPDP.EXE) to import one schema into another using the REMAP_SCHEMA option. However there is an issue in that triggers are not properly remapped. This leads to the trigger not being created at all with an error as follows:

ORA-39083: Object type TRIGGER failed to create with error: ORA-00942: table or view does not exist Failing sql is: CREATE TRIGGER "NEW_SCHEMA"."METER_ALARMS_BI"
BEFORE INSERT ON OLD_SCHEMA.METER_ALARMS ...

The reason for this is because the create SQL still refers to OLD_SCHEMA. It does say in the Oracle documentation that:

The mapping may not be 100 percent complete, because there are certain schema references that Import is not capable of finding. For example, Import will not find schema references embedded within the body of definitions of types, views, procedures, and packages.

IMHO this is a bit of a cop out by Oracle but that's another discussion!

According to Oracle Metalink note 750783.1, the workaround is to:

1. Create a SQLFILE to include the relevant DDL command(s): impdp system/** directory=test_dp DUMPFILE=export_schemas.dmp remap_schema=u1:u2 sqlfile=script.sql
   
2. Extract the affected DDL from the written SQLFILE and correct the schema reference. Then execute the command manually.
   

This is not a good way to do it especially if you have many failed objects and want to automate the process of combining multiple schema for in field upgrading of databases.

Has anyone found a better way to do this? I need a solution that must be 100% reliable if its to be used in the field. I could parse the generated SQL file but can one get this 100% correct? Is there not some way to intercept the CREATE SQL statements execute by IMPDP and correct it on the fly while importing? Could one patch the DMP file directly?