活动第13/14次（2018-04-29星期日上下午）

botang

1. [oracle@station90 dbhome_1]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 10:59:09 2018
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> show parameter nls_date
   
10. 
    
11. NAME                                     TYPE         VALUE
    
12. ------------------------------------ ----------- ------------------------------
    
13. nls_date_format                      string
    
14. nls_date_language                     string
    
15. SQL> select  sysdate from dual;
    
16. 
    
17. SYSDATE
    
18. -------------------
    
19. 2018-04-29:10:59:33
    
20. 
    
21. SQL> show parameter terri
    
22. 
    
23. NAME                                     TYPE         VALUE
    
24. ------------------------------------ ----------- ------------------------------
    
25. nls_territory                             string         AMERICA
    
26. SQL> ! echo $NLS_DATE_FORMAT
    
27. YYYY-MM-DD:HH24:MI:SS
    
28. 
    
29. SQL> alter system set nls_date_format='DD-MON-YYYY';
    
30. alter system set nls_date_format='DD-MON-YYYY'
    
31.                  *
    
32. ERROR at line 1:
    
33. ORA-02096: specified initialization parameter is not modifiable with this
    
34. option
    
35. 
    
36. 
    
37. SQL> alter session set nls_date_format='DD-MON-YYYY';
    
38. 
    
39. Session altered.
    
40. 
    
41. SQL> select  sysdate from dual;
    
42. 
    
43. SYSDATE
    
44. --------------------
    
45. 29-APR-2018
    
46. 
    
47. SQL> show parameter nls_lang
    
48. 
    
49. NAME                                     TYPE         VALUE
    
50. ------------------------------------ ----------- ------------------------------
    
51. nls_language                             string         AMERICAN
    
52. SQL> alter system set nls_language='korean';
    
53. alter system set nls_language='korean'
    
54.                  *
    
55. ERROR at line 1:
    
56. ORA-02096: specified initialization parameter is not modifiable with this
    
57. option
    
58. 
    
59. 
    
60. SQL> alter session set nls_language='korean';
    
61. 
    
62. Session altered.
    
63. 
    
64. SQL> select  sysdate from dual;
    
65. 
    
66. SYSDATE
    
67. ----------------
    
68. 29-4월 -2018
    
69. 
    
70. SQL>
    

复制代码

1. SQL> show parameter open_cursor
   
2. 
   
3. NAME                                     TYPE         VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. open_cursors                             integer         300
   
6. SQL> alter system set open_cursor=400;
   
7. alter system set open_cursor=400
   
8.                  *
   
9. ERROR at line 1:
   
10. ORA-02065: ALTER SYSTEM에 대한 부당한 옵션
    
11. 
    
12. 
    
13. SQL> alter system set open_cursors=400;
    
14. 
    
15. System altered.
    
16. 
    
17. SQL> show parameter spfile
    
18. 
    
19. NAME                                     TYPE         VALUE
    
20. ------------------------------------ ----------- ------------------------------
    
21. spfile                                     string         +DATA/orcl/spfileorcl.ora
    

复制代码

添加磁盘组：

1. select  * from v$asm_disk;
   
2. 
   
3. select  * from v$asm_diskgroup;
   
4. 
   
5. alter diskgroup data add failgroup fg1 disk '/dev/raw/raw16' name DATA_0006
   
6.                                          failgroup fg2 disk '/dev/raw/raw17' name DATA_0007
   
7.                                          rebalance power 11;

复制代码

arbn进程：



归整故障组：

1. select  * from v$asm_disk;
   
2. 
   
3. select  * from v$asm_diskgroup;
   
4. 
   
5. alter diskgroup data drop disk DATA_0005 disk DATA_0000 ;
   
6. 
   
7. alter diskgroup data undrop disks;  
   
8. 
   
9. select  * from v$asm_operation;
   
10. 
    
11. alter diskgroup data add failgroup fg1 disk '/dev/raw/raw12' name DATA_0005
    
12.                                              failgroup fg2 disk '/dev/raw/raw7' name DATA_0000
    
13.                                              rebalance power 11;
    
14.                                              
    
15. alter diskgroup data drop disk DATA_0004 disk DATA_0001  rebalance power 11 ;
    
