SSH Configuration between servers

SSH Configuration between linux servers without giving password

SSH can give you authenticated and encrypted connections to remote computers. If you set up keys you can make these connections without passwords.

I test this for RHEL5 64 bit OS

Step1: Generate a public and private key pair on the machine from where you want to login to other machines.

For SSH1:

# ssh-keygen -b 1024 -t rsa

For SSH2 (Recommended):

# ssh-keygen -b 1024 -t dsa

• You will be prompted for a file in which the key and a passphrase will be saved. 
• You may press Enter through each of these prompts. 
• If you do so, the key generation program will assume that you wish to use the default file name of id_dsa and your private key will not be protected by a password.
• Once this is done, you will see id_rsa and id_rsa.pub file in the .ssh directory in your home directory if you have not supplied a file name as I mentioned above.

Step 2: Copy the content of id_rsa.pub file to authorized_keys2 file available in .ssh directory of the remote host(s)

• Copy the public keys (id_rsa.pub) to the remote host. That is the content of id_rsa.pub file to the file called  authorized_keys2 available in .ssh directory of the host(s) where you wanted to connect without password. 
• Be aware that .ssh directory will be available in the home directory as hidden file so make sure it availability by ls -ltr command in the home directory.

# scp ~/.ssh/id_rsa.pub remote_host:/

Step 3: Login to the remote host and check if you already have authorized_keys2 file in .ssh dir. If this doesn’t exist, create it with the following commands.

# touch ~/.ssh/authorized_keys2

Step 4: On the remote host where you are in step 3, copy your public key to the authorized_keys2 file, with the following command.

# cat ~/ id_rsa.pub >> ~/.ssh/authorized_keys2

Step 5: You are set to use SSH authentication without password now. If you want you can delete or move id_rsa.pub file.

You can follow steps 2 – 5 for all other hosts where you want to have public key authentication.

Recompiling Invalid Objects

STEP 1: Use the below query to find INVALID Objects



SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS='INVALID';
Export the result of the above query to xls.

STEP 2: Identify the Object Type of each invalid object.

STEP 3: If the object type is synonym

Use the below query for generating a query to create or replace invalid synonyms

SELECT  'CREATE OR REPLACE PUBLIC SYNONYM ' ||OBJECT_NAME ||' FOR '
||OBJECT_NAME||';' FROM DBA_OBJECTS where status='INVALID' AND OBJECT_TYPE='SYNONYM';

Use the below query for generating a query to grant privileges to synonyms



SELECT 'GRANT ALL ON ' ||OBJECT_NAME ||' TO PUBLIC||;' FROM DBA_OBJECTS where status='INVALID' AND OBJECT_TYPE='SYNONYM';

STEP 4: Copy and paste the result set of both the above queries in separate file


Now execute the first result set

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME1 FOR OBJECT_NAME1;

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME2 FOR OBJECT_NAME2;


CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME3 FOR OBJECT_NAME3;

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME4 FOR OBJECT_NAME4;

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME5 FOR OBJECT_NAME5;
CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME6 FOR OBJECT_NAME6;

CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.

Now execute the second result set

GRANT ALL ON SYNONYM_NAME1 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME2 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME3 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME4 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME5 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME6 TO PUBLIC;

GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.

