Troubleshooting

If you receive the below error message.

* ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

You must configure your service name in

$ ORACLE_HOME/NETWORK/ADMIN/ tnsnames.ora.

Examle :

ADAPTIME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adaptime75)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ADAP)
)
)

* ORA-00257:archiver error. Connect internal only, until freed.


Solution : Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

Rman Configuration

Rman Configuratation in Oracle 10g
Version : 10.2.0
Mode : Arhivelog mode (no need)
OS: Windows 2003

Step 1:

First of all your must mount point out your database using ORACLE_SID in windows ORAENV in linux.

C:\>set oracle_sid=adaptime

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 25 19:51:53 2007

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

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

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

Step2 : Create Rman Tablespace

SQL> CREATE TABLESPACE RMAN001 DATAFILE 'F:\oracle\product\10.2.0\oradata\adaptime\RMAN001.DBF' SIZE 100M REUSE AUTOEXTEND ON;

Tablespace created.

Step 3:

Create RMAN user and password. Here i have been using rman01 and rman01 and assign default tablespace RMAN001 and temporary tablespace TEMP.

SQL> CREATE USER RMAN01 IDENTIFIED BY RMAN01 DEFAULT TABLESPACE RMAN001 TEMPORARY TABLESPACE TEMP;

User created.

Step 4:

Grant appropriate permission to rman user (the above user)

SQL> GRANT CONNECT, RESOURCE, RECOVERY_CATALOG_OWNER TO RMAN01;

Grant succeeded.

Step 5:

Lanch rman tool.

C:\>RMAN

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 25 20:18:26 2007

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

Step 6:

Connect catalog database using the above rman user.

RMAN> CONNECT CATALOG RMAN01/RMAN01@ADAPTIME

connected to recovery catalog database

Step 7:

Once you connect catalog database immediately run the following query using the given tablespace.

RMAN> CREATE CATALOG TABLESPACE RMAN001;

recovery catalog created

The recovery catalog will store information about target database.

Step 8:

Once connect target databse immediately Register your database. this is called incornation database (reference for the catalog database). Once incarnation database register there are some steps configre targer databaes. The target database that will store information about bacup's and recovery.

C:> RMAN TARGET / CATALOG RMAN01/RMAN01@ADAPTIME

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 25 20:21:43 2007

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

connected to target database: KEYSTONE (DBID=3390662877)
connected to recovery catalog database

RMAN> REGISTER DATABASE;

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

RMAN> SHOW ALL;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S
NCFKEYSTONE.ORA'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Thanks & Regards

Babu B

Physical Standby Database Creation

Primary DataBase : KEYSTONE

Standby DataBase : KEYSTONESTDY

Primary Database – ARHIVELOG Mode

Version : Oracle 10.2.0

Enable Force logging.

SQL> show user

USER is "SYS"

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR

---

NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR

---

YES

Configuring primary database initialization parameter’s

SQL> SHOW PARAMETER DB_NAME

NAME TYPE VALUE

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

db_name string KEYSTONE

SQL> SHOW PARAMETER DB_UNIQUE_NAME

NAME TYPE VALUE

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

db_unique_name string KEYSTONE

SQL> SHOW PARAMETER SERVICE_NAMES

NAME TYPE VALUE

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

service_names string KEYSTONE

Note:

If not the above parameter in your primary init.ora file. Just alter the above three parameter in your database using alter command.

Example:

ALTER SYSTEM SET DB_UNIQUE_NAME=KEYSTONE SCOPE=SPFILE;

SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE

NAME TYPE VALUE

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

remote_login_passwordfile string EXCLUSIVE

SQL> ALTER SYSTEM SET log_archive_format='ARC%T_%R_%S.LOG' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\keystoneSTDY_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONESTDY';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\keystone_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONE';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

Standy database initialization parameter:

The below parameter only need to configure standby database using primary database.

SQL> ALTER SYSTEM SET FAL_SERVER=KEYSTONESTDY;

System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT=KEYSTONE;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\KEYSTONE',
3 'c:\oracle\product\10.1.0\oradata\KEYSTONEDY' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\KEYSTONE',
3 'c:\oracle\product\10.1.0\oradata\KEYSTONEDY' scope=spfile;

System altered.

SQL> ALTER SYSETM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Primary database offline backup

Take Operating System level data file backup in primary database to standby database

SQL> HOST COPY -

> F:\oracle\product\10.2.0\oradata\KEYSTONE\*.DBF -

> F:\oracle\product\10.2.0\oradata\KEYSTONESTDY

F:\oracle\product\10.2.0\oradata\KEYSTONE\EXAMPLE01.DBF

