Thứ Tư, 19 tháng 8, 2015

Add New Node to ORACLE RAC

Extend GRID Software
---Step 1:

[root@rac1]$ su - grid
[grid@rac1]$ export GRID_HOME=/u01/app/11.2.0/grid
[grid@rac1]$ $GRID_HOME/bin/cluvfy stage -post hwos -n rac2

----Step 2:
[grid@rac1]$ $GRID_HOME/bin/cluvfy comp peer -refnode rac1 -n rac2 -orainv oinstall -osdba dba -verbose

----Step 3:
Verify New Node (NEW NODE PRE)
$GRID_HOME/bin/cluvfy stage -pre nodeadd -n rac2 -fixup -verbose

----Step 4:

[grid@rac1]$ export IGNORE_PREADDNODE_CHECKS=Y
[grid@rac1]$ $GRID_HOME/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={rac2}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={rac2-vip}"

----Step 5:

[root@rac2]$ /u01/app/oraInventory/orainstRoot.sh
[root@rac2]$ /u01/app/11.2.0/grid/root.sh
[grid@rac1]$ $GRID_HOME/bin/cluvfy stage -post nodeadd -n rac2 -verbose

Extend ORACLE DATABASE SOFTWARE

----Step 1:

[oracle@rac1]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
[oracle@rac1]$ $ORACLE_HOME/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={rac2}"

----Step 2:

[root@rac2]$ /u01/app/oracle/product/11.2.0/db_1/root.sh

[oracle@rac2]$ $ORACLE_HOME/bin/cluvfy comp admprv -o db_config -d $ORACLE_HOME -n rac1,rac2 -verbose

----Step 3:Add Instance to Clustered Database
dbca -> Intance Management -> Add instance



Người đăng: vào lúc Không có nhận xét nào:

Thứ Năm, 6 tháng 8, 2015

Another way: To Create An Oracle Dataguard !

@@@@@@@@@@@@@PRIMARY@@@@@@@@@@@@@

1/ Backup RMAN full + Standby Controlfile:

RMAN> run {
2> allocate channel st1 device type disk;
3> allocate channel st2 device type disk;
4> backup as compressed backupset format '/setup/rman/%d_%U.rman' database plus archivelog;
5> backup format '/setup/rman/std_controlfile.ctl' current controlfile for standby;
6> release channel st1;
7> release channel st2;
8> }

2/ Backup spfile.ora

SQL> create pfile='/setup/rman/pfile.ora' from spfile;[backup]

3/ Set new parameters

SQL>ALTER SYSTEM SET DB_UNIQUE_NAME='EWALLET';
SQL>ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(EWALLET,EWALLET_DR)';
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/app/oracle/fast_recovery_area/EWALLET/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EWALLET';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=EWALLET_DR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EWALLET_DR';
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='EWALLET','EWALLET' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='EWALLET','EWALLET' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
ALTER SYSTEM SET FAL_SERVER='EWALLET_DR';
ALTER SYSTEM SET FAL_CLIENT='EWALLET';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET STANDBY_ARCHIVE_DEST='/home/oracle/app/oracle/fast_recovery_area/EWALLET/standbylog';
SQL> create pfile='/setup/rman/pfile_for_standby.ora' from spfile;

4/ Create standby logfile to use realtime apply log
[(maximum number of logfiles for each thread + 1) * maximum number of threads]
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
SQL> alter database add standby logfile group 4 ('/home/oracle/app/oracle/oradata/EWALLET/standby1.rdo') size 200M;
SQL> alter database add standby logfile group 5 ('/home/oracle/app/oracle/oradata/EWALLET/standby2.rdo') size 200M;
SQL> alter database add standby logfile group 6 ('/home/oracle/app/oracle/oradata/EWALLET/standby3.rdo') size 200M;
SQL> alter database add standby logfile group 7 ('/home/oracle/app/oracle/oradata/EWALLET/standby4.rdo') size 200M;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

5/ Create tnsnames by netmgr


EWALLET =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.159.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EWALLET)
    )
  )

EWALLET_DR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.159.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EWALLET_DR)
    )
  )



@@@@@@@@@@@@@DR@@@@@@@@@@@@@

6/ Copy all to DR

