Tag Archives: oracle

【Oracle】 ORA-00257: archiver error. Connect internal only, until freed

The following error occurred recently while operating an Oracle database:

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

The main reason for this error is that there are too many archived logs, which leads to insufficient disk space. It can be solved by deleting logs or increasing space. The specific operation is as follows :(this error is solved by deleting logs here)
1. First log in to Oracle at the terminal using the following command

sqlplus /nolog
connect /as sysdba

2. Execute the following statement and check the use of Flash Recovery Area. Archivelog is already very large, close to 100.

select * from V$FLASH_RECOVERY_AREA_USAGE;

3. Use the following command to query the storage directory of Archivelog and backup it if necessary.

show parameter recover;

4. Delete the log files in the corresponding directory
5. After the log document is deleted, the control file must be maintained using RMAN, otherwise the space will still not be released. The terminal enters RMAN maintenance with the following command:

rman target sys/pass

Check for useless Archivelog:

crosscheck archivelog all;

Delete all Archivelog as of the previous day:

delete archivelog until time 'sysdate-1';

6. Query the use of Flash Recovery Area again, and you will find that a lot of space is released. At this point, the error has been resolved

Oracle database file is damaged, Error:ORA-01033 :ORACLE initialization or shutdown in progress

Due to create your own table space, but want to rename, will directly to smash this table space, after the myeclipse deployment project under the service is just an error, pl/SQL or navicat is to open other existing connection, is all the database file is unusable, comprehensive look at a few other people’s blog last problem solved:
Attached to the blog: http://blog.csdn.net/liverliu/article/details/6410287
I’ll just stick to his summary:
First of all, the cause of the problem is that I created a bookspace table space in f:/ LLH/directory before, but I didn’t want the table space later, so I just deleted it. This error is caused by deleting several files such as F :/ LLH /bookspace.dbf by mistake.
Ora-01033: ora-01033: Oracle initialization or shutdown in progress
1. First, run CMD under Windows and enter the DOS environment. 2. Login as DBA user
(1) sqlplus /NOLOG
 
(2) Connect sys/change_on_install as sysdba
Tip: Successful
 
(3) Shutdown Normal
Tip: the database has been closed
has been uninstalled the database
the ORACLE routine has been closed
(4) Startup Mount
Tip: ORACLE routine has been started
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 82886080 bytes
Database Buffers 33554432 Bytes
Redo Buffers 532480 bytes
database finished loading
(5) the alter database open;
 

error on line 1 :
ora-01157: unable to identify/lock data file 19 – refer to DBWR trace file
ora-01110: data file 19: “” F:/LLH/ bookspace.dbf “; The
tip file section is slightly different for each individual.
Continue to input
(6) Alter Database Datafile 19 offline Drop;

hint: the database has changed.
 
Use the last two steps in the “database has changed” loop until alter Database Open. No more error, “Database has changed”.
 
Up to this point, I will follow the original blogger’s operation. Now I need to close the command line and click computer — > Management – & gt; After the service, find the Oracle-related service and stop it, and then restart it. Oracle services are closed and started in a certain order, specific query can be online.
You can also refer to the following website content for setting, I did not try, roughly the same.
http://www.linuxidc.com/Linux/2016-04/130111.htm
 
 
 

How to make the import complete smoothly!

When you import an EXP, you may encounter constraints that prevent you from importing.
You can disable constraints to make the import complete!
When importing, I encountered the following problems:
Column 30 MOS
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (CMDB.CI_ELEMENT_LOCATION) violated – parent key not found
The processing steps are as follows:
SQL> create table cmdb.configuration_item_bak as select * from cmdb.configuration_item;
Table created.
SQL> alter table cmdb.configuration_item disable primary key;
alter table cmdb.configuration_item disable primary key
*
ERROR at line 1:
ORA-02297: cannot disable constraint (CMDB.PK_CONFIGURATION_ITEM) –
dependencies exist

SQL> Alter TABLE cmDB. configuration_item disable constraint PK_CONFIGURATION_ITEM;
alter table cmdb.configuration_item disable PK_CONFIGURATION_ITEM
*
ERROR at line 1:
ora-02297: cannot disable constraint (CMDB.PK_CONFIGURATION_ITEM) –
dependencies exist

