Real test did not work, here the error:
SQL> startup
ORA-01081: impossible de lancer ORACLE deja en cours - fermer d'abord le thread
SQL> shutdown immediate
ORA-01507: base de donnees non montee
Instance ORACLE arrÛtÚe.
SQL> startup
ORA-03113: fin de fichier sur canal de communication
SQL> conn SYS as SYSDBA
Entrez le mot de passe :
ConnectÚ Ó une instance inactive.
SQL> startup
Instance ORACLE lancÚe.
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
ORA-00209: discordance de taille de bloc pour le fichier de controle;
consultez le jounal des alertes
TESTS
C:\Documents and Settings\will>tnsping orcl9i
TNS Ping Utility for 32-bit Windows: Version 9.0.1.1.1 - Production on 16-JAN-20
10 02:53:36
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Fichiers de paramÞtres utilisÚs :
C:\oracle\ora90\network\admin\sqlnet.ora
C:\oracle\ora90\network\admin\tnsnames.ora
Adaptateur TNSNAMES utilisÚ pour la rÚsolution de l'alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL9I)))
OK (30 msec)
C:\Documents and Settings\will>lsnrctl services orcl9i
LSNRCTL for 32-bit Windows: Version 9.0.1.1.1 - Production on 16-JAN-2010 03:17:
44
Copyright (c) 1991, 2001, Oracle Corporation. All rights reserved.
Connexion Ó (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=will)(PORT=1521)))
RÚcapitulatif services...
Le service "ORCL9i" comporte 1 instance(s).
L'instance "ORCL9i", statut BLOCKED, comporte 1 gestionnaire(s) pour ce servic
e...
Gestionnaire(s) :
"DEDICATED" Útabli : 0 refusÚ : 0 statut : ready
LOCAL SERVER
Le service "PLSExtProc" comporte 1 instance(s).
L'instance "PLSExtProc", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce se
rvice...
Gestionnaire(s) :
"DEDICATED" Útabli : 0 refusÚ : 0
LOCAL SERVER
La commande a rÚussi
samedi 16 janvier 2010
vendredi 15 janvier 2010
Oracle Ressuscitado
Oracle 9i : 9.0.1.1
My computer with oracle server is not working anymore, but I have the all the files and I'm able to create the same installation structure. Can I recover it? Find out in the end!
LOCAL TEST, real test will be done soon:
0 - installed oracle 9i
1 - Started the services, created the user, set default tablespace, stopped the services
2- backed up the files
3 - Started the services, set temporary tablespace to the user.
4 - created two tables with relationships
5 - created one row for each table and commited
6 - run select statement
7 - stopped the services
8 -copied backed up files to the local files.
9 - restarted all services
well it works but the thing is: al objects become part of the system tablespace.
1 - IDENTIFYING OBJECTS
see if the objects exist, using SYSTEM schema:
select * from all_objects where object_name='EMPRESA' or object_name='PESSOA'
we see owner and created date. TO see the indexes or other objects, try this:
select * from all_objects where created to_date('15-JAN-2010','DD-MON-YYYY')
It doesnt work for me. I have to check it.
For now, try this: select * from all_objects where owner='SYSTEM' order by CREATED DESC
2 - TABLES MUST BE MOVED:
before: see the rownum
select ROWID, a.* from system.EMPRESA a
AAAH2AAABAAAPCDAAA
select ROWID, a.* from system.PESSOA a
AAAH2CAABAAAPCJAAA
alter table system.EMPRESA MOVE TABLESPACE USERS;
alter table system.PESSOA MOVE TABLESPACE USERS;
check results:
select * from all_tables where table_name='PESSOA'
indexes are still in system and now UNUSABLE:
select * from all_indexes where index_name='SYS_C002715'
Moving:
ALTER INDEX system.SYS_C002715 REBUILD TABLESPACE INDX;
Status valid, but one problem:
http://www.dba-oracle.com/t_change_table_schema_owner.htm
The data and objects are in right place, but the owner is not. Unless you import/export. At least, the data and objects are usable!
Recovering complete!
My computer with oracle server is not working anymore, but I have the all the files and I'm able to create the same installation structure. Can I recover it? Find out in the end!
LOCAL TEST, real test will be done soon:
0 - installed oracle 9i
1 - Started the services, created the user, set default tablespace, stopped the services
2- backed up the files
3 - Started the services, set temporary tablespace to the user.
4 - created two tables with relationships
5 - created one row for each table and commited
6 - run select statement
7 - stopped the services
8 -copied backed up files to the local files.
9 - restarted all services
well it works but the thing is: al objects become part of the system tablespace.
1 - IDENTIFYING OBJECTS
see if the objects exist, using SYSTEM schema:
select * from all_objects where object_name='EMPRESA' or object_name='PESSOA'
we see owner and created date. TO see the indexes or other objects, try this:
select * from all_objects where created to_date('15-JAN-2010','DD-MON-YYYY')
It doesnt work for me. I have to check it.
For now, try this: select * from all_objects where owner='SYSTEM' order by CREATED DESC
2 - TABLES MUST BE MOVED:
before: see the rownum
select ROWID, a.* from system.EMPRESA a
AAAH2AAABAAAPCDAAA
select ROWID, a.* from system.PESSOA a
AAAH2CAABAAAPCJAAA
alter table system.EMPRESA MOVE TABLESPACE USERS;
alter table system.PESSOA MOVE TABLESPACE USERS;
check results:
select * from all_tables where table_name='PESSOA'
indexes are still in system and now UNUSABLE:
select * from all_indexes where index_name='SYS_C002715'
Moving:
ALTER INDEX system.SYS_C002715 REBUILD TABLESPACE INDX;
Status valid, but one problem:
http://www.dba-oracle.com/t_change_table_schema_owner.htm
The data and objects are in right place, but the owner is not. Unless you import/export. At least, the data and objects are usable!
One thing to remember is that ‘MOVE TABLESPACE’ does not work if table contains column with LONG or LONG RAW data type. You will run into ‘ORA-00997: illegal use of LONG datatype’ error. Such tables can be moved to new tablespace using exp/imp command.
Recovering complete!
dimanche 3 janvier 2010
Readaptar oracle backup
Hi ,
Its Great Pleasure to share this Automation of Oracle Cold Backup as a Scheduled job to run as per the requirement of the DBA for Windows Platform.
Oracle 8.1.7 DB Cold Backup on Win2000.
To Automate a Coldbackup of a DB implement the following steps.
===============================================================
Require 3 files.
Backup.bat;shutdown.sql;startup.sql the contents of each files is given below.
Save the files with Proper extension as mentioned above.
Also Place them in a Proper directory... as in this case c:\
Check where are the oracle DB files located as Per your installation and mention them in Backup.bat file.
backup.bat file Content:
========================
echo Backing up DB in Cold Backup mode... >c:\Backup.log
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\shutdown.sql" >>c:\Backup.log
copy c:\oracle\oradata\orcl\*.* c:\backup >>c:\Backup.log
copy C:\oracle\admin\orcl\pfile\*.* c:\backup >>c:\Backup.log
copy C:\oracle\admin\DEVDB\bdump\*.* c:\backup >>c:\Backup.log
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\startup.sql" >>c:\Backup.log
Backup.log file is created and all the execution steps in .bat file are traced to it.
Shutdown.sql Content
====================
connect internal/password
shutdown immediate
exit
Startup.sql Content
===================
connect internal/password
startup pfile=C:\oracle\ora81\database\initorcl.ora
exit
Place all this file in C:\ or D:\ wherever you like.
Now go to Start==>Settings==>Control Panel==>Scheduled Tasks==>Add Scheduled Task==>Open(double click)==>A Scheduled Task Wizard is Opened==>Click Next==>Browse==>Choose the Batfile ==>Now Plan how you want the Backup Daily,Weekly as per your requirement==>It will ask for the System Administrator Pwd twice==>If you want open the Advance option to have a Look.
The Scheduled Task for Cold Backup is finished.
Ensure the destination Cold Backup has enough space.
The above coldbackup still can be re written as per the following requirements:
1)Also we can include Compress the Backed up files after copying
2)Delete the old copies (say not more then 3 days old backup)
3)Cold back up first to disk and then Moving it to Tape drives etc
In coming day Hot Backup Automation etc will be posted in the same Thread.
To Automate the Analyze for a User in the Prod DB as a Nightly Job.
===================================================================
To do a Nightly Analyze of Schema in a Production DB.
Require 2 files.
ANALYZE_SYS.bat & Analyze.sql
Save the files with Proper extension as mentioned above.
Also Place them in a Proper directory... as in this case c:\
ANALYZE_SYS.bat
===============
echo Analyzing SYS Schema... >c:\Analyze.log
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\analyze.sql" >>c:\Analyze.log
Analyze.sql
===========
connect internal/password@opsdb
execute DBMS_UTILITY.ANALYZE_SCHEMA('SYS','COMPUTE')
exit
The Analyze.log will trace all the Scheduled Job as Trace file.
For any Job to be Automated after Placing the files as said in the above cases.Scheduling is done as follows
===================================================================
Now go to Start==>Settings==>Control Panel==>Scheduled Tasks==>Add Scheduled Task==>Open(double click)==>A Scheduled Task Wizard is Opened==>Click Next==>Browse==>Choose the Batfile (backup.bat or ANALYZE_SYS.bat) ==>Now Plan how you want the Backup or Analyze Daily,Weekly as per your requirement==>It will ask for the System Administrator Pwd twice==>If you want open the Advance option to have a Look.
Its Great Pleasure to share this Automation of Oracle Cold Backup as a Scheduled job to run as per the requirement of the DBA for Windows Platform.
Oracle 8.1.7 DB Cold Backup on Win2000.
To Automate a Coldbackup of a DB implement the following steps.
===============================================================
Require 3 files.
Backup.bat;shutdown.sql;startup.sql the contents of each files is given below.
Save the files with Proper extension as mentioned above.
Also Place them in a Proper directory... as in this case c:\
Check where are the oracle DB files located as Per your installation and mention them in Backup.bat file.
backup.bat file Content:
========================
echo Backing up DB in Cold Backup mode... >c:\Backup.log
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\shutdown.sql" >>c:\Backup.log
copy c:\oracle\oradata\orcl\*.* c:\backup >>c:\Backup.log
copy C:\oracle\admin\orcl\pfile\*.* c:\backup >>c:\Backup.log
copy C:\oracle\admin\DEVDB\bdump\*.* c:\backup >>c:\Backup.log
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\startup.sql" >>c:\Backup.log
Backup.log file is created and all the execution steps in .bat file are traced to it.
Shutdown.sql Content
====================
connect internal/password
shutdown immediate
exit
Startup.sql Content
===================
connect internal/password
startup pfile=C:\oracle\ora81\database\initorcl.ora
exit
Place all this file in C:\ or D:\ wherever you like.
Now go to Start==>Settings==>Control Panel==>Scheduled Tasks==>Add Scheduled Task==>Open(double click)==>A Scheduled Task Wizard is Opened==>Click Next==>Browse==>Choose the Batfile ==>Now Plan how you want the Backup Daily,Weekly as per your requirement==>It will ask for the System Administrator Pwd twice==>If you want open the Advance option to have a Look.
The Scheduled Task for Cold Backup is finished.
Ensure the destination Cold Backup has enough space.
The above coldbackup still can be re written as per the following requirements:
1)Also we can include Compress the Backed up files after copying
2)Delete the old copies (say not more then 3 days old backup)
3)Cold back up first to disk and then Moving it to Tape drives etc
In coming day Hot Backup Automation etc will be posted in the same Thread.
To Automate the Analyze for a User in the Prod DB as a Nightly Job.
===================================================================
To do a Nightly Analyze of Schema in a Production DB.
Require 2 files.
ANALYZE_SYS.bat & Analyze.sql
Save the files with Proper extension as mentioned above.
Also Place them in a Proper directory... as in this case c:\
ANALYZE_SYS.bat
===============
echo Analyzing SYS Schema... >c:\Analyze.log
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\analyze.sql" >>c:\Analyze.log
Analyze.sql
===========
connect internal/password@opsdb
execute DBMS_UTILITY.ANALYZE_SCHEMA('SYS','COMPUTE')
exit
The Analyze.log will trace all the Scheduled Job as Trace file.
For any Job to be Automated after Placing the files as said in the above cases.Scheduling is done as follows
===================================================================
Now go to Start==>Settings==>Control Panel==>Scheduled Tasks==>Add Scheduled Task==>Open(double click)==>A Scheduled Task Wizard is Opened==>Click Next==>Browse==>Choose the Batfile (backup.bat or ANALYZE_SYS.bat) ==>Now Plan how you want the Backup or Analyze Daily,Weekly as per your requirement==>It will ask for the System Administrator Pwd twice==>If you want open the Advance option to have a Look.
Errors
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
Essa mensagem aparece quando o banco de dados esta shutdown. Poderia ser outra mensagem.
Essa mensagem aparece quando o banco de dados esta shutdown. Poderia ser outra mensagem.
Views
SQL> desc all_views
Name Null? Type
----------------------------------------- -------- ---------------
OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
SQL> set long 1000000
select text from all_views where view_name='YOURVIEW'
Name Null? Type
----------------------------------------- -------- ---------------
OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
SQL> set long 1000000
select text from all_views where view_name='YOURVIEW'
Inscription à :
Commentaires (Atom)
