和oracle字符集相关的参数是nls_lang。NLS_LANG的格式是：language_territory.client_charset，如AMERICAN_AMERICA.ZHS16GBK，那么第一位AMERICAN表示语言，第二位AMERICA表示日期和数字格式，第三位ZHS16GBK表示 […]
12c的cluster中，dbs下指定spfile不生效
在11g的cluster中，我们往往用dbs下的初始化文件（pfile），来指定asm中实际存在的spfile
|
1 2 3 4 5 6 |
[oracle@ol6-112-rac1 dbs]$ cd /u01/app/oracle/product/11.2.0.4/db_1/dbs [oracle@ol6-112-rac1 dbs]$ ls hc_acfsdb.dat hc_cdbrac_1.dat hc_cdbrac_2.dat initcdbrac_1.ora initcdbrac_1.ora.orig init.ora orapwacfsdb spfileacfsdb.ora [oracle@ol6-121-rac1 dbs]$ cat initcdbrac_1.ora SPFILE='+DATA/cdbrac/spfilecdbrac.ora' [oracle@ol6-112-rac1 dbs]$ |
但是在12c中，如果我去修改dbs下的pfile指定spfile，这个设置就不会生效了，如我将文件指定到/tmp目录下，你 […]
asm如何更换ocr和votedisk到新存储
一个问题会牵涉出更多问题。呵呵。 最初的问题是，asm如何更换ocr和votedisk到新存储？ （一）首先官方推荐了OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) (Doc ID 428681.1) （1） OCR […]
flashback standby database
Flashback之后的standby database如何打开，操作如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
SQL> flashback database to restore point myrs1; Flashback complete. SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2254824 bytes Variable Size 356517912 bytes Database Buffers 159383552 bytes Redo Buffers 3780608 bytes Database mounted. SQL> SQL> alter database recover managed standby database using current logfile disconnect; alter database recover managed standby database using current logfile disconnect * ERROR at line 1: ORA-01665: control file is not a standby control file SQL> SQL> SQL> alter database convert to physical standby; Database altered. SQL> SQL> SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> startup ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2254824 bytes Variable Size 356517912 bytes Database Buffers 159383552 bytes Redo Buffers 3780608 bytes Database mounted. Database opened. SQL> alter database recover managed standby database using current logfile disconnect; Database altered. SQL> |
WMSYS.WM_CONCAT不出现在12c中
WMSYS.WM_CONCAT不出现在12c中，原因是WMSYS.WM_CONCAT是一个oracle supported的internal函数，是在oracle workspace manager组件（wmsys用户）中包含的，这个函数是for workspace manager内部使用。 这意味 […]
12c的Data guard中将废弃使用using current logfile
问题起源于客户的一个12c的数据库，需要启动到非real time apply的模式，但是发现执行： alter database recover managed standby database cancel; alter database recover managed standby data […]
解决12c flex cluster中实例乱跑问题
在12c中的RAC中，由于是flex cluster，常常会出现实例乱跑的现象，如实例3跑到了节点2上，实例2跑到节点3上。而且重启之后也还是如此。 我们可以这样处理，让原来乱跑的实例改回去： 1. 关闭数据库：
|
1 |
srvctl stop database -d cdbrac -stopoption immediate |
2.检查crs中记录 […]
PDB随CDB启动
12.1.0.2之前，用startup trigger：
|
1 2 3 4 5 6 7 8 |
--在CDB中建立startup trigger CREATE TRIGGER open_all_pdbs AFTER STARTUP ON DATABASE BEGIN EXECUTE IMMEDIATE 'alter pluggable database all open'; END open_all_pdbs; / |
12.1.0.2之后，用PDB save state
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
下面的命令可以放pdb保持其关机前的状态，语法如下： ALTER PLUGGABLE DATABASE [all]|[PDB_NAME] SAVE STATE; 取消设置的话，语法如下： ALTER PLUGGABLE DATABASE [all]|[PDB_NAME] DISCARD STATE; 如： alter pluggable database pdbrac1 save state; alter pluggable database all discard state; 注意，savle state只是抓取当前的状态进行save，如果当前状态是mount，然后save state了。然后在open all pdb，再重启cdb，重启后只是会恢复到open all pdb之前的状态，即save state时的状态。 SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> startup ORACLE instance started. Total System Global Area 1560281088 bytes Fixed Size 2924784 bytes Variable Size 1056968464 bytes Database Buffers 486539264 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. SQL> --最初时，2个pdb启动后状态都是mounted的。 SQL> select NAME,OPEN_MODE from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBRAC1 MOUNTED PDBRAC2 MOUNTED SQL> alter pluggable database PDBRAC1 open; Pluggable database altered. SQL> alter pluggable database pdbrac1 save state; Pluggable database altered. SQL> SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> SQL> startup ORACLE instance started. Total System Global Area 1560281088 bytes Fixed Size 2924784 bytes Variable Size 1056968464 bytes Database Buffers 486539264 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. SQL> SQL> SQL> --save state之后，PDBRAC1就是随cdb一起启动了。 SQL> select NAME,OPEN_MODE from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBRAC1 READ WRITE PDBRAC2 MOUNTED SQL> SQL> --如果是先save state，在open all，那么记录的状态只是在open all 之前的。 SQL> alter pluggable database all save state; Pluggable database altered. SQL> alter pluggable database all open; Pluggable database altered. SQL> SQL> SQL> SQL> SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> SQL> startup ORACLE instance started. Total System Global Area 1560281088 bytes Fixed Size 2924784 bytes Variable Size 1056968464 bytes Database Buffers 486539264 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. SQL> SQL> --因此重启cdb之后，也只是恢复save state时候的状态，即open all之前的，只有PDBRAC1打开的状态。 SQL> select NAME,OPEN_MODE from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBRAC1 READ WRITE PDBRAC2 MOUNTED SQL> |
12c数据泵导入报错KUP-11014
将10.2.0.5的一个大表导入到12.1.0.2的时候， 导出参数是： [oracle10g@testdb tmp]$ cat expdp.par userid=’/ as sysdba’ DIRECTORY=DUMPDIR dumpfile=mytable_%U.dmp tables=schem […]
drop user cascade报错ora-69
1. 现象：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL> drop user test cascade; drop user test cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00069: cannot acquire lock -- table locks disabled for T1 SQL> conn test/test Connected. SQL> SQL> SQL> desc t1 Name Null? Type ----------------------------------------- -------- ---------------------------- A DATE SQL> SQL> SQL> drop table t1; drop table t1 * ERROR at line 1: ORA-00069: cannot acquire lock -- table locks disabled for T1 |
2. 原因：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SQL> ! oer[oracle@rac1 ~]$ oerr ora 69 00069, 00000, "cannot acquire lock -- table locks disabled for %s" // *Cause: A command was issued that tried to lock the table indicated in // the message. Examples of commands that can lock tables are: // LOCK TABLE, ALTER TABLE ... ADD (...), and so on. // *Action: Use the ALTER TABLE ... ENABLE TABLE LOCK command, and retry // the command. [oracle@rac1 ~]$ [oracle@rac1 ~]$ [oracle@rac1 ~]$ sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.4.0 Production on Thu May 19 11:29:19 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> SQL> select owner,table_name,table_lock from dba_tables where table_name='T1' SQL> / OWNER TABLE_NAME TABLE_LO ------------------------------ ------------------------------ -------- TEST T1 DISABLED SQL> SQL> SQL> SQL> |
3. 解决方法：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> alter table test.t1 enable table lock; Table altered. SQL> SQL> select owner,table_name,table_lock from dba_tables where table_name='T1'; OWNER TABLE_NAME TABLE_LO ------------------------------ ------------------------------ -------- TEST T1 ENABLED SQL> SQL> drop user test cascade; User dropped. SQL> |
再进一步查，发现是DBA误将table_lock认为是看 […]
如何挑选一个优质IP进行解析的绑定
大型公司的服务器往往会部署到多个地区，有多个IP，如baiduc.com这个域名，在中国是被解析成123.125.114.144，在美国是被解析成180.149.132.47，在德国是被解析成111.13.101.208。 咱们公司的内网VPN，域名是myaccess.oraclevpn.com，有 […]
RMAN备份和检查逻辑坏块
1. RMAN备份时是默认检查物理坏块。 2. 如果要检查逻辑坏块，可以用如下语句：
|
1 2 |
$ rman target / RMAN> backup check logical validate database; |
注上述语句，只是检查，不会备份的。 3. 如果要在备份的同时，进行逻辑坏块检查，可以用： [crayon-67bb793e7e54a02168571 […]