$scp /setup/rman/* oracle@192.168.159.21:/setup/rman

7/ set pfile_for_standby.ora:

*.db_unique_name='EWALLET_DR'
*.db_file_name_convert='EWALLET','EWALLET'
*.fal_client='EWALLET_DR'
*.fal_server='EWALLET'
*.log_archive_config='DG_CONFIG=(EWALLET,EWALLET_DR)'
*.log_archive_dest_1='LOCATION=/home/oracle/app/oracle/fast_recovery_area/EWALLET/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EWALLET_DR'
*.log_archive_dest_2='SERVICE=EWALLET_DR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EWALLET'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='EWALLET','EWALLET'
*.standby_archive_dest='/home/oracle/app/oracle/fast_recovery_area/EWALLET/standbylog'
*.standby_file_management='AUTO'

8/ Create directories follow pfile_for_standby.ora:

9/ Create tnsnames by netmgr or copy from primary

EWALLET =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.159.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EWALLET)
    )
  )

EWALLET_DR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.159.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EWALLET_DR)
    )
  )

10/ Make standby

[oracle@dg02 ~]$ export ORACLE_SID=EWALLET
[oracle@dg02 ~]$ rman target /
RMAN> startup nomount pfile='/setup/rman/pfile_for_standby.ora'
RMAN> restore standby controlfile from '/setup/rman/stb_controlfile.ctl';
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
[oracle@dg02 ~]$ export ORACLE_SID=EWALLET
[oracle@dg02 ~]$ sqlplus / as sysdba
SQL> alter system register;
SQL> !lsnrctl status
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
SQL> alter database add standby logfile group 4 ('/home/oracle/app/oracle/oradata/EWALLET/standby1.rdo') size 200M;
SQL> alter database add standby logfile group 5 ('/home/oracle/app/oracle/oradata/EWALLET/standby2.rdo') size 200M;
SQL> alter database add standby logfile group 6 ('/home/oracle/app/oracle/oradata/EWALLET/standby3.rdo') size 200M;
SQL> alter database add standby logfile group 7 ('/home/oracle/app/oracle/oradata/EWALLET/standby4.rdo') size 200M;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> create spfile='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfileEWALLET' from pfile='/setup/rman/pfile_for_standby.ora';

11/ Check standby

SQL>  select sequence#,applied,completion_time from v$archived_log;

 SEQUENCE# APPLIED   COMPLETIO
---------- --------- ---------
2 YES     16-JAN-15
12 YES     16-JAN-15
3 YES     16-JAN-15
4 YES     16-JAN-15
6 YES     16-JAN-15
5 YES     16-JAN-15
7 YES     16-JAN-15
8 YES     16-JAN-15
9 YES     16-JAN-15
10 YES     16-JAN-15
11 YES     16-JAN-15

 SEQUENCE# APPLIED   COMPLETIO
---------- --------- ---------
13 YES     16-JAN-15
14 YES     16-JAN-15
Người đăng: vào lúc Không có nhận xét nào:

RMAN: Restoring a Database from INCREMENTAL Backup


BACKUP:
Backup incremental level 0:
RMAN> run {
 allocate channel st1 device type disk;
allocate channel st2 device type disk;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/setup/rman/%F';
backup format '/setup/rman/%d_%U.rman' incremental level 0 database;
sql 'alter system archive log current';
backup format '/setup/rman/%d_archivelog_%U.rman' archivelog all not backed up 1 times;
release channel st1;
release channel st2;
}
Backup incremental level 1:
RMAN> run{  
allocate channel st1 device type disk;
allocate channel st2 device type disk;
backup format '/setup/rman/%d_%U.rman' incremental level 1 database;
sql 'alter system archive log current';
backup format '/setup/rman/%d_archivelog_%U.rman' archivelog all not backed up 1 times;
release channel st1;
release channel st2;
}

 RESTORE and RECOVER:
export ORACLE_SID=RMANINCRE
[oracle@dg01 ~]$ rman target /
RMAN> startup nomount;
RMAN> set DBID=3750471372; =<Get second string from controfile_autobackup>
RMAN> run{
set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/setup/rman/%F';
restore controlfile from autobackup;
}
RMAN> alter database mount;
RMAN> list backup of archivelog all;
RMAN> restore archivelog all; 
RMAN> run{
restore database;
recover database;
}

Getting Error:
archived log thread=1 sequence=8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/07/2015 01:05:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 8 and starting SCN of 1274659
Check sequence archivelog backed up With sequence number restored.if they is equal then OK.
RMAN> list backup of archivelog all;
RMAN> alter database open resetlogs;
Người đăng: vào lúc Không có nhận xét nào:

Chủ Nhật, 2 tháng 8, 2015

How to count number of rows in a cursor

create or replace function f_row_count_objects return number is
cursor c_var01 is select * from HieuPV;
rt_var02 c_var01%ROWTYPE;
var03 number;
begin
    open c_var01;
    if c_var01%ISOPEN then
    loop
        fetch c_var01 into rt_var02;
        exit when c_var01%NOTFOUND;
   end loop;
    end if;
    var03:= c_var01%ROWCOUNT;
    close c_var01;
    return var03;
end;
/
select f_row_count_objects() from dual;
Người đăng: vào lúc Không có nhận xét nào:

General Oracle Data on a table.

declare
i number(10);
begin
    for i in 1..1000 loop
        insert into hieupv values (i, i || ' HieuPV');
    end loop;
    commit;
end;
/
Người đăng: vào lúc Không có nhận xét nào:
Đăng ký: Nhận xét (Atom)