F:\oracle\product\10.2.0\oradata\KEYSTONE\SYSAUX01.DBF

F:\oracle\product\10.2.0\oradata\KEYSTONE\SYSTEM01.DBF

F:\oracle\product\10.2.0\oradata\KEYSTONE\TEMP01.DBF

F:\oracle\product\10.2.0\oradata\KEYSTONE\UNDOTBS01.DBF

F:\oracle\product\10.2.0\oradata\KEYSTONE\USERS01.DBF

6 file(s) copied.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area 289406976 bytes

Fixed Size 1248576 bytes

Variable Size 100664000 bytes

Database Buffers 180355072 bytes

Redo Buffers 7139328 bytes

Database mounted.

To Create Standby Control file

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'F:\oracle\product\10.2.0\oradata\KEYSTONESTDY\CONTROL01.CTL';

Database altered.

Create initialization file for standby database.

SQL> CREATE PFILE='F:\oracle\product\10.2.0\db_1\database\INITKEYSTONESTDY.ORA' FROM SPFILE;

File created.

Edit the following information in init.ora file (standby) : Edit BOLD line only.

KEYSTONESTDY.__db_cache_size=180355072

KEYSTONESTDY.__java_pool_size=4194304

KEYSTONESTDY.__large_pool_size=4194304

KEYSTONESTDY.__shared_pool_size=92274688

KEYSTONESTDY.__streams_pool_size=0

*.audit_file_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/adump'

*.background_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/bdump'

*.compatible='10.2.0.1.0'

*.control_files='F:\oracle\product\10.2.0/oradata/KEYSTONESTDY/\control01.ctl'

*.core_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'

*.db_name='KEYSTONE'

db_unique_name='KEYSTONESTDY'

*.db_recovery_file_dest='F:\oracle\product\10.2.0/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=KEYSTONEXDB)'

*.fal_client='KEYSTONESTDY'

*.fal_server='KEYSTONE'

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=D:\keystoneSTDY_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONESTDY'

*.log_archive_format='ARC%S_%R.%T'

*.log_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'

*.open_cursors=300

*.pga_aggregate_target=95420416

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

service_names='KEYSTONESTDY'

*.sga_target=287309824

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/udump'

INSTANCE_NAME=KEYSTONESTDY

Create standby instance

C:\>ORADIM -NEW -SID KEYSTONESTDY -INTPWD oracle -STARTMODE manual

Instance created.

Configure Net Service's names

Add the following line in SQLNET.ORA file

sqlnet.expire_time=2

And configure tnsnames.ora file. like

KEYSTONESTDY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adaptime75)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = KEYSTONESTDY)
)
)

C:\>SET ORACLE_SID=KEYSTONESTDY

C:\>SQLPLUS SYS AS SYSDBA

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 16 19:48:34 2007

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

Enter password:

Connected to an idle instance.

Create server parameter file

SQL> CREATE SPFILE FROM PFILE;

File created.

SQL> STARTUP OPEN READ ONLY

ORACLE instance started.

Total System Global Area 289406976 bytes

Fixed Size 1248576 bytes

Variable Size 100664000 bytes

Database Buffers 180355072 bytes

Redo Buffers 7139328 bytes

Database mounted.

Database opened.

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='TEMPORARY';

TABLESPACE_NAME

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

TEMP

SQL> SELECT * FROM V$TEMPFILE;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED

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

BYTES BLOCKS CREATE_BYTES BLOCK_SIZE

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

NAME

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

1 535157 16-MAY-07 3 1 ONLINE READ WRITE

20971520 2560 20971520 8192

F:\ORACLE\PRODUCT\10.2.0\ORADATA\KEYSTONESTDY\TEMP01.DBF

SQL> SHOW USER

USER is "SYS"

SQL>

Create Redo log file for standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Note:

Thanks for visit my blog. Please try physical database creation in testing env.If any problem please post your comments..

Oracle Data Pump

Oracle Data Pump is a feature of Oracle Database 10g that enables very fast bulk data and metadata movement between Oracle databases. Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp) as well as a Web-based Oracle Enterprise Manager interface.

1. Data Pump Export and Import utilities are typically much faster than the original Export and Import Utilities. A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is 15-45 times fast than original Import.

2. Data Pump jobs can be restarted without loss of data, whether or not the stoppage was voluntary or involuntary.

3. Data Pump jobs support fine-grained object selection. Virtually any type of object can be included or excluded in a Data Pump job.

4. Data Pump supports the ability to load one instance directly from another (network import) and unload a remote instance (network export).

Data Pump Export (expdp) :-

