setp 1. Before increase it, notice the column RECORDS_TOTAL of REDO LOG and DATAFILE, 16 and 100 before increament.
SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
DATABASE 316 1 1 0
0 0
CKPT PROGRESS 8180 11 0 0
0 0
REDO THREAD 256 8 1 0
0 0
REDO LOG 72 16 16 0
0 12
DATAFILE 428 100 5 0
0 36
FILENAME 524 2298 44 0
0 0
TABLESPACE 68 100 6 0
0 1
TEMPORARY FILENAME 56 100 1 0
0 1
RMAN CONFIGURATION 1108 50 1 0
0 1
LOG HISTORY 56 292 42 1
42 42
OFFLINE RANGE 200 163 0 0
0 0
ARCHIVED LOG 584 308 49 1
49 49
BACKUP SET 40 409 7 1
7 7
BACKUP PIECE 736 200 7 1
7 7
BACKUP DATAFILE 116 282 6 1
6 6
BACKUP REDOLOG 76 215 10 1
10 10
DATAFILE COPY 660 223 16 1
16 16
BACKUP CORRUPTION 44 371 0 0
0 0
COPY CORRUPTION 40 409 0 0
0 0
DELETED OBJECT 20 818 10 1
10 10
PROXY COPY 852 249 0 0
0 0
BACKUP SPFILE 36 454 2 1
2 2
DATABASE INCARNATION 56 292 5 1
5 5
FLASHBACK LOG 84 2048 35 0
0 0
RECOVERY DESTINATION 180 1 1 0
0 0
INSTANCE SPACE RESERVATION 28 1055 1 0
0 0
REMOVABLE RECOVERY FILES 32 1000 7 0
0 0
RMAN STATUS 116 141 40 1
40 40
THREAD INSTANCE NAME MAPPING 80 8 8 0
0 0
MTTR 100 8 1 0
0 0
DATAFILE HISTORY 568 57 0 0
0 0
STANDBY DATABASE MATRIX 400 10 10 0
0 0
GUARANTEED RESTORE POINT 212 2048 0 0
0 0
RESTORE POINT 212 2083 0 0
0 0
34 rows selected.
step 2: generate trace file
SQL> alter database backup controlfile to trace as '/tmp/cntl_recreate.trc' noresetlogs;
Database altered.
[oracle@localhost ~]$ cat /tmp/cntl_recreate.trc
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=OCP10G_%t_%s_%r.arc
--
-- DB_UNIQUE_NAME="OCP10G"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/apps/oracle/oraarch/OCP10G'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OCP10G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/apps/oracle/oradata/OCP10G/redo01.log' SIZE 50M,
GROUP 2 '/apps/oracle/oradata/OCP10G/redo02.log' SIZE 50M,
GROUP 3 '/apps/oracle/oradata/OCP10G/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/apps/oracle/oradata/OCP10G/system01.dbf',
'/apps/oracle/oradata/OCP10G/undotbs01.dbf',
'/apps/oracle/oradata/OCP10G/sysaux01.dbf',
'/apps/oracle/oradata/OCP10G/users01.dbf',
'/apps/oracle/oradata/OCP10G/example01.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651015758.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651853690.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651854542.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651856491.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651862296.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/oracle/oradata/OCP10G/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
step 3: prepare the sql modified from trace file
--my comments
--STARTUP NOMOUNT
--remove below line among "CREATE CONTROLFILE". it seems doesn't support remarks within the CREATE statement
cp ./cntrl_receate.sql cntrl_receate_new.sql
[oracle@localhost create-cntrl]$ cat cntrl_receate_new.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=OCP10G_%t_%s_%r.arc
--
-- DB_UNIQUE_NAME="OCP10G"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/apps/oracle/oraarch/OCP10G'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
--my comments
--STARTUP NOMOUNT
--remove below line among "CREATE CONTROLFILE". it seems doesn't support remarks within the CREATE statement
-- "-- STANDBY LOGFILE"
CREATE CONTROLFILE REUSE DATABASE "OCP10G" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/apps/oracle/oradata/OCP10G/redo01.log' SIZE 50M,
GROUP 2 '/apps/oracle/oradata/OCP10G/redo02.log' SIZE 50M,
GROUP 3 '/apps/oracle/oradata/OCP10G/redo03.log' SIZE 50M
DATAFILE
'/apps/oracle/oradata/OCP10G/system01.dbf',
'/apps/oracle/oradata/OCP10G/undotbs01.dbf',
'/apps/oracle/oradata/OCP10G/sysaux01.dbf',
'/apps/oracle/oradata/OCP10G/users01.dbf',
'/apps/oracle/oradata/OCP10G/example01.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651015758.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651853690.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651854542.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651856491.arc';
-- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651862296.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/oracle/oradata/OCP10G/temp01.dbf'
SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
step 4: shutdown current database
step 5: backup existing control file
[oracle@localhost oradata]$ cd OCP10G
[oracle@localhost OCP10G]$ mkdir cntrl_bak
[oracle@localhost OCP10G]$ ls con*
control01.ctl control02.ctl control03.ctl
[oracle@localhost OCP10G]$ mv con*.ctl ./cntrl_bak/
[oracle@localhost OCP10G]$ ls*.ctl ./cntrl_bak/
bash: ls*.ctl: command not found
[oracle@localhost OCP10G]$ ls *.ctl ./cntrl_bak/
ls: *.ctl: No such file or directory
./cntrl_bak/:
control01.ctl control02.ctl control03.ctl
step 6: startup nomount
SQL> spool recreate_cntrl.log
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 2073088 bytes
Variable Size 872418816 bytes
Database Buffers 704643072 bytes
Redo Buffers 14700544 bytes
step 7: invoke the scripts
SQL> @cntrl_recreate_new.sql
SQL> -- The following are current System-scope REDO Log Archival related
SQL> -- parameters and can be included in the database initialization file.
SQL> --
SQL> -- LOG_ARCHIVE_DEST=''
SQL> -- LOG_ARCHIVE_DUPLEX_DEST=''
SQL> --
SQL> -- LOG_ARCHIVE_FORMAT=OCP10G_%t_%s_%r.arc
SQL> --
SQL> -- DB_UNIQUE_NAME="OCP10G"
SQL> --
SQL> -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
SQL> -- LOG_ARCHIVE_MAX_PROCESSES=2
SQL> -- STANDBY_FILE_MANAGEMENT=MANUAL
SQL> -- STANDBY_ARCHIVE_DEST=?/dbs/arch
SQL> -- FAL_CLIENT=''
SQL> -- FAL_SERVER=''
SQL> --
SQL> -- LOG_ARCHIVE_DEST_1='LOCATION=/apps/oracle/oraarch/OCP10G'
SQL> -- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
SQL> -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
SQL> -- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
SQL> -- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
SQL> -- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
SQL> -- LOG_ARCHIVE_DEST_STATE_1=ENABLE
SQL>
SQL> --
SQL> -- The following commands will create a new control file and use it
SQL> -- to open the database.
SQL> -- Data used by Recovery Manager will be lost.
SQL> -- Additional logs may be required for media recovery of offline
SQL> -- Use this only if the current versions of all online logs are
SQL> -- available.
SQL>
SQL> -- After mounting the created controlfile, the following SQL
SQL> -- statement will place the database in the appropriate
SQL> -- protection mode:
SQL> -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
SQL>
SQL> --STARTUP NOMOUNT
SQL> CREATE CONTROLFILE REUSE DATABASE "OCP10G" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 32
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1000
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/apps/oracle/oradata/OCP10G/redo01.log' SIZE 50M,
9 GROUP 2 '/apps/oracle/oradata/OCP10G/redo02.log' SIZE 50M,
10 GROUP 3 '/apps/oracle/oradata/OCP10G/redo03.log' SIZE 50M
11 DATAFILE
12 '/apps/oracle/oradata/OCP10G/system01.dbf',
13 '/apps/oracle/oradata/OCP10G/undotbs01.dbf',
14 '/apps/oracle/oradata/OCP10G/sysaux01.dbf',
15 '/apps/oracle/oradata/OCP10G/users01.dbf',
16 '/apps/oracle/oradata/OCP10G/example01.dbf'
17 CHARACTER SET WE8ISO8859P1
18 ;
Control file created.
SQL>
SQL> -- Configure RMAN configuration record 1
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1');
PL/SQL procedure successfully completed.
SQL> -- Commands to re-create incarnation table
SQL> -- Below log names MUST be changed to existing filenames on
SQL> -- disk. Any one log file from each branch can be used to
SQL> -- re-create incarnation records.
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651015758.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651853690.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651854542.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651856491.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/apps/oracle/oraarch/OCP10G/OCP10G_1_1_651862296.arc';
SQL> -- Recovery is required if any of the datafiles are restored backups,
SQL> -- or if the last shutdown was not normal or immediate.
SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL>
SQL> -- All logs need archiving and a log switch is needed.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL>
SQL> -- Database can now be opened normally.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>
SQL> -- Commands to add tempfiles to temporary tablespaces.
SQL> -- Online tempfiles have complete space information.
SQL> -- Other tempfiles may require adjustment.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/oracle/oradata/OCP10G/temp01.dbf'
2 SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Tablespace altered.
SQL> -- End of tempfile additions.
SQL> --
step 8: verify the result of REDO LOG and DATAFILE
SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
---------------------------- ----------- ------------- ------------ -----------
LAST_INDEX LAST_RECID
---------- ----------
DATABASE 316 1 1 0
0 0
CKPT PROGRESS 8180 11 0 0
0 0
REDO THREAD 256 8 1 0
0 0
REDO LOG 72 32 3 0
0 0
DATAFILE 428 1000 5 0
0 0
FILENAME 524 4146 9 0
0 0
TABLESPACE 68 1000 6 0
0 1
TEMPORARY FILENAME 56 1000 1 0
0 1
RMAN CONFIGURATION 1108 50 1 0
0 1
LOG HISTORY 56 292 1 1
1 1
OFFLINE RANGE 200 1063 0 0
0 0
ARCHIVED LOG 584 308 3 1
3 3
BACKUP SET 40 1227 0 0
0 0
BACKUP PIECE 736 1000 0 0
0 0
BACKUP DATAFILE 116 1128 0 0
0 0
BACKUP REDOLOG 76 215 0 0
0 0
DATAFILE COPY 660 1016 0 0
0 0
BACKUP CORRUPTION 44 1115 0 0
0 0
COPY CORRUPTION 40 1227 0 0
0 0
DELETED OBJECT 20 818 0 0
0 0
PROXY COPY 852 1017 0 0
0 0
BACKUP SPFILE 36 454 0 0
0 0
DATABASE INCARNATION 56 292 1 1
1 1
FLASHBACK LOG 84 2048 0 0
0 0
RECOVERY DESTINATION 180 1 1 0
0 0
INSTANCE SPACE RESERVATION 28 1055 0 0
0 0
REMOVABLE RECOVERY FILES 32 1000 0 0
0 0
RMAN STATUS 116 141 0 0
0 0
THREAD INSTANCE NAME MAPPING 80 8 8 0
0 0
MTTR 100 8 1 0
0 0
DATAFILE HISTORY 568 57 0 0
0 0
STANDBY DATABASE MATRIX 400 10 10 0
0 0
GUARANTEED RESTORE POINT 212 2048 0 0
0 0
RESTORE POINT 212 2083 0 0
0 0
34 rows selected.
SQL> spool off
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 2073088 bytes
Variable Size 872418816 bytes
Database Buffers 704643072 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
step 9: note that the size of controlfile is creaased
[oracle@localhost OCP10G]$ ls -lrt
total 1625676
drwxrwxr-x 2 oracle oracle 4096 Oct 28 13:57 cntrl_bak
-rw-r----- 1 oracle oracle 52429312 Oct 28 14:03 redo02.log
-rw-r----- 1 oracle oracle 52429312 Oct 28 14:03 redo01.log
-rw-r----- 1 oracle oracle 30416896 Oct 28 14:03 temp01.dbf
-rw-rw---- 1 oracle oracle 5251072 Oct 28 14:04 users01.dbf
-rw-r----- 1 oracle oracle 492838912 Oct 28 14:04 undotbs01.dbf
-rw-r----- 1 oracle oracle 524296192 Oct 28 14:04 system01.dbf
-rw-r----- 1 oracle oracle 304095232 Oct 28 14:04 sysaux01.dbf
-rw-r----- 1 oracle oracle 52429312 Oct 28 14:04 redo03.log
-rw-r----- 1 oracle oracle 104865792 Oct 28 14:04 example01.dbf
-rw-rw---- 1 oracle oracle 14630912 Oct 28 14:04 control03.ctl
-rw-rw---- 1 oracle oracle 14630912 Oct 28 14:04 control02.ctl
-rw-rw---- 1 oracle oracle 14630912 Oct 28 14:04 control01.ctl
[oracle@localhost OCP10G]$ ll -l cntrl_bak/
total 21696
-rw-rw---- 1 oracle oracle 7389184 Oct 28 13:56 control01.ctl
-rw-rw---- 1 oracle oracle 7389184 Oct 28 13:56 control02.ctl
-rw-rw---- 1 oracle oracle 7389184 Oct 28 13:56 control03.ctl
--fallback
In case of fallback, just
a). shutdown abort the instance
b). cp over the backed control files and startup again
Tuesday, October 28, 2008
Tuesday, October 21, 2008
Runing advisor without voilate license agreement.
I like the advisors introudced in 10g, but company always tries to cut cost which affects buying the tuning package license. Luckily there is back door, i.e we can call them from command line. Below is the illustration of using segment advisor.
googled and get the sample template from http://www.oracle-base.com/articles/10g/SpaceObjectTransactionManagement10g.php
cat call_segment_advisor.sql
DECLARE
l_object_id NUMBER;
BEGIN
-- Create a segment advisor task for the SCOTT.EMP table.
-- update your own owner, table name (case-sensitive)
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'EMP_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For EMP');
DBMS_ADVISOR.create_object (
task_name => 'EMP_SEGMENT_ADVISOR',
object_type => 'TABLE',
attr1 => 'DBAM1',
attr2 => 'M1_OUTBOUND_USAGE',
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => 'EMP_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => 'EMP_SEGMENT_ADVISOR');
END;
/
-- Create a segment advisor task for the USERS tablespace.
-- DBMS_ADVISOR.create_task (
-- advisor_name => 'Segment Advisor',
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- task_desc => 'Segment Advisor For USERS');
-- DBMS_ADVISOR.create_object (
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- object_type => 'TABLESPACE',
-- attr1 => 'USERS',
-- attr2 => NULL,
-- attr3 => NULL,
-- attr4 => 'null',
-- attr5 => NULL,
-- object_id => l_object_id);
-- DBMS_ADVISOR.set_task_parameter (
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- parameter => 'RECOMMEND_ALL',
-- value => 'TRUE');
-- DBMS_ADVISOR.execute_task(task_name => 'USERS_SEGMENT_ADVISOR');
--END;
--/
-- Display the findings.
SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40
SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR')
ORDER BY f.task_name, f.impact DESC;
select message from dba_advisor_findings f
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR') ;
select more_info from dba_advisor_findings f
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR') ;
exec dbms_advisor.delete_task('EMP_SEGMENT_ADVISOR');
SQL> @call_segment_advisor.sql
PL/SQL procedure successfully completed.
TASK_NAME IMPACT OBJECT_TYPE SCHEMA OBJECT_NAME MESSAGE MORE_INFO
-------------------- ---------- -------------------- -------------------- ------------------------------ ---------------------------------------- ----------------------------------------
EMP_SEGMENT_ADVISOR TABLE DBAM1 M1_OUTBOUND_USAGE The free space in the object is less tha Allocated Space:15398338560: Used Space:
n the size of the last extent. 14503648178: Reclaimable Space :89469038
2:
SQL> select message from dba_advisor_findings f
2 WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR');
MESSAGE
----------------------------------------
The free space in the object is less tha
n the size of the last extent.
SQL> select more_info from dba_advisor_findings f WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR');
MORE_INFO
----------------------------------------
Allocated Space:15398338560: Used Space:
14503648178: Reclaimable Space :89469038
2:
:
SQL> select more_info FROM dba_advisor_findings f WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR6');
MORE_INFO
----------------------------------------
Allocated Space:21937386023: Used Space:
21863850510: Reclaimable Space :73535513
:
--delete the task if no longer needed, thus the script can be re-used easily without changing task name
SQL> exec dbms_advisor.delete_task('EMP_SEGMENT_ADVISOR2');
PL/SQL procedure successfully completed.
Read more the Oracle document "Oracle® Database PL/SQL Packages and Types Reference", DBMS_ADVISOR allows us to call all seven
BTW, the speed is surpising with ASSM. For above case, regardless the table size is about 21Gb, it finishes within 5 seconds.
Cheers!
googled and get the sample template from http://www.oracle-base.com/articles/10g/SpaceObjectTransactionManagement10g.php
cat call_segment_advisor.sql
DECLARE
l_object_id NUMBER;
BEGIN
-- Create a segment advisor task for the SCOTT.EMP table.
-- update your own owner, table name (case-sensitive)
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'EMP_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For EMP');
DBMS_ADVISOR.create_object (
task_name => 'EMP_SEGMENT_ADVISOR',
object_type => 'TABLE',
attr1 => 'DBAM1',
attr2 => 'M1_OUTBOUND_USAGE',
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => 'EMP_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => 'EMP_SEGMENT_ADVISOR');
END;
/
-- Create a segment advisor task for the USERS tablespace.
-- DBMS_ADVISOR.create_task (
-- advisor_name => 'Segment Advisor',
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- task_desc => 'Segment Advisor For USERS');
-- DBMS_ADVISOR.create_object (
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- object_type => 'TABLESPACE',
-- attr1 => 'USERS',
-- attr2 => NULL,
-- attr3 => NULL,
-- attr4 => 'null',
-- attr5 => NULL,
-- object_id => l_object_id);
-- DBMS_ADVISOR.set_task_parameter (
-- task_name => 'USERS_SEGMENT_ADVISOR',
-- parameter => 'RECOMMEND_ALL',
-- value => 'TRUE');
-- DBMS_ADVISOR.execute_task(task_name => 'USERS_SEGMENT_ADVISOR');
--END;
--/
-- Display the findings.
SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40
SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR')
ORDER BY f.task_name, f.impact DESC;
select message from dba_advisor_findings f
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR') ;
select more_info from dba_advisor_findings f
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR') ;
exec dbms_advisor.delete_task('EMP_SEGMENT_ADVISOR');
SQL> @call_segment_advisor.sql
PL/SQL procedure successfully completed.
TASK_NAME IMPACT OBJECT_TYPE SCHEMA OBJECT_NAME MESSAGE MORE_INFO
-------------------- ---------- -------------------- -------------------- ------------------------------ ---------------------------------------- ----------------------------------------
EMP_SEGMENT_ADVISOR TABLE DBAM1 M1_OUTBOUND_USAGE The free space in the object is less tha Allocated Space:15398338560: Used Space:
n the size of the last extent. 14503648178: Reclaimable Space :89469038
2:
SQL> select message from dba_advisor_findings f
2 WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR');
MESSAGE
----------------------------------------
The free space in the object is less tha
n the size of the last extent.
SQL> select more_info from dba_advisor_findings f WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR');
MORE_INFO
----------------------------------------
Allocated Space:15398338560: Used Space:
14503648178: Reclaimable Space :89469038
2:
:
SQL> select more_info FROM dba_advisor_findings f WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR6');
MORE_INFO
----------------------------------------
Allocated Space:21937386023: Used Space:
21863850510: Reclaimable Space :73535513
:
--delete the task if no longer needed, thus the script can be re-used easily without changing task name
SQL> exec dbms_advisor.delete_task('EMP_SEGMENT_ADVISOR2');
PL/SQL procedure successfully completed.
Read more the Oracle document "Oracle® Database PL/SQL Packages and Types Reference", DBMS_ADVISOR allows us to call all seven
advisors defined in the view DBA_ADVISOR_DEFINITIONS.BTW, the speed is surpising with ASSM. For above case, regardless the table size is about 21Gb, it finishes within 5 seconds.
Cheers!
Subscribe to:
Posts (Atom)
