创建和更新spfile时先删除或者备份共享目录上的spfile. 最好先不删除,如果删除它会把连接所在的目录删除掉,重建的时候会搞个DB_U
[Oracle@node1 pfile]$ export ORACLE_SID=MRAC1
[oracle@node1 pfile]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 27 10:27:35 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> alter system set log_archive_dest_1='+archlog' scope=both;
alter system set log_archive_dest_1='+archlog' scope=both
*
? 1 ?????:
ORA-32017: ?? SPFILE ???
ORA-16179: ????? SPFILE ? "log_archive_dest_1" ??????
确实LOCATION
SQL> alter system set log_archive_dest_1='location=+archlog' scope=both;
??????
SQL> alter system set log_archive_format='arch_%s_%t.arc' scope=both;
alter system set log_archive_format='arch_%s_%t.arc' scope=both
*
? 1 ?????:
ORA-02095: ????????????
只能SPFILE
SQL> alter system set log_archive_format='arch_%s_%t.arc' scope=spfile;
??????
SQL> alter system set log_archive_start=true;
alter system set log_archive_start=true
*
? 1 ?????:
ORA-02095: ????????????
SQL> alter system set log_archive_start=true scope=spfile;
??????
查看活动实例
SQL> col inst_name for a30
SQL> select * from v$active_instances;
INST_NUMBER INST_NAME
----------- ------------------------------
1 node1:MYRAC1
2 node2:MYRAC2
使用SPFILE文件启动的
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/myrac/spfilemyrac.ora
节点2也使用同一个文件启动的
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/myrac/spfilemyrac.ora
节点2参数文件得到立即更新
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=+archlog
log_archive_dest_10 string
SQL> shutdown immediate
ORA-01507: ??????
ORACLE ???????
SQL> startup mount
SQL> show parameter log
ORA-01034: ORACLE not available
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> exit
? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options ??
[oracle@node1 pfile]$ export ORACLE_SID=MYRAC1
[oracle@node1 pfile]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 27 10:51:08 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-19905: log_archive_format must contain %s, %t and %r
SQL> show parameter log_archive
ORA-01034: ORACLE not available
SQL> host
[oracle@node1 pfile]$ pwd
/opt/oracle/database/admin/MYRAC/pfile
[oracle@node1 pfile]$ vi myrac1_pfile.ora
MYRAC2.__db_cache_size=75497472
MYRAC1.__db_cache_size=62914560
MYRAC1.__java_pool_size=4194304
MYRAC2.__java_pool_size=4194304
MYRAC1.__large_pool_size=4194304
MYRAC2.__large_pool_size=4194304
MYRAC2.__shared_pool_size=79691776
MYRAC1.__shared_pool_size=92274688
MYRAC1.__streams_pool_size=0
MYRAC2.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/database/admin/MYRAC/adump'
*.background_dump_dest='/opt/oracle/database/admin/MYRAC/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.1.0'
*.control_files='+DATA/myrac/controlfile/current.256.772910373'
*.core_dump_dest='/opt/oracle/database/admin/MYRAC/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='MYRAC'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYRACXDB)'
MYRAC1.instance_number=1
MYRAC2.instance_number=2
*.job_queue_processes=10
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_listener='LISTENERS_MYRAC_OLTP'
*.remote_login_passwordfile='exclusive'
*.sga_target=167772160
MYRAC2.thread=2
MYRAC1.thread=1
*.undo_management='AUTO'
MYRAC1.undo_tablespace='UNDOTBS1'
MYRAC2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/opt/oracle/database/admin/MYRAC/udump'
*.log_archive_start=true
*.log_archive_dest_1='location=+archlog'
*.log_archive_format='arch_%s_%t_%r.arc'
[oracle@node1 pfile]$ exit
exit
SQL> startup nomount pfile='/opt/oracle/database/admin/MYRAC/pfile/myrac1_pfile.ora';
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 100665588 bytes
Database Buffers 62914560 bytes
Redo Buffers 2973696 bytes
这个参数被废弃了无关紧要!
SQL> show parameter log_archive
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string
log_archive_dest_1 string location=+archlog
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest string
log_archive_format string arch_%s_%t_%r.arc
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean TRUE
log_archive_trace integer
SQL> alter database archivelog;
???????
SQL> archive log list;
??????? ????
???? ??
???? +ARCHLOG
????????? 86
????????? 88
?????? 88
SQL> alter database open;
???????
SQL> col instance_name a30
SP2-0158: ??? COLUMN ?? "a30"
SQL> col inst_name for a30
SQL> select * from v$active_instances;
INST_NUMBER INST_NAME
----------- ------------------------------
1 node1:MYRAC1
SQL> alter session set nls_language=american;
Session altered.
先把原来的AMSCMD里的删除
SQL> create spfile='+DATA/MyRAC/spfileMYRAC.ora' from pfile;
File created.
SQL> startup
ORA-01565: error in identifying file '+DATA/MYRAC/spfileMYRAC.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/MYRAC/spfileMYRAC.ora
ORA-01000: maximum open cursors exceeded
[oracle@node1 oracle]$ cat pfile_01.ora
*.SPFILE='+DATA/MYRAC/spfileMYRAC.ora'
删除SPFILE 重新创建并且修改语言到英文环境 SSH客户端没地方设置
SQL> create spfile='+data/myrac/spfileMYRAC.ora' from pfile='/opt/oracle/database/admin/MYRAC/pfile/myrac1_pfile.ora';
File created.
指定SPFILE目录和指定PFILE
结果节点2成功启动了SPFILE, 反而节点1的SPFILE 定位在本地目录上
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 100665588 bytes
Database Buffers 62914560 bytes
Redo Buffers 2973696 bytes
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/database/product/1
0.2.0/db_1/dbs/spfileMYRAC1.or
a
经过几番折腾把本地SPFILE移走到其他目录下终于启动了共享磁盘上的SPFILE文件
[oracle@node1 dbs]$ mv spfileMYRAC1.ora /opt/oracle/database
[oracle@node1 dbs]$ exit
关闭先前的实例
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
启动内存模式
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 100665588 bytes
Database Buffers 62914560 bytes
Redo Buffers 2973696 bytes
查看spfile路径发现正确用到了共享磁盘上的
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/myrac/spfilemyrac.ora
加载控制文件
SQL> alter database mount
2 ;
Database altered.
打开数据库
SQL> alter database open;
Database altered.
查看是否归档
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +ARCHLOG
Oldest online log sequence 87
Next log sequence to archive 89
Current log sequence 89
节点1 交换日志
SQL> alter system switch logfile;
System altered
自动生成多级目录
ASMCMD> pwd
+archlog/myrac/archivelog/2012_09_27
ASMCMD> ls
thread_1_seq_88.256.795093263
thread_1_seq_89.257.795108837.
节点直接启动开启数据库模式
并且查看归档
节点2 交换下日志
SQL> alter system switch logfile;
ASMCMD> ls
thread_1_seq_88.256.795093263
thread_1_seq_89.257.795108837
thread_2_seq_47.258.795108927
注意事项
1 创建pfile和spfile 最好指定目录两边都要
2 创建和更新spfile时先删除或者备份共享目录上的spfile. 最好先不删除,如果删除它会把连接所在的目录删除掉,重建的时候会搞个DB_UNKONW 怪怪地.
3 注意共享磁盘上的spfile 结尾是不带数字的
4 归档文件路径只要指定一级目录就够了,它会自动创建多级目录的.
How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AMMySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.
MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AMThe MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.
Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AMMySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.
SQL Commands in MySQL: Practical ExamplesApr 14, 2025 am 12:09 AMSQL commands in MySQL can be divided into categories such as DDL, DML, DQL, DCL, etc., and are used to create, modify, delete databases and tables, insert, update, delete data, and perform complex query operations. 1. Basic usage includes CREATETABLE creation table, INSERTINTO insert data, and SELECT query data. 2. Advanced usage involves JOIN for table joins, subqueries and GROUPBY for data aggregation. 3. Common errors such as syntax errors, data type mismatch and permission problems can be debugged through syntax checking, data type conversion and permission management. 4. Performance optimization suggestions include using indexes, avoiding full table scanning, optimizing JOIN operations and using transactions to ensure data consistency.
How does InnoDB handle ACID compliance?Apr 14, 2025 am 12:03 AMInnoDB achieves atomicity through undolog, consistency and isolation through locking mechanism and MVCC, and persistence through redolog. 1) Atomicity: Use undolog to record the original data to ensure that the transaction can be rolled back. 2) Consistency: Ensure the data consistency through row-level locking and MVCC. 3) Isolation: Supports multiple isolation levels, and REPEATABLEREAD is used by default. 4) Persistence: Use redolog to record modifications to ensure that data is saved for a long time.
MySQL's Place: Databases and ProgrammingApr 13, 2025 am 12:18 AMMySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen
MySQL: From Small Businesses to Large EnterprisesApr 13, 2025 am 12:17 AMMySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.
What are phantom reads and how does InnoDB prevent them (Next-Key Locking)?Apr 13, 2025 am 12:16 AMInnoDB effectively prevents phantom reading through Next-KeyLocking mechanism. 1) Next-KeyLocking combines row lock and gap lock to lock records and their gaps to prevent new records from being inserted. 2) In practical applications, by optimizing query and adjusting isolation levels, lock competition can be reduced and concurrency performance can be improved.
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
SublimeText3 Linux new version
SublimeText3 Linux latest version
Atom editor mac version download
The most popular open source editor
MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
SublimeText3 Mac version
God-level code editing software (SublimeText3)
