Scenario 7 – Recovery read only tablespace from loss of control files


Vəziyyət:

  • Bütün kontrol fayllar silinmişdir
  • Kontrol faylımızın yaranma scripti var
  • Tablespce-lərdən biri read only-dir

.

İlk olaraq bir tablespace yaradırıq , only read only edirik, kontrol faylın backup-nı alırıq  və bütün kontrol faylları silirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> create tablespace tbs_test datafile '/u01/app/oracle/oradata/DB11G/test_tbs.dbf' size 5m ;

Tablespace created.

SQL> alter tablespace tbs_test read only;

Tablespace altered.

SQL> alter database backup controlfile to trace as '/home/oracle/backups/ctl_backup.dat';

Database altered.

SQL> !rm  /home/oracle/backups/controlfiles/*.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more inf

[/pcsh]

ctl_backup.dat faylından istifadə edərək kontrol faylı yaradırıq və DB-nı işə salmağa cəh edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  ARCHIVELOG

2      MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log'  SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log'  SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

'/u01/app/oracle/oradata/DB11G/system01.dbf',

'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',

'/u01/app/oracle/oradata/DB11G/users2_01.dbf',

'/home/oracle/backups/fortest/tbs1.dbf',

'/home/oracle/backups/fortest/tbs2.dbf',

'/home/oracle/backups/fortest/users02.dbf',

'/home/oracle/backups/fortest/users03.dbf',

'/u01/app/oracle/product/11.2/db_1/dbs/undotbs13.dbf'

CHARACTER SET AL32UTF8;  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

Control file created.

[/pcsh]

Və biz görürük ki kontrol faylın yaranma skriptində read only tablespace barədə məlumat yoxdur.

[pcsh lang=”applescript” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> startup force;

ORACLE instance started.

::::::::::::::output trimmed::::::::::::::

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/DB11G/system01.dbf'

SQL>

[/pcsh]

Data faylların adını sorğuladıqda görürük ki tbs_test readonly tablespacesinin datafaylları barədə məlumat yoxdur:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::

8 rows selected.

[/pcsh]

DB-nı recover etməyə cəhd edirik və redo məlumatları əlavə etmək üçün redo log-ları təqdim edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;
ORA-00279: change 13911238 generated at 05/09/2015 10:57:10 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13911238 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/DB11G/test_tbs.dbf'
ORA-01112: media recovery not started

[/pcsh]

Recovery başlamır, çünki oracle itmiş read only datafaylı UNNAMED00004 kimi qəbul etdiyindən redolog-lar əlavə oluna bilmir. Ona görədə biz UNNAMED00004 faylının adını onun orijinal adına dəyişirik

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004
::::::::::::::output trimmed::::::::::::::
9 rows selected.


SQL> alter database rename file '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004' to '/u01/app/oracle/oradata/DB11G/test_tbs.dbf';

Database altered.

[/pcsh]

İndi recovery davam edə bilər , çünki datafayl öz düzgün adına rename olundu. DB-ni recover edib bazanı açıqırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13916733 generated at 05/09/2015 12:55:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13916733 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL>select tbs.name,dt.name  from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;

NAME       NAME
-----------    - -------------------------------------------------

SYSTEM       /u01/app/oracle/oradata/DB11G/system01.dbf

SYSAUX       /u01/app/oracle/oradata/DB11G/sysaux01.dbf

TBS_TEST       /u01/app/oracle/oradata/DB11G/test_tbs.dbf

::::::::::::::output trimmed::::::::::::::


9 rows selected.

[/pcsh]Situation:

  • All control files are lost
  • You have a creation script of the control file
  • One of the tablespaces is read only

.

First, create tablespace and make it read only, then take backup of the control file and delete all available control files.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> create tablespace tbs_test datafile '/u01/app/oracle/oradata/DB11G/test_tbs.dbf' size 5m ;

Tablespace created.

SQL> alter tablespace tbs_test read only;

Tablespace altered.

SQL> alter database backup controlfile to trace as '/home/oracle/backups/ctl_backup.dat';

Database altered.

SQL> !rm  /home/oracle/backups/controlfiles/*.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more inf

[/pcsh]

Create control file using ctl_backup.dat file and try opening the database:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  ARCHIVELOG

2      MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log'  SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log'  SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

'/u01/app/oracle/oradata/DB11G/system01.dbf',

'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',

'/u01/app/oracle/oradata/DB11G/users2_01.dbf',

'/home/oracle/backups/fortest/tbs1.dbf',

'/home/oracle/backups/fortest/tbs2.dbf',

'/home/oracle/backups/fortest/users02.dbf',

'/home/oracle/backups/fortest/users03.dbf',

'/u01/app/oracle/product/11.2/db_1/dbs/undotbs13.dbf'

CHARACTER SET AL32UTF8;  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20

Control file created.

[/pcsh]

And we saw that there is not information about readonly datafile on this script.

[pcsh lang=”applescript” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> startup force;

ORACLE instance started.

::::::::::::::output trimmed::::::::::::::

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/DB11G/system01.dbf'

SQL>

[/pcsh]

Select the name  of datafiles and you say it is no information about tbs_test readonly datafile:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::

8 rows selected.

[/pcsh]

Try to recover the database and provide the redo log file to applying redo entries:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;
ORA-00279: change 13911238 generated at 05/09/2015 10:57:10 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13911238 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/DB11G/test_tbs.dbf'
ORA-01112: media recovery not started

[/pcsh]

Recovery hasn’t started because it applied information about missing read only datafile which is missing since Oracle accept it as UNNAMED00004.

Select name of datafiles, now you can see UNNAMED00004 file, which is the our missing read only datafile and rename the datafile:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004
::::::::::::::output trimmed::::::::::::::
9 rows selected.


SQL> alter database rename file '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004' to '/u01/app/oracle/oradata/DB11G/test_tbs.dbf';

Database altered.

[/pcsh]

Now recovery will continue because the datafile has been renamed to its  correct name. Recover database and open it:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13916733 generated at 05/09/2015 12:55:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13916733 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL>select tbs.name,dt.name  from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;

NAME       NAME
-----------    - -------------------------------------------------

SYSTEM       /u01/app/oracle/oradata/DB11G/system01.dbf

SYSAUX       /u01/app/oracle/oradata/DB11G/sysaux01.dbf

TBS_TEST       /u01/app/oracle/oradata/DB11G/test_tbs.dbf

::::::::::::::output trimmed::::::::::::::


9 rows selected.

[/pcsh]

Scenario 6 – Recover from lost of a member of Multiplexed Control File


Vəziyyət:

  • Kontrol fayllar multipleks olunmuşdur
  • Kontrol fayllardan biri silinmişdir

Bu halda kontrol fayllar multipleks olunduğu üçün yenidən kontrol fayl yaratmağa ehtiyac qalmır. Silinmiş kontrol faylları bərpa etmək üçün sadəcə  bazanı söndürüb, qaydasında olan kontrol faylı silinmiş faylın yerinə kopylayıb adını silinmiş faylın adına dəyişirik. Gəlin bunu test edək:

İlk olaraq bütün kontrol fayllarımızın adını sorğulayırıq, daha sonra onlardan birini silib DB-nı reboot edirik.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> !rm /home/oracle/backups/controlfiles/control01.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Daha sonra qaydasında olan kontrol faylı silinmiş faylın yerinə kopyalayıb adını dəyişirik və bazanı işə salırıq.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ cp /home/oracle/backups/controlfiles/control02.ctl /home/oracle/backups/contolfiles/control01.ctl

SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

Database mounted.

Database opened.

[/pcsh]Situation:

  • Control files multiplexed
  • One of the controlfiles is lost

If you have a multiplexed control file, there is no need to create a new control file. Instead, only shut down the database and copy the available control file to the directory of the missed one. Let’s test it:

Select name of all control files, delete one of them and reboot the database.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> !rm /home/oracle/backups/controlfiles/control01.ctl

SQL> startup force;

ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Copy available control file to the directory of the missed control file and open the database:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ cp /home/oracle/backups/controlfiles/control02.ctl /home/oracle/backups/contolfiles/control01.ctl

SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

Database mounted.

Database opened.

[/pcsh]

Scenario 5 – Recovering from loss of all control files using binary backup control file


Recovering from loss of all control files using backup control

Vəziyyət:

  • Bütün kontrol fayllar silinmişdir
  • Kontrol faylın binary backupı var

.

Bu vəziyyəti yoxlamaq üçün ilk olaraq kontrol faylın binary backup-nı alırıq və bazanı söndürürük. DB-nı mount vəziyyətinə gətirmək istədikdə error ilə qarşılaşırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> alter database backup controlfile to '/home/oracle/backups/ctl_binary_bckp.ctl';

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> host
[oracle@orcl ~]$ cd /home/oracle/backups/controlfiles/
[oracle@orcl controlfiles]$ rm *.ctl
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Bu vəziyyətdən çıxış üçün binary backup-ı silinmiş kontrol faylların yerinə adını silinmiş faylların adına dəyişərək kopyalayır və bazanı mount edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cd /home/oracle/backups/
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl
[oracle@orcl backups]$ sqlplus / as sysdba

SQL> alter database mount;
Database altered.

[/pcsh]

Recover database using backup control file komandasından istifadə edərək bazanı binary backup-dan recover etməyə cəhd edək:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL>

[/pcsh]

Burada oracle bizə deyirki kontrol faylin backup-ı alındıqdan sonra edilmish deyishiklikler teqdim edilmelidir. Yuxarıda error bizə dəyishiklik olunmush redolog faylin adını ve scn nomresini teqdim edir. Spesifik scn-den 13877247  sonra deyishikler olunmush faylı dəqiq tapmaq üçün biz  log və archivelog  faylların yerləşdiyi view-ni sorğulayırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select group#,status,first_change# from v$log;

GROUP# STATUS    FIRST_CHANGE#
----- --------  -------------
	1 CURRENT 	13877246

	3 INACTIVE 	13847806

	2 INACTIVE 	3847403s

[/pcsh]

Yuxarıdakı nəticədən görünür ki, 2 və 3 inactive olduğu üçün 1-ci qrupda olan dəyişikliklər əlavə olunmalıdır. Ona görədə biz yeniden recovery emrini işə salırıq və ona parametr olaraq redo log faylını /u01/app/oracle/oradata/DB11G/redo01.log veririk.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.

 

SQL> alter database open resetlogs;
Database altered.

[/pcsh]Situation:

  • All control files are lost
  • You have binary backup of control file

.

At first lets take a binary backup of control files and shutdown force database. When you attempt to mount the database, you encounter with error:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> alter database backup controlfile to '/home/oracle/backups/ctl_binary_bckp.ctl';

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/backups/controlfiles/control01.ctl
/home/oracle/backups/controlfiles/control02.ctl
/home/oracle/backups/controlfiles/control03.ctl

SQL> host
[oracle@orcl ~]$ cd /home/oracle/backups/controlfiles/
[oracle@orcl controlfiles]$ rm *.ctl
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

[/pcsh]

Then copy binary backup of control file to te original control file’s destination and rename to the original control file name. Then mount the database:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cd /home/oracle/backups/
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl
[oracle@orcl backups]$ cp ctl_binary_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl
[oracle@orcl backups]$ sqlplus / as sysdba

SQL> alter database mount;
Database altered.

[/pcsh]

You can use the recover database using backup control file command to tell Oracle that an attempt to recover the database from the binary copy of the control file:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL>

[/pcsh]

Changes after backup are not presented . To find a file that contains changes after specific scn 13877247 we query the v$log view

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select group#,status,first_change# from v$log;

GROUP# STATUS    FIRST_CHANGE#
----- --------  -------------
	1 CURRENT 	13877246

	3 INACTIVE 	13847806

	2 INACTIVE 	3847403s

[/pcsh]

From result above we see that because second and third group is inactive ve must apply first group redo file . That’s why retrie recovery command with  /u01/app/oracle/oradata/DB11G/redo01.log parameter.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> recover database using backup controlfile;

ORA-00279: change 13877247 generated at 05/07/2015 18:14:49 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 13877247 for thread 1 is in sequence #13
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.

 

SQL> alter database open resetlogs;
Database altered.

[/pcsh]

Scenario 4 – Recover from loss of the control files when no backup files are available


Vəziyyət:

  • Bütün kontrol fayllar silinmişdir
  • Kontrol Faylların backuplarıda həmçinin silinmişdir
.

Kontrol fayllar silinmişdir və kontrol faylların backup-ı yoxdur. Buna görə kontrol fayl create controlfile komandası vasitəsilə yenidən yaradılmalıdır. Bunu test etmək üçün  kontrol faylları silirik, kontrol fayl və redo log faylların yerləşdiyi yeri sorğulayedib nəticəni  saxlayırıq. Bu nəticə kontrol faylı yaradan zaman lazım olacaq. Daha sonra bazanı sondürürük. Bazanı mount etməyə cəhd etdikdə error baş verməlidir:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ rm *.ctl

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/redo03.log
/u01/app/oracle/oradata/DB11G/redo02.log
/u01/app/oracle/oradata/DB11G/redo01.log

SQL> shutdown;

Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup;

SQL> ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;
alter database mount

*

ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
SQL> Alter database backup controlfile to trace;
Alter database backup controlfile to trace
*
ERROR at line 1:
ORA-01507: database not mounted

[/pcsh]

Indi isə aşağıdakı qaydada create controlfile komandasından istifadə edərək kontrol faylı yaradırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cd /u01/app/oracle/oradata/DB11G/


[oracle@orcl DB11G]$ ls
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  users2_01.dbf

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292

LOGFILE
GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log'  SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log'  SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE
'/u01/app/oracle/oradata/DB11G/system01.dbf',
'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',
::::::::::::::output trimmed::::::::::::::
'/u01/app/oracle/oradata/DB11G/users2_01.dbf'
CHARACTER SET AL32UTF8; 
Control file created.
SQL> alter database open;

Database altered.

[/pcsh]Situation:

  • All control files have been lost
  • Backup of control files also lost
.

Control files lost and you have not backup of the control file. Therefore, you need to recreate the control file by the command create controlfile. To test this situation remove  control files, select name of datafiles, redo logs files and copy result somewhere. We will use this result when manually creating the control file. Then shut down the database.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ rm *.ctl

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/redo03.log
/u01/app/oracle/oradata/DB11G/redo02.log
/u01/app/oracle/oradata/DB11G/redo01.log

SQL> shutdown;

Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup;

SQL> ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;
alter database mount

*

ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
SQL> Alter database backup controlfile to trace;
Alter database backup controlfile to trace
*
ERROR at line 1:
ORA-01507: database not mounted

[/pcsh]

Now you must manually create a statement which, when run, creates a control file. To manually create, control you need the names of all of the data files in the database. You can construct a CREATE CONTROLFILE statement as shown above:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl controlfiles]$ cd /u01/app/oracle/oradata/DB11G/


[oracle@orcl DB11G]$ ls
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  users2_01.dbf

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292

LOGFILE
GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log'  SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log'  SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE
'/u01/app/oracle/oradata/DB11G/system01.dbf',
'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',
::::::::::::::output trimmed::::::::::::::
'/u01/app/oracle/oradata/DB11G/users2_01.dbf'
CHARACTER SET AL32UTF8; 
Control file created.
SQL> alter database open;

Database altered.

[/pcsh]

Scenario 3 – When control files, datafiles and Redo Logs are lost


Vəziyyət:

  •  Bütün kontrol, redo log və data fayllar silinmişdir
  • Archive log-lar qaydasındadır
  • Bazanın full backup-ı var
  • Baza archive log rejimindədir
  • Kontrol faylın backup-ı var

Bu halda biz bazanı recover edə bilərik, lakin son dəyişikliklər hansı ki online redo log-larda yerləşir, redo loglar silindiyi üçün bərpa edilə bilməyəcək (Buna görədə Oracle redlo logları müxtəlif  yerlərdə multipleks etməyi məsləhət görür).

1

Bu ssenarini test etmək üçün içində məlumat olan bir cədvəl yaradırıq:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> create table test_tbl as select * from all_tables;
SQL> commit;
SQL> select count(*) from test_tbl;

 COUNT(*)
----------
     2736

[/pcsh]

Bazanın bakup-nı alırıq

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> backup database;
Then insert a lot more data to the table
SQL> insert into test_tbl select * from all_tables;
2737 rows created.
SQL> /
SQL> select count(*) from test_tbl;
 COUNT(*)
----------
   52002

[/pcsh]

Bütün kontrol, redo log və  data faylları silirik .

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ cd  /u01/app/oracle/oradata/DB11G/ 
[oracle@orcl DB11G]$ ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@orcl DB11G]$ rm *
Then shut down darabase and restore control file from autobackup
SQL> shut abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
RMAN> restore controlfile from autobackup;
Then mount and recovery database
RMAN> alter database mount;
database mounted
RMAN> restore database;
RMAN> recover database;
::::::::::::::output trimmed::::::::::::::
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/05/2015 15:36:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18 and starting SCN of 13707555

[/pcsh]

Sonda redo log silindiyindən Oracle archived faylı tapa bilmədiyi  üçün  error baş verir. Daha sonra bazanı resetlogs parametri ilə open edirik:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> alter database open resetlogs;
database opened

[/pcsh]

Bərpadan sonra cədvəlimizdəki sətirlərin sayını sorğulayırıq və nəticədə görürük ki, biz yalnız arxiv loglar vasitəsilə yadda saxlaya bildiyimiz məlumatları restore ede bilmşik. Online redologlarda olan məlumatlar istə bərpa olunmamışdır.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select count(*) from test_tbl;
 COUNT(*)
----------
     2736

[/pcsh]Situation:

  • All control files, redo log files and data files have been lost
  • Archive logs are available
  • We have a full backup of this database
  • The database running in archive log mode
  • We have a backup of the control file

In this situation we can recover database but we cannot be able apply last changes that were in the online redo logs (That is why Oracle recommends multiplexing the redo log files to different hard drives.).

1

To test this recovery scenario first we create a table with data:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> create table test_tbl as select * from all_tables;
SQL> commit;
SQL> select count(*) from test_tbl;

 COUNT(*)
----------
     2736

[/pcsh]

Then backup the database

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> backup database;
Then insert a lot more data to the table
SQL> insert into test_tbl select * from all_tables;
2737 rows created.
SQL> /
SQL> select count(*) from test_tbl;
 COUNT(*)
----------
   52002

[/pcsh]

Then we remove all control files, data files and redo log files to crash the database.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ cd  /u01/app/oracle/oradata/DB11G/ 
[oracle@orcl DB11G]$ ls
control01.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@orcl DB11G]$ rm *
Then shut down darabase and restore control file from autobackup
SQL> shut abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
RMAN> restore controlfile from autobackup;
Then mount and recovery database
RMAN> alter database mount;
database mounted
RMAN> restore database;
RMAN> recover database;
::::::::::::::output trimmed::::::::::::::
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/05/2015 15:36:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18 and starting SCN of 13707555

[/pcsh]

Suddenly you get an error because oracle cannot find the archived file because redo log is deleted. Then open the database with resetlogs option:

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> alter database open resetlogs;
database opened

[/pcsh]

Select rowcount of the table, and you’ll see we could only recover data in archived logs. Data in online redo logs are lost.

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select count(*) from test_tbl;
 COUNT(*)
----------
     2736

[/pcsh]

Scenario 2 – Recovery when control files and data files are lost


Vəziyyət:

  • Bütün kontrol fayllar və Data fayllar silinmişdir
  • Redo loglar və archive logs yerindədir
  • Bazanın full backup-ı alınmışdır
  • Baza archive log rejimində işləyir
  • Kontrol faylın backup-ı alınmışdır

Remove all the control files to other directory which is equalivalent to loseing all of control files.

[oracle@orcl u01]$ rm /u01/app/oracle/oradata/DB11G/*.dbf *.ctl

Bazanı söndürüb nomount rejimində işə salırıq:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> shut abort

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

[/pcsh]

Kontrol faylı backup-dan geri qaytarıb, bazanı mount rejimində işə salırıq:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

using target database control file instead of recovery catalog

database mounted

[/pcsh]

Sonra bazanı restore və recovery edirik və bazanı resetlogs komandası ilə open edirik:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;
database opened

[/pcsh]Situation:

  • All control files and datafiles have been lost
  • Redo logs and archivelogs are available
  • We have full backup of this database
  • Database runing in archive log mode
  • We have a backup of control file

Remove all the control files:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl u01]$ rm /u01/app/oracle/oradata/DB11G/*.dbf *.ctl

[/pcsh]

Shutdown database in abort mode and startup it nomount mode

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> shut abort

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

[/pcsh]

To restore control file from backup and mount the database

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

using target database control file instead of recovery catalog

database mounted

[/pcsh]

Then, restore and recovery database

[pcsh lang=”applescript” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> restore database;

RMAN> recover database;

[/pcsh]

Complete recovery is done. Then open database with resetlogs and all is done.

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> alter database open resetlogs;
database opened

[/pcsh]

Scenario 1 – Restore control file from backup after loss of all control files


Vəziyyət:

  • Kontrol faylın bütün nüsxələri silinmişdir
  • Data fayllar və online redo loglar qaydasındadır
  • Kontrol faylımızın backup-ı var
  • Database archive log rejimində işləyir

İlk olaraq biz kontrol fayllarımızın harada yerləşdiyinə baxırıq

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/DB11G/control01.ctl

/u01/app/oracle/oradata/DB11G/control02.ctl

[/pcsh]

Daha sonra kontrol saylların itməsini simulasiya etmək üçün  bütün kontrol faylları başqa bir direktoriyaya köçürürük:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> !mv /u01/app/oracle/oradata/DB11G/*.ctl /u01/

[/pcsh]

Kontrol faylın silinməsini yoxlamaq üçün, kontrol fayldan gələn  bir məlumatı sorğulayırıq. Məlumat olmadığı üçün error baş verməlidir. Lakin bunu etməzdən oncə SQLplus-a yenidən qoşulmaq lazımdır, çünki əgər siz öncəki sessiyaya qoşula qalmısınızsa error baş verməyə bilər.

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> exit
[oracle@orcl]$ sqlplus / as sysdba

SQL> select * from v$database;

select * from v$database

*ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/DB11G/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

[/pcsh]

Artır bizim bütün kontrol fayllarımız itmişdir, indi bu vəziyyətdən geri qayıtmaq üçün aşağıdakı addımları etmək lazımdır.

Bazanı sondürüb nomount vəziyyətinə kimi işə salmaq lazımdır. Daha sonra kontrol fayllarımızı aoutobackup-dan geri qaytarırıq:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

RMAN> restore controlfile from autobackup;

:::::::::::::: output trimmed ::::::::::::::

output file name=/u01/app/oracle/oradata/DB11G/control01.ctl

output file name=/u01/app/oracle/oradata/DB11G/control02.ctl

Finished restore at 28-APR-15

Then bring the database mount state and recover the database

RMAN> alter database mount;

RMAN> recover database;

Issue resetlogs command  and  open database

RMAN> alter database open resetlogs;
database opened

[/pcsh]

https://www.youtube.com/watch?v=M9VrdfRaO38Situation:

  • All copies of control file have been lost
  • Data files and online redo logs are fine
  • We have a backup of the control file
  • Database is running in archive log mode

First, we look where is located our control files

[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/DB11G/control01.ctl

/u01/app/oracle/oradata/DB11G/control02.ctl

[/pcsh]

Then remove all the control files to another directory which is equivalent to losing all of the control files.

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> !mv /u01/app/oracle/oradata/DB11G/*.ctl /u01/

[/pcsh]

Make sure that an error occurs, access something which would only come from the control file. But firs reconnect to SQLplus , because errors may not be seen when still being connected to previous session.

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> exit
[oracle@orcl]$ sqlplus / as sysdba

SQL> select * from v$database;

select * from v$database

*ERROR at line 1:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/DB11G/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

[/pcsh]

So there are no more control files there.

For recovery follow the steps bellow:

Shut down database, then start the database in nomount state and restore the controlfile from autobackup:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::

RMAN> restore controlfile from autobackup;

:::::::::::::: output trimmed ::::::::::::::

output file name=/u01/app/oracle/oradata/DB11G/control01.ctl

output file name=/u01/app/oracle/oradata/DB11G/control02.ctl

Finished restore at 28-APR-15

[/pcsh]

Bring the database mount state and recover the database. Then issue resetlogs command  and  open database:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

RMAN> alter database mount;

RMAN> recover database;

RMAN> alter database open resetlogs;
database opened

[/pcsh]

https://www.youtube.com/watch?v=M9VrdfRaO38

How to create OCR mirror diskgroup and OCR backup


İlk olaraq Virtualbox-da 2 ədəd disk yaradırıq. Bunun üçün VirtualBoxManager-i açıb diski əlavə edəcəyimiz nod-lardan birini seçirik, daha sonra Settings->Storage->Create new disk seçirik:

Image

Hadrd drive tipi kimi vdi(virtual box image) seçirik

vdi

Növbəti pəncərədə fixed size seçirik və next vuraraq davam edirik
fixed
Yaradacağımız diskin adını, yerləşəcəyi yeri və diskin olçüsünü təyin edib, create düyməsini sıxaraq diskimizi yaradırıq
path
disc1
Bu qayda ilə ikinci diskimizidə yaradırıq
d2
Daha sonra disklərimizi paylaşıla bilən etmək üçün File->Virtual Media Manager-ə daxil oluruq
sharable
Bu pəncərədə yuxarıda yaratdığımız diskləri bir bir seçib modify düyməsini sıxmaqla açılan pəncərədən Shareable seçməklə disklərimizi paylaşıla bilən edirik.
sh
Daha sonra 2-ci nod-a bu shared diskləri əlavə edirik storage bölməsindən əlavə edirik
Daha sonra hər iki nodumuzu start edirik
root user-nin adından aşağıdakı komandaları icra edərək təzə əlavə olunmuş disklərimizi görürük

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 ~]# cd /dev
[root@node1 dev]# ls sd*
sda  sda1  sda2   sdh  sdi

[/pcsh]

Daha sonra Fdisk əmrindən istifadə edərək sdhsdi disklərini partition-lara ayırırıq
Hər iki disk üçün aşağıdakı arcıllığı icra etmək lazımdır: 

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

fdisk/dev/sdh
n
p
1
1
Enter
w

[/pcsh]

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 dev]# fdisk /dev/sdh
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xa67a97e6.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-522, default 1): 1
Last cylinder, +cylinders or +size{K,M,G} (1-522, default 522):
Using default value 522

Command (m for help): w
The partition table has been altered!

[/pcsh]

Bütün disklər müvəffəqiyyətlə partitionlara ayrıldıqdan sonra  /dev direktoriyasına daxil olub ls komandası ilə aşağıdakı nəticəni  almalıyıq:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 dev]# ls sd*
sda sda1 sda2 sdh sdh1 sdi sdi1

[/pcsh]

Aşağıdakı əmri yeni disklərimiz üçün işlədib onlarin SCSI ID-lərini götürüb 99-oracle-asmdevices.rules faylının içinə aşağıdakı kimi yazırıq (hər iki nod-da)

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 dev]# /sbin/scsi_id -g -u -d /dev/sdi1
1ATA_VBOX_HARDDISK_VBec9d247a-6a947164
[root@node1 dev]# /sbin/scsi_id -g -u -d /dev/sdh1
1ATA_VBOX_HARDDISK_VBc245e63c-5fc4ab9a

[root@node1 dev]# vi /etc/udev/rules.d/99-oracle-asmdevices.rules
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VBc245e63c-5fc4ab9a", NAME="test-disk1", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VBec9d247a-6a947164", NAME="test-disk2", OWNER="oracle", GROUP="dba", MODE="0660"

[/pcsh]

Dəyişikliklər olunmuş partionları yükləyirik:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 dev]# /sbin/partprobe /dev/sdh1
[root@node1 dev]# /sbin/partprobe /dev/sdi1

[/pcsh]

 UDEV qaydalarını yenidən yükləyib start edirik

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 dev]# /sbin/udevadm control --reload-rules
[root@node1 dev]# /sbin/start_udev
Starting udev: [ OK ]

[/pcsh]

Daha sonra SYSASM adından login olub aşağıdakı əmri icra etmək asm diskstringlərimiz barədə məlumatı ala bilərik:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@node1 Desktop]$ sqlplus / as sysasm
SQL> show parameter asm_diskstring

NAME TYPE VALUE
---------------------- ----------- ------------------------------
asm_diskstring string /dev/asm*, /dev/ocr*

[/pcsh]

Daha sonra aşağıdakı əmri icra etməklə asm_disktring-ə  yeni disklərimizin yolunu əlavə edirik

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> alter system set asm_diskstring='/dev/asm*','/dev/ocr*','/dev/test*' scope=both;

System altered.

SQ> show parameter asm_diskstring

NAME TYPE VALUE
------------------------ ----------- ------------------------------
asm_diskstring string /dev/asm*, /dev/ocr*, /dev/test*

[/pcsh]

Oracle userinin adından girib Grid eviroment dəyişənlərini təyin ediririk və asmca komandasını işlədirik. Aşağıdakı pəncərə açılır və bu alətin vasitəsilə aşağıdakı ardıcıllıqla diskqrup yaradırıq:
asmca
Create düyməsini vururuq açılan pəncərədən yaratdığımız diskləri, redundancy növünü seçirik, disk qrupumuza ad verib, yeni diskqrupu yaradırıq:
cdq
OCRCHECK komandasını işlətməklə OCR faylının vəziyyəti barədə lazımi məlumatları alırıq:
ocrcheck
OCR faylımız üçün mirror diskgroup əlavə etmək üçün aşağıdakı komandanı icra edirik

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 ~]# ocrconfig -add +TESTDG

[/pcsh]

Daha sonra bir daha ocrcheck komandasını işlətməklə mirorumuzun əlavə olunduğunu görürük. Bu şəkildə 2 mirror var, ümumilikdə 5 mirorr disk qrupu əlavə etmək olar.
chchk2
Hər hansı OCR mirroru silmək üçün aşağıdakı komandanı işlədirik

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 ~]# ocrconfig -delete +TESTDG

[/pcsh]

ocrcheck komandasını icra etməklə biz mirrorun silindiyinə əmin ola bilərik

rmdg

 OCR və ya OCRMIRROR-u dəyişək üçün aşağıdakı komanda icra olunur

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 ~]# ocrconfig -replace +DGOCRMIRROR -replacement +TESTDG

[/pcsh]

ocrconfig -showbackup komandasi icra edərək biz ocr backuplarımıza baxa bilərik:

 1cc594b124e1b1bae5e00eedf82fa40d

 ocrconfig –manualbackup komandası vasitəsilə OCR faylımızın manual backupını ala bilərik:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 ~]# ocrconfig -manualbackup
node1 2015/01/09 12:27:52 /u01/app/11.2.0/grid/cdata/rac-scan/backup_20150109_122752.ocr
node2 2015/01/08 18:59:45 /u01/app/11.2.0/grid/cdata/rac-scan/backup_20150108_185945.ocr

[/pcsh]

First at all we create two discs on VirtualBox. For it we opening VirtualBoxManager and select one of the nodes where we need to add disc, then select  Settings->Storage->Create new disk seçirik:

Image

Select  vdi (virtual box image) for hard drive type

vdi

Select fixed size and click  next to continue
fixed
Define name, location and size of the disc, then click create button to create the disc.
path
disc1
Bu qayda ilə ikinci diskimizidə yaradırıq
d2
To make disks sharable, go to File->Virtual Media Manager 
sharable
Bu pəncərədə yuxarıda yaratdığımız diskləri bir bir seçib modify düyməsini sıxmaqla açılan pəncərədən Shareable seçməklə disklərimizi paylaşıla bilən edirik.
sh
Daha sonra 2-ci nod-a bu shared diskləri əlavə edirik storage bölməsindən əlavə edirik
Daha sonra hər iki nodumuzu start edirik
root user-nin adından aşağıdakı komandaları icra edərək təzə əlavə olunmuş disklərimizi görürük

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 ~]# cd /dev
[root@node1 dev]# ls sd*
sda  sda1  sda2   sdh  sdi

[/pcsh]

Daha sonra Fdisk əmrindən istifadə edərək sdhsdi disklərini partition-lara ayırırıq
Hər iki disk üçün aşağıdakı arcıllığı icra etmək lazımdır: 

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

fdisk/dev/sdh
n
p
1
1
Enter
w

[/pcsh]

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 dev]# fdisk /dev/sdh
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xa67a97e6.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-522, default 1): 1
Last cylinder, +cylinders or +size{K,M,G} (1-522, default 522):
Using default value 522

Command (m for help): w
The partition table has been altered!

[/pcsh]

Bütün disklər müvəffəqiyyətlə partitionlara ayrıldıqdan sonra  /dev direktoriyasına daxil olub ls komandası ilə aşağıdakı nəticəni  almalıyıq:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 dev]# ls sd*
sda sda1 sda2 sdh sdh1 sdi sdi1

[/pcsh]

Aşağıdakı əmri yeni disklərimiz üçün işlədib onlarin SCSI ID-lərini götürüb 99-oracle-asmdevices.rules faylının içinə aşağıdakı kimi yazırıq (hər iki nod-da)

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 dev]# /sbin/scsi_id -g -u -d /dev/sdi1
1ATA_VBOX_HARDDISK_VBec9d247a-6a947164
[root@node1 dev]# /sbin/scsi_id -g -u -d /dev/sdh1
1ATA_VBOX_HARDDISK_VBc245e63c-5fc4ab9a

[root@node1 dev]# vi /etc/udev/rules.d/99-oracle-asmdevices.rules
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VBc245e63c-5fc4ab9a", NAME="test-disk1", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VBec9d247a-6a947164", NAME="test-disk2", OWNER="oracle", GROUP="dba", MODE="0660"

[/pcsh]

Dəyişikliklər olunmuş partionları yükləyirik:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 dev]# /sbin/partprobe /dev/sdh1
[root@node1 dev]# /sbin/partprobe /dev/sdi1

[/pcsh]

 UDEV qaydalarını yenidən yükləyib start edirik

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 dev]# /sbin/udevadm control --reload-rules
[root@node1 dev]# /sbin/start_udev
Starting udev: [ OK ]

[/pcsh]

Daha sonra SYSASM adından login olub aşağıdakı əmri icra etmək asm diskstringlərimiz barədə məlumatı ala bilərik:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@node1 Desktop]$ sqlplus / as sysasm
SQL> show parameter asm_diskstring

NAME TYPE VALUE
---------------------- ----------- ------------------------------
asm_diskstring string /dev/asm*, /dev/ocr*

[/pcsh]

Daha sonra aşağıdakı əmri icra etməklə asm_disktring-ə  yeni disklərimizin yolunu əlavə edirik

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

SQL> alter system set asm_diskstring='/dev/asm*','/dev/ocr*','/dev/test*' scope=both;

System altered.

SQ> show parameter asm_diskstring

NAME TYPE VALUE
------------------------ ----------- ------------------------------
asm_diskstring string /dev/asm*, /dev/ocr*, /dev/test*

[/pcsh]

Oracle userinin adından girib Grid eviroment dəyişənlərini təyin ediririk və asmca komandasını işlədirik. Aşağıdakı pəncərə açılır və bu alətin vasitəsilə aşağıdakı ardıcıllıqla diskqrup yaradırıq:
asmca
Create düyməsini vururuq açılan pəncərədən yaratdığımız diskləri, redundancy növünü seçirik, disk qrupumuza ad verib, yeni diskqrupu yaradırıq:
cdq
OCRCHECK komandasını işlətməklə OCR faylının vəziyyəti barədə lazımi məlumatları alırıq:
ocrcheck
OCR faylımız üçün mirror diskgroup əlavə etmək üçün aşağıdakı komandanı icra edirik

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 ~]# ocrconfig -add +TESTDG

[/pcsh]

Daha sonra bir daha ocrcheck komandasını işlətməklə mirorumuzun əlavə olunduğunu görürük. Bu şəkildə 2 mirror var, ümumilikdə 5 mirorr disk qrupu əlavə etmək olar.
chchk2
Hər hansı OCR mirroru silmək üçün aşağıdakı komandanı işlədirik

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 ~]# ocrconfig -delete +TESTDG

[/pcsh]

ocrcheck komandasını icra etməklə biz mirrorun silindiyinə əmin ola bilərik

rmdg

 OCR və ya OCRMIRROR-u dəyişək üçün aşağıdakı komanda icra olunur

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 ~]# ocrconfig -replace +DGOCRMIRROR -replacement +TESTDG

[/pcsh]

ocrconfig -showbackup komandasi icra edərək biz ocr backuplarımıza baxa bilərik:

 1cc594b124e1b1bae5e00eedf82fa40d

 ocrconfig –manualbackup komandası vasitəsilə OCR faylımızın manual backupını ala bilərik:

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[root@node1 ~]# ocrconfig -manualbackup
node1 2015/01/09 12:27:52 /u01/app/11.2.0/grid/cdata/rac-scan/backup_20150109_122752.ocr
node2 2015/01/08 18:59:45 /u01/app/11.2.0/grid/cdata/rac-scan/backup_20150108_185945.ocr

[/pcsh]

OCR üçün mirror diskgroup-un yaradılması, OCR backupın alınması


İlk olaraq Virtualbox-da 2 ədəd disk yaradırıq. Bunun üçün VirtualBoxManager-i açıb diski əlavə edəcəyimiz nod-lardan birini seçirik, daha sonra Settings->Storage->Create new disk seçirik:

Image

 

Continue reading “OCR üçün mirror diskgroup-un yaradılması, OCR backupın alınması”

Creating automated Full and Incremental backups with RMAN


1)Scriptlərimizi saxlamaq üçün scripts qovluğunu yaradırıq

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

cd /home/oracle/
mkdir Scripts

[/pcsh]

2)Daha sonra həmin qovluğa daxil olub rman_full.sh faylını yazırıq və aşağıdakı FULL backup scripini fayla yazırıq.

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

vi rman_full.sh
ORACLE_SID=DB11G
ORACLE_HOME=/u01/app/oracle/product/11.2/db_1
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH
$ORACLE_HOME/bin/./rman target sys/db11g@$ORACLE_SID log /tmp/rman.log <<EOF
set encryption on identified by 'db11g_rman' only;
run
{
backup incremental level 0 database format '/home/oracle/backup/rman/FULL_backup_OF_%d_ON_%D_%M_%Y_%s_%t.bkp' plus archivelog format '/home/oracle/backup/rman/ARC_%D_%M_%Y_%s_%t.bkp';
}
delete obsolete;
Y
EOF

[/pcsh]

4) Daha sonra inremental RMAN backup almaq üçün scripts papkasina girib fayl yaradırıq və aşağıdakı scripti ona yazırıq

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@valehnote scripts]$ vi daily_rman_inc.sh
 
ORACLE_SID=DB11G
ORACLE_HOME=/u01/app/oracle/product/11.2/db_1
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH
$ORACLE_HOME/bin/./rman target sys/db11g@$ORACLE_SID log /tmp/rman.log <<EOF
set encryption on identified by 'db11g_rman' only;
run
{
backup incremental level 1 database format '/home/oracle/backup/rman/INC_backup_OF_%d_ON_%D_%M_%Y_%s_%t.bkp' plus archivelog format '/home/oracle/backup/rman/ARC_%D_%M_%Y_%s_%t.bkp';
}
delete obsolete;
Y
EOF

[/pcsh]1) We are creating  scripts directory for storing our scripts

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

cd /home/oracle/
mkdir Scripts

[/pcsh]

2) To take full RMAN backups we create rman_full.sh file. Then write full backup script above to this file .

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

vi rman_full.sh
ORACLE_SID=DB11G
ORACLE_HOME=/u01/app/oracle/product/11.2/db_1
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH
$ORACLE_HOME/bin/./rman target sys/db11g@$ORACLE_SID log /tmp/rman.log <<EOF
set encryption on identified by 'db11g_rman' only;
run
{
backup incremental level 0 database format '/home/oracle/backup/rman/FULL_backup_OF_%d_ON_%D_%M_%Y_%s_%t.bkp' plus archivelog format '/home/oracle/backup/rman/ARC_%D_%M_%Y_%s_%t.bkp';
}
delete obsolete;
Y
EOF

[/pcsh]

4) To take incremental backups we creating this script in scripts directory

[pcsh lang=”bash” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@valehnote scripts]$ vi daily_rman_inc.sh
 
ORACLE_SID=DB11G
ORACLE_HOME=/u01/app/oracle/product/11.2/db_1
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH
$ORACLE_HOME/bin/./rman target sys/db11g@$ORACLE_SID log /tmp/rman.log <<EOF
set encryption on identified by 'db11g_rman' only;
run
{
backup incremental level 1 database format '/home/oracle/backup/rman/INC_backup_OF_%d_ON_%D_%M_%Y_%s_%t.bkp' plus archivelog format '/home/oracle/backup/rman/ARC_%D_%M_%Y_%s_%t.bkp';
}
delete obsolete;
Y
EOF

[/pcsh]