Oracle 9i AS installation error
Solution: Follow its prompts to edit the host file, plus 127.0.0.1 localhost #localhost
The similarities and similarities of spfile and pfile
spfile, server parameter file is a new feature in Oracle9i and later. It allows Oracle9i to change most initialization parameters that were not dynamically changed when using databases online and users in the past. In addition, the server parameter file enables Oracle9i to remember the settings of initialization parameters that have been changed dynamically between sessions.
pfile, which is comparable to Oracle8i and previous versions.
You can create a spfile based on pfile: for example: create spfile from pfile ='path/'
Database upgrade error - irrelevant character set
The database was upgraded from Oracle 8.0.5 to 8.1.5, and an error occurred while restoring the backup data. Error prompt: Unrelated character sets (805 to 820)
1. Rebuild the Oracle 8i database, specify the same character set when building the library, and then port it; view the original database character set, select * from nls_database_parameters
2. Set the environment variable NLS_LANG of the original database Oracle 805 is consistent with the ora 8i database. The character set can be converted when executing export.
3. It is best to use both exp and imp, and do not use the dmp file exported by Oracle 805's exp, and use the imp of Oracle 816 to export.
Start iSQL*Plus
To start iSQL*Plus, follow these steps:
Connect to the Internet or intranet and start a web browser. In the Web browser's Location field or Address field, enter the Oracle9i HTTP Server URL, and then enter isqlplus. iSQL*Plus URL is as follows: http://machine_name.domain:7777/isqlplus
If you do not know the Oracle9i HTTP Server URL, please consult your system administrator.
Press Enter to go to the URL. The iSQL*Plus "Login" screen will be displayed in the web browser.
In the Username field and Password field, enter the Oracle9i username and password. If you do not know the Oracle9i username and password, please consult the database administrator.
Leave the Connection Identifier field empty to connect to the default database. When you enter the Oracle Net database alias, you can connect to a database other than the default database. If restricted database access is configured, the Connection Identifier field becomes a drop-down list of available databases to be selected.
Click Login to connect to the relevant database. The iSQL*Plus "working screen" will be displayed on the web browser.
You can now enter and execute SQL, PL/SQL, and SQL*Plus statements and commands in the input area of the Work Screen.
Start Enterprise Manager in Oracle 816
System environment:
1. Operating system: Windows 2000
2. Database: Oracle 8i R2 (8.1.6) for NT Enterprise Edition
3. Installation path: C:\ORACLE
Error phenomenon:
After installing Oracle 816 for NT by default, if you want to use Oracle816 for NT's OEM tool to manage the database, start → Programs → Oracle - OraHome81 → Enterprise Manager → Console. At this time, the following screen appears in the system:
Click "OK", and the screens 2 and 3 will appear.
Enter the IP address of the machine in the "Add Management Server" column, such as: 192.1.1.1, click "OK", and the screen four will appear.
If the oradb displays the screen, click "OK", screen 5 will appear, and "Oracle Enterprise Manager Login".
Enter password/password: system/manager, click "OK". Screen Six will appear, and the following error prompt will appear.
VTK-1000 : Unable to connect to the management server oradb. Please vertify that you have entered the correct host name nd the status of the Oracle Manager Server. Solution:
1. Use Enterprise Manager to configure auxiliary tools
Start →Program →Oracle - OraHome81 →Enterprise Manager →Configuration Assistant
a. Use the Configuration Assistant tool to create a new archive, as shown in Figure 7.
Click "Next" and screen eight appears.
b. Log in first Username: system, password: manager, service: oradb, Note: The entered user must have DBA permissions. Click "Next" and the screen ninth appears. (9i is sysman/oem_temp)
c. Create a user with this data library. The system defaults to the machine name. Here we set the user name: test, password: test, enter the password twice, click "Next", and the screen ten will appear.
d. Because the input is a new user, the system requires the user table space to be specified. This example uses the system recommendation.
Click "Next", the system is creating the OEM_REPOSITORY tablespace, and the screen 11 appears after the creation is completed.
e. The system displays "Create a profile library summary", click "Finish" to display "Configuration assistive tool progress", as shown in Figure 12.
Click "Show detailed information" to see the prompt that the system is creating various objects.
The system processing is completed, click "Close", as shown in Figure 13.
2. Start OracleOraHome81ManagementServer service: Start → Settings → Control Panel → Management Tools → Services, as shown in Figure 14, find the location of Oracle-related services.
Click "Start", as shown in Figure 15. If the archive library was not created in the previous step, the startup will fail.
Set the OracleOraHome81ManagementServer service to automatically start.
3. Re-run Oracle Enterprise Manager Login: Start → Program → Oracle - Oracle - OraHome81 → Enterprise Manager → Console
The default administrator of Oracle Enterprise Manager is sysman/oem_temp, and the system administrator should change the password immediately.
As shown in Figure 16, do not write sysman/oem_temp directly in the administrator column, but enter "user name/password" separately.
Click "OK", and Figure 17 appears, a security warning, requiring you to change sysman's password immediately.
In this example, enter the manager twice as the password. After entering the password, click "Change", and the system officially brings up the screen of "Oracle Enterprise Manager console SYSMAN@oradb".
When starting OMS when backing up and restoring the database, VTD is always prompted - 0057 error occurred while connecting to the archive library Oracle sysman emSDK client ons client NoDomainException
"emca" to create a "repository" for oem
start
oemctl start oms
$ORACLE_HOME/network/snmp/peer/start_peer -a
dbastudio <= initial account/password=sysman/oem_temp
Database creation script
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: D:\ORACLE
Steps to build a library:
1. Create related directories manually
D:\Oracle\admin\test
D:\Oracle\admin\test\adhoc
D:\Oracle\admin\test\bdump
D:\Oracle\admin\test\cdump
D:\Oracle\admin\test\create
D:\Oracle\admin\test\exp
D:\Oracle\admin\test\pfile
D:\Oracle\admin\test\udump
D:\Oracle\oradata\test
D:\Oracle\oradata\test\archive
2. Manually create the initial startup parameter file: D:\Oracle\admin\test\pfile\, content:
3. Manually create D:\Oracle\Ora81\DATABASE\ file,
Content: IFILE='D:\Oracle\admin\test\pfile\'
4. Use the command to create D:\Oracle\Ora81\DATABASE\
Command: D:\Oracle\Ora81\bin\orapwd file=D:\Oracle\Ora81\DATABASE\ password=ORACLE entries=5
5. Through commands, a new instance management service is generated in the service, and the startup method is manual.
set ORACLE_SID=test
D:\Oracle\Ora81\bin\oradim -new -sid test -startmode manual -pfile "D:\Oracle\admin\test\pfile\"
6. Generate various database objects D:\>svrmgrl
--Create a database
connect INTERNAL/Oracle
startup nomount pfile="D:\Oracle\admin\test\pfile\"
CREATE DATABASE test
LOGFILE 'D:\Oracle\oradata\test\' SIZE 2048K,'D:\Oracle\oradata\test\' SIZE 2048K, 'D:\Oracle\oradata\test\' SIZE 2048K
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE 'D:\Oracle\oradata\test\' SIZE 58M REUSE AUTOEXTEND ON NEXT 640K
MAXDATAFILES 254
MAXINSTANCES 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET ZHS16GBK;
Control files and log files are generated when the above statement is executed
connect INTERNAL/Oracle
--Modify the system tablespace
ALTER TABLESPACE SYSTEM DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 50);
ALTER TABLESPACE SYSTEM MINIMUM EXTENT 64K;
--Create a rollback tablespace
CREATE TABLESPACE RBS DATAFILE 'D:\Oracle\oradata\test\' SIZE 256M REUSE
AUTOEXTEND ON NEXT 5120K
MINIMUM EXTENT 512K
DEFAULT STORAGE(INITIAL 512K NEXT 512K MINEXTENTS 8 MAXEXTENTS 4096);
--Create user tablespace
CREATE TABLESPACE USERS DATAFILE 'D:\Oracle\oradata\test\' SIZE 128M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
--Create temporary tablespace
CREATE TABLESPACE TEMP DATAFILE 'D:\Oracle\oradata\test\' SIZE 32M REUSE
AUTOEXTEND ON NEXT 640K
MINIMUM EXTENT 64K
DEFAULT STORAGE(INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TEMPORARY;
--Create a tool tablespace
CREATE TABLESPACE TOOLS DATAFILE 'D:\Oracle\oradata\test\' SIZE 64M REUSE
AUTOEXTEND ON NEXT 320K
MINIMUM EXTENT 32K
DEFAULT STORAGE ( INITIAL 32K NEXT 32K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
--Create index tablespace
CREATE TABLESPACE INDX DATAFILE 'D:\Oracle\oradata\test\' SIZE 32M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
--Create a rollback segment
CREATE PUBLIC ROLLBACK SEGMENT RBS0 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS1 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS2 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS3 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS4 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS5 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS6 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS7 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS8 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS9 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS10 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS11 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS12 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS13 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS14 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS15 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS16 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS17 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS18 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS19 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS20 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS21 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS22 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS23 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
CREATE PUBLIC ROLLBACK SEGMENT RBS24 TABLESPACE RBS STORAGE ( OPTIMAL 4096K );
--Make the rollback segment online
ALTER ROLLBACK SEGMENT "RBS0" ONLINE;
ALTER ROLLBACK SEGMENT "RBS1" ONLINE;
ALTER ROLLBACK SEGMENT "RBS2" ONLINE;
ALTER ROLLBACK SEGMENT "RBS3" ONLINE;
ALTER ROLLBACK SEGMENT "RBS4" ONLINE;
ALTER ROLLBACK SEGMENT "RBS5" ONLINE;
ALTER ROLLBACK SEGMENT "RBS6" ONLINE;
ALTER ROLLBACK SEGMENT "RBS7" ONLINE;
ALTER ROLLBACK SEGMENT "RBS8" ONLINE;
ALTER ROLLBACK SEGMENT "RBS9" ONLINE;
ALTER ROLLBACK SEGMENT "RBS10" ONLINE;
ALTER ROLLBACK SEGMENT "RBS11" ONLINE;
ALTER ROLLBACK SEGMENT "RBS12" ONLINE;
ALTER ROLLBACK SEGMENT "RBS13" ONLINE;
ALTER ROLLBACK SEGMENT "RBS14" ONLINE;
ALTER ROLLBACK SEGMENT "RBS15" ONLINE;
ALTER ROLLBACK SEGMENT "RBS16" ONLINE;
ALTER ROLLBACK SEGMENT "RBS17" ONLINE;
ALTER ROLLBACK SEGMENT "RBS18" ONLINE;
ALTER ROLLBACK SEGMENT "RBS19" ONLINE;
ALTER ROLLBACK SEGMENT "RBS20" ONLINE;
ALTER ROLLBACK SEGMENT "RBS21" ONLINE;
ALTER ROLLBACK SEGMENT "RBS22" ONLINE;
ALTER ROLLBACK SEGMENT "RBS23" ONLINE;
ALTER ROLLBACK SEGMENT "RBS24" ONLINE;
--Modify the temporary tablespace of the sys user to TEMP
alter user sys temporary tablespace TEMP;
--Create a data dictionary table
@D:\Oracle\Ora81\Rdbms\admin\;
@D:\Oracle\Ora81\Rdbms\admin\
@D:\Oracle\Ora81\Rdbms\admin\
@D:\Oracle\Ora81\Rdbms\admin\
connect system/manager
@D:\Oracle\Ora81\sqlplus\admin\
connect internal/Oracle
@D:\Oracle\Ora81\Rdbms\admin\
exit
--Generate SQL*Plus Help System
sqlplus SYSTEM/manager
@D:\Oracle\Ora81\sqlplus\admin\help\
exit
--Modify the default tablespace and temporary tablespace of the system user
svrmgrl
connect internal/Oracle
alter user system default tablespace TOOLS;
alter user system temporary tablespace TEMP;
exit
7. Set the test instance startup service to automatic startup mode
D:\Oracle\Ora81\bin\oradim -edit -sid test -startmode auto
Start ARCHIVELOG mode
C:\>svrmgrl
Oracle Server Manager Release 3.1.6.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All rights reserved.
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SVRMGR> connect internal
Connection successfully
SVRMGR>shutdown
The database has been closed.
The database has been removed.
The ORACLE instance has been closed.
SVRMGR> startup mount
The ORACLE instance has been started.
The total global area of the system has 57124108 bytes
Fixed Size 70924 bytes
Variable Size 40198144 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Loaded into the database.
SVRMGR> alter database archivelog;
The statement has been processed.
SVRMGR> archive log start
The statement has been processed.
SVRMGR> alter database open;
The statement has been processed.
SVRMGR> alter system switch logfile; --Force the system to switch logs, and the generation of archived logs can be observed immediately.
The statement has been processed.
SVRMGR> exit
The server manager ends.
Modify database parameter file c:\Oracle\admin\oradb\pfile\,
Cancel the # comment of the following statement
log_archive_start = true
log_archive_dest_1 = "location=C:\Oracle\oradata\oradb\archive"
log_archive_format = %%ORACLE_SID%%T%TS%
Close the database and restart
Check the directory C:\Oracle\oradata\oradb\archive, and you can see similar files, indicating that the archive is successful.
PLUS: The part that needs to be executed in Oracle9i
SQL> alter system set log_archive_dest_1 ="location=E:\Oracle\oradata\test\archive" scope = spfile;
SQL> alter system set log_archive_start = true scope = spfile;
Add some concepts
Explain the meaning of the parameter item in the parameter file regarding the archive redo log
Is the archive mode automatic or manual, true is automatic, false is manual
log_archive_start = true
The path to save the archive log file
log_archive_dest_1 = "location=C:\Oracle\oradata\oradb\archive"
Naming method for archive log files
log_archive_format = %%ORACLE_SID%%T%TS%
Archive command:
Start the automatic archive mode. After the system restarts, the archive method will be set according to the value of the parameter log_archive_start.
SVRMGR> archive log start
Start manual archive mode
SVRMGR> archive log stop
View archive information: Whether the redo log is archived, is it automatically archived or manually archived, archive path, and the oldest online log sequence number
SVRMGR> archive log list
Archive a full online redo log without archive
SVRMGR> archive log next
Archive all full online redo logs but not archived
SVRMGR> archive log all
Note: A transaction is written to the redo log even if it is not committed.
The program package and package body under SYS can only be compiled into valid
Question: The package and package body under my sys are both invalid. However, the STANDARD package body can be compiled to valid. Other packages are all invalid. Compilation prompts:
VBO-4553: PL/SQL package compilation is not. For more information, press the display error on this property page. ORA-06554:????? PL/SQL ???? DBMS_STANDARD
Prompt when connecting SQLPLUS: ERROR:ORA-06554: package DBMS_STANDARD must be created before using PL/SQL
Error accessing package DBMS_APPLICATION_INFO
ERROR:ORA-06554: package DBMS_STANDARD must be created before using PL/SQL
answer:
Recreate these packages with the relevant files below $ORACLE_HOME/RDBMS/ADMIN.
SYSDBA login and run
$ORACLE_HOME/RDBMS/ADMIN/
$ORACLE_HOME/RDBMS/ADMIN/
How to implement: insert data into table TPROCESS in database A, and the database is automatically updated to table TLOG in database B?
1. at db1
chk
CONNECT_STRING_DB2 = ...
tnsping CONNECT_STING_DB2
sql> create public database link DB2 using 'CONNECT_STING_DB2';
2. at db2
chk
CONNECT_STRING_DB1 = ...
tnsping CONNECT_STING_DB1
sql> create public database link DB1 using 'CONNECT_STING_DB1';
3. at db1
sql> create trigger ... after insert ...
insert into @DB2 ... <<== using database link !!!
:NEW.column1, :NEW.column2 ...
EXP database from a database whose character set is not a Chinese character set, and then import it into a Chinese character set database
EXP/IMP is a database-to-export import tool, but EXP/IMP has strict requirements on the character set of the database, if the character set of the database outputted by EXP is incompatible with the character set of the imported database. The import cannot be completed. At this time, a special method needs to be taken to operate. Since this operation is a certainly harmful operation, if the database suddenly fails during the operation, the database may not start normally. Therefore, you must back up the database before operation.
1. Execute sqlplus in the original database, connect to the sys user, modify the character set parameters, and then exit.
SQL> connect sys/******;
update props$ set value$ = 'ZHS16GBK' where name = 'NLS_CHARACTERSET';
update props$ set value$ = 'ZHS16GBK' where name = 'NLS_NCHAR_CHARACTERSET';
commit;
SQL> quit;
2. Execute svrmgrl, close and restart the Oracle database
SVRMGR> connect internal/******; (password is generally "Oracle")
SVRMGR> shutdown immediate;
SVRMGR> startup mount;
SVRMGR> alter system enable restricted session;
SVRMGR> alter system set job_queue_process=0;
SVRMGR> alter database open;
SVRMGR> alter database character set ZHS16GBK;
SVRMGR> exit;
3. Modify the environment variable NLS_LANG
By AMERICAN_AMERICA.WE8ISO8859P1
Convert to SIMPLIFIED CHINESE_CHINA.ZHS16GBK
If it is WINDOWS, use Regedit to modify the key value under ORACLE. If it is a UNIX system, modify the environment variable: NLS_LANG=...;export NLS_LANG
If it is OpenVMS: Modify the settings in the file and execute again
4. Execute exp to export data
5. Modify the environment variable NLS_LANG to the original value
6. Execute sqlplus, connect to the sys user, restore the character set parameters, and then exit.
connect sys/******; (password is generally "manager")
update props$ set value$ = 'WE8ISO8859P1' where name = 'NLS_CHARACTERSET';
update props$ set value$ = 'WE8ISO8859P1' where name = 'NLS_NCHAR_CHARACTERSET';
commit;
SQL> quit;
7. Repeat step 2 to close and restart the Oracle database
8. Import the exported dmp file into the target database (character set is Chinese)
DBLink was created successfully, but could not connect
question:
ORACLE 8.1.6 for NT, you need to connect another ORACLE 8 FOR UNIX, of course, you need to create DBLINK. However, when you create a local network service name, you can connect to it. The DBLINK you created just can't connect to it. Why?
Use select <xxx> from <table name>@<db_link>
Error is: ORA-02085: The database link is connected to ORA8I (ora8i is the name of DBLINK)
answer:
If your source database GLOBAL_NAME=TRUE, then the name of your DBLINK must be the same as the global name of the remote database, otherwise a 2085 error will occur.
You can set the GLOBAL_NAME=FALSE of the source database, or you can set the name of the DBLINK to the global name of the remote database when creating the DBLINK.
How to Configure the 9i Agent with Multiple Network Cards
fact: Oracle Intelligent Agent 9.0.1
fix:
Configuring the Version 9.0.1 Agent for Use with Multiple Network Cards (NIC)(From the Oracle IA User's Guide Release 9.0.1 Part Number A88771-02):
As with version 8.1.7 of the Intelligent Agent, 9i Intelligent Agent users have three options to configure the Agent on a machine with multiple network default the Agent will bind to the primary NIC on its machine ('le0' on UNIX platforms and 'network0' on Windows NT platforms). The other two options are:
a. Ability to bind to a NIC specified by the user.