RDBMS version: 11.2.0.4
OS : RHEL 7.4
Due to a miscommunication, our build team had created a prod DB with 4K block size. The DB is 1.5 Terabytes in size.
I need to migrate this DB from 4k block size to 8k block size .
I have 2 questions on this.
Question1.
expdp took 9 hours so, I assume import will take at least 20 hours. So, I don't want try expdp,impdp option now. So, I am thinking of running CTAS. ie. I will create a 8k tablespace and run the following during off-peak hours and in phased manner. Is it fine ? Can I move objects owned by SYS like this ?
StepA
ALTER SYSTEM SET DB_4K_CACHE_SIZE=512M SCOPE=spfile;
StepB.
create tablespace tbs_app8k datafile '+DATA' size 30g
blocksize 8K;
StepC. grant space quota on tbs_app8k tablespace to the user
StepE. Run CTAS for all tables
create table xyz_bkp
tablespace tbs_app8k
as
select * from xyz;
StepE. Now drop the original tables and rename the _BKP tables to the original table names
StepF. Create constraints and indexes.
Question2. I cannot do a full RMAN backup and restore in this scenario because the restored database will end up with 4K block size. Right ?