16. 
    
17. alter diskgroup data add failgroup fg1 disk '/dev/raw/raw11' name DATA_0004
    
18.                                              failgroup fg2 disk '/dev/raw/raw8' name DATA_0001
    
19.                                              rebalance power 11;
    
20.                                              
    
21. alter diskgroup data drop disk DATA_0003 disk DATA_0002  rebalance power 11;
    
22. 
    
23. alter diskgroup data add failgroup fg1 disk '/dev/raw/raw10' name DATA_0003
    
24.                                              failgroup fg2 disk '/dev/raw/raw9' name DATA_0002
    
25.                                              rebalance power 11;
    
26.                                          

复制代码

创建磁盘组：

1. create diskgroup highdata high redundancy
   
2. failgroup fg1 disk 'ORCL:ASMDISK1' name highdata_0001
   
3. failgroup fg2 disk 'ORCL:ASMDISK2' name highdata_0002
   
4. failgroup fg3 disk 'ORCL:ASMDISK3' name highdata_0003;
   
5. 
   
6. select  * from v$asm_disk;
   
7. 
   
8. select  * from v$asm_diskgroup;

复制代码

ASM磁盘组里的所有东西都是别名，“文件夹”的别名的“文件号”和“文件化身号”都是4294967295，而“文件”的文件号是比较小的数字而且在磁盘组内唯一，“文件”的“文件化身号”越大代表越新。所以如果要对磁盘组目录下的文件按照创建/修改时间来升序排序的话，要根据文件别名的第三列按照数字来排序：

[oracle@station90 ~]$ asmcmd ls +FRA/ORCL/ARCHIVELOG/2018_04_29   | sort -t . -k 3 -n
thread_1_seq_28.269.974716305
thread_1_seq_29.270.974716347
thread_1_seq_30.271.974716365
thread_1_seq_31.272.974716393
thread_1_seq_32.273.974716417
thread_1_seq_33.274.974716877
thread_1_seq_34.275.974718007
thread_1_seq_35.276.974724811
thread_1_seq_36.277.974730669
thread_1_seq_37.278.974732405
thread_1_seq_38.279.974736817
thread_1_seq_39.280.974736857
thread_1_seq_40.281.974739181

创建带自定义模板的表空间：

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 18:09:44 2018
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> create tablespace tbshigh2 datafile  '+highdata(temp1)' size 10M ;
   
10. 
    
11. Tablespace created.
    
12. 
    
13. SQL>
    

复制代码

一个normal冗余度的磁盘组具有最大的灵活性：既可以有(mirror coarse/fine)的模板，也可以有(high coarse/fine)，更可以有(unprotected coarse/fine)：

1. select  * from v$asm_alias  where group_number=3;
   
2. 
   
3. select  * from v$asm_file  where group_number=3 and file_number=258;
   
4. 
   
5. select  * from v$asm_template where group_number=3;
   
6. 
   
7. alter diskgroup highdata add template temp1 attributes ( mirror  fine  );
   
8. 
   
9. alter diskgroup highdata add template temp2 attributes ( unprotected  coarse );
   
10. 
    
11. select  * from v$asm_template where group_number=3;
    
12. 
    
13. -------------------
    
14. select  * from v$asm_template where group_number=1;
    
15. 
    
16. alter diskgroup data add template temp1 attributes ( unprotected  fine  );

复制代码

关于别名：

1. [oracle@station90 ~]$ asmcmd
   
2. ASMCMD> ls
   
3. DATA/
   
4. FRA/
   
5. HIGHDATA/
   
6. ASMCMD> cd HIGHDATA/
   
7. ASMCMD> ls
   
8. ORCL/
   
9. RCAT/
   
10. ASMCMD> cd RCAT/     
    
11. ASMCMD> ls
    
12. DATAFILE/
    
13. ASMCMD> cd DATAFILE/
    
14. ASMCMD> ls
    
15. TBSHIGH.256.974741575
    
16. TBSHIGH2.258.974743821
    
17. ASMCMD> pwd
    
18. +HIGHDATA/RCAT/DATAFILE
    
19. ASMCMD> exit
    
20. [oracle@station90 ~]$ sqlplus /nolog
    
