Core2Apps DBA: February 2014

Thursday 20 February 2014

Reset Linux root user Password

Reset Linux root user Password

Method 1
1. Boot the computer with Linux Installation CD.
2. Enter into Linux Rescue mode by enering the following command in boot screen. boot: linux rescue
3. When the shell prompt appears, enter the following commands to change root password.

$ chroot /mnt/sysimage
$ passwd

Method 2

1. When you see the grub screen press 'e' on linux which contains the word 'kernell' press 'e'
2. Go to end of that line and write 'linux single'
3. Then press 'esc' twice and press 'b' to reboot the system
4. Now wait for the prompt and type the following command to reset the password for root.

#passwd root

The above steps will prompt you to enter your new password.

Forgot root Password Linux?

1. Select the kernel ( reboot the linux server , press Down arrow key)

2. Press the e key to edit the entry

3. Select second line (the line starting with the word kernel)

4. Press the e key to edit kernel entry so that you can append single user mode

5. Append the letter S (or word Single) to the end of the (kernel) line

6. Press ENTER key

7. Now press the b key to boot the Linux kernel into single user mode

8. At prompt type passwd command to reset password:

Configure DBConsole for Oracle EBS R12 Database

Configure the DBConsole Repository

Following command can be executed to configure DBConsole along with the repository.

Change the directory to database ORACLE_HOME and execute the environment file.
[oratest@oracle 11.1.0]$ . TEST_oracle.env

Then move to [ORACLE_HOME]/bin and execute the following command.
[oratest@oracle bin]$ emca -config dbcontrol db -repos create

Start/Stop & Check DBconsole Status

Then using the emctl command DBconsole could be started, stopped and also status could be checked.

[oratest@oracle bin]$ emctl start dbconsole

[oratest@oracle bin]$ emctl stop dbconsole

[oratest@oracle bin]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.7.0
Copyright (c) 1996, 2008 Oracle Corporation. All rights reserved.
https://oracle.calspence.lk:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /d03/oracle/TEST/db/tech_st/11.1.0/oracle.umanage.lk_TEST/sysman/log

Now can login through the sys user.

Refer following metalink document for more information.
Master Note for Enterprise Manager Configuration Assistant (EMCA) in Single Instance Database Environment [ID 1099271.1]

If you failed to unlock the SYSMAN account , you should drop the SYSMAN user manually. If youdon’t drop the SYSMAN schema, you’ll raise an error when you try to recreate it

CONFIG: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17
 
oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17

The Java stack trace will look like this, more or less based on version and release:

at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1530)
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeScript(SQLEngine.java:880)
at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java
at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java
at oracle.sysman.emcp.EMReposConfig.createRepository(EMReposConfig.java:492)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:218)
at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:147)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:222)
at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:535)
at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1215)
at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:519)
at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:468)

Drop the user and dependent on version a few other objects, like:

DROP USER sysman CASCADE;
DROP PUBLIC SYNONYM setemviewusercontext;
DROP ROLE mgmt_user;
DROP PUBLIC SYNONYM mgmt_target_blackouts;
DROP USER mgmt_view;

 then

$emca -config dbcontrol db -repos create

Wednesday 19 February 2014

11g to 10g Import ( Import data into a target database with a lower compatibility level)

Import data into a target database with a lower compatibility level.

Sample configuration:

Source database: 11.1.0.6.0 with COMPATIBLE=11.1.0
Target database: 10.2.0.4.0 with COMPATIBLE=10.2.0
Solution:

Start the export job using the 11.1.0.6.0 Export Data Pump client that connects to the 11.1.0.6.0 source database. Use the parameter VERSION=10.2 to create a dumpfile set with a lower compatibility level.
Transfer the dumpfile set to the server where the target database is located.
Start the import of the data using the 10.2.0.4.0 Import Data Pump client that connects to the 10.2.0.4.0 target database.
-- Step 1: export from 11.1.0.6 source database
-- with 11.1.0.6 Export Data Pump client and specify VERSION:

% expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log SCHEMAS=scott VERSION=10.2

-- Step 2: transfer dumpfile to target server

-- Step 3: import into 10.2.0.4 target database
-- with 10.2.0.4 Import Data Pump client:

% impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=impdp_s.log SCHEMAS=scott

How to Change the DBID, DBNAME Using NID Utility ORACLE Database

Steps to change DBID and DBNAME:

Source database name is old and we will convert it to fms02.

[oracle@netmindtwcint ~]$ export ORACLE_SID=fms01
[oracle@netmindtwcint ~]$ sqlplus sys as sysdba

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1345376 bytes
Variable Size 360712352 bytes
Database Buffers 167772160 bytes
Redo Buffers 5832704 bytes
Database mounted.
SQL> exit;

[oracle@netmindtwcint ~]$ nid target=sys/*** dbname=fms02

DBNEWID: Release 11.2.0.2.0 - Production on Fri Apr 27 14:40:10 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to database FMS01 (DBID=3355521024)

Connected to server version 11.2.0

Control Files in database:
/fms01/dbs/control01.ctl
/fms01/dbs/control02.ctl
/fms01/dbs/control03.ctl

Change database ID and database name FMS01 to FMS02? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3355521024 to 3803284043
Changing database name from FMS01 to FMS02
Control File /fms01/dbs/control01.ctl - modified
Control File /fms01/dbs/control02.ctl - modified
Control File /fms01/dbs/control03.ctl - modified
Datafile /fms01/dbs/system01.db - dbid changed, wrote new name
Datafile /fms01/dbs/undotbs01.db - dbid changed, wrote new name
Datafile /fms01/dbs/sysaux01.db - dbid changed, wrote new name
Datafile /fms01/dbs/users01.db - dbid changed, wrote new name
Datafile /fms01/dbs/indx01.db - dbid changed, wrote new name
Datafile /fms01/dbs/tools01.db - dbid changed, wrote new name
Datafile /fms01/dbs/data/ora_data.db - dbid changed, wrote new name
Datafile /fms01/dbs/temp01.db - dbid changed, wrote new name
Control File /fms01/dbs/control01.ctl - dbid changed, wrote new name
Control File /fms01/dbs/control02.ctl - dbid changed, wrote new name
Control File /fms01/dbs/control03.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to FMS02.

Modify parameter file and generate a new password file before restarting.

Database ID for database FMS02 changed to 3803284043.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

[oracle@netmindtwcint ~]$ cd /data1/oracle11g/product/11.2.0/dbhome_2/dbs/

Now, create new password file for new name:

[oracle@netmindtwcint dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwfms02 password=sys force=y

Make new init.ora file and from existing init.ora file and change db_name parameter from fms01 to fms02

[oracle@netmindtwcint dbs]$ cp initfms01.ora initfms02.ora

change *.db_name='fms01' to *.db_name='fms02' in init file.

Change the $ORACLE_HOME/network/admin/tnsnames.ora file wherever it has the old db name.

If there is a static registration of the database in the listener.ora file then change the database name in the following file $ORACLE_HOME/network/admin/listener.ora.

Open the database with Resetlogs option:

[oracle@netmindtwcint dbs]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 27 15:00:03 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter password:
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1345376 bytes
Variable Size 167774368 bytes
Database Buffers 360710144 bytes
Redo Buffers 5832704 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
fms02