PostgreSQL Hot Backup Script

Bu yazıda postgreSQL’in bash scriptleri ile backup alınmasını paylaşacağım. Bu backup yöntemi sayesinde veritabanınızı kapatmaya gerek kalmadan backup alabiliriniz. Bu işlemi yaparken veritabanınızın archivelog modda olduğu varsayılmaktadır.

Postgresql’in kurulu olduğu dizin yapısı şu şekildedir.

/
postgresql
--10 
----archives
----data 
------base
------pg_wal
------logs
------....
------....
------....
--backup

/postgresql/data dizini postgresql’in base dizinidir. Burdaki dosyaları tar’layıp /postgresql/backup dizinine atacağız. Bunu yapan bash scripti aşağıdadır. Bu scriptin postgres kullanıcısı ile çalıştırılması gerekmektedir.

datetoday=$(date '+%Y-%m-%d')

psql -c "SELECT pg_start_backup('hotbackup start');"

cd /postgres/10/

tar -cf /postgres/backup/PGDBBACKUP_$datetoday.tar data/

psql -c "select pg_stop_backup();"

tar -cf /postgres/backup/PGDBBACKUPARC_$datetoday.tar archives/

find /postgres/backup/ -name '*.tar' -mtime +10 -exec rm {} \;
 
cd ./archives/

for i in $(find . -name "*.backup" -mtime +3); do
   echo "$i"
   walname=$(echo $i | cut -c3-42)
   /usr/pgsql-10/bin/pg_archivecleanup -d /postgres/10/archives "$walname"
done 

Depends: apache2-bin (= 2.4.25-3+deb9u7) but 2.4.10-10+deb8u14 is to be installed

Debian sistemde dist-upgrade yapıldığı sırada mevcut sistem üzerinde kurulu olan paketlerin remove edilme ihtimali bulunmaktadır. (Genel olarak tüm linux sistemlerde bu vardır)

apt dist-upgrade komutunu çalıştırıldığında remove edilecek paketlerin listesi size sunulacaktır. Remove edilecek paketlerin çalışan sisteminizi etkilemeyeceğinden emin olun aksi halde geri dönmek zor olur ve veri kaybı yaşayabilirsiniz.

dist-upgrade işlemini bilinçsizce yapması sonucu bu tür durumlara düşmemek için felaket senaryoları hazırlamak şart.

# apt dist-upgrade

 Reading package lists… Done
 Building dependency tree       
 Reading state information… Done
 Calculating upgrade… The following packages were automatically installed and are no longer required:
   ...
 Use 'apt-get autoremove' to remove them.
 Done
 The following packages will be REMOVED:
   apache2 mariadb-client-10.0 mariadb-server-10.0

İşlem sonrası mysql’e erişim sırasında karşılaşılan hata:

# mysql -u root -p
 Enter password: 
 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")
 r

# service mysqld start
 Failed to start mysqld.service: Unit mysqld.service failed to load: No such file or directory.

# systemctl
UNIT             LOAD   ACTIVE SUB     DESCRIPTION
apache2.service  loaded failed failed  LSB: Apache2 web server
mysql.service    loaded failed failed  LSB: Start and stop the mysql database server daemon

# systemctl start mysql.service
 Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details

Aslında sistem üzerinden apache ve mysql servisi var gibi görünüyor. Başlatmaya çalıştığınızda aşağıdaki hatalarla karşılaşıyorsunuz.

# systemctl status mysql.service
 ● mysql.service - LSB: Start and stop the mysql database server daemon
    Loaded: loaded (/etc/init.d/mysql)
    Active: failed (Result: exit-code) since Sat 2019-06-01 22:28:49 +03; 6s ago
   Process: 1777 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)
Jun 01 22:28:19 /etc/init.d/mysql[1802]: /etc/init.d/mysql: line 110: /usr/bin/mysqld_safe: No such file or directory
 Jun 01 22:28:49 /etc/init.d/mysql[2085]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
 Jun 01 22:28:49 /etc/init.d/mysql[2085]: /etc/init.d/mysql: line 75: /usr/bin/mysqladmin: No such file or directory
 Jun 01 22:28:49 /etc/init.d/mysql[2085]: 
 Jun 01 22:28:49 mysql[1777]: Starting MariaDB database server: mysqld . . . . . . . failed!

