Oracle 9i RAC - Create User

[Marcus Kinlay]

I need to create a new user (with his own tablespaces + data files) in RAC environment (using shared disks).

Any help appreciated

[Robert Gamble]

That is a very complicated question, with many questions to be asked before answering.

I suggest you peruse the manual --> http://docs.hp.com/hpux/onlinedocs/B5158-90044/B5158-90044.html.

Biggest Question: Is the user ID to be created in the OS, or in Oracle ? Both ...

[Yago Hernandez]

Suppose you have Oracle9i-RAC installed and your shared disks are in use.

Then creating a user has no difference with creating a user in Oracle non-RAC, except that
the datafiles of the tablespaces must be on the
shared disks.

First, create the tablespace for data:

CREATE TABLESPACE HELEN_DATA
DATAFILE
'\oracle_home1\helen_data.dbf' SIZE 1024 M
AUTOEXTEND ON NEXT 16 M MAXSIZE UNLIMITED
PERMANENT
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

The datafile, in this case \oracle_home1\helen_data.dbf, must be on one of your shared disks, in order to be accesible from all your instances.

Create the temporary tablespace:

CREATE TEMPORARY TABLESPACE HELEN_TEMP
TEMPFILE
'\oracle_home1\helen_temp.dbf' SIZE 1024 M
AUTOEXTEND ON NEXT 16 M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

The datafile, in this case \oracle_home1\helen_temp.dbf, must be on one of your shared disks, in order to be accesible from all your instances.


Finally, create the user:

CREATE USER HELEN IDENTIFIED BY HELEN_PASSWORD
DEFAULT TABLESPACE HELEN_DATA
TEMPORARY TABLESPACE HELEN_TEMP
QUOTA UNLIMITED ON HELEN_DATA
QUOTA UNLIMITED ON HELEN_TEMP;

And grant her some minimum privileges:

GRANT CONNECT TO HELEN;
GRANT RESOURCE TO HELEN;

Make sure you see the user and the tablepsaces from all instances.

[Marcus Kinlay]

The Oracle user to be created will not be an OS User (purely Oracle).

Also, I tried connecting to (one instance of the cluster of) Oracle via telnet and SQL*Plus and created the user as suggested (create tablespace XXX, etc; create user AAA, etc) but this only created the used in one instance. Perhaps the problem is to do with the way to connect to the cluster?

[Yago Hernandez]

How are you connecting via SQLPLUS (do you use a service name)? Do you know the names of each instance on each node?
When you connect to each one, are you really in a different instance (select instance_name from v$instance)? and are you realy in the same database (select dbid, name from v$database)?

[Marcus Kinlay]

The two instances in the cluster reside on separate servers, both are the default instance (ORCL), they share a RAID5 disk pack.

The connection to SQL*Plus is made from a telnet session on one of the server machines in the cluster, using the default instance (ie the Ora instance on that machine) - "$ sqlplus system/manager"

The tablespace, datafiles and user are created via the usual CREATE commands (as per those indicated in the previous response to mny question).

Result: neither the user nor the tablespace nor the datafiles exist in the second instance.

Do I need to create the tablespace, reusing the same datafiles, in the second instance? Do I then need to create the user in this second instance?

[V. V. Ravi Kumar_1]

hi,
U can check through v$database(dbid, name ) and v$instance (instance_name) .
The database name , id should be same for both the instances while instance_name will be different (like ORCL1 and ORCL2).
If above condition is not true then check if the RAC is properly installed.

U need not create tablespaces , users on other instance , since the database is same , they sould automatically appear in the second node.

Also make sure that userid and groupids at OS level on all the nodes are same.

Regards

[Yago Hernandez]

The RAC architecture consists of one database and several instances. To Oracle, a database are the physical data files; and an instance are the processes and memory structures.

So that, the idea is that you have one database (physical) by some way shared between serveral nodes; that is, the files are shared between several servers.

And in each server there is one instance that is mounted onto the (shared)database.

If you create an user, the changes are written onto the database, that is are written on the shared datafiles. As all instances read the same datafiles, the changes are effective on all instances.

If you cannot see the user on the other instance, then that instance is not on the same database. Either RAC is not well configured or you are not connecting to the appropiate instance.

When connecting via SQLPLUS you can specify the instance (service name) you want to connect with:

sqlplus system@INSTANCE_NAME/manager

The service name must be well defined in $ORACLE_HOME/network/admin/tnsnames.ora

If you do not specify the service name, you are connecting to the value of the environmet parameter $ORACLE_SID instance.

sqlplus system/manager

