I was checking the users in my unix box. Some old users where created by the previous administrator. When i tried to delete from the smc it was throwing error, “The account is mandatory, cant be deleted”.
So, There is a workaround.
1. remove the entry from the passwd file # vi /etc/passwd
2. #pwconv
3. cat /etc/shadow
As far as I have recently read, Oracle doesn’t allow you to set some physical features of sub-partitions such as compression clause. I have been mistaken that composite partitioned tables can not be compressed at all. However in data warehouse environments, a general requirement is being able to compress segments of historic partitions of RANGE + HASH partitioned tables. I have tried a few scenarios and finally found a solution. Yes, Oracle allows it.
Naïve Approaches
One approach can be to compress all subsegments by trying recreating partition with COMPRESS clause in parallel. However this approach will not work:
ALTER TABLE X MOVE PARTITION 2007JAN NOLOGGING COMPRESS PARALLEL 16;
You will immediately face with ORA-14257: cannot move partition other than a Range or Hash partition exception. This means Oracle can not recreate all subpartition segments at a time.
Another approach is to try recreating compressed subpartition segments by using COMPRESS option one by one:
ALTER TABLE X MOVE SUBPARTITION 2007JAN_01 NOLOGGING COMPRESS PARALLEL 16;
This time you will come up with ORA-14160: this physical attribute may not be specified for a table subpartition. That’s because Oracle doesn’t allow you to set some physical attributes of subpartitions. NOLOGGING and COMPRESS are two of those attributes. Those attributes inherit from partition of subpartition.
Solution
Change the physical attribute at partition level
The first thing you have to do is to change physical attribute (compression attribute) of the partition which owns the subpartition by using MODIFY clause:
ALTER TABLE X MODIFY PARTITION 2007JAN COMPRESS;
This statement will modify partition parameter in such a way that all new data blocks created by direct path inserts will be compressed. Remember that the table has composite partitioning. This means subpartition segment blocks are the compressed blocks.
Recreate subpartition segments
“ALTER TABLE…” statement that we have used above affects only the blocks that are created after the statement has been issued. In order to compress existing subpartition blocks, we need to recreate them:
ALTER TABLE X MOVE SUBPARTITION 2007JAN_01 PARALLEL 16;
ALTER TABLE X MOVE SUBPARTITION 2007JAN_02 PARALLEL 16;
ALTER TABLE X MOVE SUBPARTITION 2007JAN_03 PARALLEL 16;
ALTER TABLE X MOVE SUBPARTITION 2007JAN_04 PARALLEL 16;
All these four subpartition segments will be recreated in compressed form within the tablespace(s) in which they have been currently residing. That is because they inherit the compression attribute from their parents (partition of subpartitions).
Check *_SEGMENTS view to see the effect
As you execute the following command you will see that your segments have already been compressed:
SELECT partition_name, bytes / 1024 / 1024 / 1024 gigabytes
FROM user_segments
WHERE segment_name = ‘X’;
In order to test this scenario I have used one of the summary tables of our data warehouse. Table is RANGE+HASH partitioned and one of these partitions is ~85 GB before compression and ~22 GB after all sixteen subpartitions have been compressed.
Conclusion
Remember that Oracle unfolds, considering that the term is correct, the compressed blocks if you update the data in compressed ones, which decreases the benefit you obtain by using compression. So Oracle advises using compression option with read only tablespaces to guarantee the gain.
we had to recover one of our development databases from old backup.
As part of recovery process, our restore went fine and also were able to re-create controlfile. During recovery, it asked for Archive logs. We checked with our Unix team for required archivelogs and found out they don’t have required archive logs.
It was critical for us to recover database because of some project deadline.
Error:
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 9867098396261 generated at 03/21/2008 13:37:44 needed for
thread 1
ORA-00289: suggestion : /arcredo/XSCLFY/log1_648355446_2093.arc
ORA-00280: change 9867098396261 for thread 1 is in sequence #2093
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’
After doing some research, I found out one hidden parameter (_ALLOW_RESETLOGS_CORRUPTION=TRUE) will allow us to open database even though it’s not properly recovered.
We forced open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE. It allows us to open database but instance crashed immediately after open. I checked the alert.log file and found out we have undo tablespace corruption.
Alert log shows below error
Errors in file /u01/XSCLFYDB/admin/XSCLFY/udump/xsclfy_ora_9225.trc:
ORA-00600: internal error code, arguments: [4194], [17], [9], [], [], [], [], []
Tue Mar 25 12:45:55 2008
Errors in file /u01/XSCLFYDB/admin/XSCLFY/bdump/xsclfy_smon_24975.trc:
ORA-00600: internal error code, arguments: [4193], [53085], [50433], [], [], [], [], []
Doing block recovery for file 433 block 13525
Block recovery from logseq 2, block 31 to scn 9867098416340
To resolve undo corruption issue, I changed undo_management to “Manual” in init.ora. Now it allowed us to open database successfully. Once database was up and running, I created new undo tablespace and dropped old corrupted undo tablespace. I changed back the undo_management to “Auto” and undo_tablespace to “NewUndoTablespace”.
It resolved our issue and database was up and running without any issue.
_ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated.
As per Oracle Metalink, there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all. Before you try this option, ensure that you have a good and valid backup of the current database.Solution:
1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) Recover database
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”
9) Bounce database.