Garbled date
Oracle Server settings are as follows
NLS_CHARACTERSET WE8ISO8859P1
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET AL16UTF16
Registration on the machine
NLS_LANGUAGE AMERICAN_AMERICA.WE8ISO8859P1
question:
The Java program passes the date to Oracle's function, and after passing the lower(to_char(xsched_date,'DY')) function, the date becomes garbled. Excuse me:
1. How should my character set be set?
2. The server's NLS_CHARACTERSET is different from NLS_NCHAR_CHARACTERSET, it doesn't matter? What does NLS_NCHAR_CHARACTERSET mean?
answer:
1 First check the information about the character set
SQL>col value$ format a40
SQL> select * from nls_database_parameters;
SQL> select * from V$NLS_PARAMETERS;
2 The main corresponding client side is checked
SQL> select * from $ WHERE NAME='NLS_LANGUAGE';
//You can change 'AMERICAN' to 'SIMPLIFIED CHINESE', but this has nothing to do with this question
3 Modify the character set
SQL> select * from $ WHERE NAME='NLS_CHARACTERSET';
//Change the WE8ISO8859P1 on your machine to
SQL>UPDATE $ SET VALUE$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';
SQL>commit;
4 NLS_NCHAR_CHARACTERSET AL16UTF16 UTF16 is a subset of ZHS16GBK, which can display most Chinese characters.
Change character set
It is best not to modify the props$ table, as it may cause abnormal work.
If it is a client, just modify the registry;
Just use the server side "alter database character set" character set
Oracle client packaging and installation issues
My program is C/S architecture, so the client computer needs to install the ORACLE client program. I don’t know if there is a way to package the ORACLE client installer into my installer and automatically install it.
answer:
1. In fact, the installation of ORACLE is very simple. You first install a set of clients, then package these files (take them all from ORACLE_HOME), and export the ORACLE part in the registry. Then write an installer yourself and copy these files according to a certain path (do not modify the relative path). If the ORACLE_HOME you installed has changed, modify the ORACLE_hone parameter in the REG, and then load the REG file. All of these can be achieved by writing programs.
2. In fact, Oracle driver Microsoft also has it, mainly because it lacks the name of the network service. Use software to track it and see what you want to use.
Unable to determine host IP error
question:
Win2003+Oracle 9iR2
During the installation, I told me that I could not determine the host's IP. I ignored it and reported it several times, but in the end I still couldn't install it.
Answer: Modify the hosts file. Confirm that the nslookup command can be executed correctly
Set the global database name in Oracle9i when there is no domain name
set globle_name=false
Problems with dual network cards connecting to external network
question:
The server has dual network cards, one is used internally and the other is connected to the external network, and the client is installed to connect to the server. When the external network cable is unplugged, the connection is normal, and the external network cable is plugged in, but it cannot be connected.
Answer: Remove the gateway of the intranet and add a NETBEUI protocol on it.
The difference between the enterprise version and the standard version of Oracle9i database
The main difference between the Enterprise Edition and Standard Edition of Oracle9i Database is that the Enterprise Edition has stronger parallel and distributed processing capabilities. The Standard Edition is a database management system designed for the development, deployment and management of department-level Internet applications. It is a first-class database for traditional department-level applications. If there are many people who use it exceed department-level standards or access the database, the enterprise version should be selected.
The standard version contains the following components: Jserver Enterprise Edition
InterMedia
Enterprise Manager
Workflow Standard Edition
Standard Management Pack
Object Option
Networking kit
Object for OLE
Distributed Option
64 bit Option
ODBC Driver
Sql*Plus
OCI
The Enterprise Edition includes all components in the Standard Edition and also contains the following additional components:
Jserver Enterprise Edition
InterMedia
Object Option
Networking kit
Object for OLE
Advance Replication Option
Distributed Option
Parallel Query Option
SQL*Plus
VIR
Workflow Enterprise Edition
Advanced backup & recovery
Connection Pool
64 bit Option
ODBC Driver
OCI
Enterprise Manager
Enterprise Backup Utility
Please also refer to /cgi-bin/topic_show.cgi?id=117&h=1&bpg=13&age=0
Oracle and IIS address conflict
question:
The system disk of my machine is C drive, and Oracle8i is also installed on C drive. In the past, I had already set up the IIS configuration in the "Control Panel---Administrative Tools-Internet Service Manager", so in the past, I could see my website as soon as I opened http://127.0.0.1/, I could see my website. Since I installed Oracle8i, when I opened http://127.0.0.1/, a page about Oracle was displayed. The default WEB site in IIS has been "stopped". When I restarted again, it said that "the address has been used!". I don't know how to restore it to the previous situation.
Answer: Stop ORACLE HTTP service in service management
How to start a control file if it is damaged
System environment:
1. Operating system: Windows 2000 Server, 128M memory of machine
2. Database: Oracle 8i R2 (8.1.6) for NT Enterprise Edition
3. Installation path: C:\ORACLE error phenomenon: Because the hard disk has a bad channel, a control file is damaged, or a control file is deleted manually, the Oracle-related service in the control panel can be started successfully, but it cannot connect with SQL*Plus, and the following error is displayed
ORA-01034: ORACLE not available solution:
C:\>svrmgrl
svrmgrl>connect internal
svrmgrl>shutdown abort modification (C:\Oracle\admin\oradb\pfile\) file so that unusable control files are not defined before modification in the control_files parameter
control_files = ("C:\Oracle\oradata\oradb\", "C:\Oracle\oradata\oradb\",
"C:\Oracle\oradata\oradb\")
After modification
control_files = ("C:\Oracle\oradata\oradb\", "C:\Oracle\oradata\oradb\")svrmgrl>startup
Note: At least two control files must exist, even one can be used normally.
How to open the database if a certain data file is corrupted - ORA-01033: ORACLE initialization or shutdown in progress error
System environment:
1. Operating system: Windows 2000 Server, 128M memory of machine
2. Database: Oracle 8i R2 (8.1.6) for NT Enterprise Edition
3. Installation path: C:\ORACLE
Error phenomenon:
Due to misoperation, a data file in the database was deleted by mistake.
Oracle-related services in the control panel are shown to be started, but cannot connect with SQL*Plus.
The following error is displayed
ORA-01033: ORACLE initialization or shutdown in progress
Simulation phenomenon:
create tablespace test datafile
'c:\' size 5M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
default storage (initial 128K next 1M pctincrease 0)
/
Close all services
net stop "OracleWebAssistant0"
net stop "OracleOraHome81TNSListener"
net stop "OracleServiceORADB"
shutdown
Delete files in the operating system
Restart the service
net start "OracleWebAssistant0"
net start "OracleOraHome81TNSListener"
net start "OracleServiceORADB"
OracleServiceORADB in the service shows that it has been started, but cannot connect with SQL*Plus.
Show ORA-01033: ORACLE initialization or shutdown in progress
Solution:
First, take the data file offline and open the database
C:\>svrmgrl
svrmgrl>connect internal
svrmgrl>shutdown
svrmgrl>startup mount
--ARCHIVELOG mode command, file name must be capitalized
svrmgrl>alter database datafile 'C:\' offline;
--NOARCHIVELOG mode command
svrmgrl>alter database datafile 'C:\' offline drop;
svrmgrl>alter database open;
--Query the data file connection and offline status
SQL> select file#,name,status from v$datafile;
SQL> drop tablespace test;
The tablespace has been discarded.
Lost online log files
Two situations
1. What is missing is the inactive log file;
2. What is missing is the currently activated log file.
If this is the first case, and the failed log file group has multiple members, you can first shutdown the database, and then use the operating system command to overwrite the corrupted log file member file (all log files in the same log member group are mirrored). If its physical location is not available, you can copy it to a new drive. Use alter database rename file ‘xxxx’ to ‘xxxx’ to change the file location, and then start the database, and immediately perform a cold backup if it is normal. If there is only one log member in the corrupt log group, first mount the database, convert it into noarchivelog mode, execute alter database add logfile member 'xxx' to group 'x' to add a member to the relevant group, and then execute alter database drop logfile member 'bad_file' to delete the damaged log file. Due to the change in the structure of the database, you need to back up the control file. Then change the database back to archivelog mode and make a cold backup.
If the currently activated log file is missing, the database is not mirrored and all members of the current log group become unavailable. First, shutdown abort the database, restore all data files from the most recent full backup, and start the database to mount state. If the original log file physical location is not available, use alter database rename file 'xxx' to 'xxx' to change the file storage location. Then, use the recover database until cancel command to restore the database, and enter cancel after prompting that the last archive log is used up. Then use alter database open resetlogs to open the database. If there is no problem, perform a cold backup immediately. Notice! All information contained in the corrupted redo log will be lost, which means that data submitted before the database crash may be lost. This can be a huge loss for some demanding applications, so try to have multiple log members per log group and place them on different drives to prevent media failure.
Need to upgrade or purchase services?
question:
The database server managed by our unit, the configuration is: Compaq Alpha Es40 +512M + single 800Mhz CPU. Only run the ORACLE database.
Because the requirements for this unit are not very clear when designing the system, the above configuration was configured by the supplier based on the computer application situation of our unit at that time. The engineer of Compaq said that the machine was very good when installing it, but the configuration of the memory and CPU was a little smaller, and I don’t quite understand whether the impact is great.
Unexpectedly, the growth of business demand has greatly exceeded the original expectations. At the beginning, the number of PCs on the client was no more than 50, and soon it exceeded 100. I adjusted the number of single-user processes under the Unix system to 200, thinking that this should not be a big problem. As a result, the good times did not last long. 200 in less than half a year was not enough. I have now adjusted the maximum number of processes to 400. I would like to ask if the Oracle server opens a separate process on the server for each client? Does the number of clients that a server can connect to have a reference experience value in theory relative to the memory size? If so, how much is it?
answer:
Servers can be divided into dedicated servers and shared servers. A dedicated server opens one server-side process for each client process; a shared server opens several server-side processes to serve all client programs;
Generally speaking, if you use a dedicated server, you can simply calculate based on the number of concurrent connections that require 2m of memory. 512M memory has relatively more connections and a large processing volume is not enough. Oracle9i installation recommends 512m memory.
Oracle's design idea is to assume that machines have unlimited capabilities and are not very good in the past because of poor performance of machines. However, with the development of technology, the capabilities of machines doubled, and the cost was greatly reduced. You can buy a good machine with very little money. Now it seems that this development idea is extremely successful.
2. Shared services do not mean that they do not occupy memory. They only adopt the shared pool mode to suspend user processes that are connected to the database but have no data for the time being. If there are many concurrent users and frequent, the system performance will be reduced.
In addition, the SGA area that uses the most memory in Oracle database should be the SGA area. In most systems with reasonable configurations, the SGA area will account for 1/3 or even more than 1/2 of the system memory. Unreasonable SGA area configuration will greatly affect the database performance. As for the SGA area size and division, it is related to the application system's data volume, client number, data processing and program code, etc.
Based on my experience in database work, 2G memory should be required, and your unit's database is likely to need optimization.
3. I think everyone has overlooked one thing: system load is not only related to the number of users, but more related to the transaction volume.
There may be tens of millions of registered user tables in the database. What settings should be paid attention to when creating them.
For super large tables, the first thing to pay attention to is the setting of storage parameters, estimate the amount of row data, and roughly estimate the final storage space requirements of the table below. Set the next_extent and max_extents parameters when building the table (the parameters of next_extent and max_extents in the end table are not expanded). The pct_increase parameter must be set to 0, otherwise next_extent will grow geometrically in the future.
If the data of this table changes frequently (add, delete, modify) and PCT_FREE and PCT_USED parameters need to be set (please find information when defining and using the parameter); if the space is sufficient, it is best not to use the VARCHAR2 data type in the field definition. If there is no data at present, it will allow the database to reserve space to reduce the data fragmentation created by modifying the data.
The search and *** of super large tables mainly rely on indexes. How to build and use the indexes well is the responsibility of the system developers. The search of super large tables should first be divided into different table spaces (preferably different storage media) from the main table. Secondly, the search of super large tables will also occupy a large amount of space, and the storage parameters of the search should also be set.
For super large tables, a little unreasonable structural design, or even a bad program code, may make the performance of the entire system unbearable. Some principles should be remembered: on super large tables, there should be no constraint relationships with other data tables, and the constraint relationships can be implemented through program code; for super large tables, do not associate them with other data tables at will, unless they are associated through primary keys (at least index columns) and where conditions can use the index;
If you use ORACLE 8, partitioning super large tables is very valuable.
After reinstalling the Oracle server, restore the previous data system environment
1. Operating system: Windows 2000 Server, 128M memory of machine
2. Database: Oracle 8i R2 (8.1.6) for NT Enterprise Edition
3. Installation path: C:\ORACLE
illustrate:
The prerequisite for recovery is to retain all the following files:
Initialize parameter file
All data files
All redo log files (online logs, archive logs)
All control files
Internal password file
step:
1. When reinstalling the database, create the same name as the original database.
2、shutdown
3. Stop all Oracle services on NT and delete all new files in the new database (similar files above)
4. Copy all the above files back to the original directory
5、startup
How to change the Japanese system to English
Change a key value in the registry.
The specific details are as follows: Change the skill value of HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> HOME0 -> NLS_LANG to AMERICAN_AMERICA.WE8ISO8859P1, and then it's normal.
Previous page123456Next pageRead the full text