Use the below query to check whether the objects has been recompiled and the status is VALID

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS
WHERE STATUS='VALID' AND OBJECT_TYPE='SYNONYM' AND OBJECT_NAME IN ('OBJECT_NAME1','OBJECT_NAME2','OBJECT_NAME3','OBJECT_NAME4',
'OBJECT_NAME5';

STEP 5: If the object type is not synonym


Recompile other objects as the show below

Uploading CSV in SQLLDR

Normal CSV Upload



Step 1: Before loading data you need two files control file and csv file.
Step 2: Change the following in the control file

•   Path of the csv file.
•   Table name.
•   Column names for which the data has to be inserted.

Step 3: Execute the command sqlldr control=c:\ctrl3, log=c:\tablename.log userid=username/password@connectionname
    


Blob and Date Column CSV Upload via SQLLDR
  
Step 1: Before loading data you need two files control file and csv file .

This is different from normal upload since it has date field to be loaded and XML_BLOB field that need to loaded.

Prerequisite: Char_Set_ID will be given
  
Step 2: Export the Char_Set  table (for the Char_Set_ID given) excluding the char_set_xml column as csv.

Step 3: Now export the char_set_xml column for each of the given Char_Set_ID as single dat file.
  
Step 4: Change the following in the csv file

•  In the XML_BLOB (char_Set_xml) column mention the path of the dat file XML_BLOB (char_Set_xml).
•  Also check for the date format of the date fields as mentioned in the control file.

Step 5: Change the following in the control file

•  Path of the csv file.
•  Table name.
•  Column names for which the data has to be inserted.
• Give the correct format of the date fields. For Example insert_date Date "MM/DD/YYYY HH24:MI:SS",
• XML_BLOB column has to be mentioned as below in the control file. For Example lob file filler char,CHAR_SET_XML LOBFILE(lob_file) TERMINATED BY EOF
  

Step 4: Execute the command sqlldr control=c:\ctrl, log=c:\tablename.log userid=suvin/welcome@connectionname

 
Control File Content


LOAD DATA

INFILE "c:\char_set.csv"
Append INTO TABLE CHAR_SET
FIELDS TERMINATED BY ','
(
CHAR_SET_ID,
CHAR_SET_ID,
CHAR_SET_NAME,
QUERY_TABLE_ID,
lob_file filler char,
CHAR_SET_XML LOBFILE(lob_file) TERMINATED BY EOF,
domain_name,
insert_user,
insert_date Date "MM/DD/YYYY HH24:MI:SS",
update_user,
update_date Date "MM/DD/YYYY HH24:MI:SS"
)

Sample

Microsoft Windows XP [Version 5.1.2600]

(C) Copyright 1985-2001 Microsoft Corp.


C:\Documents and Settings\;sqlldr userid=suvin/welcome@tmstest control=c:/CTRL4, log=tablename_04_NOV.log


SQL*Loader: Release 10.1.0.2.0 - Production on Wed Nov 3 17:13:44 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.


Commit point reached - logical record count 53


C:\Documents and Settings\sqlldr userid=suvin/welcome@tmsprod1 control=c:/CTRL4, log=tablename_04_NOV.log


SQL*Loader: Release 10.1.0.2.0 - Production on Wed Nov 3 17:16:01 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.


Commit point reached - logical record count 53


C:\Documents and Settings\;

How To Repair the Master Boot Record In Windows XP

Repairing the master boot record on your Windows XP system is accomplished using the fixmbr command, available in Recovery Console. This is necessary when the master boot record has become corrupt due to a virus or some kind of damage.




Follow these easy steps to repair a damaged master boot record in Windows XP.



Difficulty: Easy

Time Required: Repairing the master boot record on a Windows XP system takes less than 15 minutes


Here's How:

Enter Windows XP Recovery Console.
 
When you reach the command prompt (detailed in Step 6 in the link above), type the following and then press Enter.
 
fixmbr
 
The fixmbr utility will write a master boot record to the hard drive that you're currently using to boot into Windows XP. This will repair any corruption or damage that the master boot record may have.
 
Take out the Windows XP CD, type exit and then press Enter to restart your PC.
 
Assuming that a corrupt master boot record was your only issue, Windows XP should now start normally

How To Recover Windows 7 Multi-boot Option with Windows XP

Hello guys i think this is the common problem to all, as if you use Windows 7 as a second operating system with Windows Vista or Windows XP boot menu option automatically creates a new operating system option in the boot menu there.  However, if you have Windows 7, and then try installing Windows XP, you will overwrite the MBR with one that does not recognize the code, the boot loader of Windows 7.
If any body of us are in the same situation and looking for the way to fix and get back Windows 7 option in boot menu then follow below simple steps.

1. Open a command prompt in the Xp/Vista operating( i mean in the older operating systems ) systems and run from the Windows 7 DVD the following command, Hear H is my drive letter replace yours:

H:\boot\ bootsect.exe /nt60 all

Just restart, now you should see the Windows 7 menu.

2. To restore the menu entry for your earlier version of Windows, open an elevated Command Prompt window and type this command:

bcdedit /create {ntldr} –H “Menu description goes here”

                                                     (or)


Download and Install EasyBCD on your Windows 7 to add boot entry for XP.

Launch the app and go to Add/Remove Entries. Under “Add an Entry” open the Windows tab and select the Type as “Windows NT/2k/XP/2k3”.

Give it a name like ‘Windows XP’ and click on ‘Add Entry’. Then select Save.


That’s it. Now restart you PC and you’ll be presented with two options, Windows 7 and Windows XP. Select the one which you want to work on.


Cheers:
Substitute your own description for the placeholder text, being sure to include the quotation marks. The next time you start your computer, the menus should appear as you intended.

killing rac sessions

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45



SELECT s.inst_id,

s.sid,

s.serial#,

p.spid,

s.username,

s.program

FROM gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND';


The basic syntax for killing a session is shown below.


SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';



In a RAC environment, you optionally specify the INST_ID, shown when querying the

GV$SESSION view. This allows you to kill a session on different RAC node.


SQL> ALTER SYSTEM KILL SESSION 'sid,serial#@inst_id';


In addition to the syntax described above, you can add the IMMEDIATE clause.


SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;


DISCONNECT SESSION


SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;


The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation

states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram

suggests both clauses are optional, but in reality, one or both must be specified or you

receive an error.


SQL> alter system disconnect session '30,7';

alter system disconnect session '30,7'

*

ERROR at line 1:

ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword


The Windows Approach

To kill the session on the Windows operating system, first identify the session, then

substitute the relevant SID and SPID values into the following command issued from the

command line.


C:> orakill ORACLE_SID spid


The UNIX Approach

To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.


% kill spid

If after a few minutes the process hasn't stopped, terminate the session using the following.


% kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using.


% ps -ef
grep ora


----------------Session Waits--------------------

SQL> select event, seconds_in_wait, sid from v$session_wait where sid in (10,14);

Export and Import Dump

------------Importing Full Dump File-------------




If you want to Import all the objects in a dump file then you can type the

following command.



$impdp hr/hr DUMPFILE=dpump_dir1:expfull.dmp FULL=y

LOGFILE=dpump_dir2:full_imp.log



This example imports everything from the expfull.dmp dump file. In this example,

a DIRECTORY parameter is not provided. Therefore, a directory object must be provided

on both the DUMPFILE parameter and the LOGFILE parameter



-------------Importing Objects of One Schema to another Schema---------------



The following example loads all tables belonging to hr schema to scott schema



$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

REMAP_SCHEMA=hr:scott



If SCOTT account exist in the database then hr objects will be loaded into scott schema.

If scott account does not exist, then Import Utility will create the SCOTT account with

an unusable password because, the dump file was exported by the user SYSTEM and imported

by the user SYSTEM who has DBA privileges.



-------------Loading Objects of one Tablespace to another Tablespace--------------------



You can use remap_tablespace option to import objects of one tablespace to another

tablespace by giving the command



$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

REMAP_TABLESPACE=users:sales



The above example loads tables, stored in users tablespace, in the sales tablespace.



---------Generating SQL File containing DDL commands using Data Pump Import---------



You can generate SQL file which contains all the DDL commands which Import would have

executed if you actually run Import utility



The following is an example of using the SQLFILE parameter.



$ impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp

SQLFILE=dpump_dir2:expfull.sql



A SQL file named expfull.sql is written to dpump_dir2.



-----------------------Importing objects of only a Particular Schema---------------------



$impdp hr/hr SCHEMAS=hr,oe DIRECTORY=dpump_dir1 LOGFILE=schemas.log

DUMPFILE=expdat.dmp



-----------------Importing Only Particular Tables---------------



The following example shows a simple use of the TABLES parameter to import only the employees and jobs tables from the expfull.dmp file. You can create the expfull.dmp dump file used in this example by running the example provided for the Full Database Export in Previous Topic.



$impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLES=employees,jobs



This will import only employees and jobs tables from the DUMPFILE

RMAN IMPLEMENTATION in Oracle 9i,10g,11g -- Create Catalog Repository

Target Database Name: Test
Catalog Database Name: Catdb

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME                                                                       
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATAFILE\O1_MF_USERS_5QKOR4NW_.DBF       
E:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATAFILE\O1_MF_SYSAUX_5QKOR41T_.DBF      
E:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATAFILE\O1_MF_UNDOTBS1_5QKOR4H0_.DBF    
E:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATAFILE\O1_MF_SYSTEM_5QKOR3S8_.DBF      

Steps 1: Create Repository Tablespace 

SQL> CREATE TABLESPACE RMAN
  2  DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATAFILE\RMAN.DBF'
  3  SIZE 200M REUSE AUTOEXTEND ON
  4  EXTENT MANAGEMENT LOCAL
  5  SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

TABLESPACE_NAME                                                                 
------------------------------                                                  
SYSTEM                                                                          
UNDOTBS1                                                                        
SYSAUX                                                                          
TEMP                                                                            
USERS                                                                           
RMAN                                                                            

6 rows selected.

Steps 2: Create Repository Schema

SQL> CREATE USER RMAN IDENTIFIED BY RMAN
  2  TEMPORARY TABLESPACE TEMP
  3  DEFAULT TABLESPACE RMAN
  4  QUOTA UNLIMITED ON RMAN;

User created.

Steps 3: Grant Privileges to user RMAN.

SQL> GRANT RECOVERY_CATALOG_OWNER TO RMAN;

Grant succeeded.

SQL> GRANT CONNECT,RESOURCE TO RMAN;

Grant succeeded.


Steps 4: Creating the Recovery Catalog

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\administrator>RMAN CATALOG RMAN@CATDB

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Feb 15 11:34:31 2010

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

recovery catalog database Password:
connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog created

RMAN> Exit
Recovery Manager complete

Steps 5: Let the Catalog Database keep information in its control file for 30 days. (Optional)


SQL> SHOW PARAMETER CONTROL_FILE_RECORD_KEEP_TIME

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
control_file_record_keep_time        integer     7 

SQL> ALTER SYSTEM SET CONTROL_FILE_RECORD_KEEP_TIME=30;

System altered.

SQL> SHOW PARAMETER CONTROL_FILE_RECORD_KEEP_TIME

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
control_file_record_keep_time        integer     30                             
SQL> DISC
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> EXIT

Steps 6: Setup Target Database (Register Database)

EACH DATABASE TO BE BACKED UP BY RMAN MUST BE REGISTERED.

C:\Documents and Settings\administrator>RMAN CATALOG=RMAN/RMAN@CATDB TARGET=SYS/
TEST@TEST

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Feb 15 11:54:28 2010

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

connected to target database: TEST (DBID=1969872048)
connected to recovery catalog database

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> LIST INCARNATION;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
321     328     TEST     1969872048       PARENT  1          30-AUG-05
321     322     TEST     1969872048       CURRENT 534907     25-NOV-08

RMAN>


------------------------Backup in RMAN-----------------------


Recovery Manager: Release 10.2.0.1.0 - Production on Mon Feb 15 13:38:42 2010

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

RMAN> connect target sys@synapse

target database Password:
connected to target database: SYNAPSE (DBID=3340585066)

RMAN> backup database;

Starting backup at 15-FEB-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=129 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\UNDOTBS01
.DBF
input datafile fno=00009 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\PORTING01
.DBF
input datafile fno=00001 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\SYSTEM01.
DBF
input datafile fno=00007 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\VI1.DBF
input datafile fno=00003 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\SYSAUX01.
DBF
input datafile fno=00004 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\USERS01.D
BF
input datafile fno=00006 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\ORASER01.
DBF
input datafile fno=00005 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\EXAMPLE01
.DBF
input datafile fno=00008 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\ORASYNAPS
E.DBF
channel ORA_DISK_1: starting piece 1 at 15-FEB-10
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/15/2010 13:43:
35
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 2147483648 limit
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 15-FEB-10
channel ORA_DISK_1: finished piece 1 at 15-FEB-10
piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\SYNAPSE\BACKUPSET\2010
_02_15\O1_MF_NCSNF_TAG20100215T133909_5QL0KL06_.BKP tag=TAG20100215T133909 comme
nt=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/15/2010 13:43:
35
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 2147483648 limit

RMAN> delete backup of database;

using channel ORA_DISK_1

RMAN> backup database;

Starting backup at 15-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\UNDOTBS01
.DBF
input datafile fno=00009 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\PORTING01
.DBF
input datafile fno=00001 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\SYSTEM01.
DBF
input datafile fno=00007 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\VI1.DBF
input datafile fno=00003 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\SYSAUX01.
DBF
input datafile fno=00004 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\USERS01.D
BF
input datafile fno=00006 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\ORASER01.
DBF
input datafile fno=00005 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\EXAMPLE01
.DBF
input datafile fno=00008 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\SYNAPSE\ORASYNAPS
E.DBF
channel ORA_DISK_1: starting piece 1 at 15-FEB-10
channel ORA_DISK_1: finished piece 1 at 15-FEB-10
piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\SYNAPSE\BACKUPSET\2010
_02_15\O1_MF_NNNDF_TAG20100215T141344_5QL2B2OW_.BKP tag=TAG20100215T141344 comme
nt=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 15-FEB-10
channel ORA_DISK_1: finished piece 1 at 15-FEB-10
piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\SYNAPSE\BACKUPSET\2010
_02_15\O1_MF_NCSNF_TAG20100215T141344_5QL2NLDB_.BKP tag=TAG20100215T141344 comme
nt=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-FEB-10

RMAN>

Manual Database Creation in Oracle 10g

Manual Creation of database in windows with oracle 10g

STEP: 1

Now open a command prompt and set oracle SID as set oracle_sid=mandb

--------------------------------------------------------------------------------
C:\Documents and Settings\administrator.SUVIN>set oracle_sid=mandb
--------------------------------------------------------------------------------

STEP: 2

Start a windows service with internal password oradim –new –sid
intpwd is the syntax.

--------------------------------------------------------------------------------
C:\Documents and Settings\administrator.SUVIN>oradim -new -sid mandb -intpwd mandb
Instance created.
--------------------------------------------------------------------------------

STEP: 3

Create a directory called mandb. In my case I created it in d:\ drive
D:\mandb\data
D:\mandb\redo
D:\mandb\control
D:\mandb\bdump
D:\mandb\cdump
D:\mandb\udump
D:\mandb\pfile
D:\mandb\spfile
D:\mandb\temp

(Note: all my parameter files and .sql file that are going to discuss following are based on my
location, you can change the location according to yours)

STEP: 4

Now create a pfile for the database namely initmandb.ora in D:\mandb\pfile
Give the necessary parameter in the pfile.I gave thelease amount of parameters.

----Contents of initmandb.ora-----

db_name=mandb
instance_name=mandb

db_block_size=8192
db_cache_size=25165824

background_dump_dest=D:\mandb\bdump
core_dump_dest=D:\mandb\cdump
user_dump_dest=D:\mandb\udump

control_files=("D:\mandb\control\control01.ctl", "D:\mandb\control\CONTROL02.ctl",
"D:\mandb\control\CONTROL03.ctl")

shared_pool_size=50331648

undo_management=AUTO

STEP: 5

Now type following in your current command prompt sqlplus/nolog and in sql
prompt type conn sys/mandb as sysdba then you should see that you
are connected to an idle instance

--------------------------------------------------------------------------------
C:\Documents and Settings\administrator.SUVIN>sqlplus/nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 1 15:18:36 2010

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

SQL> conn sys/mandb as sysdba
Connected to an idle instance.
SQL>
--------------------------------------------------------------------------------

STEP: 6

Now start the instance in nomount mode as,

startup nomount pfile=D:\mandb\pfile\initmandb.ora

why are you starting the database in nomount mode ?
The reason is still we are not created control files. “An instance would be started in
the NOMOUNT stage only during database creation or the re-creation of control files.

------------------------------------------------------------------------------------
SQL> startup nomount pfile=D:\mandb\pfile\initmandb.ora
ORACLE instance started.

Total System Global Area 109051904 bytes
Fixed Size 1247540 bytes
Variable Size 75499212 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
SQL>

------------------------------------------------------------------------------------

STEP: 7

Now create the database by the following command...

CREATE DATABASE mandb
LOGFILE
GROUP 1('D:\mandb\redo\redo01.log') SIZE 50M,
GROUP 2('D:\mandb\redo\redo02.log') SIZE 50M,
GROUP 3('D:\mandb\redo\redo03.log') SIZE 50M
DATAFILE 'D:\mandb\data\system01.dbf' SIZE 200M autoextend on
SYSAUX DATAFILE 'D:\mandb\data\sysaux01.dbf' SIZE 200M
UNDO TABLESPACE UNDOTBS
DATAFILE 'D:\mandb\data\UNDOTBS.dbf' SIZE 100M
default temporary tablespace TEMP
tempfile 'D:\mandb\temp\temp_01_db01.dbf' SIZE 50M
extent management local uniform size 128k
;

Run the script in the SQL prompt
--------------------------------------------------------------------------------------
SQL> CREATE DATABASE mandb
2 LOGFILE
3 GROUP 1('D:\mandb\redo\redo01.log') SIZE 50M,
4 GROUP 2('D:\mandb\redo\redo02.log') SIZE 50M,
5 GROUP 3('D:\mandb\redo\redo03.log') SIZE 50M
6 DATAFILE 'D:\mandb\data\system01.dbf' SIZE 200M autoextend on
7 SYSAUX DATAFILE 'D:\mandb\data\sysaux01.dbf' SIZE 200M
8 UNDO TABLESPACE UNDOTBS
9 DATAFILE 'D:\mandb\data\UNDOTBS.dbf' SIZE 100M
10 default temporary tablespace TEMP
11 tempfile 'D:\mandb\temp\temp_01_db01.dbf' SIZE 50M
12 extent management local uniform size 128k
13 ;

Database created.

SQL>

once you run this you can see the control files, redo log file, Alert log file, .dbf files and .trc
(Background Trace files & User Trace files) files are created in D:\mandb\ folder.

--------------------------------------------------------------------------------------

STEP: 8

Now you can shutdown the database using shutdown command.

---------------------------------
Database dismounted.
ORACLE instance shut down.
SQL>
---------------------------------

Once the database shutdown reboot your PC

STEP: 9

Connect again as sysdba to default database

---------------------------------
sqlplus/nolog,
SQL>conn sys/mandb as sysdba
---------------------------------

check which database you are in by using the following command

show parameter service_name

STEP: 10

Now place the pfile initmandb.ora in this lacation
D:\Oracle\product\10.2.0\db_1\database\

Then try to connect like this

SQL>conn sys/mandb as sysdba

and startup your database.

You dont have to mention your pfile in th startup command since you have placed it in the
default pfile location

D:\Oracle\product\10.2.0\db_1\database\

------------------------------------------------------------------

C:\Documents and Settings\administrator.SUVIN>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 1 15:48:15 2010

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

Enter user-name: sys/mandb as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 109051904 bytes
Fixed Size 1247540 bytes
Variable Size 75499212 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL>

------------------------------------------------------------------

STEP: 11

Execute catalog.sql SQL>@d:\ORANT\rdbms\admin\catalog.sql
Execute catproc.sql SQL>@d:\ORANT\rdbms\admin\catproc.sql

STEP: 12



Now you need to edit the following files

D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
D:\Oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora

Here are the files that I used…you can change those accordingly
# TNSNAMES.ORA Network Configuration File: D:\ORANT\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
SURANGA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = suranga)
)
)
mandb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = inal075)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mandb)
)
)
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)
6
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)