Verify the dbid column in v$database, to see if you are on the same physical database. Verify the instance_number on v$instance: One instance should have instance number 1 and the other the number 2. Instance names must also be different. If they really are ORCL for both, then you do not have a RAC. Check also the PFILE $ORACLE_HOME/dbs/init.ora parameters of each instance: you should see the INSTANCE_NAME, INSTANCE_NUMBER and THREAD parameters and they must be differen for each instance. (If you dont have a PFILE because you have a SPFILE, generate it from the SPFILE: CREATE PFILE FROM SPFILE;)

Hope this helps

[Yogeeraj_1]

hi,

Above is a great description of the RAC architecture.

For administration tasks, it is preferable to use the Oracle Enterprise Manager (OEM). The OEM is a system management tool that provides an integrated solution for centrally managing your heterogeneous environment. It also combines a graphical console, management server, Oracle Intelligent Agent, repository database, and tools to provide an integrated, comprehensive systems management platform for managing Oracle products.

Much of your tasks are simplified with this tool and the risks of making any errors are minimised.

hope this helps!

regards
Yogeeraj

[Brian Crabtree]

The other part of a RAC cluster is that (for HP-UX) the volume groups have to be in sync to be able to add a datafile to all of the instances. (A datafile can be added to a single instance, but will generate errors in the alertlog of the other instance, and will be unreadable).

This is not a simple process, and you should know a bit about Serviceguard clustering, volume group management, logical volume management, and Oracle administration before doing this.

Ok, there are some assumptions in this. First, you have a volume group created (vgdata) that does not have any logical volumes created, and is not currently active on any systems. Second, you have a cluster up and running.

1. vgchange -a y /dev/vgdata
2. lvcreate -L 500 -n lvuser /dev/vgdata
3. chown oracle:dba /dev/vgdata/rlvuser
4. vgchange -a n /dev/vgdata
5. vgchange -S y -c y /dev/vgdata
6. vgexport -s -v -p -m vgdata.map /dev/vgdata
7. vgchange -a s /dev/vgdata
8. Move the vgdata.map file to each system
(on each system)
9. mkdir /dev/vgdata
10. mknod /dev/vgdata/group c 64 0x010000
11. vgimport -s -v -p -m vgdata.map /dev/vgdata
12. chown oracle:dba /dev/vgdata/rlvuser
(on one system)
13. Create the tablespace with the /dev/vgdata/rlvuser as the datafile (or a symlink to this location). Symlinks should be used where possible, as this will simplify administration.

Please note, this is NOT to be used for active volume groups. It is similar, but more complex, and libable for more problems. I would recommend getting RAC training over trying to do self-learning on it.

Thanks,

Brian

[Brian Crabtree]

The other part of a RAC cluster is that (for HP-UX) the volume groups have to be in sync to be able to add a datafile to all of the instances. (A datafile can be added to a single instance, but will generate errors in the alertlog of the other instance, and will be unreadable).

This is not a simple process, and you should know a bit about Serviceguard clustering, volume group management, logical volume management, and Oracle administration before doing this.

Ok, there are some assumptions in this. First, you have a volume group created (vgdata) that does not have any logical volumes created, and is not currently active on any systems. Second, you have a cluster up and running.

1. vgchange -a y /dev/vgdata
2. lvcreate -L 500 -n lvuser /dev/vgdata
3. chown oracle:dba /dev/vgdata/rlvuser
4. vgchange -a n /dev/vgdata
5. vgchange -S y -c y /dev/vgdata
6. vgexport -s -v -p -m vgdata.map /dev/vgdata
7. vgchange -a s /dev/vgdata
8. Move the vgdata.map file to each system
(on each system)
9. mkdir /dev/vgdata
10. mknod /dev/vgdata/group c 64 0x010000
11. vgimport -s -v -p -m vgdata.map /dev/vgdata
12. chown oracle:dba /dev/vgdata/rlvuser
(on one system)
13. Create the tablespace with the /dev/vgdata/rlvuser as the datafile (or a symlink to this location). Symlinks should be used where possible, as this will simplify administration.

Please note, this is NOT to be used for active volume groups. It is similar, but more complex, and libable for more problems. I would recommend getting RAC training over trying to do self-learning on it.

Thanks,

Brian

[Brian Crabtree]

The other part of a RAC cluster is that (for HP-UX) the volume groups have to be in sync to be able to add a datafile to all of the instances. (A datafile can be added to a single instance, but will generate errors in the alertlog of the other instance, and will be unreadable).

This is not a simple process, and you should know a bit about Serviceguard clustering, volume group management, logical volume management, and Oracle administration before doing this.