Apache ve Mysql gibi uygulamaları apt gibi paket yöneticisi ile silmeniz durumda datalarınızın olduğu dizinler genelde sistemden silinmez. Tekrar apache ve mysql yüklenmesi durumunda muhtelemen aşağıdaki hatayı alacaksınız.

# apt install mariadb-server

Reading package lists… Done
 Building dependency tree       
 Reading state information… Done
 Some packages could not be installed. This may mean that you have
 requested an impossible situation or if you are using the unstable
 distribution that some required packages have not yet been created
 or been moved out of Incoming.
 The following information may help to resolve the situation:
 The following packages have unmet dependencies:
  mariadb-server : Depends: mariadb-server-10.1 (>= 10.1.37-0+deb9u1) but it is not going to be installed
 E: Unable to correct problems, you have held broken packages.

# apt install apache2
 Reading package lists… Done
 Building dependency tree       
 Reading state information… Done
 Some packages could not be installed. This may mean that you have
 requested an impossible situation or if you are using the unstable
 distribution that some required packages have not yet been created
 or been moved out of Incoming.
 The following information may help to resolve the situation:
 The following packages have unmet dependencies:
  apache2 : Depends: apache2-bin (= 2.4.25-3+deb9u7) but 2.4.10-10+deb8u14 is to be installed
E: Unable to correct problems, you have held broken packages.

# apt install mariadb-server-10.0
 Reading package lists… Done
 Building dependency tree       
 Reading state information… Done
 Some packages could not be installed. This may mean that you have
 requested an impossible situation or if you are using the unstable
 distribution that some required packages have not yet been created
 or been moved out of Incoming.
 The following information may help to resolve the situation:
 The following packages have unmet dependencies:
  mariadb-server-10.0 : Depends: libdbi-perl but it is not going to be installed
                        Depends: mariadb-client-10.0 (>= 10.0.38-0+deb8u1) but it is not going to be installed
                        Depends: mariadb-server-core-10.0 (>= 10.0.38-0+deb8u1) but it is not going to be installed
                        PreDepends: mariadb-common but it is not going to be installed
 E: Unable to correct problems, you have held broken packages.

Bu hatayı almanızın nedeni mevcut repositorinizde bulunan uygulama sürümünün sisteminiz tarafından desteklenmediğinden kaynaklanmaktadır. Öncelikle Ağache için /etc/apache2 dizinini yedeklemelisiniz. Aynı şekilde Mysql ve diğer uygulamalarınız data dizinleri için de geçerli. Yedeklerin sağlam olduğundan emin olduktan sonra distribütörünüzün web sitesinden güncel repository’i sisteminize tanıtmalısınız. Bu linkten debian için uygun repository’i bulabilirsiniz. Repository’i sisteme tanıttıktan sonra Apache ve Mysql’i yükleyebilirsiniz.

Çözüm için aşağıdaki repo adreslerini /etc/apt/sources.list dosyası içerisine yazdım

# vi /etc/apt/sources.list

deb http://security.debian.org/debian-security stretch/updates main contrib non-free
deb http://ftp.de.debian.org/debian stretch main
   
# mv /etc/apache2 /etc/apache2.bck
# mv /etc/php /etc/php.bck
# cp -r /var/lib/mysql /var/lib/mysql.bck

# apt-get clean
# apt-get --purge remove apache2-bin 
Reading package lists… Done
 Building dependency tree       
 Reading state information… Done
 The following packages will be REMOVED:
   apache2-bin*
 0 upgraded, 0 newly installed, 1 to remove and 27 not upgraded.
 After this operation, 0 B of additional disk space will be used.
 Do you want to continue? [Y/n] y

