-- 유저 별 select 'select '||'count(*)'||' ' ||'from ' ||table_name||';' from user_tables;
-- 전체 건수 뽑는 쿼리 뽑기 select 'select '||'count(*)'||' ' ||' from '|| owner||'.' ||table_name||';' from dba_tables order by owner,table_name ;;
-- 유저 별 함수 사용 select table_name ,num_rows "통계건수" ,to_number( dbms_xmlgen.getxmltype('SELECT COUNT(*) C FROM ' || TABLE_NAME).Extract('//text()')) "실제 건수" FROM dba_tables;
select table_name ,num_rows "통계건수" ,to_number( dbms_xmlgen.getxmltype('SELECT COUNT(*) C FROM ' || TABLE_NAME).Extract('//text()')) "실제 건수" FROM user_tables;
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 1 17:01:41 2023 Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19C (DBID=1191478324) connected to auxiliary database: ORA19C (not mounted)
RMAN> run { 2> allocate channel prmy1 type disk; 3> allocate auxiliary channel stby1 type disk; 4> duplicate target database for standby from active database nofilenamecheck dorecover; 5> }
using target database control file instead of recovery catalog allocated channel: prmy1 channel prmy1: SID=23 device type=DISK
Starting Duplicate Db at 01-JUN-23 current log archived
contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '/u01/app/oracle/product/19c/db_1/dbs/orapwstandby' ; } executing Memory Script
Starting backup at 01-JUN-23 Finished backup at 01-JUN-23
contents of Memory Script: { restore clone from service 'primary' standby controlfile; } executing Memory Script
Starting restore at 01-JUN-23
channel stby1: starting datafile backup set restore channel stby1: using network backup set from service primary channel stby1: restoring control file channel stby1: restore complete, elapsed time: 00:00:01 output file name=/oradata/STANDBY/control01.ctl output file name=/oradata/STANDBY/control02.ctl Finished restore at 01-JUN-23
contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script: { set newname for tempfile 1 to "/oradata/STANDBY/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/oradata/STANDBY/system01.dbf"; set newname for datafile 3 to "/oradata/STANDBY/sysaux01.dbf"; set newname for datafile 4 to "/oradata/STANDBY/undotbs01.dbf"; set newname for datafile 7 to "/oradata/STANDBY/users01.dbf"; restore from nonsparse from service 'primary' clone database ; sql 'alter system archive log current'; } executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradata/STANDBY/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 01-JUN-23
channel stby1: starting datafile backup set restore channel stby1: using network backup set from service primary channel stby1: specifying datafile(s) to restore from backup set channel stby1: restoring datafile 00001 to /oradata/STANDBY/system01.dbf channel stby1: restore complete, elapsed time: 00:00:25 channel stby1: starting datafile backup set restore channel stby1: using network backup set from service primary channel stby1: specifying datafile(s) to restore from backup set channel stby1: restoring datafile 00003 to /oradata/STANDBY/sysaux01.dbf channel stby1: restore complete, elapsed time: 00:00:15 channel stby1: starting datafile backup set restore channel stby1: using network backup set from service primary channel stby1: specifying datafile(s) to restore from backup set channel stby1: restoring datafile 00004 to /oradata/STANDBY/undotbs01.dbf channel stby1: restore complete, elapsed time: 00:00:01 channel stby1: starting datafile backup set restore channel stby1: using network backup set from service primary channel stby1: specifying datafile(s) to restore from backup set channel stby1: restoring datafile 00007 to /oradata/STANDBY/users01.dbf channel stby1: restore complete, elapsed time: 00:00:01 Finished restore at 01-JUN-23
sql statement: alter system archive log current current log archived
contents of Memory Script: { restore clone force from service 'primary' archivelog from scn 2276859; switch clone datafile all; } executing Memory Script
Starting restore at 01-JUN-23
channel stby1: starting archived log restore to default destination channel stby1: using network backup set from service primary channel stby1: restoring archived log archived log thread=1 sequence=13 channel stby1: restore complete, elapsed time: 00:00:01 channel stby1: starting archived log restore to default destination channel stby1: using network backup set from service primary channel stby1: restoring archived log archived log thread=1 sequence=14 channel stby1: restore complete, elapsed time: 00:00:01 Finished restore at 01-JUN-23
contents of Memory Script: { set until scn 2277311; recover standby clone database delete archivelog ; } executing Memory Script
executing command: SET until clause
Starting recover at 01-JUN-23
starting media recovery
archived log for thread 1 with sequence 13 is already on disk as file /arch/1_13_1138290295.arc archived log for thread 1 with sequence 14 is already on disk as file /arch/1_14_1138290295.arc archived log file name=/arch/1_13_1138290295.arc thread=1 sequence=13 archived log file name=/arch/1_14_1138290295.arc thread=1 sequence=14 media recovery complete, elapsed time: 00:00:01 Finished recover at 01-JUN-23
contents of Memory Script: { delete clone force archivelog all; } executing Memory Script
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 1 17:46:05 2023 Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19C (DBID=1191478324) connected to auxiliary database: ORA19C (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
Starting Duplicate Db at 01-JUN-23 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=379 device type=DISK current log archived
contents of Memory Script: { backup as copy reuse passwordfile auxiliary format '/u01/app/oracle/product/19c/db_1/dbs/orapwstandby' ; } executing Memory Script
Starting backup at 01-JUN-23 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK Finished backup at 01-JUN-23
contents of Memory Script: { restore clone from service 'primary' standby controlfile; } executing Memory Script
Starting restore at 01-JUN-23 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service primary channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oradata/STANDBY/control01.ctl output file name=/oradata/STANDBY/control02.ctl Finished restore at 01-JUN-23
contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script: { set newname for tempfile 1 to "/oradata/STANDBY/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/oradata/STANDBY/system01.dbf"; set newname for datafile 3 to "/oradata/STANDBY/sysaux01.dbf"; set newname for datafile 4 to "/oradata/STANDBY/undotbs01.dbf"; set newname for datafile 7 to "/oradata/STANDBY/users01.dbf"; restore from nonsparse from service 'primary' clone database ; sql 'alter system archive log current'; } executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradata/STANDBY/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 01-JUN-23 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service primary channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata/STANDBY/system01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service primary channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata/STANDBY/sysaux01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service primary channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata/STANDBY/undotbs01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: using network backup set from service primary channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradata/STANDBY/users01.dbf channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 01-JUN-23
sql statement: alter system archive log current current log archived
contents of Memory Script: { restore clone force from service 'primary' archivelog from scn 2283316; switch clone datafile all; } executing Memory Script
Starting restore at 01-JUN-23 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service primary channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=21 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: using network backup set from service primary channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=22 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 01-JUN-23
contents of Memory Script: { set until scn 2283480; recover standby clone database delete archivelog ; } executing Memory Script
executing command: SET until clause
Starting recover at 01-JUN-23 using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 21 is already on disk as file /arch/1_21_1138290295.arc archived log for thread 1 with sequence 22 is already on disk as file /arch/1_22_1138290295.arc archived log file name=/arch/1_21_1138290295.arc thread=1 sequence=21 archived log file name=/arch/1_22_1138290295.arc thread=1 sequence=22 media recovery complete, elapsed time: 00:00:01 Finished recover at 01-JUN-23
contents of Memory Script: { delete clone force archivelog all; } executing Memory Script
############################################################################# #1,#2 서버에 모두 적용 alter system set local_listener='LISTENER1','LISTENER2' scope=both; #############################################################################
alter database recover managed standby database cancel; alter database flashback on; alter database recover managed standby database using current logfile disconnect;
모니터링 COLUMN NAME FORMAT A24 COLUMN VALUE FORMAT A16 COLUMN DATUM_TIME FORMAT A24 SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
1. 육전 - 미미원 : 방송을 워낙에 많이 탔지만, 간이 안된 음식 안 좋아하는 풍자 누나가 갈 곳은 아님. 간을 아예 하지 않고 그 맛 그대로 느끼라고 부침가루+계란물 해서 부쳐줌. 옆에서 계속 부쳐줌. 파절이랑 섞어서 먹으면 끝내줌. 아주 고급진 곳이라 조선대학교 교수들이 외지인들 오면 데리고 가는 곳이라고 이미 널리 알려져있음.
2. 뼈해장국 - 어등뼈해장국 : 장담컨데 살이 그렇게 많이 붙은 뼈해장국은 이 집이 유일함. 돼지고기가 국내산이 아니라고 알고있는데 그래도 맛있음. 진짜 미쳐버리게 맛있는 집.
3. 곰탕 - 하얀집 : 나주 하얀집 멀티집. (본점 아님. 본점보다 맛있는 곳) 광주 월곡동에 있음. 주차가 어려워서 잘 안 가게 되지만, 주차를 엄청나게 멀리 하고 걸어서 가도 먹을만한 가치가 있는 곳. 김치 개맛있음. 국물 한 수저 떠 먹을때 야구로 비유하자면 '좌중을 가르는 2타점 적시타' 이고, 김치 입에 넣으면 만루홈런임. 곰탕+김치 같이 입에 넣으면 한 번에 6점임. 주구장창 줄 서서 먹는 집. 해외에서 온 손님들(동남아시아 분들) 데리고 가면 완탕 하는 유일한 집. 졸라 맛있음.
4. 백반 - 신안동 진식당 : 여기 게장 먹어보십쇼. 저희 어머니가 게장을 영혼까지 다해 잘 하셔서 게장에 대해서는 빠삭하게 잘 압니다. 게장은 인천 송도 신도시 남순게장집이 원탑이고요. 그 집보다 살짝 더 간이 들어간 집. 대한민국 게장 2탑이 이 집입니다. 여기 댓글 보니 이 집 추천 하신 분들이 엄청나게 많네요. 고등어구이, 삼치구이 찌개류 싹다 맛있습니다. 약 10평 정도 되는 작은 식당에서 입소문 덕분에 2호점 낸 곳임.
5. 곱창 - 월곡동 알천소곱창 : 광주 사람인데, 서울에서 10년 동안 회사생활하면서 신도림 대왕곱창을 정말 좋아했는데, 그집보다 맛있는 곳이 이곳임. 된장국이 존맛이라 리필해달라고 하면 계속 해주고, 나중에 술 취해서 사장님께 "아!!! 된장찌개에 들어간 된장 좀 주심 안됩니까!" 했더니 위생팩에 된장을 주걱으로 담아서 주심. (신송된장이라고 함. 식자재 가야 살 수 있음) 곱창 예술. 대창 예술. 간장에 청양고추 가위로 잘라서 찍어드세요.
6. 엄마네돼지찌개 : 독보적으로 매운 곳. 매운 음식 먹고 스트레스 풀기 딱 좋고, 풍자누나도 이야기했지만 조미료 매운맛이 아니라 고추가루로 매운맛을 낸 곳임. 다음날 뒤지게 힘들지만, 한 번 쯤 먹어볼만 함.
7. 뚝불 - 행복담 : 여긴 인스타갬성, 데이트 하기 괜찮은 집. 크림으로 만든 순대국 or 뚝불을 먹을 수 있음. 크림의 정체를 알 수는 없지만 허연 크림이 뚝배기 위에 잔뜩 쌓여있음. 이게 대체 뭘까... 하고 한입 뜨면 "와...씨..." 이렇게 됨. 크림이 콩 갈아서 만든 것 같은데, 진짜 너무 맛있음. 근데 진짜 최고는 위에 나열한 6곳이 최고임.
8. 순대국 - 가마솥토종순대국 : 장덕동에 있음. 청양고추 넣고 새우젓으로 간 맞춰서 먹으면 얼큰짭짤 최고. 순대도 퀄리티 좋고, 점심 저녁 안 가리고 사람들 웨이팅 걸려있는 순대국 최애집. 종종 퇴근길에 포장해서 집에서 술안주하면 눈물이 쥬르륵....
9. 청국장 - 진미청국장 : 산정동 광주여자대학교 후문쪽에 있음. 허름하디 허름한 곳이고, 내부 인테리어 보면 러시아 시골 와있는 느낌이지만, 맛은 보장함. 버섯청국장 추천. 비빔밥 나오고, 거기에 청국장 넣고 박박 비벼 먹으면 잠 자다가도 생각남.
10. 추어탕 - 뚝방추어탕 : 풍영정천 뚝방에 있음. 7천원이라는 아주 싼 가격인데, 맛이 된장 베이스라 정말 추어탕 안 먹는 사람들도 잘 먹음. 입에 걸리는 게 없을 정도로 부드럽고, 음식 나오기 전에 밑반찬이 좋음. 열무잎에 갈치속젓 밥 얹혀서 먹으면 추어탕 나오기 전에 한 공기 금방 먹음. 여기도 웨이팅 엄청 길고 (특히 어른들) 회전율이 빨라서 아주 괜찮음. 그리고 여기 동태탕도 진짜 맛있음. 생강향이 은은하게 나고, 가격도 혜자.
[root@rac1 dev]# ls -al sd* brw-rw----. 1 root disk 8, 0 6월 8 16:11 sda brw-rw----. 1 root disk 8, 1 6월 8 16:11 sda1 brw-rw----. 1 root disk 8, 2 6월 8 16:11 sda2 brw-rw----. 1 root disk 8, 16 6월 8 16:11 sdb brw-rw----. 1 root disk 8, 17 6월 8 16:11 sdb1 brw-rw----. 1 root disk 8, 32 6월 8 16:11 sdc brw-rw----. 1 root disk 8, 33 6월 8 16:11 sdc1 brw-rw----. 1 root disk 8, 48 6월 8 16:11 sdd brw-rw----. 1 root disk 8, 49 6월 8 16:11 sdd1 brw-rw----. 1 root disk 8, 64 6월 8 16:11 sde brw-rw----. 1 root disk 8, 65 6월 8 16:11 sde1 brw-rw----. 1 root disk 8, 80 6월 8 16:11 sdf brw-rw----. 1 root disk 8, 81 6월 8 16:11 sdf1 brw-rw----. 1 root disk 8, 96 6월 8 16:11 sdg brw-rw----. 1 root disk 8, 97 6월 8 16:11 sdg1 brw-rw----. 1 root disk 8, 112 6월 8 16:16 sdh brw-rw----. 1 root disk 8, 113 6월 8 17:19 sdh1 brw-rw----. 1 root disk 8, 128 6월 8 16:11 sdi brw-rw----. 1 root disk 8, 144 6월 8 16:11 sdj brw-rw----. 1 root disk 8, 160 6월 8 16:11 sdk brw-rw----. 1 root disk 8, 176 6월 8 16:11 sdl brw-rw----. 1 root disk 8, 192 6월 8 16:11 sdm
-- 파티션 생성 [root@rac1 dev]# fdisk /dev/sdi Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0x1763f317.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): p Partition number (1-4, default 1): First sector (2048-204799, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799): Using default value 204799 Partition 1 of type Linux and of size 99 MiB is set
Command (m for help): w The partition table has been altered!
Calling ioctl() to re-read partition table. Syncing disks. [root@rac1 dev]# fdisk /dev/sdj Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0x697410e9.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): Using default response p Partition number (1-4, default 1): First sector (2048-204799, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799): Using default value 204799 Partition 1 of type Linux and of size 99 MiB is set
Command (m for help): w The partition table has been altered!
Calling ioctl() to re-read partition table. Syncing disks. [root@rac1 dev]# fdisk /dev/sdk Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0x0dde1be5.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): Using default response p Partition number (1-4, default 1): First sector (2048-204799, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799): Using default value 204799 Partition 1 of type Linux and of size 99 MiB is set
Command (m for help): w The partition table has been altered!
Calling ioctl() to re-read partition table. Syncing disks. [root@rac1 dev]# fdisk /dev/sdl Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0xd45bb1c8.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): Using default response p Partition number (1-4, default 1): First sector (2048-204799, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799): Using default value 204799 Partition 1 of type Linux and of size 99 MiB is set
Command (m for help): w The partition table has been altered!
Calling ioctl() to re-read partition table. Syncing disks. [root@rac1 dev]# fdisk /dev/sdm Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0x57f6bcbe.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): Using default response p Partition number (1-4, default 1): First sector (2048-204799, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799): Using default value 204799 Partition 1 of type Linux and of size 99 MiB is set
Command (m for help): w The partition table has been altered!
-- disk group 생성 SQL> create diskgroup asmdata external redundancy disk '/dev/oracleasm/disks/ASM01' ;
Diskgroup created.
-- DISK 추가 SQL> create diskgroup asmdata external redundancy disk '/dev/oracleasm/disks/ASM01' ;
Diskgroup created.
SQL> alter diskgroup asmdata add disk '/dev/oracleasm/disks/ASM02' REBALANCE POWER 5;
Diskgroup altered.
SQL> alter diskgroup asmdata add disk '/dev/oracleasm/disks/ASM03' REBALANCE POWER 5;
Diskgroup altered.
alter diskgroup asmdata add disk '/dev/oracleasm/disks/ASM02' REBALANCE POWER 5; ※ REBALANCE POWER : 0부터 1024까지 설정 할 수 있으며 숫자가 높을수록 리밸런스 속도가 빨라지지만
I/O 가 늘어남으로 시스템 상태 사용률이 낮거나 업무시간 이후에 높이는 것 추천!!
SQL> col path for a30 SQL> col name for a14 SQL> select group_number, disk_number, name, mount_status, path, total_mb, free_mb 2 from v$asm_disk 3 order by 1,3;
