ORA - 16179

ORA - 16179 : incremental changes to "log_archive_dest_1" not allowed with spfile

SQL> alter system set log_archive_dest_1='d:\archive\' scope=spfile; 

alter system set log_archive_dest_1='d:\ora\' scope=spfile 

* 

ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-16179: 

incremental changes to "log_archive_dest_1" not allowed with SPFILE

When i try to enable Archivelog mode in production server, i faced this ORA-16179. 

production server is version 10.1.0.2 and host is solaris sparc 64bit.

Cause:

Incremental changes to a log_archive_dest_n parameter cannot be made when using an SPFILE. 

Solution: 

Specify either LOCATION or SERVICE plus all other attributes to  be set in one ALTER SYSTEM/SESSION SET command.

Action :

SQL> ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/db1/archive';

System altered.

Reff: Metalink Doc ID: 194494.1

EM : Policy violation

Policy rule 1 : Insufficient Number of Control Files

Cause : there is only one control file in the database

Solution : Control file multiplexed..

Action : Shutdown immediate;

Startup mount;

alter system set control_files='/path/control01.ctl','/path/newcontrol02.ctl' scope=spfile;

shutdown immediate;

startup;

Policy rule 2 : Insufficient Number of Redo Logs

cause : i have two groups in my database, but each group has only one member.

Solution : Log member multiplexed

Action : Using EM control log members added...

Policy rule 3 : EXECUTE UTL_FILE privileges to PUBLIC

Cause : bydefault 'UTL_FILE' privilege grant to PUBLIC profile.

Action : Revoke execute utl_file from public;

But still policy violation still shown.
Reason, there are many other polices are set to public profile ( UTL_SMTP, UTL_TCP, UTL_HTTP and DBMS_RANDOM).

EM- java.lang.Exception:UnknownHostException

Environment : Windows XP sp3, Oracle 10.2.0.4.0

Problem : When i connect to EM console, with SYS user. it thrown an exception error message

Error : java.lang.Exception: UnknownHostException sending request :: naachi-514ccc13

Cause : Recently I was changed the server hostname. previously it was "naachi-514ccc13", now it is "naachi".

Solution : d:\> emca -config dbcontrol db -repose recreate

The following action will be performed automatically by the above single line.

>>> Stop the database control
>>> Drop the database control
>>> Create the database control
>>> Start the database control

Status : successfully problem solved.

Clone using Export Dump

create a new database or clone a existing database or recover using oracle export Dump.

1. You must have full backup dump

2. List the content from export dump

Imp file=’/dump/xyz.dmp’ log=’/dump/imp.log’ full=y show=y
It will list all sql statement in log (imp.log)

c. Open the imp.log file, search and find the tablespace and users sql statements

i. Create tablespace .......
ii. Create users ....
iii. Create index..
iv. And what every you want

d. Copy and past it in a file.sql. now u will the script for creating a database.

e. Copy and save ‘create user.. ‘ is seperate file and ‘create tablespace..’ in sepereate sql file.

3. Create required folders (bdump,udump, also oradata)

4. Edit the pfile, use it from backup, if not. copy it from old db alert log or statspack report if u have.
5. Create oracle service

Windows : oradim –new –sid xxx
Solaris : export oracle_sid= xxx

6. Startup the database

Sqlplus “/as sysdba”
Startup nomount pfile=’/pfile.ora’

7. Run the script

$> @ file.sql
It will create all tablespaces

8. Open the database

Alter database mount;
Alter database open;

9. Create the users

Run the script to create users
@users.sql’

10. Import the datas

Imp file=’/dump/xyz.dmp’ log=’/dump/imp.log’ full=y

After import data, update the database metrics using analyze command..