21. 
    
22. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 18:21:29 2018
    
23. 
    
24. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
25. 
    
26. SQL> conn / as sysasm
    
27. Connected.
    
28. SQL> alter diskgroup highdata add alias '+HIGHDATA/RCAT/DATAFILE/TBSHIGH2.dbf' for '+HIGHDATA/RCAT/DATAFILE/TBSHIGH2.258.974743821';
    
29. 
    
30. Diskgroup altered.
    

复制代码

在数据库那边直接创建（还带模板）：

1. SQL> create tablespace tbshigh3 datafile '+highdata(temp1)/RCAT/DATAFILE/TBSHIGH3.dbf' size 10M;
   
2. 
   
3. Tablespace created.
   
4. 
   
5. SQL>
   

复制代码

暴力删除磁盘组：

1. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 18:21:29 2018
   
2. 
   
3. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
4. 
   
5. SQL> conn / as sysasm
   
6. Connected.
   
7. SQL> alter diskgroup highdata add alias '+HIGHDATA/RCAT/DATAFILE/TBSHIGH2.dbf' for '+HIGHDATA/RCAT/DATAFILE/TBSHIGH2.258.974743821';
   
8. 
   
9. Diskgroup altered.
   
10. 
    
11. SQL> shutdown immediate
    
12. ORA-15097: cannot SHUTDOWN ASM instance with connected client
    
13. SQL> shutdown abort
    
14. ASM instance shutdown
    
15. SQL> startup
    
16. ASM instance started
    
17. 
    
18. Total System Global Area  283930624 bytes
    
19. Fixed Size                    2212656 bytes
    
20. Variable Size                  256552144 bytes
    
21. ASM Cache                   25165824 bytes
    
22. ASM diskgroups mounted
    
23. SQL> drop diskgroup highdata;
    
24. drop diskgroup highdata
    
25. *
    
26. ERROR at line 1:
    
27. ORA-15039: diskgroup not dropped
    
28. ORA-15053: diskgroup "HIGHDATA" contains existing files
    
29. 
    
30. 
    
31. SQL> drop diskgroup highdata including contents;
    
32. 
    
33. Diskgroup dropped.
    
34. 
    
35. 
    
36. 
    

复制代码

1. Diskgroup dropped.
   
2. 
   
3. create diskgroup highdata high redundancy
   
4. failgroup fg1 disk 'ORCL:ASMDISK1' name highdata_0001
   
5. failgroup fg2 disk 'ORCL:ASMDISK2' name highdata_0002
   
6.   4  failgroup fg3 disk 'ORCL:ASMDISK3' name highdata_0003;
   
7. 
   
8. Diskgroup created.
   

复制代码

1. 
   
2. oracle@station90 ~]$ sqlplus /nolog
   
3. 
   
4. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 18:30:23 2018
   
5. 
   
6. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
7. 
   
8. SQL> conn / as sysdba
   
9. Connected to an idle instance.
   
10. SQL> startup
    
11. ORACLE instance started.
    
12. 
    
13. Total System Global Area 1603411968 bytes
    
14. Fixed Size                    2213776 bytes
    
15. Variable Size                  620759152 bytes
    
16. Database Buffers          973078528 bytes
    
17. Redo Buffers                    7360512 bytes
    
18. Database mounted.
    
19. ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
    
20. ORA-01110: data file 7: '+HIGHDATA/rcat/datafile/tbshigh.256.974741575'
    
21. 
    
22. 
    
23. SQL> archive log list;
    
24. Database log mode               Archive Mode
    
25. Automatic archival               Enabled
    
26. Archive destination               USE_DB_RECOVERY_FILE_DEST
    
27. Oldest online log sequence     49
    
28. Next log sequence to archive   51
    
29. Current log sequence               51
    
30. SQL> alter database create datafile 7 ;
    
31. 
    
32. Database altered.
    
33. 
    
34. SQL> recover datafile 7 ;
    
35. ORA-00283: recovery session canceled due to errors
    
36. ORA-01110: data file 7: '+HIGHDATA/rcat/datafile/tbshigh.256.974741575'
    
37. ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
    
38. ORA-01110: data file 7: '+HIGHDATA/rcat/datafile/tbshigh.256.974741575'
    