# apt install apache2 mariadb-server-10.0
# systemctl status mysql
 ● mysql.service - LSB: Start and stop the mysql database server daemon
    Loaded: loaded (/etc/init.d/mysql)
    Active: active (running) since Sat 2019-06-01 23:28:14 +03; 2s ago
   Process: 7806 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
   Process: 7841 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
    CGroup: /system.slice/mysql.service
            ├─7865 /bin/bash /usr/bin/mysqld_safe
            ├─7932 /usr/sbin/mysqld --basedir=/usr --datadir=... --plugin-dir=/usr/lib/mysql/plugin --user=mysql --skip-log-error --pid-file=
            └─7933 logger -t mysqld -p daemon error
 Jun 01 23:28:13 mysqld[7933]: 190601 23:28:13 [Note] InnoDB: Waiting for purge to start
 Jun 01 23:28:13 mysqld[7933]: 190601 23:28:13 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.42-84.2 started; log sequ…310291801
 Jun 01 23:28:13  mysqld[7933]: 190601 23:28:13 [Note] Plugin 'FEEDBACK' is disabled.
 Jun 01 23:28:13 mysqld[7933]: 190601 23:28:13 [Note] Server socket created on IP: '::'.
 Jun 01 23:28:13  mysqld[7933]: 190601 23:28:13 [Note] /usr/sbin/mysqld: ready for connections.
 Jun 01 23:28:13 mysqld[7933]: Version: '10.0.38-MariaDB-0+deb8u1'  socket: '/var/run/mysqld/mysqld.sock'  (Debian)

Apache’i yükledikten sonra php modülünün aktif edilmesi gerekebilir. Bunun için aşağıdaki adımları izlemalisiniz. Bu işlemi yaptığımda php modülünü aktif edemedim ve mevcut sistemde bulunan dosları kopyalayarak çözdüm.

# a2enmod php7.3

Bu işe yaramayınca aşağıdaki şekilde çözdüm.

# cp /etc/apache2.bck/mod-available/php* /etc/apache2.bck/mod-available/
# a2enmod php7

Yeni yüklenen apache.conf ve php.ini dosyaları arasında herhangi bir fark var mı diye aşağıdaki şekilde kontrol edebilirsiniz.

# diff /etc/apache2.old/apache2.conf /etc/apache2/apache2.conf 

# diff /etc/php.old/7.3/cli/php.ini /etc/php/7.3/cli/php.ini 

Apache nin tekrar yklenmesinden kaynaklı mevcut siteleriniz de silinmiş lacaktır. Sistemde bulunan siteler aşağıdaki şekilde aktif edilebilir.

# cp /etc/apache2.bck/sites-available/*.conf /etc/apache2/sites-available/

# a2ensite hostname.conf
# a2ensite hostname2.conf
# a2ensite hostname3.conf

# service apache2 reload

Goldengate Drop Extract Capture Process

Goldengate capture processlerinin tümü aşağıdaki şekilde drop edilebilir.

BEGIN
     FOR i IN (SELECT capture_name FROM dba_capture)
     LOOP
         DBMS_CAPTURE_ADM.STOP_CAPTURE(i.capture_name, true);
         DBMS_CAPTURE_ADM.DROP_CAPTURE (i.capture_name, true);
     END LOOP;
 END;
 /

Manuel olarak bazı process’leri drop etmek için aşağıdaki adımları uygulayabilirsiniz.

$ sqlplus / as sysdba
SQL> select capture_name from dba_capture;
Result:
CAPTURE_NAME
-------------
GGS$CAP_DSSSDDSS

SQL> exec DBMS_CAPTURE_ADM.STOP_CAPTURE('GGS$CAP_DSSSDDSS', true);

SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE('GGS$CAP_DSSSDDSS', true);

DROP_CAPTURE komutunu çalıştırmadan önce process’in active olması ihtimaline karşı STOP_CAPTURE komutunun çalıştırılması gerekmektedir. Aksi halde aşağıdaki gibi hata verecektir.

ORA-01338 Other process is attached to Logminer session
ORA-06512 at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 238
ORA-06512 at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 225
ORA-06512 at "SYS.DBMS_CAPTURE_ADM", line 268

Goldengate içerisinden extract process’lerini drop etmek için aşağıdaki adımları uygulayabilirsiniz.

GGSCI> dblogin userid gguser password xxxx
GGSCI> stop extract_process_name
GGSCI> unregister extract extract_process_name
GGSCI> delete extract_process_name

Goldengate ile Replike Edilen Tabloların Db İçerisinden Listelenmesi

Oracle goldengate ile source ve target dbler arasında tablo replikasyonu yapıldığında hangi tablonun replike edildiği bilgisini dirprm dizini içerisinden aramak gerekmektedir. Bu zahmetten kurtulmak için bash scripti ile parametre dosyalarını her gece okuyup db içerisine anlaşılır bir şekilde kaydedebiliriz.