# LISTENER.ORA Network Configuration File: D:\ORANT\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\ORANT)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = suranga)
(ORACLE_HOME = D:\ORANT)
(SID_NAME = suranga)
)
(SID_DESC =
(GLOBAL_DBNAME = mandb)
(ORACLE_HOME = D:\mandb)
(SID_NAME = mandb)
)
)
Now start configuring tns Service Name in Client System from where you need to
access the Oracle 10g Enterprise Server
Open Net Manager
Start->Programs->Oracle10gHome->Configuration and Migration Tools->Net Manager

Create a User in mandb Database and test the connection in Net Manager
by changing the login.

How to recover dropped table in Oracle 10g using flashback feature

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Feb 17 11:07:27 2010

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

Enter user-name: suvin@test
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
STEP 1 : WE ARE GOING TO DROP TABLE TABLE2.
SQL> desc table2;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(3)

SQL> select * from table2;

ID
----------
2
2
3
4
4
5

6 rows selected.

STEP 2 : NOW DROP THE TABLE.

SQL> drop table table2;

Table dropped.

SQL> show user
USER is "SUVIN"

SQL> select * from table2;
select * from table2
*
ERROR at line 1:
ORA-00942: table or view does not exist

Now We are confirmed that table has been dropped.

SQL> show user
USER is "SUVIN"