For this example, once your export your database before that you must be give privilege on this user. If you need to export you can give " EXP_FULL_DATABASE " and if you need import you can give " IMP_FULL_DATABASE "

SQL> CONNECT SYS/BABU@KEYSTONE AS SYSDBA
Connected.

SQL> GRANT CREATE ANY DIRECTORY TO ORTHONOVC16;

Grant succeeded.

SQL> CREATE OR REPLACE DIRECTORY OTHOC16 AS 'D:\ORTHOC16';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY OTHOC16 TO ORTHONOVC16;

Grant succeeded.

SQL> GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE TO ORTHONOVC16;

Grant succeeded.

Table level Export :-

SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE tables=G_USER DIRECTORY=OTHOC16 DUMPFILE=ORTHO_G_USER.DMP LOGFILE=ORTHOLOG.LOG


The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema level export :-

SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE SCHEMAS=ORTHONOVC16 DIRECTORY=OTHOC16 DUMPFILE=ORTHONOVC16.DMP LOGFILE=ORTHONOVC16.LOG
DataBase level export :-
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE FULL=Y DIRECTORY=OTHOC16 DUMPFILE=DBORTHO.DMP LOGFILE=DBORTHO.LOG
Other export's :

Exclude = View, Proceudre, Function , Constraint , Index

Include = Table: " in ( 'emp') "

Content = ALL (by default ) / data_only / metadata_only

Estimate_Only = Before export your dumpfile you can estimate your dumpfile size using the bellow

parameter " ESTIMATE_ONLY = Y "

Change DB Name

First of all you can mount point your database, using windows ORACLE_SID and Linux or UNIX ORAENV command.

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
TAJ

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1248528 bytes
Variable Size 109052656 bytes
Database Buffers 163577856 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL>

NID: it’s a naming utility; you can able to change your database name and database id.

C:\>nid help=y

DBNEWID: Release 10.2.0.1.0 - Production on Mon May 14 10:48:33 2007

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

Keyword Description (Default)

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

TARGET Username/Password (NONE)

DBNAME New database name (NONE)

LOGFILE Output Log (NONE)

REVERT Revert failed change NO

SETNAME Set a new database name only NO

APPEND Append to output log NO

HELP Displays these messages NO

Here, I have change my database name using NID utility.

My previous database name “TAJ” my target (new) database name is “BABU”

C:\>NID TARGET=SYS/BABU@LAMP DBNAME=BABU SETNAME=Y

DBNEWID: Release 10.2.0.1.0 - Production on Mon May 14 10:37:36 2007

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

Connected to database TAJ (DBID=1565772587)

Connected to server version 10.2.0

Control Files in database:
F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\CONTROL01.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\CONTROL02.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\CONTROL03.CTL

Change database name of database TAJ to BABU? (Y/[N]) => Y

Proceeding with operation
Changing database name from TAJ to BABU
Control File F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\CONTROL01.CTL - modified
Control File F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\CONTROL02.CTL - modified
Control File F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\CONTROL03.CTL - modified
Datafile F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\SYSTEM01.DBF - wrote new name
Datafile F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\UNDOTBS01.DBF - wrote new name
Datafile F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\SYSAUX01.DBF - wrote new name
Datafile F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\USERS01.DBF - wrote new name
Datafile F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\EXAMPLE01.DBF - wrote new name
Datafile F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\RMAN01.DBF - wrote new name
Datafile F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\TEMP01.DBF - wrote new name
Control File F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\CONTROL01.CTL - wrote new name
Control File F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\CONTROL02.CTL - wrote new name
Control File F:\ORACLE\PRODUCT\10.2.0\ORADATA\LAMP\CONTROL03.CTL - wrote new name
Instance shut down

Database name changed to BABU.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

C:\>SET ORACLE_SID=LAMP

C:\>SQLPLUS /NOLOG

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 14 10:39:40 2007

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

SQL> CONNECT SYS AS SYSDBA
Enter password:
Connected to an idle instance.

Note : Please edit your database name in init.ora file.

SQL> CREATE PFILE FROM SPFILE;

File created.

Note: Once create pfile, Please check your DB_NAME in init.ora file. If not set database name manually edit your database name.

SQL> CREATE PFILE FROM SPFILE;

File created.

SQL> CREATE SPFILE FROM PFILE;

File created.

SQL> HOST
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

Once create your pfile and spfile, immediately need to create password file.

C:\>orapwd file=F:\oracle\product\10.2.0\db_1\database\pwdbabu.ora entries=5 password=oracle force=y

C:\>exit

SQL> startup
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1248528 bytes
Variable Size 109052656 bytes
Database Buffers 163577856 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
BABU

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME

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

lamp

Thanks & Regards

Babu B