İşlemi tamamlandıktan sonra aşağıdaki şekilde bir yapıya sahip olacağız.

TBL_SOURCE    TBL_TARGET    DEF_FILE_NAME    MAPPING_STR
HR.EMPLOYEE HR.EMPLOYEE /gg/dirprm/rephr.prm MAP HR.EMPLOYEE, TARGET HR.EMPLOYEE, colmap( usedefaults, systime = @GETENV (GGHEADER, COMMITTIMESTAMP));

Goldengate scheması içerisinde aşağıdaki tabloyu oluşturduktan sonra sh scriptini linux üzerinde çalıştırıp tablonun içerisini kontrol edebilirsiniz. Kullandığınız goldengate schemasına göre scripti düzenleyebilirsiniz. Bu örnekte “ggowner” scheması kullanılmıştır.

CREATE TABLE GGOWNER.REPLICATED_TABLES
(
TBL_SOURCE VARCHAR2(120 BYTE),
TBL_TARGET VARCHAR2(120 BYTE),
DEF_FILE_NAME VARCHAR2(120 BYTE),
MAPPING_STR VARCHAR2(4000 BYTE)
)
TABLESPACE TBS_GG;

Aşağıdaki bash scripti /home/oracle/ veya kendi belirleyeceğiniz bir dizin altına yükleyebilirsiniz.

$ cat ./gg_replicated_tables.sh

#!/bin/bash

/tmp/ggrepliketbl.list
/tmp/ggrepliketbl.insert
/tmp/ggrepliketbl.listnew

echo "truncate table ggowner.replicated_tables; " > /tmp/ggrepliketbl.insert

echo "/" >> /tmp/ggrepliketbl.insert

