Unable to set Target Credentials

When attempting to connect the Target Credentials for a Windows machine either in 10g Grid Control or in 10g Database Control the following error will appear Error: Connection to host as user failed: ERROR: Wrong password for user

The reason for this problem is user does not have the OS right logon as a batch job.


To solve this problem, follow below steps

Grant the "log on as a batch job right" to the user who will be set in the credentials.
The following is an example on Windows 2000:-

1. Go to control panel/administrative tools

2. click on local security policy

3. click on local policies

4. click on user rights assignments

5. double click on log on as a batch job

6. click on "add" and add the user that was entered in the "normal username" or "privileged username" section of the EM Console.

Now test the connection.

Backu and Recovery

In this document, I discussed how to take backup (source) and recover (target/other m/c) the database in different machine in NOARCHIVE MODE.

Following are two important parameters which need to set accordingly.

Alter System Set

db_recovery_file_dest_size=20G scope=both

/

Alter System Set

db_recovery_file_dest= ''F:\oracle\product\10.2.0\flash_recovery_area'' scope=both

/

Taking Backup (RMAN)

1. Shut down Database

Sql> Shutdown immediate;

2. Startup database in MOUNT mode.

Sql> Startup Mount;

3. Now start RMAN (by typing RMAN at command prompt)

C:> RMAN TARGET /

4. Check the configuration

RMAN> SHOW ALL;

5. Check for default device and CONTROLFILE AUTOBACKUP parameters.

Default Device should be DISK (if backup is on harddisk)

CONTROLFILE AUTOBACKUP should be ON; (This command will take the control files and spfile auto backup).

Execute below statements

RMAN>CONFIGURE DEFAULT DEVICE TYPE TO DISK;

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP On;

6. Take the backup

RMAN> BACKUP DATABASE;

Recovery Process

Backup Files should copied to target machine ( db_recovery_file_dest ) directory. Usually this will be \flash_recovery_area directory in oracle home.

Restore the database to different location/machine with different file structures

1.shutdown immediate

sql>shutdown immediate;

2.Startup nomount

sql> startup nomount

3. Connect to RMAN

C:> RMAN TARGET /

4. Restore the control file

RMAN> restore controlfile from autobackup;

if backup is done in different location we have to mention the location

restore controlfile from autobackup recovery area='E:\oracle\product\10.2.0/flash_recovery_area' db_name='wg';

5.Alter the database (to get mount)

sql> Alter Database Mount;

6. Change the path names (as files stored in source m/c is different from target m/c (directories)

RMAN> run {

set newname for datafile 1 to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSTEM01.DBF';

set newname for datafile 2 to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\UNDOTBS01.DBF';

set newname for datafile 3 to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSAUX01.DBF';

set newname for datafile 4 to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\USERS01.DBF';

set newname for datafile 5 to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\EXAMPLE01.DBF';

}

7. Alter the database and rename with new file names

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO03.LOG' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO03.LOG';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO02.LOG' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO02.LOG';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO01.LOG' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\REDO01.LOG';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSTEM01.DBF' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSTEM01.DBF';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\UNDOTBS01.DBF' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\UNDOTBS01.DBF';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSAUX01.DBF' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\SYSAUX01.DBF';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\USERS01.DBF' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\USERS01.DBF';

SQL>alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\EXAMPLE01.DBF' to 'e:\ORACLE\PRODUCT\10.2.0\ORADATA\WG\EXAMPLE01.DBF';

8. Restore the database using restore command.

RMAN> restore database;

9. Open the database with resetlogs option

Sql> Alter Database Open resetlogs;

10. With above command recovery process is completed. Users can now logon to new machine with same password as source.

Control Files for Migration

his article discuss the procedure to generate the auto control files for given schema and store the control files in text format.

This process saves lot of time and effort. I assume the oracle directory is created before running this process.

This procedure reads the schema tables and generate generic control file based on data types. For each table process generates one control file and stores in default oracle directory path.

Create Or Replace Procedure CreateControlFiles(PDateFormat VarChar2)
Is

Cursor C1 Is
Select 'LOAD DATA'|| chr (10) ||
'INFILE ''' || lower (table_name) || '.dat''' || chr (10) ||
'INTO TABLE '|| table_name || chr (10)||
'FIELDS TERMINATED BY ''|'''||chr (10)||
'TRAILING NULLCOLS' || chr (10) || '(' CtrText,
Table_Name
From User_Tables;


Cursor C2(CvTableName VarChar2) Is
Select Decode(Column_Id, 1, '', ' , ')||
Rpad(Column_Name, 33, ' ') ||
Decode (Data_Type,
'VARCHAR2', 'CHAR NULLIF ('||Column_Name||'=BLANKS)',
'FLOAT', 'DECIMAL EXTERNAL NULLIF('||Column_Name||'=BLANKS)',
'NUMBER', Decode (Data_Precision, 0,
'INTEGER EXTERNAL NULLIF ('||Column_Name||
'=BLANKS)', Decode (Data_Scale, 0,
'INTEGER EXTERNAL NULLIF ('||
Column_Name||'=BLANKS)',
'DECIMAL EXTERNAL NULLIF ('||
Column_Name||'=BLANKS)')),
'DATE', 'DATE "'||PDateFormat||'" NULLIF ('||Column_Name||'=BLANKS)', Null)
ColText
From User_Tab_Columns
Where Table_Name = CvTableName
Order By Column_Id;


LvFile Utl_File.File_Type;

Begin

For C1Rec In C1
Loop
LvFile := Utl_File.FOpen('EXCEL_DATA',C1Rec.Table_Name||'.Txt','W');

Utl_File.Put_Line(LvFile,C1Rec.CtrText);

For C2Rec In C2(C1Rec.Table_Name)
Loop

Utl_File.Put_Line(LvFile,C2Rec.ColText);

End Loop;

Utl_File.Put_Line(LvFile,')');
Utl_File.FClose(LvFile);
End Loop;
End;
/