Tag Experience

  • 首页   /  
  • 标签： "Experience"
    • ( 页面3 )

How to solve ORA-12560 Error ?

留下评论 ,

记得，前几日，在一台RHEL as 5.4 server上，用Windows下的Oracle客户端访问该服务器上的Oracle服务时，遭遇ORA-12560级别的错误。当时，很是费解： ①确认Windows客户端可以正常与该服务器正常通信； ②确认该服务器上的Oracle监听程序已经正确开启； ③采用SSH2协议通过22端口连接到该服务器上去的时候，可以正常操纵数据库； 犹豫之际，难到Linux服务器上的防火墙问题？ 果不其然，就是Oracle所在服务器上开启防火墙，并且没有开放1521端口。 给出解决办法： ①直接编辑（以root用户执行）/etc/sysconfig/system-config-securitylevel 配置文件： 如下是已经编辑之后的该文件：

[oracle@JM ~]$ cat /etc/sysconfig/system-config-securitylevel
# Configuration file for system-config-securitylevel
–enabled
–port=1521:tcp
[oracle@JM ~]$
[oracle@JM ~]$ cat /etc/sysconfig/system-config-securitylevel

# Configuration file for system-config-securitylevel

--enabled

--port=1521:tcp

[oracle@JM ~]$

如果，想要禁用防火墙，需要将该文件中的–enabled改成–disabled.如上，是启用防火墙，但是放开1521端口号，允许的协议是TCP协议，需要注意的是，该文件中的格式问题，即以–开始。 ②进入Linux的图形界面，进行操作。图形操作较为简单，在此不再赘述。 最终，Windows客户端可以正常访问该Linux服务器上的Oracle数据库！

Multiplexing Oracle Control File

Multiplexing Oracle Control File有3条评论 ,

1 Multiplexing the control file when using SPFILE Firstly, Obtain the information of your control file SQL> select * from v$controlfile;

STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
  /u01/oradata/helen/control01.ctl NO 16384 430
  /u01/oradata/helen/control02.ctl NO 16384 430

also you can issue this command show parameter control_files to get the information of your control file. Secondly, Make sure that your server is using the SPFILE SQL> show parameter spfile; Aafter this command is executed,we should get some value like this

NAME TYPE VALUE
spfile string /u01/app/oracle/dbs/spfilehelen.ora

if there is no result returned (a truly result for the VALUE column in the above table),it means that your server is now using a PFILE. 😆  How to Multiplexing Control File when using PFILE ? We will describe it soon . Now,we will multiplex the Control File: ① Alter the SPFILE

SQL> alter system set control_files= '/u01/oradata/helen/control01.ctl','/u01/oradata/helen/control02.ctl','/u01/oradata/control03.ctl' scope=spfile;

SQL> alter system set control_files='/u01/oradata/helen/control01.ctl', '/u01/oradata/helen/control02.ctl', '/u01/oradata/control03.ctl' scope=spfile; System altered. ②  Shutdown the database SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. ③  Create the additional Control File [oracle@Linux10g ~]$ cp /u01/oradata/helen/control01.ctl /u01/oradata/control03.ctl ④  Startup the database SQL> startup; ORACLE instance started. Total System Global Area  343932928 bytes Fixed Size                  1219328 bytes Variable Size              79693056 bytes Database Buffers          260046848 bytes Redo Buffers                2973696 bytes Database mounted. Database opened. ⑤ Identifying our Control File again SQL> select * from v$controlfile;

STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
  /u01/oradata/helen/control01.ctl NO 16384 430
  /u01/oradata/helen/control02.ctl NO 16384 430
  /u01/oradata/control03.ctl NO 16384 430

NOw,we have multiplexed the Control File When using SPFILE. 2 Multiplexing the control file when using PFILE First of all, you should to know if your server is now using a PFILE ?

SQL> show parameter spfile; At here,we can get the conclusion that the server is now using a PFILE,cause the result of the value column is null.
NAME TYPE VALUE
spfile string  
 
Maybe you are confused about the result of the above SQL command,but in fact it's true.Next,
①  Shutdown the database.
②  Create Control Files at the target directory where you want multiplex and give the corrent names.
[oracle@Linux10g ~]$ cp /u01/oradata/helen/control03.ctl /u01/oradata/control04.ctl
③  Modify the PFILE ,to make it includes the Control Files which we have created just now.
Before we change the PFILE,it maybe looks like:
……
*.control_files='/u01/oradata/helen/control01.ctl','/u01/oradata/helen/control02.ctl','/u01/o
radata/control03.ctl'
……
But after we've modified the file,it as :
……
*.control_files='/u01/oradata/helen/control01.ctl','/u01/oradata/helen/control02.ctl','/u01/o
radata/control03.ctl','/u01/oradata/control04.ctl'
……
④   startup the database
SQL> startup pfile='/u01/app/oracle/dbs/pfilehelen.ora';
⑤ Identifying our Control File again,to see if  it works correctly. SQL> select * from v$controlfile;

STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
  /u01/oradata/helen/control01.ctl NO 16384 430
  /u01/oradata/helen/control02.ctl NO 16384 430
  /u01/oradata/control03.ctl NO 16384 430
  /u01/oradata/control04.ctl NO 16384 430
 
Yep,It's great.We can multiplex Oracle Control File weather the server is used a SPFILE or a PFILE.
 

Introduce the Oracle Control File

Introduce the Oracle Control File有1条评论 ,

Control File

The control file is a small binary file necessary for the database to start and operate successfully. Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine whether the database is in a valid state to use. A control file is updated continuously by the Oracle server during database use, so it must be available for writing whenever the database is open. The information in the control file can be modified only by the Oracle server; no database administrator or end user can edit the control file. If for some reason the control file is not accessible, the database does not function properly. If all copies of a database’s control files are lost, the database must be recovered before it can be opened.

Contents of the Control file

A control file contains the following entries: • Database name and identifier • Timestamp of database creation • Tablespace names • Names and locations of data files and online redo log files • Current online redo log file sequence number • Checkpoint information • Begin and end of undo segments • Redo log archive information • Backup information

A control file contains the following entries:
• Database name and identifier
• Time stamp of database creation
• Tablespace names
• Names and locations of data files and onli
log files
• Current online redo log file sequence num
• Checkpoint information
• Begin and end of undo segments
• Redo log archive information
• Backup information

……………………………………End

user_tab_modifications

user_tab_modifications有3条评论 ,
USER_TAB_MODIFICATIONS describes modifications to all tables owned by the
current user that have been modified since the last time statistics were gathered on the

USER_TAB_MODIFICATIONS describes modifications to all tables owned by the current user that have been modified since the last time statistics were gathered on the tables. 使用User_tab_modifications来获取数据表的DML过程： 1，SQL> select * from USER_TAB_MODIFICATIONS ; no rows selected 2,  SQL>insert into test values(1,'Asher'); one row inserted 3, SQL>select * from USER_TAB_MODIFICATIONS ; no rows selected Why? just waiting for a moment,or after I've executed the procedure【dbms_stats.flush_database_monitoring_info()】，we will get the expected result. 4, SQL> exec dbms_stats.flush_database_monitoring_info(); PL/SQL procedure successfully completed. 5,SQL> select * from USER_TAB_MODIFICATIONS ; The correct result will appears.