for fname in /gg/dirprm/*.prm;
do
while read LINE
do
echo | awk '{ print substr("'"$fname $LINE"'",1,900)}' >> /tmp/ggrepliketbl.list
done < $fname
done

search_string="'"
replace_string="__"
sed "s/$search_string/$replace_string/g" /tmp/ggrepliketbl.list > /tmp/ggrepliketbl.listnew

search_string=".prm"
replace_string=".prm','"

sed "s/$search_string/$replace_string/g" /tmp/ggrepliketbl.listnew >/tmp/ggrepliketbl.list

while read LINE
do
echo "insert into ggowner.replicated_tables (DEF_FILE_NAME,MAPPING_STR) values ('$LINE'); " >> /tmp/ggrepliketbl.insert
done < /tmp/ggrepliketbl.list

echo "commit;" >> /tmp/ggrepliketbl.insert
echo "exit;" >> /tmp/ggrepliketbl.insert

sqlplus / as sysdba @/tmp/ggrepliketbl.insert
sqlplus / as sysdba <<EOF
update ggowner.replicated_tables set tbl_source = trim(replace(REGEXP_SUBSTR(replace(replace(replace(MAPPING_STR,' ',' '),' ,',','),' ,', ','), 'MAP (\S),'), 'MAP', '')), tbl_target = trim(replace(REGEXP_SUBSTR(replace(replace(replace(MAPPING_STR,' ',' '),' ,',','),', ', ','), ',TARGET (\S),'), 'TARGET', ''));
update ggowner.replicated_tables set tbl_source = trim(replace(tbl_source,',','')),
tbl_target = trim(replace(tbl_target,',',' '));

delete from ggowner.replicated_tables where tbl_source is null and tbl_target is null and MAPPING_STR not like '%MAP%';

commit;
exit;
EOF

rm /tmp/ggrepliketbl.list
rm /tmp/ggrepliketbl.insert
rm /tmp/ggrepliketbl.listnew

Eğer goldengate replikasyonu yapılan tabloların listesini her gece güncellemek istiyorsanız ilgili scripti crontab’a ekleyebilirsiniz. Scripti crontab içerisinden çalıştıracaksanız oracle_sid ve oracle_home değişkenlerinin set edildiğinden mutlaka emin olun.

PostgreSQL Tablo Yetki Problemi – ERROR: permission denied for schema “schema_name”

Postgresql de kullanıcıların owneri olmadıkları tablolara select çekebilmesi için verilen “grant select on tbl_name to user” tek başına yeterli olmamaktadır. Bu yetki tanımlandığı halde aşağıdaki hatayı alıyorsanız çözümü bu yazıda bulabilirsiniz.,

ERROR:  permission denied for schema "schema_name"
SQL state: 42501

Bu örnekte db create edip farklı kullanıcılardan o db de bulunan tablolara select yetkisi tanımlama işlemi yapacağım.

Aşağıdaki gibi db create edilmiştir. Bu db’nin owneri postgres (Postgres default ve admin yetkisine sahip user’dir) kullanıcısıdır.

CREATE DATABASE "TESTDB"
WITH
OWNER = postgres
ENCODING = 'UTF8'
CONNECTION LIMIT = -1;

TESTDB create edildikten sonra db içerisinde default olarak public scheması gelecektir. Bu schemanın owneri postgres olacaktır. “emrah” kullanıcısına ait yeni bir schema oluşturup içerisine tablo oluşturacağım.

CREATE SCHEMA newschema
AUTHORIZATION emrah;

COMMENT ON SCHEMA newschema
IS 'this schema depend to emrah';

-- emrah kullanıcısından login olup tablo create edilmiştir.
CREATE TABLE newschema.tbl1 (
col1 character(100),
col2 character(100)
);
ALTER TABLE newschema.tbl1
OWNER to emrah;

Emrah kullanıcısından tabloya Taner kullanıcısı için select yetkisi tanımlama işlemini şağıdaki gibi yapıyorum

-- Yetki emrah kullanıcısından login olunarak tanımlanmıştır.
GRANT SELECT ON TABLE newschema.tbl1 TO taner;

Taner kullanıcısı ile login olup select çektiğimde aşağıdaki hata ile karşılaşıyorum.

SQL> SELECT * FROM newschema.tbl1;

ERROR: permission denied for schema newschema
LINE 1: SELECT * FROM newschema.tbl1
^
SQL state: 42501
Character: 15

Postgrenin bu hatayı vermesinin nedeni create edilmiş schema (newschema) üzerinde Taner kullanıcısının usage yetkisinin bulunmamasından kaynaklanmaktadır. Yetki aşağıdaki şekilde tanımlanmalıdır.

GRANT USAGE ON SCHEMA newschema TO taner;

Eğer ilgili schema içerisinde Taner kullanıcısına table create yetkisi de tanımlamak istiyorsanız aşağıdaki yetkiyi de beraberinde verebilirsiniz. Bu yetki tanımlandığında ilgili schema içerisinde; kullanıcı table create ve owneri olduğu tablolara drop/alter yetkisinde sahip olacaktır.

GRANT CREATE ON SCHEMA newschema TO taner;

Procedure İçerisinden Dictionary Viewlere Erişim

Oracle db içerisinde procedure oluşturup scheduler job içerisinden çağırıldığı durumlarda yetki hatası ile karşılaşılabilir. (Eğer procedure içerisinden v$session gibi dictionary viewlere select çekiliyorsa)

Procedure içerisinden dictionary objelere ulaşmak istiyorsanız aşağıdaki yetkinin ilgili kullanıcıya tanımlanması gerekmektedir.

Kullanıcı DBA rolüne sahip olsa bile bu yetkilerin verilmesi gerekmektedir.

-- Tüm dictionary viewlere erişim yetkisi için
SQL> grant select any dictionary to dbuser;

-- Aşağıdaki şekilde de tanımlayabiliriz
SQL> grant select on v_$session to dbuser;


SQL> grant select_catalog_role to dbuser;

Oracle Snapshot Standby Oluşturma

Oracle’nin flashback teknolojisi sayesinde pyhsical standby olarak ayarlanmış db lerin read/write modda açmak mümkün. Bilindiği üzere pyhsical standby db leri en fazla read only modda açıp sadece select komutlarını çalıştırabilir konuma getirebiliriz. Bu durumda varolan data üzerinde herhangi bir değişikliğe izin verilmeyecektir.

Snapshot standby olarak ayarlanan bir physical standby db; read/write moda geçerek production makinesinin o andaki verileri üzerinde istenilen tüm islemleri yapmayı mümkün kılacaktır.

Örneğin t anında snyc olan bir pyhsical standby dbsi snapshot standby durumuna alındığında t1 anında production makinesinde yapılan bir değişiklik standby tarafında görünmeyecektir.

Önemli Not 1: Snapshot standby moduna alınacak db nin flashback özelliğinin açık olması gerekmektedir.

Önemli Not 2: Production db üzerinde snapshot özelliğini açmadan önce mutlaka db nin kapatılıp tekrar açılması gerekmektedir. Aksi taktirde oracle data tutarlılığı konusunda garanti vermemektedir.

Önemli Not 3: Bu özellik sadece Oracle Database Enterprise Edition sürümlerinde desteklenmektedir.

Önemlii Not 4: Flashback dosyalarının nereye oluşturulacağı kontrolü ve belirtilen dizinin doluluk oranının db_recovery_file_dest_size parametresinden düşük olduğundan mutlaka emin olun.

Pyhsical standby db nin flashback özelliği açık olmayabilir. Snapshot moduna alınacak db nin flashback özelliğinin açılmasının gözden kaçmaması adına aşağıdaki komutlara eklenmiştir.

SQL> select open_mode,database_role,flashback_on from v$database;

Result:
open_mode database_role flashback_on
MOUNT PHYSICAL STANDBY NO

Pyhsical standby dan snapshot standy moduna almak için aşamalar aşağıdaki şekildedir. Aşamaları sırayla uygulayarak pyhsical standby db yi read/write moda alabilirsiniz.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
SQL> ALTER DATABASE OPEN;

Aşamalar sırasıyla yapıldığı taktirde db read/write modda açılacaktır.

SQL> select open_mode,database_role,flashback_on from v$database;

Result:
open_mode database_role flashback_on
READ/WRITE SNAPSHOT STANDBY YES

Snapshot standby olarak ayarlanmış bir db nin tekrar physical standby’a çevirilmesi durumunda aşağıdaki adımları takip edebilirsiniz.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; 
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SQL> select open_mode,database_role,flashback_on from v$database;

Result:
open_mode database_role flashback_on
MOUNT PHYSICAL STANDBY NO

Tablespace İçerisindeki Objelerin Taşınması

Tablespace içerisinden büyük objelerin silinmesi durumunda disk üzerinde alan kazanımı olmadığı için tablespace’i recreate etme yoluna gidebiliriz. Bu durumda diskten alan kazanmanın yanında tablespace içerisinde bulunan objeler defragmente olup db üzerinde performans artışlarını beraberinde getirecektir. Tablespaceyi recreate etmenin aşamaları şu şekildedir:

  1. Yeni tablespace create etmek (bundan sonra TS_DATA_NEW olarak adlandırılacaktır)
  2. default_tablespace’i TS_DATA olan schemaların alter edilmesi ve gerekli quota ayarlarının yapılması.
  3. Varolan tablespace (TS_DATA olarak adlandırılacaktır) içerisindeki tüm objelerin move edilmesi.
    1. Tablo partition ve subpartitionların taşınması
    2. Lobların taşınması
    3. Lob partitionların taşınması
    4. İndexlerin taşınması
    5. Partitionlu indexlerin taşınması
  4. TS_DATA’yı offline konumuna almak
  5. Gerekli olması durumunda modify default attributes yapmak
  6. TS_DATA’yı drop etmek
  7. TS_DATA_NEW’i rename edip TS_DATA olarak değiştirmek.
  8. İndexlerin kontrolü

1. Create Tablespace

Yeni oluşturulacak TS_DATA_NEW tablespace için örnek komut aşağıdaki gibidir. Tabiki varolan tablespace üzerinde tanımlanan kota, yetki v.s gibi yetkilerin de varolan tablespace den alınıp yeni oluşturulan tablespaceye tanımlanması gerekmektedir. Aksi halde objeler taşındıktan sonra yetki problemleri nedeniyle başka sorunlara sebebiyet verebiliriz.

CREATE TABLESPACE TS_DATA_NEW 
DATAFILE '+DATA'
SIZE 31G;

2. Default Tablespace Değiştirmek

select username,default_tablespace,'alter user '||username||' default tablespace TS_DATA_NEW;' 
from dba_users
where default_tablespace='TS_DATA';

3.1. Tablo Partitionların Taşınması

Tablo partition ve sub partitionların taşınması için örnek komutaşağıdaki şekildedir. Burada klasik yöntem ile taşınacaktır. Sistemin kesintiye uğramaması için redef gibi taşıma yöntemlerini de tercih edebilirsiniz.

select 'ALTER TABLE '||table_owner||'.'||table_name||'  MOVE PARTITION '||partition_name||' TABLESPACE TS_DATA_NEW parallel 8;'
from dba_tab_partitions
where tablespace_name = 'TS_DATA';

Subpartitionların taşınması aşağıdaki şekilde gerçekleştirilebilir.

select 'ALTER TABLE '||table_owner||'.'||table_name||'  MOVE SUBPARTITION '||subpartition_name||' TABLESPACE TS_DATA_NEW parallel 8;' 
from dba_tab_subpartitions
where tablespace_name = 'TS_DATA';

3.2. Lob ve Lobparitionların Taşınması

Loblar aşağıdaki şekilde taşınabilir.

select owner,table_name,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME, 'alter table '||owner||'.'||table_name||' move lob ('||COLUMN_NAME||') store as '||SEGMENT_NAME||' (tablespace TS_DATA_NEW);' 
from dba_lobs
where tablespace_name ='TS_DATA';

Lob partitionlar aşağıdaki şekilde taşınabilir.

SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as  (tablespace TS_DATA_NEW);' 
FROM DBA_LOB_PARTITIONS
WHERE TABLESPACE_NAME = 'TS_DATA' ;

3.3. İndexlerin Taşınması

İndexler aşağıdaki şekilde taşınabilir.

select distinct 'ALTER INDEX '||owner||'.'||index_name||' REBUILD ONLINE TABLESPACE TS_DATA_NEW PARALLEL 16; ALTER INDEX '||owner||'.'||index_name||' NOPARALLEL;' 
from dba_indexes
where tablespace_name = 'TS_DATA';

İndex partitionları aşağıdaki şekilde taşınabilir.

select 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||partition_name||' TABLESPACE TS_DATA_NEW  
ONLINE PARALLEL 16; ALTER INDEX '||index_owner||'.'||index_name||' NOPARALLEL;'
from dba_ind_partitions
where tablespace_name = 'TS_DATA';

4. Tablespace’i Offline durumuna getirmek

Aşağıdaki şekilde TS_DATA’yı offline konuma getirebiliriz. Burada normal şeçeneğini kullanmak mantıklı olabilir. Çünkü tablespace içerisinde unutulan bir obje olduğu durumlarda uyarı verecektir.

ALTER TABLESPACE TS_DATA OFFLINE NORMAL/IMMEDIATE;

5. Modify Default Attribute

Varolan TS_DATA tablespace’i drop ettikten sonra dba_part_tables ve dba_part_indexes viewlerinden taşınan objelerin ownerleri altında bulunan objelerin kontrolünün yapılması gerekmektedir. Tablespace drop edildikten sonra bu viewlerin içerisinde bulunan DEF_TABLESPACE_NAME kolonunda bulunan değerlerin bazınlar veya bir çoğu “_$deleted$25$3” gibi bir değere dönebilir.

Bunun bu şekilde değişmesinin nedeni partitionlu tablo ve indexlerin move edilmesi sonucunda metadata diyebileceğimiz adresin değişmemesinden kaynaklanmaktadır. Buradan bu kayıtları bulup aşağıdaki şekilde alter edilmesi gerekmektedir. Aksi halde insert gibi transactionlarda “ORA-00959: tablespace ‘_$deleted$25$3’ does not exist” gibi hatalar alabilirsiniz.

alter index SCHEMA.INDEX_NAME modify default attributes tablespace TBS_EXISTING;

alter table SCHEMA.TABLE_NAME modify default attributes tablespace TBS_EXISTING;

6. Drop Tablespace

Varolan tablespace aşağıdaki şekilde drop edilebilir.

DROP TABLESPACE TS_DATA INCLUDING CONTENTS AND DATAFILES;

7. Rename Tablespace

Varolan tablespace aşağıdaki şekilde rename edilebilir. Oracle’nin rename tablespace komutunu 11g ve üzeri sürümlerde desteklediğini unutmamak gerekir.

ALTER TABLESPACE TS_DATA_NEW RENAME TO TS_DATA;

Bunun çalıştırılmasının ardından 2. aşamada değiştirdiğimiz default tablespace değeri de otomatik değişecektir. Yine de her ihtimale karşı dba_users view’inden kontrol edilmesi daha sağlıklı olacaktır.

8. İndexlerin Kontrolü

Son olarak indexlerin kontrolünü yapmak gerekiyor. Aşağıdaki şekilde yapılabilir.

select  * from dba_indexes where tablespace_name = 'TS_DATA' and status <> 'USABLE'; 

select * from dba_ind_partitions where tablespace_name = 'TS_DATA' and status <> 'USABLE';

Objelerin Otomatik Taşınması

Aşağıdaki sql komutu ile tablespace içerisindeki tüm ojeleri kolayca taşıyabilirsiniz.

SELECT *
     FROM (SELECT 0 sira,
                  owner,
                  table_name,
                     'alter table ' || owner || '.' || table_name || ' modify default attributes tablespace  TBS_EXISTING ;'    move_sql
             FROM dba_part_tables
            WHERE DEF_TABLESPACE_NAME = '&&tbsname'
           UNION ALL
           SELECT 1 sira,
                  owner,
                  table_name,
                     'alter index ' || owner || '.' || index_name || ' modify default attributes tablespace  TBS_EXISTING ;'    move_sql
             FROM dba_part_indexes
            WHERE DEF_TABLESPACE_NAME = '&&tbsname'
           UNION ALL
           SELECT 2 sira,
                  owner,
                  table_name,
                     'ALTER TABLE ' || owner || '.' || table_name || '  MOVE TABLESPACE  TBS_EXISTING parallel 8;'    move_sql
             FROM dba_tables
            WHERE tablespace_name = '&&tbsname'
           UNION ALL
           SELECT 3 sira,
                  table_owner                                   owner,
                  table_name,
                     'ALTER TABLE ' || table_owner || '.' || table_name || '  MOVE PARTITION ' || partition_name || ' TABLESPACE  TBS_EXISTING parallel 8;'    move_sql
             FROM dba_tab_partitions
            WHERE tablespace_name = '&&tbsname'
           UNION ALL
           SELECT 4 sira,
                  owner,
                  table_name,
                     'alter table ' || owner || '.' || table_name || ' move lob (' || COLUMN_NAME || ') store as ' || SEGMENT_NAME || ' (tablespace  TBS_EXISTING );'    move_sql
             FROM dba_lobs
            WHERE tablespace_name = '&&tbsname'
           UNION ALL
           SELECT 5 sira,
                  owner,
                  table_name,
                     'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD TABLESPACE  TBS_EXISTING PARALLEL 16;
  ALTER INDEX ' || owner  || '.' || index_name || ' NOPARALLEL;'    move_sql
             FROM dba_indexes
            WHERE tablespace_name = '&&tbsname'
           UNION ALL
           SELECT 6 sira,
                  i.owner,
                  i.table_name,
                     'ALTER INDEX ' || p.index_owner || '.' || p.index_name || ' REBUILD PARTITION ' || p.partition_name || ' TABLESPACE  TBS_EXISTING PARALLEL 16;
  ALTER INDEX ' || p.index_owner || '.' || p.index_name || ' NOPARALLEL;'
             FROM dba_ind_partitions p, dba_indexes i
            WHERE     p.tablespace_name = '&&tbsname'
                  AND p.index_owner = i.OWNER
                  AND p.index_name = i.index_name) aa
 ORDER BY 2, 3, 1;

Not: Bu yazı zaman içerisinde tekrar güncellenecektir.

Resource Privileges in Oracle Database

If you don’t have resource privileges in oracle databases, you don’t create the following objects:

SQL> select privilege from dba_sys_privs where grantee='RESOURCE';

CREATE TABLE
CREATE TYPE
CREATE TRIGGER
CREATE CLUSTER
CREATE SEQUENCE
CREATE OPERATOR
CREATE PROCEDURE
CREATE INDEXTYPE

Also, you must have unlimited tablespace within the resource role. 

You can use the following syntax to give resource privileges:

SQL> grant resource to user;