Hello All,
I have a situation problem, I have been asked to move database because the performance degrading. Here is the situation:
"Because of DBA error, an important database was created with a 2k block size, and as it is physically on a SAN with an 8k write chunk, we are dealing with unnecessary performance implications.
Please prepare a detailed, technical (how you plan to do it) _and_ logistical (what you plan to do, in what order, when and how long you think it would take) project plan for our conversion of our database from a 2k block size to an 8k block size.
For estimation purposes, a full database export of this database takes 12 hours, and we estimate a full database import would take about two to three times as long because of index re-creation time. Assume our cost of down-time is $10,000 per hour and as a result it's very important to come up with methods of reducing downtime. Needless to say, 36 hours of downtime, at a cost of $360,000 is completely out of the question. Obviously, with such a high cost of downtime, we have properly resourced the environment in terms of disk space on the SAN. As well, we have a spare server available if necessary to further reduce the downtime. "
[highlight #CC0000]Here are my steps to include in the project plan.[/highlight]
1) Assuming the space server (SS) will be the new home the changed database.
2) Backup database on Current Server (CS)
3) Install Oracle 11g software on the Spare Server (SS) SAN with an 8k write chunk.
4) In current Server (CS), use a prep_script that will collect info and re-create user connections, passwords, tablespaces, system roles and privileges. See prep_script.sql that will create create_users.sql.
5) Collect tablespace information (file directories, sizes etc) on CS.
6) Create a dumb empty shell database on SS with the same name and file structure as database on CS with 2k-block size.
7) Ask the System Administrator (SA) to mount SS so that it is visible as a file system on CS.
8) SHUTDOWN IMMEDIATE the database on CS during the change window.
9) SHUTDOWN IMMEDIATE the database on SS during the change window.
10) Sftp (or scopy or parallel Create Table As Select) the datafiles, online redo logs, and control files from the CS server to the SS, keeping the file directories placements the same.
11) Run create_users.sql
12) STARTUP database on the SS.
Please advise on steps I might be missing. For large production database that cannot afford prolonged downtime especially which OPTION to take in step 10.
I have a situation problem, I have been asked to move database because the performance degrading. Here is the situation:
"Because of DBA error, an important database was created with a 2k block size, and as it is physically on a SAN with an 8k write chunk, we are dealing with unnecessary performance implications.
Please prepare a detailed, technical (how you plan to do it) _and_ logistical (what you plan to do, in what order, when and how long you think it would take) project plan for our conversion of our database from a 2k block size to an 8k block size.
For estimation purposes, a full database export of this database takes 12 hours, and we estimate a full database import would take about two to three times as long because of index re-creation time. Assume our cost of down-time is $10,000 per hour and as a result it's very important to come up with methods of reducing downtime. Needless to say, 36 hours of downtime, at a cost of $360,000 is completely out of the question. Obviously, with such a high cost of downtime, we have properly resourced the environment in terms of disk space on the SAN. As well, we have a spare server available if necessary to further reduce the downtime. "
[highlight #CC0000]Here are my steps to include in the project plan.[/highlight]
1) Assuming the space server (SS) will be the new home the changed database.
2) Backup database on Current Server (CS)
3) Install Oracle 11g software on the Spare Server (SS) SAN with an 8k write chunk.
4) In current Server (CS), use a prep_script that will collect info and re-create user connections, passwords, tablespaces, system roles and privileges. See prep_script.sql that will create create_users.sql.
5) Collect tablespace information (file directories, sizes etc) on CS.
6) Create a dumb empty shell database on SS with the same name and file structure as database on CS with 2k-block size.
7) Ask the System Administrator (SA) to mount SS so that it is visible as a file system on CS.
8) SHUTDOWN IMMEDIATE the database on CS during the change window.
9) SHUTDOWN IMMEDIATE the database on SS during the change window.
10) Sftp (or scopy or parallel Create Table As Select) the datafiles, online redo logs, and control files from the CS server to the SS, keeping the file directories placements the same.
11) Run create_users.sql
12) STARTUP database on the SS.
Please advise on steps I might be missing. For large production database that cannot afford prolonged downtime especially which OPTION to take in step 10.