How to change the location of datafiles in Oracle database?

Below are steps to move the datafiles without shutting down the database.

1. Make the tablespaces offline.
ALTER TABLESPACE tablespace_name offline;

2.copy the files to the new location at the OS level

3.Modify the location of the datafiles
ALTER TABLESPACE tablespace_name RENAME DATAFILE 'datafile_name' to 'datafile_name';

4.Make the tablesapces online
ALTER TABLESPACE tablespace_name online;

Changing the ODS schema password in OAS!!!

OID Database Schema Owner

The Oracle Internet Directory runs on an Oracle database

and creates two database users: ODS and ODSCOMMON. ODS

is the schema owner that contains all of the database

objects (tables, views, objects, etc.) used for OID

functionality and directory storage. When the OID needs

to login to the database, it uses the ODS database

account which has a default password of ODS. You should

secure this database user account before putting the

LDAP directory into production.

1. Change the ODS password to the desired value, and

create the new wallet:

oidpasswd connect= change_oiddb_pwd=true

oidpasswd connect= create_wallet=true

2. Restart OID:

opmnctl stopproc process-type=OID

opmnctl status

opmnctl startproc process-type=OID


3. Change the password in Enterprise Manager:

1. Login to EM

2. Click Targets tab > Click All Targets tab

3. Find the Name reported in the emagent.trc file (e.g.,10_1_2_portoidd.libby.oracle.com_LDAP) > click on it

4. Click Monitoring Configuration at the bottom right

5. Enter:

Username = ods

Password =

Using the OID Database Password Utility


The DBA can change this password by using the OID

Database Password Utility (included with the OID

installation). The following example uses this utility

to change the database password for ODS:


# $ORACLE_HOME/bin/oidpasswd

current password: ods

new password: new_secret_password

confirm password: new_secret_password

password set.

How oidpasswd Works

The oidpasswd utility connects as the ODSCOMMON user and

uses the role ODS_SERVER with the original password to

perform the following:

1. Changes the password for the ODS user for the OID

schema database.

2. Updates the SYSTEM.ODSINSTANCES table with the new,

encrypted password for ODS.

3. Changes the password for the ODS_SERVER role to the

new password.

4. Updates the $ORACLE_HOME/ldap/admin/oidpwdr file with

the encrypted password.


NOTE: Some Oracle OID patches and scripts may assume the

user ODS/ODS. It is advised to change the password for

ODS back to its default of ODS during application of

patches or when running OID scripts.


But what about ODSCOMMON?

When connecting to the database schema, the OID

executables, such as oidctl or oidldapd servers connect

as the database user ODSCOMMON. The password for

ODSCOMMON is ODSCOMMON, and that password cannot be

changed. It is hardcoded in the executables and changing

it on the database level will cause an ORA-01017 error.

There is no security risk, however, not being able to

change the ODSCOMMON password, as it has only CONNECT

privilege. Once connected as ODSCOMMON, the executable

will obtain the privileges it needs via the role

ODS_SERVER, which is protected by the ODS password. The

password is obtained by the executable from the

SYSTEM.ODSINSTANCES table. This is the password

encrypted and set by the oidpasswd utility.

How to Unlock/Reset Super User cn=orcladmin When the ODS's Password Has Been Forgotten or is Unknown?

Solution

If the ODS password has been lost then follow steps 1-7:

1. Shutdown the OID processes

% opmnctl stopproc ias-component=OID

2. Verify that there is no OID process running

% ps -ef | grep -i oidmon
% ps -ef | grep -i oidldapd
% ps -ef | grep -i odisrv

3. Go to the $ORACLE_HOME/ldap/admin directory and rename the files oidpwdlldap1 and oidpwdrxxxx

where xxxx is the instance name

4. Login to the OID database via sqlplus as an user with DBA privileges (example. SYS or SYSTEM) and reset the ODS schema password:

SQL> alter user ods identified by password;

Note: replace password for the desirable value.

5. Verify that you are able to login to the database using following command.

% sqlplus ods/password@connect_string

Where connect_string is the alias defined in the tnsnames.ora to connect to the OID database.

6. Execute the oidpasswd command to recreate the files renamed in step 3

%oidpasswd connect=connect_string create_wallet=true

Note: To use in this step the same password defined in step 4.

7. Verify that the files renamed in step 3 have been recreated under $ORACLE_HOME/ldap/admin directory

8. If the orcladmin account is locked then use following command

% oidpasswd connect=connect_string unlock_su_acct=true

9. If the password for orcladmin needs to be changed then use following command

% oidpasswd connect=connect_string reset_su_password=true

10. Start the OID process

% opmnctl startproc ias-component=OID

11. Verify that the account orcladmin has been unlocked or that the password has been changed successfully

% $ORACLE_HOME/bin/ldapbind -p port -D cn=orcladmin -w passwd

For example:

$ORACLE_HOME/bin/ldapbind -p 389 -D cn=orcladmin -w welcome1

ORA-08104: this index object string is being online built or rebuilt

Run the below script with the object id and your problem will be resolved.

DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;

BEGIN
OBJECT_ID := 63556;
WAIT_FOR_LOCK := NULL;

RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (OBJECT_ID);
COMMIT;
END;

After running the above script your index will be ready for use.