Ok, there are some assumptions in this. First, you have a volume group created (vgdata) that does not have any logical volumes created, and is not currently active on any systems. Second, you have a cluster up and running.

1. vgchange -a y /dev/vgdata
2. lvcreate -L 500 -n lvuser /dev/vgdata
3. chown oracle:dba /dev/vgdata/rlvuser
4. vgchange -a n /dev/vgdata
5. vgchange -S y -c y /dev/vgdata
6. vgexport -s -v -p -m vgdata.map /dev/vgdata
7. vgchange -a s /dev/vgdata
8. Move the vgdata.map file to each system
(on each system)
9. mkdir /dev/vgdata
10. mknod /dev/vgdata/group c 64 0x010000
11. vgimport -s -v -p -m vgdata.map /dev/vgdata
12. chown oracle:dba /dev/vgdata/rlvuser
(on one system)
13. Create the tablespace with the /dev/vgdata/rlvuser as the datafile (or a symlink to this location). Symlinks should be used where possible, as this will simplify administration.

Please note, this is NOT to be used for active volume groups. It is similar, but more complex, and libable for more problems. I would recommend getting RAC training over trying to do self-learning on it.

Thanks,

Brian

[Brian Crabtree]

The other part of a RAC cluster is that (for HP-UX) the volume groups have to be in sync to be able to add a datafile to all of the instances. (A datafile can be added to a single instance, but will generate errors in the alertlog of the other instance, and will be unreadable).

This is not a simple process, and you should know a bit about Serviceguard clustering, volume group management, logical volume management, and Oracle administration before doing this.

Ok, there are some assumptions in this. First, you have a volume group created (vgdata) that does not have any logical volumes created, and is not currently active on any systems. Second, you have a cluster up and running.

1. vgchange -a y /dev/vgdata
2. lvcreate -L 500 -n lvuser /dev/vgdata
3. chown oracle:dba /dev/vgdata/rlvuser
4. vgchange -a n /dev/vgdata
5. vgchange -S y -c y /dev/vgdata
6. vgexport -s -v -p -m vgdata.map /dev/vgdata
7. vgchange -a s /dev/vgdata
8. Move the vgdata.map file to each system
(on each system)
9. mkdir /dev/vgdata
10. mknod /dev/vgdata/group c 64 0x010000
11. vgimport -s -v -p -m vgdata.map /dev/vgdata
12. chown oracle:dba /dev/vgdata/rlvuser
(on one system)
13. Create the tablespace with the /dev/vgdata/rlvuser as the datafile (or a symlink to this location). Symlinks should be used where possible, as this will simplify administration.

Please note, this is NOT to be used for active volume groups. It is similar, but more complex, and libable for more problems. I would recommend getting RAC training over trying to do self-learning on it.

Thanks,

Brian

[Brian Crabtree]

The other part of a RAC cluster is that (for HP-UX) the volume groups have to be in sync to be able to add a datafile to all of the instances. (A datafile can be added to a single instance, but will generate errors in the alertlog of the other instance, and will be unreadable).

This is not a simple process, and you should know a bit about Serviceguard clustering, volume group management, logical volume management, and Oracle administration before doing this.

Ok, there are some assumptions in this. First, you have a volume group created (vgdata) that does not have any logical volumes created, and is not currently active on any systems. Second, you have a cluster up and running.

1. vgchange -a y /dev/vgdata
2. lvcreate -L 500 -n lvuser /dev/vgdata
3. chown oracle:dba /dev/vgdata/rlvuser
4. vgchange -a n /dev/vgdata
5. vgchange -S y -c y /dev/vgdata
6. vgexport -s -v -p -m vgdata.map /dev/vgdata
7. vgchange -a s /dev/vgdata
8. Move the vgdata.map file to each system
(on each system)
9. mkdir /dev/vgdata
10. mknod /dev/vgdata/group c 64 0x010000
11. vgimport -s -v -p -m vgdata.map /dev/vgdata
12. chown oracle:dba /dev/vgdata/rlvuser
(on one system)
13. Create the tablespace with the /dev/vgdata/rlvuser as the datafile (or a symlink to this location). Symlinks should be used where possible, as this will simplify administration.

Please note, this is NOT to be used for active volume groups. It is similar, but more complex, and libable for more problems. I would recommend getting RAC training over trying to do self-learning on it.

Thanks,

Brian

[Printaporn_1]

Hi,

as stated in above post , lot of things to understand and done to create a new tablespace in RAC.
better try to understand your cluster , what is the service ? to connect to the DB cluster.
Not only one instance.
after that configure it with Net Assistant.
after successful connect with SQLPLUS.
Check gv$database , what instance and else ???