STEP 2 : CHECK WHEATHER THE TABLE IS AVAILABLE IN RECYCLEBIN.

SQL> select object_name,original_name from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$T0xRBK9YSomiRRmhwn/xPA==$0 TABLE2

STEP 2 : USE THIS FLASHBACK COMMAND TO RECOVER THE DROPPED TABLE.

SQL> flashback table table2 to before drop;

Flashback complete.

SQL> select * from table2;

ID
----------
2
2
3
4
4
5

6 rows selected.

SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit

IF THE DROPPED TABLE IS NOT IN RECYCLEBIN THEN YOU CANT USE FLASHBACK FEATURE TO RECOVER THE TABLE.

Else, you'll need a Backup or a dump to restore your Table.

For instance, by duplicating your database elsewhere and get back the Table by DataPump (expdp/impdp).

Exporting and Importing Tables in Oracle 10g

SQL> $exp porting@test file=c:/activity1.dmp log=c:/activity1.log tables=activity_master

Export: Release 9.2.0.1.0 - Production on Wed Feb 10 16:20:12 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table ACTIVITY_MASTER 13658 rows exported
Export terminated successfully without warnings.

SQL> conn
Enter user-name: raja@test
Enter password:
Connected.


SQL> drop table activity_master;
drop table activity_master
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop table activity_master cascade constraints;

Table dropped.


SQL> $imp raja@test file=c:/activity1.dmp log=c:/activity1.log tables=activity_master fromuser=porting touser=raja;

Import: Release 9.2.0.1.0 - Production on Wed Feb 10 16:33:14 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by PORTING, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "ACTIVITY_MASTER" 13658 rows imported
About to enable constraints...
Import terminated successfully without warnings.

SQL>

Oracle 10g Express Edition Installation on Windows and connecting to sys schema

Download Oracle Database 10g Express Edition here
http://www.oracle.com/technology/software/products/database/xe/index.html

Download these files for Windows
OracleXEUniv.exe
OracleXEClient.exe