SQL> Alter TABLE CMDB. Configuration_item disable constraint PK_CONFIGURATION_ITEM cascade;
Table altered.
SQL> alter table cmdb.configuration_item disable primary key;
Table altered.
SQL> Alter TABLE cmDB. configuration_item disable constraint PK_CONFIGURATION_ITEM;
Table altered.
 
I thought the import should be ok 🙂
imp/file=cmdb.dmp fromuser=cmdb touser=cmdb ignore=y
But the question remains?
Why is that?The original constraint error here was a violation of the constraint to import data in the file, regardless of whether the constraint on the table was disabled or not.
Just because the table already has the same data as the file, a constraint in the file was violated when importing it.
So you can complete the import by clearing the table data.
truncate table cmdb.configuration_item ;

then import:
-bask-3.00 $imp/file= CMdb.dmp Fromuser = CMDB Touser = CMDB ignore=y
Import: Release 11.2.0.2.0 – Production on Wed Aug 17 03:27:17 2011
Copyright (C) 1982, 2009, Oracle and/or its Affiliates. All rights Reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production
With the Partitioning option
Export file created by Export :V11.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server USES AL32UTF8 character set (possible charset)
export client USES US7ASCII character set (possible charset conversion)
. It is important to import CMDB’s objects into CMDB
imported
 

Deployment project error: Oracle 11g ora-12505, TNS:listener does not currently know of SID given in connect descriptor

As the data is read from the other party’s view, due to the confidentiality requirement of Party A, the listener.ora file said on the Internet cannot be modified. After checking the original URL = JDBC: Oracle: Thin: @10.22.1.99:1250: SSDB
The url to
JDBC: oracle: thin: @ (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (TCP) PROTOCOL = (HOST = 10.22.1.96) (1521) PORT =)) (CONNECT_DATA = (SERVICE_NAME = SSDB)))
or
JDBC: oracle: thin: the username/password @// 10.22.1.96:1521/instance name
The pro test will be successful.
 
Note the difference between oracle SID and service name selection here:
The error indicates that the SID is not found because the view provided by Party A is logged in as the service name and the configuration file is SID logged in.
The SID is internal and is a name for the instance to be used internally. The service name is external and is a database-level name that tells outsiders what the database is called.
 

Solution to Oracle error 17002

First of all, there was a 12505 error the day before yesterday. I checked it on the Internet and soon solved it. The solution :Oracle_12505 error solution
Today again meet a problem, a habit, online method, found at sixes and sevens, then, to service the block at, oracle sqldeveloper connection need to start listening, I found my listening has not started, then manually start the monitor, to practice again, even, also can go under the DOS startup, entering DOS, direct input:
LSNRCTL start can start
LSNRCTL stop stop service
LSNRCTL status to check the status
In addition, I have changed the location of the configuration file (in the link above)
listener. Ora

# listener.ora Network Configuration File: E:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Administrator)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = E:\app\Administrator

tnsnames.ora

# tnsnames.ora Network Configuration File: E:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = Administrator)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

“Permission denied” error in Oracle10g installation

Reason: Before the software was uploaded to the Linux server, it was unzipped and then uploaded in the Windows environment. When it was uploaded, it was already the folder after decompression, so the execution permission of those three files was lost.
Solutions:
1, enter database folder
2, chmod 755 runInstaller(grant permissions)
3, enter install LLL a(view files)
4, chmod 755. Oui (solve install/. Oui this hidden file does not have permissions to execute)
5, chmod 755 unzip(solve unzip permissions)
6, then execute in the Database folder./runInstaller

Solution of Oracle error 6550

When exporting data using the EXP command, the following appears:
EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 41:
PLS-00302: component ‘SET_NO_OUTLINES’ must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully
Cause
Use of Higher Version Export utility (10.2.0.1) on Lower Version database(9.2.0.6).
The main reason is that the server and client versions are not correct
You can modify oracle’s in the environment variable (Path)
D:/oracle/product/10.2.0/db_1/bin; D:/oracle/product/10.2.0/client_1/bin; The location of the
 

Oracle 11gR2 RAC ohasd failed to start solution

