But I would like to test it how it is working.
1. Install the 11gR2 64-bits, as I explained here
2. Upgrade your database, here below a FSCM9 database (or create from scratch - I did not tested it yet)
The upgrade is quite standard, start the db with old version and run the 11.2 check utility :
@/apps/oracle/product/11.2.0/rdbms/admin/utlu112i.sql
Then start your db in upgrade mode, and run the upgrade script (with 11.2 binaries) :
@$ORACLE_HOME/rdbms/admin/catupgrd.sql
After restarting the db in normal mode, check it afterwards :
@$ORACLE_HOME/rdbms/admin/utlu112s.sql
There is an additional script to run :
@$ORACLE_HOME/rdbms/admin/catuppst.sql
And finally, recompile the objects :
@$ORACLE_HOME/rdbms/admin/utlrp.sql
3. Because 11gR2 64-bits is not coming with 32-bits libraries, as I wrote here, you'll need to install a 32-client into a new Oracle home for the application and batch server clients.
That is fine, nothing much to say about the client install, check the runtime option :
4. Create the required symbolic link $ORACLE_HOME/lib/libclntsh.so.9.0 pointing to $ORACLE_HOME/lib/libclntsh.so.11.1 (assuming ORACLE_HOME is
your Oracle client home path directory). Do not forget to configure the tnsnames.ora as well.
5. Change the ORACLE_HOME env variable to the Oracle client home pathdirectory of the Peoplesoft Unix account, and reconfigure the
application and batch domains.
Finally every should run fine.
One very interesting feature of 11gR2 for Peoplesoft is the possibility to deferred the segment creation, it will be very nice to
avoid segment creation for thousands of unused and empty tables., I should also have a try to rebuild the tables without segments...
Enjoy,
Nicolas.
3 comments:
Hey Nicolas, I was wondering if you could do some testing on the deferred allocation?
I don't have access to Linux at the moment to load 11gr2 on.
It's this:
- given the deferred segment allocation, I'm thinking: instead of a huge export/import to bring a large schema into it, what if we can change the table segment allocation via a simple ALTER TABLE MOVE?
Getting a PL/SQL procedure to do it in the background would be child's play.
The manual seems to imply we can:
"When you issue an ALTER TABLE ... MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement."
And the syntax for ALTER TABLE also allows for the SEGMENT clause to be specified.
So, rather than exporting/importing an entire database to get rid of the initial segment(s), we may be able to do it via MOVE with/without ALTER TABLE?
Needs to be tested and I don't have access to the required gear.
Are you game to do it and publish the results? I'm sure other Peoplesoft folks would love to know the results.
Hi Nuno,
Yes, that was my plan to test it out.
However, I have some issue with this clause "SEGMENT CREATION DEFERRED" especially combined with ALTER TABLE command.
See here below :
SQL> create table mytable (id number);
Table created.
SQL> select bytes from user_segments where segment_name='MYTABLE';
no rows selected
Ok, fine, that is expected, now I MOVE the table :
SQL> alter table mytable move;
Table altered.
SQL> select bytes from user_segments where segment_name='MYTABLE';
BYTES
----------
65536
Hey, wait, that should not create the segment according to my table creation statement, right ?
Then here we enter into dark side, cannot figure out how put this propery back and remove the created segment (which is what we all want for the thousands of empty table on a Peoplesoft db) :
SQL> alter table mytable segment creation deferred;
alter table mytable segment creation deferred
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
SQL> alter table mytable move segment creation deferred;
alter table mytable move segment creation deferred
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
If someone got it, I would be happy.
Thanks,
Nicolas.
That would be really nice, here the results of space of a demo FSCM9 :
SQL> select segment_type, count(*) "NbSegments", trunc(sum(bytes)/1024/1024) "SizeMb"
2 from user_segments
3 where segment_type in ('TABLE','INDEX')
4 and segment_name not like 'BIN%'
5 group by segment_type;
TABLE 54010 15437
INDEX 62230 63497
SQL> set serveroutput on
SQL> declare
2 v_count number;
3 v_nb_empty_tbl number:=0;
4 v_size_empty_tbl number:=0;
5 v_nb_empty_idx number:=0;
6 v_size_empty_idx number:=0;
7 v_bytes number;
8 begin
9 for tbl in (select table_name from user_tables) loop
10 begin
11 execute immediate 'select count(*) from '||tbl.table_name||' where rownum=1' into v_count;
12 if v_count=0 then
13 begin
14 select bytes
15 into v_bytes
16 from user_segments
17 where segment_type='TABLE'
18 and segment_name =tbl.table_name;
19 exception when no_data_found then v_bytes:=0;
20 end;
21 v_nb_empty_tbl:=v_nb_empty_tbl+1;
22 v_size_empty_tbl:=v_size_empty_tbl+v_bytes;
23 for idx in (select index_name from user_indexes where table_name=tbl.table_name) loop
24 begin
25 select bytes
26 into v_bytes
27 from user_segments
28 where segment_name=idx.index_name
29 and segment_type='INDEX';
30 exception when no_data_found then v_bytes:=0;
31 end;
32 v_nb_empty_idx:=v_nb_empty_idx+1;
33 v_size_empty_idx:=v_size_empty_idx+v_bytes;
34 end loop;
35 end if;
36 exception when others then dbms_output.put_line('ERROR : '||tbl.table_name||' --> ORA'||sqlcode||': '||sqlerrm);
37 end;
38 end loop;
39 dbms_output.put_line('Nb empty tables => '||to_char(v_nb_empty_tbl)||' -- Size (Mb) => '||to_char(trunc(v_size_empty_tbl/1024/1024)));
40 dbms_output.put_line('Nb empty indexes => '||to_char(v_nb_empty_idx)||' -- Size (Mb) => '||to_char(trunc(v_size_empty_idx/1024/1024)));
41 end;
42 /
Nb empty tables => 47405 -- Size (Mb) => 12360
Nb empty indexes => 54788 -- Size (Mb) => 53508
PL/SQL procedure successfully completed.
SQL>
Conclusion : we could save 65.8Gb over a database of 78.8Gb, wouldn't it be nice ?
Post a Comment