[Dbconfig-common-devel] oracle.

[Miguel Gea Milvaques]

En/na sean finney ha escrit:
> hey miguel,
> 
> On Thu, Aug 25, 2005 at 02:29:36AM +0200, Miguel Gea Milvaques wrote:
> 
>>I've began searching information to add oracle support to
>>dbconfig-common. I _never_ work with oracle before, so it's going to do
>>a hard work to me.
> 
> 
> that'd be really cool!  i've only used it once or twice myself (and i
> had a DBA standing behind me telling me what to do), so i can't be
> too much help unfortunately.  i remember you had to source a shell
> library, and set an environment variable or two, and then you can use
> some cmdline programs to talk to the server, but that's about all i can
> seem to remember...

Hi sean,
As you said to you, I'm working in dbconfig-common oracle support, It's
being hard as I thought, but I think It could be at the last stage
before write it.

I was looking for questions and his answers before begin. This is my
questions, answers and problems I found, that I want to comment you
before begin writing.

0.- Which environment variables are needed? This is my environment
oracle related settings:

ORACLE_BASE=/home/oracle/oracle
ORACLE_SID=orcl
ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
ORACLE_SID=orcl
ORACLE_TERM=xterm
ORACLE_OWNER=oracle
LD_LIBRARY_PATH=${ORACLE_HOME}/lib; export LD_LIBRARY_PATH
DISABLE_HUGETLBFS=1


1.- How to create the database (PARTIALLY SOLVED):
        $ dbca -createDatabase -templateName General_Purpose.dbc
-gdbName prueba -sid prueba -sysPassword x -systemPassword x -silent

   NOTE: Maybe is necessary to add other (optional?) parameters. The
        -silent parameter is to avoid graphic output.
         Here there are the first problem.
         There are two different forms to create the database, manually
         and with a database assistant (dbca). In both cases, the
         database hast to be created *locally*. It could be done with
         ssh... but I don't think it could be a good idea.

2.- How to create/grant new users (SOLVED)?
	There are three indentification methods in oracle (local,
        external and global). Permissions could be granted and users
        must to be unlocked.
        http://www.adp-gmbh.ch/ora/concepts/users.html#external_user

   Examples:
     create user alfredo identified by alfredos_secret;
     create user alfredo identified externally;
     create user alfredo identified globally as 'external_name';
     grant connect to alfredo; // give connection permissions
     alter user alfredo account unlock; // unlock user account
   Examples (grant)
     grant select, insert on customer to fred, mary, joe;
     grant insert on order_table to update_role;
     grant all on customer to fred;
     grant select on customer_view to mary;
     grant create any cluster to customer_role;
     grant select any table to fred;
     grant create any table to public;
     grant create tablespace to dba_role;

     createrole all_customer;
     grant select, update on customer to all_customer;
     grant select on item_table to all_customer;
     grant all_customer to fred, mary, joe;

3.- How to populate the database or execute any sql command (SOLVED):
        $ sqlplus scott/x at orcl  @tables-oracle.sql

4.- How to drop database (PARTIALLY SOLVED):
        $ dbca -deleteDatabase -sourceDB <source database sid>
[-sysDBAUserName <user name  with SYSDBA privileges> -sysDBAPassword
<password for sysDBAUserName user name>] -silent

This seems to work only locally too.

5.- How to restore the database (previous dropping it)? (TODO) There are
two possibilities to backup/restore the database, the firs is to use
rman environment than seems a potent form to backup the database, but it
must to be activated/configurated (I was not be able to do a
backup/restore with it ) and imp/exp utilities, I've not tested them but
seems to be easy, this will permit to backup the database remotelly.

6.- Which debconf templates are needed? (TODO)
	Additional (to pgsql templates)
        - type of user (local, external, global)
http://www.adp-gmbh.ch/ora/concepts/users.html#external_user
        - optionally create database?. It must to be thought and it
could be added to pgsql templates.

7.- How to prepare the package? Oracle is a non free software but I
think as oracle is optional, it don't need to go to contrib.
8.- Dependences...


A lot of work. Please look it and comment me anythig thou think.



-- 
e-mail: Miguel Gea Milvaques <debian(@nospam)miguelgea.com
Blog: http://www.livejournal.com/users/xerakko/
GnuPG key: 0x580808C4
Key fingerprint = 85A0 06FD 9A6C 4701 27C2  5536 3533 50CA 5808 08C4
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: OpenPGP digital signature
Url : http://lists.alioth.debian.org/pipermail/dbconfig-common-devel/attachments/20051115/c3646258/signature.pgp