CRS-4124: Oracle High Availability Services startup failed.
CRS-4000: Command Start failed, or completed with errors.
ohasd failed to start: Inappropriate ioctl for device
Ohasd failed to start the at/u01/app/11.2.0/grid/CRS/install/rootcrs. Pl line 443. When I installed 11gR2 RAC for the first time, I encountered this classic problem of 11.0.2.1. After checking on the Internet, I realized that it was a bug and the solution was very simple.
Just before root.sh, execute the following command
/bin/dd if=/var/tmp/.oracle/npohasd of=/dev/null bs=1024 count=1
If there is a
/bin/dd: opening`/var/tmp/.oracle/npohasd’: No such file or directory
While the file is still running, it is not Adding a daemon to Inittab until it is ready to run. The DD command is usually executed while Adding a daemon to Inittab.
Another solution is to change file permissions
chown root:oinstall /var/tmp/.oracle/npohasd
To perform root. Don’t forget to delete the configuration before sh:/u01/app/11.2.0/grid/CRS/install/roothas. Pl – deconfig – force – verbose
Reference:
https://cn.forums.oracle.com/forums/thread.jspa?threadID=2350285
http://blog.csdn.net/tianlesoftware/article/details/8207629

TNS-03505: Failed to resolve name – accidental error!

The original address: http://blog.csdn.net/zhpsam109/article/details/727289
Modify TNsnames. ora with VI to add a local service name configuration with the following types:
 
(DESCRIPTION = = XXX

(ADDRESS_LIST =
(ADDRESS = (TCP) PROTOCOL = (HOST = 192.168.100.131) (1521) PORT =))

(CONNECT_DATA =

(= DEDICATED SERVER) (SERVICE_NAME = XXX)
)
)
 
After the configuration, use the command WQ, save and exit, and then test with TNSPing XXX, error occurs:
 
TNS-03505: Failed to resolve name
 
After a good check, the content added in tnsnames.ora above is correct and hard to understand! had to search the Internet to find a solution, just know is TNS|NAME before a space, re-open tnsnames.ora with VI, and then delete XXX in front of the space, with tnsping XXX, the problem is solved!
A small problem, toss me for a long time, ha ha!

Oracle12c client32 bit installation error: [ins-20802] Oracle net configuration assistant failed

Oracle12c Client32 [INS-20802] Oracle Net Configuration Assistant failed

Installation Of Oracle Client 12.1.0.2.0 (32-bit) Fails With An Error Message “[ins-20802] Oracle Net Configuration Assistant failed
.
installing Oracle Client12c client32 bit operating system in window 2008R2 has encountered an ins-20802 Oracle Net Configuration Assistant failure due to the failure of the execution method of the plug-in
solution:
problem because Installer found the wrong value, we open install/oraparam.ini file can see MSVCREDIST_LOC=vcredist_x64.exe, actually our file is vcredist_x86.exe, so this caused the problem.

we just go to the software directory :\client32\stage\ext\bin and install vcredist_x86.exe, the installation is complete, then go back to the oracle client where the error occurred, click retry, ok, perfectly solve the problem .

Oracle Data Guard Ping [arc2]: heartbeat failed to connect to standby “. Error is 12514 fault analysis

a set of DG built by my friends, after a long time, it has been reported as the following error:

ORA-12514: TNS:listener does not currentlyknow of service requested in connect descriptor

PING[ARC2]: Heartbeat failed to connect tostandby ‘PD’. Error is 12514.


The most common reason for the

error is static registration, and then DG parameter.

but the parameter here, which I’ve looked at for a long time, is not a problem:

SQL> show parameter dest_2


VALUE

the NAME, TYPE,

———————————————– ——————————

db_create_online_log_dest_2 string

log_archive_dest_2 string SERVICE = PD VALID_FOR = (ONLINE_L

OGFILE PRIMARY_ROLE) DB_UNIQUE

_NAME = PD


During

, I also asked my friend to do a lot of tests, including the reconstruction of password files, thought over the possible problems of DG, but there were still problems.

view related processes: there are no RFS processes in the secondary library, and no LNS processes in the primary library.

SQL> select process, status, thread#,sequence#, block#, blocks from v$managed_standby;

the PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

——— ———— ——————– ———- ———-

the ARCH CONNECTED 0 0 0 0

the ARCH CONNECTED 0 0 0 0

the ARCH CONNECTED 0 0 0 0

the ARCH CONNECTED 0 0 0 0

later, I asked my friends to take a look at the tnsping PD again. In fact, I had already asked my friends to take this test at the very beginning.

tnsping is normal, that is, the test network is passable and the listener is normal. This is also something our tnsping can do, but tnsping can’t check whether the service_name or SID in the TNsnames.ora file is correct, so we have friends post both files as well.

connected to this configuration with sqlplus, which also worked.

problem looks very strange, because DG itself has few parameters, I think about all the questions I can think of, but it doesn’t work, then I see the test result written by my friend:

[oracle @ DB11g_ST trace] $tnsping PD

TNS Ping Utility for Linux: Version 11.2.0.1.0-production on 31-jul-2013 16:11:18

Copyright (c) 1997, 2009, Oracle. All rights reserved.

informs the parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (TCP) PROTOCOL = (HOST = DB11g) (1521) PORT =) (CONNECT_DATA = (= DEDICATED SERVER) (SERVICE_NAME = former)))

OK (10 msec)

is interested in the HOST here, I suspected the problem of firewall before, but the firewall was closed, so I asked my friend to change this to IP address, and it was OK.

SQL> select process, status, thread#,sequence#, block#, blocks from v$managed_standby;

the PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

——— ———— ——————– ———- ———-

the ARCH CONNECTED 0 0 0 0

the ARCH CLOSING 1, 156, 2049, 1600

the ARCH CONNECTED 0 0 0 0

the ARCH CONNECTED 0 0 0 0

RFS IDLE 0 0 0 0

RFS IDLE 0 0 0 0

RFS IDLE 0 0 0 0

RFS IDLE 1 157 27 1

this time, the RFS process also appears.


The /etc/hosts file of the

friend host library is copied directly from the past. As follows:

/root @ DB11g_ST ~ # more/etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 DB11g localhost. Localdomain localhost

192.168.0.89 DB11g_ST

192.168.0.88 DB11g

problem is here, DB11g here has a loopback address: 127.0.0.1. Later, my friend commented this out and used the alias test, which worked fine.

is a big part of this problem, where we’ve been thinking about databases and what configurations of databases might cause this problem. Until the root cause of the problem is finally found, which is caused by the configuration of /etc/hosts.

, another friend told me another thing yesterday, they have a materialized view, they can’t refresh, the table is only 2G, it’s not too big. But it just doesn’t refresh. Later is also a long time to study, and finally locate the problem is the firewall, there is a ban on the transmission of large packets, the two sides of the firewall to remove this rule.

due to the process of this fault, we need to reflect that when we deal with the fault, do not always limited to the database level, maybe other configuration, will also cause this problem. In the process of troubleshooting, think of expanding our thinking, not in their own lane thinking rao too long.

[Oracle] when inserting data, “ora-00001: unique constraint” appears

background:

background service testing found an error unique constraint on inserting data into the oracle database table, as follows: 

### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (TEST53.SYS_C0032604) violated
### The error may exist in class path resource [sqlmapper/ACMClaimMapper.xml]
### The error may involve com.las.core.dal.dao.ACMClaimMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into TBL_ACM_CLAIM ( SEQ_NO, ACCT_NO, FUND_CHANNEL,STATUS,CREATE_DATE,CSM_NAME,CSM_CARD,LOAN_DATE,LOAN_AMT ) values ( ?,?,?,?,?,?,?,?,?)
### Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (TEST53.SYS_C0032604) violated
; SQL []; ORA-00001: unique constraint (TEST53.SYS_C0032604) violated
; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (TEST53.SYS_C0032604) violated

cause:

according to the tip of the index number, found in the table field “SEQ_NO”, found that is because the test database is by another database synchronization, automatic sequence number in the table were disrupted, lead to the next when inserting data, automatically generates SQL serial number in the table has data, so the error led to the only constraints. Of course, data deletion errors and human error operations can cause this problem.

resolved:

first, we can query where the sequence number of this table is located (my table is TBL_ACM_CLAIM) : 

select SEQ_ACM_CLAIM.NEXTVAL  from DUAL

do not execute this SQL, because if it is not executed once, the index will increase by +1. If the value of SEQ_NO in the table is greater than the value of the query, the index value will be updated. Set the sequence step size to 100 (usually 1). Execute the following SQL: 

alter sequence SEQ_ACM_CLAIM increment by 100

note: SEQ_ACM_CLAIM is not a table name, and the index value cannot modify the current value, but can only be modified to increase, not decrease.

hint:

query all indexes: 

select * from user_sequences;

delete index: 

drop sequence SEQ_ACM_CLAIM;

Create indexes:

CREATE UNIQUE INDEX SEQ_ACM_CLAIM ON TBL_ACM_CLAIM(SEQ_NO);

summary:

delete library to run road, one second is enough.