-- 유저 별 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;
[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;
SQL> create user asmtest identified by asmtest default tablespace asmtbs temporary tablespace temp; create user asmtest identified by asmtest default tablespace asmtbs temporary tablespace temp * ERROR at line 1: ORA-65096: invalid common user or role name
SQL> set line 200 SQL> col path for a50 SQL> select group_number, mount_status, path, total_mb 2 from v$asm_disk where mount_status='CLOSED'; . GROUP_NUMBER MOUNT_STATUS PATH TOTAL_MB ------------ --------------------- -------------------------------------------------- ---------- 0 CLOSED /dev/oracleasm/disks/CRS4 0
SQL> COL LABEL FOR A10 SQL> COL STATE FOR A10 SQL> SELECT A.NAME AS DISK_GROUP, D.NAME "LABEL", A.STATE 2 FROM V$ASM_DISK D, V$ASM_DISKGROUP A 3 WHERE D.GROUP_NUMBER=A.GROUP_NUMBER 4 ORDER BY 2;
DISK_GROUP LABEL STATE ------------------------------------------------------------------------------------------ ---------- ---------- CRS CRS_0000 MOUNTED CRS CRS_0001 MOUNTED CRS CRS_0002 MOUNTED DATA DATA2 MOUNTED DATA DATA_0000 MOUNTED FRA FRA_0000 MOUNTED
6 rows selected.
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;
SQL> ALTER DISKGROUP CRS ADD DISK '/dev/oracleasm/disks/CRS4' REBALANCE POWER 5; ALTER DISKGROUP CRS ADD DISK '/dev/oracleasm/disks/CRS4' REBALANCE POWER 5 * ERROR at line 1: ORA-15032: not all alterations performed ORA-15260: permission denied on ASM disk group
SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [+ASM1:/home/oracle]> sqlplus / as sysasm
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 8 17:22:00 2023 Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
SQL> ALTER DISKGROUP CRS ADD DISK '/dev/oracleasm/disks/CRS4' REBALANCE POWER 5;
DISK_GROUP LABEL STATE ---------- ---------- ---------- CRS CRS_0000 MOUNTED CRS CRS_0001 MOUNTED CRS CRS_0002 MOUNTED CRS CRS_0003 MOUNTED DATA DATA2 MOUNTED DATA DATA_0000 MOUNTED FRA FRA_0000 MOUNTED
7 rows selected.
-- ASM DISKGROUP 세부 정보 확인 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; GROUP_NUMBER DISK_NUMBER NAME MOUNT_STATUS PATH TOTAL_MB FREE_MB ------------ ----------- -------------- --------------------- ------------------------------ ---------- ---------- 1 0 CRS_0000 CACHED /dev/oracleasm/disks/CRS1 1020 760 1 1 CRS_0001 CACHED /dev/oracleasm/disks/CRS2 1020 764 1 2 CRS_0002 CACHED /dev/oracleasm/disks/CRS3 1020 760 1 3 CRS_0003 CACHED /dev/oracleasm/disks/CRS4 1020 816 2 1 DATA2 CACHED /dev/oracleasm/disks/DATA2 50996 47884 2 0 DATA_0000 CACHED /dev/oracleasm/disks/DATA 30716 28820 3 0 FRA_0000 CACHED /dev/oracleasm/disks/FRA 20476 19084
7 rows selected.
-- CRS_003 삭제
SQL> alter diskgroup crs drop disk CRS_0003;
-- ASM DISKGROUP 세부 정보 확인 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;