39. 
    
40. 
    
41. SQL> alter database create datafile 7  as new;
    
42. alter database create datafile 7  as new
    
43.                                      *
    
44. ERROR at line 1:
    
45. ORA-02236: invalid file name
    
46. 
    
47. 
    
48. SQL> alter database rename file '+HIGHDATA/rcat/datafile/tbshigh.256.974741575' to '+HIGHDATA/rcat/datafile/+HIGHDATA/rcat/datafile/TBSHIGH.256.974745093';
    
49. alter database rename file '+HIGHDATA/rcat/datafile/tbshigh.256.974741575' to '+HIGHDATA/rcat/datafile/+HIGHDATA/rcat/datafile/TBSHIGH.256.974745093'
    
50. *
    
51. ERROR at line 1:
    
52. ORA-01511: error in renaming log/data files
    
53. ORA-15122: ASM file name
    
54. '+HIGHDATA/rcat/datafile/+HIGHDATA/rcat/datafile/TBSHIGH.256.974745093'
    
55. contains an invalid file number
    
56. 
    
57. 
    
58. SQL> alter database rename file '+HIGHDATA/rcat/datafile/tbshigh.256.974741575' to '+HIGHDATA/rcat/datafile/TBSHIGH.256.974745093';
    
59. Database altered.
    
60. 
    
61. SQL> recover datafile 7 ;
    
62. Media recovery complete.
    
63. SQL> alter database open ;
    
64. alter database open
    
65. *
    
66. ERROR at line 1:
    
67. ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
    
68. ORA-01110: data file 8: '+HIGHDATA/rcat/datafile/tbshigh2.258.974743821'
    
69. 
    
70. 
    
71. SQL> alter database create datafile 8  as new;
    
72. alter database create datafile 8  as new
    
73.                                      *
    
74. ERROR at line 1:
    
75. ORA-02236: invalid file name
    
76. 
    
77. 
    
78. SQL> alter database create datafile 8  ;
    
79. 
    
80. Database altered.
    
81. 
    
82. SQL> alter database rename file '+HIGHDATA/rcat/datafile/tbshigh2.258.974743821' to '+HIGHDATA/rcat/datafile/TBSHIGH2.257.974745279';
    
83. 
    
84. Database altered.
    
85. 
    
86. SQL> recover datafile 8 ;
    
87. Media recovery complete.
    
88. SQL> alter database open ;
    
89. alter database open
    
90. *
    
91. ERROR at line 1:
    
92. ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
    
93. ORA-01110: data file 9: '+HIGHDATA/rcat/datafile/tbshigh3.dbf'
    
94. 
    
95. 
    
96. SQL> alter database create datafile 9 ;
    
97. 
    
98. Database altered.
    
99. 
    
100. SQL> recover datafile 9 ;
     
101. Media recovery complete.
     
102. SQL> alter database open ;
     
103. 
     
104. Database altered.
     
105. 
     
106. SQL>
     

复制代码

另外一个用到它的库：

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 18:28:58 2018
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected to an idle instance.
   
9. SQL> startup
   
10. ORACLE instance started.
    
11. 
    
12. Total System Global Area 6680915968 bytes
    
13. Fixed Size                    2213936 bytes
    
14. Variable Size                 3556771792 bytes
    
15. Database Buffers         3087007744 bytes
    
16. Redo Buffers                   34922496 bytes
    
17. Database mounted.
    
18. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
    
19. ORA-01110: data file 6: '+HIGHDATA/orcl/datafile/tbshighorcl.257.974741625'
    
20. 
    
21. 
    
22. SQL> alter database create datafile 6 as new;
    
23. 
    
24. Database altered.
    
25. 
    
26. SQL> show parameter db_recovery_file
    
27. 
    
28. NAME                                     TYPE         VALUE
    
29. ------------------------------------ ----------- ------------------------------
    
30. db_recovery_file_dest                     string         +FRA
    
31. db_recovery_file_dest_size             big integer 3882M
    
32. SQL> recover datafile 6;
    
33. Media recovery complete.
    
34. SQL> alter database open ;
    
35. 
    
36. Database altered.
    
37. 
    
38. SQL>
    

复制代码