ORA-00988: missing or invalid password(s)

1040309

ORA-00988: missing or invalid password(s)

Cause: More usernames than passwords were specified in a GRANT statement. A valid password must be specified for each username listed in the GRANT statement.

Action: Enter a valid password for each username.


One of Reason is

User might trying to create the password with quotes.

Eg:

Alter User Team Identified by 'password'
/

Above statement will give error ORA-00988: missing or invalid password(s)


Modified by statement to

Alter User Team Identified by password
/

ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [

Problem : ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [ due to sudden power failure.

when try to startup Oracle 10g database. it shows that database mounted and then followed by this error

SQL> startup
ORACLE instance started.

Total System Global Area 197772160 bytes
Fixed Size 1347876 bytes
Variable Size 71304572 bytes
Database Buffers 88080384 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],
[], [], []


Solution: This problem can be solved by using below commands

1. Shutdown the server

Sql> Shutdown Immediate

2. Start the database with MOUNT option

Sql> Startup Mount

3. Recovery the database using below command

Sql> Recover Database

4. Open the database

Sql> Alter Database Open

Above steps will resolve above problem.

Ora-12203 SQL*PLUS CONNECTS ON SERVER, FAILS FROM CLIENT

Problem Description:
====================

You can connect through tnsnames on the server, and you can tnsping the
server's alias, but connection and tnsping fails when you attempt to connect
from a client machine.

Example:
--------

On server:
~~~~~~~~~~
tnsping ... OK.
expands properly, shows server name, connecting to proper port.
sqlplus uid/pwd@... connects.

On client:
~~~~~~~~~~~
sqlplus uid/pwd@... fails with
ORA-12203, "TNS:unable to connect to destination"

tnsping ... fails with
TNS-12541, "TNS:no listener"

Problem Explanation:
====================
One of simple reason might be firewall on server machine is enabled.
Disable the firewall and check the client connection.


Problem Explanation:
====================

You have multiple Network Interface Cards (NICs) on the server, and the server
name resolves to different IP addresses based on where it is pinged from: hence
the server has one IP address when pinged from itself, but another when pinged
from the network.


Search Words:
=============

sqlnet, NIC, IP, dual, ethernet, card, FDDI, configured, listen, binding,
/etc/hosts

Solution Description:
=====================

You must ensure that the IP address mapped to the server is consistent. If
there are two network cards, one should be primary to the server: this should
be the one that the DNS name of the server resolves to, from the server AND
from the network.

When using TCP/IP 'ping', the server name must resolve to the same IP address
from all locations.  Test by using ping from the server, and from the client,
and ensure that the IP addresses match.

If not, check the "/etc/hosts" file for incorrect entries, and check the
bindings on the cards on the server.

Check for unusual situations: in one case, this was solved because the server
was configured with two NIC cards, one on Ethernet, the other FDDI.  The
Ethernet card was configured as the default card - even though it was not
connected to the network!  When it was removed, the problem was resolved.


Solution Explanation:
=====================

The listener registers the IP address of the server when it starts up. This is
based on how the DNS name or IP address is resolved on the server.

When a client attempts to connect, it connects to the IP address of the server,
as resolved from the client. If the IP address resolves differently on the
server and the client (even if the name is the same) then the client will not
be able to find the server

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;
/